Bug #1956 Parser allows naming of PRIMARY KEY
Submitted: 25 Nov 2003 16:23 Modified: 27 Sep 2008 9:31
Reporter: Arjen Lentz Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:4.0.12, 4.1.0, 5.1.52 OS:Any (Any)
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[25 Nov 2003 16:23] Arjen Lentz
Description:
When specifying a PRIMARY KEY, the parser still accepts a name for the index even though the MySQL server will always name it PRIMARY.
So, the parser should NOT accept this, otherwise a user can get into a confusing situation as his input is ignored/changed.

How to repeat:
CREATE TABLE pk (i INT NOT NULL);
ALTER TABLE ADD PRIMARY KEY bla (i);

'bla' after PRIMARY KEY should not be accepted.

Suggested fix:
Fix grammar in parser.
[26 Nov 2003 2:45] [ name withheld ]
>'bla' after PRIMARY KEY should not be accepted.

Or, rather, only 'PRIMARY' should be. This sould help dumps, shouldn't it ?

Reciprocally, no other index should be named as 'PRIMARY'.
[27 Sep 2008 9:31] Konstantin Osipov
Verified against 5.1.29.
There is no reason to accept broken syntax.
Should be checked with the standard, and either documented or fixed.
[12 Dec 2009 23:07] Andy Nicastro
It's an annoying bug when doing metadata reports and it is still not fixed in 5.1.39.
Andynic
[27 Oct 2010 14:30] Valeriy Kravchuk
Still happens in 5.1.52:

macbook-pro:5.1 openxs$ bin/mysql -uroot test2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.52-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.52-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE pk (i INT NOT NULL);
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE pk ADD PRIMARY KEY bla (i);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pk\G
*************************** 1. row ***************************
       Table: pk
Create Table: CREATE TABLE `pk` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[11 Nov 2017 17:51] Federico Razzoli
Still true in 8.0.3, and I made this test just to show that it breaks any rule of common sense:

mysql> use test
Database changed
mysql> CREATE TABLE `pk` (
    ->   `i` int(11) NOT NULL,
    ->   PRIMARY KEY (`i`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table pk drop index i;
ERROR 1091 (42000): Can't DROP 'i'; check that column/key exists
mysql> alter table pk drop index `PRIMARY`;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
[30 Aug 2018 11:30] Umesh Shastry
Also, Bug #92242