Opened 5 years ago

Closed 4 years ago

#15933 closed Bug (fixed)

mysql inspectdb loosing primary_key information

Reported by: andi Owned by: Anssi Kääriäinen
Component: Core (Management commands) Version: 1.3
Severity: Normal Keywords: inspectdb mysql
Cc: andi Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no


I've been inspecting a mysql 4 (4.0.16-standard) database. The resulting models file didn't handle all the primary_keys right if the primary key column is used in any other key.

Example table layout:

  `PrimaryKeyColumn` int(10),
  `AnotherColumn`    int(10),
  `SomethingElse`    int(10),
  PRIMARY KEY `PrimaryKeyColumn`,
  UNIQUE KEY `AnotherKey` (`PrimaryKeyColumn`,`AnotherColumn`)

The SHOW INDEX FROM %s query would return the following table:

mysql> show index from test;                                                                                                                                                    
| Table | Non_unique | Key_name   | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| test  |          0 | PRIMARY    |            1 | PrimaryKeyColumn | A         |           0 |     NULL | NULL   |      | BTREE      |         | 
| test  |          0 | AnotherKey |            1 | PrimaryKeyColumn | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
| test  |          0 | AnotherKey |            2 | AnotherColumn    | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 

After reading row 2 the primary_key information would be lost.
To preserve this information i added a check to only update the unique field. Patch is attached.

I had to apply #14618 to use inspectdb with my mysql version.

Attachments (1)

django_mysql4_multiple_keys_keep_primary.diff (802 bytes) - added by andi 5 years ago.

Download all attachments as: .zip

Change History (10)

comment:1 Changed 5 years ago by andi

Cc: andi added
Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

comment:2 Changed 5 years ago by Aymeric Augustin

Needs tests: set
Triage Stage: UnreviewedAccepted

comment:3 Changed 5 years ago by anonymous

I've been thinking about this patch over the weekend. The provided patch is ok for primary keys. If checking for uniqueness a multi-field unique key would mark all involved fields as unique. A single field-unique key is detected correct.

I've written the following code to detect single/multi-field unique keys correctly:

    def get_indexes(self, cursor, table_name):
        Returns a dictionary of fieldname -> infodict for the given table,
        where each infodict is in the format:
            {'primary_key': boolean representing whether it's the primary key,
             'unique': boolean representing whether it's a unique index}
        cursor.execute("SHOW INDEX FROM %s" % self.connection.ops.quote_name(table_name))
        indexes = {}
        uniques = {}
        for row in cursor.fetchall():
            if indexes.has_key(row[4]) and not indexes[row[4]]['primary_key']: 
                    indexes[row[4]]['primary_key'] = (row[2] == 'PRIMARY')
                indexes[row[4]] = {'primary_key': (row[2] == 'PRIMARY'), 'unique': False}
            if row[2] != 'PRIMARY' and not bool(row[1]):
                    if not uniques.has_key(row[2]):
                        uniques[row[2]] = []
                    uniques[row[2]] += [row[4],]
        for key in uniques:
                fields = uniques[key]
                if len(fields) == 1:
                    field = fields[0]
                    indexes[field]['unique'] = True
                    unique_together += [fields,]
        #TODO: do something with the unique_together list
        return indexes

I've hacked a couple of test cases together in a test-django-project to verify the unique and primary fields. Is there any central location within django where db related tests should be ?

Also it seems that there is currently no way to pass the information of multi-field unique keys to the inspectdb management command?

comment:4 Changed 5 years ago by andi

I forgot to login. The previous comment is from me.

comment:5 Changed 5 years ago by Ramiro Morales

Component: Database layer (models, ORM)Core (Management commands)
Keywords: inspectdb added
UI/UX: unset

comment:6 Changed 5 years ago by Ramiro Morales

Keywords: mysql4 added

comment:7 Changed 5 years ago by Claude Paroz

Keywords: mysql added; mysql4 removed

Was able to reproduce on MySQL 5.1

comment:8 Changed 5 years ago by Anssi Kääriäinen

Owner: changed from nobody to Anssi Kääriäinen

If I understand correctly only single field indexes should be inspected by Django.

PostgreSQL deals with this issue by skipping all multi-column indexes. The intention is to do the same for Oracle. Oracle has a very similar bug (#18082). So, I think MySQL should skip multi-column indexes, too.

I am assigning this bug to myself. My intention is to unify the get_indexes methods across all backends to inspect only single-column indexes.

comment:9 Changed 4 years ago by Anssi Kääriäinen

Resolution: fixed
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top