Bug #11227 DESC says "MUL" where it shoud say "UNI"
Submitted: 10 Jun 2005 7:59 Modified: 25 Jul 2005 19:52
Reporter: Carsten Pedersen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.4-beta-standard-log OS:Linux (Linux (apparently also Win))
Assigned to: Jim Winstead CPU Architecture:Any

[10 Jun 2005 7:59] Carsten Pedersen 
Description:
mysql> CREATE TABLE t (id INT NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE);
Query OK, 0 rows affected (0.06 sec)

mysql> DESC t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI |         |       |
| name  | varchar(20) | NO   | MUL |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

INFORMATION_SCHEMA gives the same result:

mysql> SELECT COLUMN_KEY  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t' AND COLUMN_NAME='name';
+------------+
| COLUMN_KEY |
+------------+
| MUL        |
+------------+
1 row in set (0.00 sec)

It's not a question of the field being defined wrong. The following behaves as expected for a UNIQUE column:

mysql> INSERT INTO t VALUES (1, "test"), (2, "test");
ERROR 1062 (23000): Duplicate entry 'test' for key 2

This has been marked as Critical, as any external tool trying to analyze the table structure will have no way of knowing that the index is indeed UNIQUE.

How to repeat:
See above

Suggested fix:
I don't believe there is a workaround
[10 Jun 2005 14:22] MySQL Verification Team 
Verified with 5.0.7-beta-debug
[22 Jun 2005 19:45] 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/internals/26342
[29 Jun 2005 13:55] Magnus Blåudd 
Approved
[22 Jul 2005 21:45] Jim Winstead 
Fixed in 5.0.11.
[25 Jul 2005 19:52] Mike Hillyer 
Documented in 5.0.11 changelog:

<listitem><para>A <literal>UNIQUE VARCHAR</literal> column would be mis-identified as <literal>MUL</literal> in table descriptions. (Bug #11227)</para></listitem>