Alembic - Using composite primary keys causes incorrect table definition in MySQL-PHP Chinese Network Q&A

I have multiple "versioned" database SQLAlchemy models that use composite primary keys, achieved by combining an auto-incrementing integer field ("id") and a datetime field ("record_valid_from"). I'm trying to run this model in a local Docker container against a MySQL database.

The model definition is roughly as follows:

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

Other models look similar, with various relationships between them.

When using Alembic to automatically generate migration scripts (alembic revision --autogenerate -m "init database"), the generated Python code appears to produce invalid SQL statements.

More specifically, I encountered:

(pymysql.err.OperationalError) (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key')< ;/pre> Here is the migration code (note: I have simplified it): 
 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)
 Has anyone experienced a similar situation? Or know how to solve this problem? 
I tried the following: 

  Call op.create_primary_key after creating the table (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 keys defined').
 Remove sa.PrimaryKeyConstraint and call op.create_primary_key directly. result:  The migration was successful and running normally. 
 Attempting to create a new ORM model resulted in the following error: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1364, "Field 'id' has no default value").

reply all (1)

P粉9211651812023-07-25 14:49:14 1 floor

I spent a few hours solving this problem and fixed it myself. For anyone who encounters a similar problem, here is the answer:

Actually, the order of the primary key fields in the PrimaryKeyConstraint has an impact. My problem was solved by changing the order, instead of using sa.PrimaryKeyConstraint('record_valid_from', 'id'), I changed to sa.PrimaryKeyConstraint("id", "record_valid_from").

Hope this helps.