Opened 12 months ago

Last modified 10 months ago

#29097 new Bug

Migrations using MySQL fail for constraints using composite indexes

Reported by: geertjanvdk Owned by:
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords: introspection mysql migration index constraint
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no


When running migrations which create (unique) composite indexes, the MySQL introspection fails to get the correct index:

ValueError: Found wrong number (0) of constraints for product_versions(product_id, ordering)

This was previously reported as fixed (see, but it is not. Using 1.11.5, and 1.11.9, it fails.

The fix is very easy: in DatabaseIntrospection.get_constraints(), an ORDER BY is missing when selecting from the table INFOMRATION_SCHEMA.KEY_COLUMN_USAGE:


    def get_constraints(self, cursor, table_name):
        Retrieves any constraints or keys (unique, pk, fk, check, index) across one or more columns.
        constraints = {}
        # Get the actual constraint names and columns
        name_query = """
            SELECT kc.`constraint_name`, kc.`column_name`,
                kc.`referenced_table_name`, kc.`referenced_column_name`
            FROM information_schema.key_column_usage AS kc
                kc.table_schema = DATABASE() AND
                kc.table_name = %s
            ORDER BY kc.`constraint_name`, kc.`ordinal_position`

The ORDER BY assures the order the columns were specified. the SHOW INDEX statement does this by default.

The above should work for all MySQL versions supported.

Note that Django 2.0 has the same issue.

Patch available

Change History (3)

comment:1 Changed 12 months ago by Basu Dubey

Owner: changed from nobody to Basu Dubey
Status: newassigned

comment:2 Changed 12 months ago by Tim Graham

Easy pickings: unset
Needs tests: set
Triage Stage: UnreviewedAccepted

comment:3 Changed 10 months ago by Basu Dubey

Owner: Basu Dubey deleted
Status: assignednew
Note: See TracTickets for help on using tickets.
Back to Top