this is a bug,
https://bitbucket.org/zzzeek/alembic/issues/333/batch-fails-on-tables-that-have-indexes
is added. thanks for reporting!
On 10/16/15 4:20 AM, Dheeraj Gupta wrote:
> Hi,
>
> I have a single table in my sqlite database for which I am using alembic
> for migration. After the initial revision (creating table and columns),
> I needed to modify the models so that some of the columns allowed NULL.
>
> My original models.py looks like:
>
> # models.py ###########################################33
> from sqlalchemy import (BigInteger,
> Column,
> Integer,
> SmallInteger,
> String)
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.schema import MetaData
>
>
> convention = {
> "ix": 'ix_%(column_0_label)s',
> "uq": "uq_%(table_name)s_%(column_0_name)s",
> "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
> "pk": "pk_%(table_name)s"
> }
>
> mymetadata = MetaData(naming_convention=convention)
> Base = declarative_base(metadata=mymetadata)
>
>
> class MyBase(Base):
> __abstract__ = True
> __table_args__ = {'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB',
> 'sqlite_autoincrement': 'True'}
>
> class TRequest(MyBase):
> __tablename__ = "trequests"
> id_ = Column("id", BigInteger().with_variant(Integer, "sqlite"),
> primary_key=True)
> req_uuid = Column(String(32), unique=True, index=True, nullable=False)
> ip1 = Column(String(15), nullable=True)
> ip2 = Column(String(15), nullable=True)
> port1 = Column(Integer, nullable=True)
> port2 = Column(Integer, nullable=True)
> proto = Column(String(3), nullable=True)
> tstamp = Column(Integer, nullable=True)
> win = Column(SmallInteger, nullable=False)
> status = Column(SmallInteger)
> status_changed = Column(Integer)
> accessed = Column(Integer)
> data_file = Column(String(255))
> pcap_file = Column(String(255))
>
> # End File##############################################
>
> req_uuid has a uniuq index which is named 'ix_trequests_req_uuid' per
> the naming convention
>
> In my base revision script, the upgrade() function is:
> def upgrade():
> ### commands auto generated by Alembic - please adjust! ###
> op.create_table('trequests',
> sa.Column('id', sa.BigInteger().with_variant(sa.Integer, "sqlite"),
> nullable=False),
> sa.Column('req_uuid', sa.String(length=32), nullable=False),
> sa.Column('ip1', sa.String(length=15), nullable=False),
> sa.Column('ip2', sa.String(length=15), nullable=False),
> sa.Column('port1', sa.Integer(), nullable=False),
> sa.Column('port2', sa.Integer(), nullable=False),
> sa.Column('tstamp', sa.Integer(), nullable=False),
> sa.Column('proto', sa.String(length=3), nullable=False),
> sa.Column('win', sa.SmallInteger(), nullable=False),
> sa.Column('status', sa.SmallInteger(), nullable=True),
> sa.Column('accessed', sa.Integer(), nullable=True),
> sa.Column('status_changed', sa.Integer(), nullable=True),
> sa.Column('data_file', sa.String(length=15), nullable=True),
> sa.Column('pcap_file', sa.String(length=15), nullable=True),
> sa.PrimaryKeyConstraint('id', name=op.f('pk_trequests')),
> mysql_charset='utf8',
> mysql_engine='InnoDB',
> sqlite_autoincrement=True
> )
> op.create_index(op.f('ix_trequests_req_uuid'), 'trequests',
> ['req_uuid'], unique=True)
> # End ###########################
>
> Now, I am adding a new revision to remove nullable=True from some
> columns. The relevant upgrade function is
>
>
> from net_transcript.db.models import convention as naming_convention
>
> def upgrade():
> ### commands auto generated by Alembic - please adjust! ###
> with op.batch_alter_table('trequests', schema=None,
> naming_convention=naming_convention) as
> batch_op:
> batch_op.alter_column('port1',
> existing_type=sa.INTEGER(),
> nullable=True)
> batch_op.alter_column('port2',
> existing_type=sa.INTEGER(),
> nullable=True)
> batch_op.alter_column('proto',
> existing_type=sa.VARCHAR(length=3),
> nullable=True)
> ### end Alembic commands ###
>
> # End #######################################
>
> However, on running the command I get an OperationalError
> (sqlite3.OperationalError) index ix_trequests_req_uuid already exists
> [SQL: u'CREATE UNIQUE INDEX ix_trequests_req_uuid ON _alembic_batch_temp
> (req_uuid)']
>
> I looked at the source and in
> https://bitbucket.org/zzzeek/alembic/src/459549c9ab7fef91b2dc8986bc0643bb2f6ec0c8/alembic/operations/batch.py?at=master&fileviewer=file-view-default#batch.py-170
> the Index is being defined by same name. I commented this code block and
> the migration succeeded without an error.
> Is this a bug or am I doing something wrong?
>
>
> Regards,
> Dheeraj
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alembic+unsubscr...@googlegroups.com
> <mailto:sqlalchemy-alembic+unsubscr...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
