Bug #856 Naming a key "Primary" causes trouble

• Submitted: 16 Jul 2003 12:05
• Modified: 13 Mar 2004 9:52
• Reporter: Carsten Pedersen
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.1a/5.0
• OS: Linux (Linux Mandrake/Win XP/Suse 9.0)
• Assigned to: Bugs System
• CPU Architecture: Any

[16 Jul 2003 12:05] Carsten Pedersen

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.

[10 Feb 2004 19:21] MySQL Verification Team

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'

[11 Mar 2004 2:15] Victor Vagin

subj: bk commit - 4.1 tree (1.1588)

ChangeSet
  1.1588 03/08/28 22:08:59 [e-mail] +27 -0
  fixed bug #856 (Naming a key "Primary" causes trouble)

[13 Mar 2004 9:52] Victor Vagin

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