[Django] #29097: Migrations using MySQL fail for constraints using composite indexes

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[Django] #29097: Migrations using MySQL fail for constraints using composite indexes

Django
#29097: Migrations using MySQL fail for constraints using composite indexes
-------------------------------------+-------------------------------------
               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.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/054.e8833e3cf4c0640a74c63cc2b7ea9bf6%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29097: Migrations using MySQL fail for constraints using composite indexes

Django
#29097: Migrations using MySQL fail for constraints using composite indexes
-------------------------------------+-------------------------------------
     Reporter:  geertjanvdk          |                    Owner:  basu96
         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
-------------------------------------+-------------------------------------
Changes (by basu96):

 * owner:  nobody => basu96
 * status:  new => assigned


--
Ticket URL: <https://code.djangoproject.com/ticket/29097#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.d841281e714cea694ec92385ef735b07%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29097: Migrations using MySQL fail for constraints using composite indexes

Django
In reply to this post by Django
#29097: Migrations using MySQL fail for constraints using composite indexes
-------------------------------------+-------------------------------------
     Reporter:  geertjanvdk          |                    Owner:  Basu
                                     |  Dubey
         Type:  Bug                  |                   Status:  assigned
    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
-------------------------------------+-------------------------------------
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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.d5cbfd91a994cebdec695086b1404da0%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29097: Migrations using MySQL fail for constraints using composite indexes

Django
In reply to this post by Django
#29097: Migrations using MySQL fail for constraints using composite indexes
-------------------------------------+-------------------------------------
     Reporter:  geertjanvdk          |                    Owner:  (none)
         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
-------------------------------------+-------------------------------------
Changes (by Basu Dubey):

 * status:  assigned => new
 * owner:  Basu Dubey => (none)


--
Ticket URL: <https://code.djangoproject.com/ticket/29097#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.959d916ffc476a4837bf159277182a14%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29097: Migrations using MySQL fail for constraints using composite indexes

Django
In reply to this post by Django
#29097: Migrations using MySQL fail for constraints using composite indexes
-------------------------------------+-------------------------------------
     Reporter:  geertjanvdk          |                    Owner:  (none)
         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
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

 * component:  Database layer (models, ORM) => Migrations


--
Ticket URL: <https://code.djangoproject.com/ticket/29097#comment:4>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

--
You received this message because you are subscribed to the Google Groups "Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.ae89d6827da357663d29d2165406a2a3%40djangoproject.com.