| Bug #69055 | wrong PRI Key in SHOW COLUMNS | ||
|---|---|---|---|
| Submitted: | 24 Apr 2013 14:22 | Modified: | 24 Apr 2013 16:11 |
| Reporter: | martin fuxa | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
| Version: | 5.6.10, 5.5.30 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[24 Apr 2013 16:11]
MySQL Verification Team
Thank you for the bug report. Quoting the Manual: http://dev.mysql.com/doc/refman/5.5/en/create-table.html "If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY. " So is returned UNIQUE KEY `skupina_am` (`skupina`,`rk`,`nazev`), columns as PRI
Description: cols in table without PRIMARY KEY are incorrectly reported by DESC as PK. see test case below. How to repeat: CREATE TABLE `test24` ( `id` int(10) NOT NULL, `skupina` int(10) NOT NULL, `rk` smallint(3) NOT NULL, `nazev` varchar(100) NOT NULL, `zdroj` enum('a','b','c') NOT NULL, UNIQUE KEY `skupina_am` (`skupina`,`rk`,`nazev`), UNIQUE KEY `skupina_am_2` (`skupina`,`rk`,`zdroj`) ) ENGINE=InnoDB; there isn't any PK. But DESC / SHOW COLUMNS says PRI, and documentation http://dev.mysql.com/doc/refman/5.6/en/show-columns.html If Key is PRI, the column is a PRIMARY KEY or is one of the columns in a multiple-column PRIMARY KEY. desc test24; +---------+-------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------------+------+-----+---------+-------+ | id | int(10) | NO | | NULL | | | skupina | int(10) | NO | PRI | NULL | | | rk | smallint(3) | NO | PRI | NULL | | | nazev | varchar(100) | NO | PRI | NULL | | | zdroj | enum('a','b','c') | NO | | NULL | | +---------+-------------------+------+-----+---------+-------+ Suggested fix: Key for `skupina` in DESC does not show wrong PRI, correct is UNI. And non-first column shold have empty Key according with DOC > If Key is empty, the column either is not indexed or is indexed only as a secondary column in a multiple-column, nonunique index.