mysql inspectdb loosing primary_key information

[andi]

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.

[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?

[andi]

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

[Claude Paroz]

Was able to reproduce on MySQL 5.1

[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.

[Anssi Kääriäinen]

Fixed in a18e43c5bb8cb7c82c84ca1b42d847a62ac9f077