autogenerate wrongly determined a primary key integer column to autoincrement

[sqlalchemy-bot]

Migrated issue, originally created by Eric Hui (@ehui)

Hi, I use pyramid, added the following table in my models init.py file

pageview_table = schema.Table('pageview', metadata,
  schema.Column('brand_id', types.Integer, schema.ForeignKey('brand.id'), nullable=False),
  schema.Column('platform', types.Integer, primary_key=True),
  schema.Column('object_type', types.Integer, primary_key=True),
  schema.Column('object_id', types.Integer, default=0, primary_key=True),
  schema.Column('viewcount', types.Integer, default=1, nullable=False),
  mysql_engine='InnoDB'
)

ran --autogenerate and got following upgrade

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('pageview',
    sa.Column('brand_id', sa.Integer(), nullable=False),
    sa.Column('platform', sa.Integer(), nullable=False),
    sa.Column('object_type', sa.Integer(), nullable=False),
    sa.Column('object_id', sa.Integer(), nullable=False),
    sa.Column('viewcount', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['brand_id'], ['brand.id'], ),
    sa.PrimaryKeyConstraint('platform', 'object_type', 'object_id')
    )
    ### end Alembic commands ###

when I checked it out in "offline" mode (upgrade head --sql), got the following

CREATE TABLE pageview (
    brand_id INTEGER NOT NULL, 
    platform INTEGER NOT NULL AUTO_INCREMENT, 
    object_type INTEGER NOT NULL, 
    object_id INTEGER NOT NULL, 
    viewcount INTEGER NOT NULL, 
    PRIMARY KEY (platform, object_type, object_id), 
    FOREIGN KEY(brand_id) REFERENCES brand (id)
);

I would like to be able to have the option to not AUTO_INCREMENT....

---

I was able to temporarily get around using the following in upgrade

op.alter_column('pageview','platform',existing_type=sa.Integer(),server_default=False,autoincrement=False,existing_autoincrement=True)

works well except the default is 0 instead of NULL, am I misunderstanding something with server_default?

mysql> show columns from pageview;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| brand_id    | int(11) | NO   | MUL | NULL    |       |
| platform    | int(11) | NO   | PRI | 0       |       |
| object_type | int(11) | NO   | PRI | NULL    |       |
| object_id   | int(11) | NO   | PRI | NULL    |       |
| viewcount   | int(11) | NO   |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+

thanks!

[sqlalchemy-bot]

Changes by Eric Hui (@ehui):

• edited description

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

just the "autoincrement=False" directive is enough for it to not say "AUTO_INCREMENT". Ideally, you'd want to put this in the "platform" Column definition inside the "pageview_table" Table you created in your pyramid app. There's a bug which I have just fixed in 97b7227 that would prevent this from being propagated to the migrate script. If you want to stay on 0.4.0, then add "autoincrement=False" to the column definition into your updgrade() script manually.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed