Alembic - composite primary key results in incorrect table definition for MySQL

Question

I have multiple "versioned" database SQLALchemy models that use a composite primary key by combining an auto-increment int field ("id") and a datetime field ("record_valid_from"). I'm trying to run this against a MySQL database setup locally in a docker container.

The model definition looks something like this:

from sqlalchemy.orm import (DeclarativeBase, Mapped)

class classA(DeclarativeBase):
    id: Mapped[int] = mapped_column(primary_key=True, index=True, autoincrement=True)
    record_valid_from: Mapped[datetime] = mapped_column(DateTime,
        primary_key=True,
        default=get_current_timestamp # this is a python method returning datetime.now()
    )
    active: Mapped[bool] = mapped_column(Boolean, default=True,
        comment="TRUE if latest version, FALSE otherwise"
    )
    ... # some more fields and logic

The other models look similar with various different relations between them.

When auto-generating a migration script using alembic (alembic revision --autogenerate -m "init database") the resulting python code seems to be producing invalid SQL.

More specifically, I'm running into:

(pymysql.err.OperationalError) (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')

Here is the migration code (Note: I simplified it a little bit):

def upgrade() -> None:
    op.create_table('classA',
        sa.Column('name', sa.String(length=100), nullable=False),
        sa.Column('record_valid_from', sa.DateTime(), nullable=False),
        sa.Column('active', sa.Boolean(), nullable=False),
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.PrimaryKeyConstraint('record_valid_from', 'id')
    )
    op.create_index(op.f('ix_classA_id'), 'classA', ['id'], unique=False)

Anyone experienced something similar and/or knows how to fix this?

Things I tried:

• call op.create_primary_key after the table is created (see: https://alembic.sqlalchemy.org/en/latest/ops.html#alembic.operations.Operations.create_primary_key). Result: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1068, 'Multiple primary key defined')
• removing sa.PrimaryKeyConstraint and then call op.create_primary_key directly. Result:
  • migration works correctly
  • trying to create a new ORM model resulted in: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1364, "Field 'id' doesn't have a default value")

Answer 1

I spend a couple of hours on this issue and fixed it myself. For anyone with a similar problem here's the answer:

The order in which the primary key fields are included in the PrimaryKeyConstraint matters in fact. My problem was solved by reverting the order, instead of sa.PrimaryKeyConstraint('record_valid_from', 'id') I changed it to sa.PrimaryKeyConstraint("id", "record_valid_from")

I hope this helps.