[Django] #29097: Migrations using MySQL fail for constraints using composite indexes
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
geertjanvdk |
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) | Keywords: introspection mysql
Severity: Normal | migration index constraint
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 1
UI/UX: 0 |
-------------------------------------+-------------------------------------
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
https://code.djangoproject.com/ticket/28697), 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:
Thus:
{{{
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
WHERE
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
https://github.com/geertjanvdk/django/commit/ab0bcb327c51aab71f58789eb55a7981dcb06fdb
--
Ticket URL: <https://code.djangoproject.com/ticket/29097>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: introspection mysql | Triage Stage:
migration index constraint | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => basu96
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/29097#comment:1>
Django
-------------------------------------+-------------------------------------
| Dubey
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
migration index constraint |
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tim Graham):
* easy: 1 => 0
* needs_tests: 0 => 1
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/29097#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: introspection mysql | Triage Stage: Accepted
migration index constraint |
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => new
* owner: Basu Dubey => (none)
--
Ticket URL: <https://code.djangoproject.com/ticket/29097#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Migrations | Version: 1.11
Severity: Normal | Resolution:
Keywords: introspection mysql | Triage Stage: Accepted
migration index constraint |
Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* component: Database layer (models, ORM) => Migrations
--
Ticket URL: <https://code.djangoproject.com/ticket/29097#comment:4>
Django
-------------------------------------+-------------------------------------
| Ramezani
Type: Bug | Status: assigned
Component: Migrations | Version: 1.11
Severity: Normal | Resolution:
Keywords: introspection mysql | Triage Stage: Accepted
migration index constraint |
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* owner: (none) => Hasan Ramezani
* status: new => assigned
* needs_tests: 1 => 0
Comment:
Seems it has been fixed in
[https://github.com/django/django/commit/4fba321a457e52df77d0b512d043019e9f6b0bc9]
I Added a patch to test it.
Question: Do we need to add `kc.'constraint_name'` to `ORDER BY` as well?
--
Ticket URL: <https://code.djangoproject.com/ticket/29097#comment:5>
Django
-------------------------------------+-------------------------------------
| Ramezani
Component: Migrations | Version: 1.11
Severity: Normal | Resolution: fixed
Keywords: introspection mysql | Triage Stage: Accepted
migration index constraint |
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
`kc.'constraint_name'` in an `ORDER BY` should not be necessary. I think
we can close this issue because ordering was added in
4fba321a457e52df77d0b512d043019e9f6b0bc9 and creating a regression test
may not be feasible (see [https://github.com/django/django/pull/9941
discussion]).
--
Ticket URL: <https://code.djangoproject.com/ticket/29097#comment:6>