Bug #11228 DESC shows arbitrary column as "PRI"
Submitted: 10 Jun 2005 8:40 Modified: 22 Sep 2010 20:09
Reporter: Carsten Pedersen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S1 (Critical)
Version:5.0.4-beta-standard-log OS:Linux (Linux)
Assigned to: Paul DuBois CPU Architecture:Any

[10 Jun 2005 8:40] Carsten Pedersen
Description:
mysql> CREATE TABLE t (i1 INT NOT NULL, i2 INT NOT NULL, UNIQUE i1idx (i1), UNIQUE i2idx (i2));
Query OK, 0 rows affected (0.07 sec)

Expexted output would be two rows showing UNI, but we get:

mysql> DESC t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i1    | int(11) | NO   | PRI |         |       |
| i2    | int(11) | NO   | UNI |         |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

SHOW CREATE TABLE t follows the specification given by the original CREATE TABLE statement and does not indicate that any of the columns is a primary key.

When we drop what is supposedly the primary key, it moves somewhere else:

mysql> ALTER TABLE t DROP INDEX i1idx;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i1    | int(11) | NO   |     |         |       |
| i2    | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.03 sec)

This is a major problem for external tools, and to some extent DBAs for a couple of reasons. First, you cannot DROP the supposed PRIMARY KEY:

mysql> ALTER TABLE t DROP PRIMARY KEY;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists

Second this output of  DESCRIBE will lead the user to believe that "another" primary key cannot be specified, while you can in fact do so:

mysql> ALTER TABLE t ADD PRIMARY KEY (i1);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i1    | int(11) | NO   | PRI |         |       |
| i2    | int(11) | NO   | UNI |         |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

How to repeat:
See above. Using INFORMATION_SCHEMA to extract the index properties yields the same results.

Suggested fix:
Don't show the index as a primary key unless it really has been specified as such
[13 Aug 2005 1:09] Andrey Hristov
Hi Carsten,
after some tracing I found why this does happen. The sources read
(sql/table.cc)
 642         /*
 643           If the UNIQUE key doesn't have NULL columns and is not a part
key
 644           declare this as a primary key.
 645         */
 646

However, this is in the memory, not on the disk (frm). Therefore, one can create
a PRIMARY KEY despite of that INFORMATION_SCHEMA or DESCRIBE (which filters the
data returned by INF_SCHEMA) report that there is a PRI key already. After
grepping the sources it looks like PRI_KEY_FLAG (which is a bit of the field
table->field[xx]->flags is only used in ha_ndbcluster.cc extensively otherwise
not). I suppose this is used only for client info reasons and not by the server
itself when working with the indices since there is another structure in
table->s.
[19 May 2006 8:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6617
[23 Jun 2006 8:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8127
[30 Jun 2006 15:20] Michael Widenius
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The intention with the original code was to show that MySQL treats the first given unique key as a primary key. Clients can use the marked primary key as a real primary key to validate row changes in case of conflicting updates.  The ODBC driver (and other drivers) may also use this fact to optimize/check updates.

We should not change this at in a stable release as old clients can depend on the current behaviour and the current behaviour is very useful.

The change also broke ndb cluster, which depended on the old behaviour.

I am reverting the bug fix in 4.1 and above...
[30 Jun 2006 15:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8560
[30 Jun 2006 17:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8566
[30 Jun 2006 22:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8577
[12 Oct 2006 9:56] Tonci Grgin
Duplicate report: Bug#22520
[17 Sep 2010 10:04] Jon Stephens
Kostja alerted me today to the fact that this behaviour doesn\t actually seem to be documented anywhere. I\ve temporarily re-opened this bug as a Docs bug and assigned it to myself so that I'll have a reminder to investigate and correct the docs issue if need be.
[17 Sep 2010 11:38] Jon Stephens
See also BUG#51546, BUG#33134, BUG#37130
[22 Sep 2010 20:09] Paul DuBois
http://dev.mysql.com/doc/refman/5.1/en/show-columns.html says:

"
A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.
"