MySQL Bugs: #856: Naming a key "Primary" causes trouble

Bug #856	Naming a key "Primary" causes trouble
Submitted:	16 Jul 2003 14:05	Modified:	13 Mar 2004 10:52
Reporter:	Carsten Pedersen
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.1.1a/5.0	OS:	Linux (Linux Mandrake/Win XP/Suse 9.0)
Assigned to:	Bugs System	Target Version:

Description:
mysql> create table t (a int, index `primary` (a)); 
Query OK, 0 rows affected (0.00 sec) 
 
I now have a table with an index called "primary" -- it should show up as MUL 
in DESCRIBE, but: 
 
mysql> desc t; 
+-------+---------+-----------+------+-----+---------+-------+ 
| Field | Type    | Collation | Null | Key | Default | Extra | 
+-------+---------+-----------+------+-----+---------+-------+ 
| a     | int(11) | binary    | YES  | PRI | NULL    |       | 
+-------+---------+-----------+------+-----+---------+-------+ 
1 row in set (0.00 sec) 
 
and trying to create a real primary key gets me in trouble too: 
 
mysql> alter table t add b int not null primary key; 
ERROR 1061: Duplicate key name 'PRIMARY' 

How to repeat:
See above 

Suggested fix:
At a minmum, DESCRIBE should be changed to check that a key really is a 
primary key before showing PRI in the Key column 
 
Best of all would be to disallow calling a key "primary" unless it really is.

Same behavior:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.1-alpha-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> create table t (a int, index `primary` (a));
Query OK, 0 rows affected (0.06 sec)

mysql> desc t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table t add b int not null primary key;
ERROR 1061 (42000): Duplicate key name 'PRIMARY'

subj: bk commit - 4.1 tree (1.1588)

ChangeSet
  1.1588 03/08/28 22:08:59 vva@eagle.mysql.r18.ru +27 -0
  fixed bug #856 (Naming a key "Primary" causes trouble)

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix will be in mysql-4.1.2