Opened 4 years ago

Closed 3 years ago

#15933 closed Bug (fixed)

mysql inspectdb loosing primary_key information

Reported by: andi Owned by: akaariai
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

Description

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:

CREATE TABLE `test` (
  `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 4 years ago.

Download all attachments as: .zip

Change History (10)

comment:1 Changed 4 years ago by andi

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

comment:2 Changed 4 years ago by aaugustin

  • Needs tests set
  • Triage Stage changed from Unreviewed to Accepted

comment:3 Changed 4 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')
            else:
                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],]
        unique_together=[]
        for key in uniques:
                fields = uniques[key]
                if len(fields) == 1:
                    field = fields[0]
                    indexes[field]['unique'] = True
                else:
                    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 4 years ago by andi

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

comment:5 Changed 4 years ago by ramiro

  • Component changed from Database layer (models, ORM) to Core (Management commands)
  • Keywords inspectdb added
  • UI/UX unset

comment:6 Changed 3 years ago by ramiro

  • Keywords mysql4 added

comment:7 Changed 3 years ago by claudep

  • Keywords mysql added; mysql4 removed

Was able to reproduce on MySQL 5.1

comment:8 Changed 3 years ago by akaariai

  • Owner changed from nobody to akaariai

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 3 years ago by akaariai

  • Resolution set to fixed
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.
Back to Top