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