MySQL Bugs: #22520: tables without a primary key but with a unique index report it is a primary key

Bug #22520	tables without a primary key but with a unique index report it is a primary key
Submitted:	20 Sep 2006 14:26	Modified:	12 Oct 2006 9:55
Reporter:	Cezary Okupski	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.26-BK, 5.0.22-max, 1.2.3rc, 1.2.4rc	OS:	Linux (Linux, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	alter, Drop, INDEX, key, NOT NULL, primary, unique

Description:
Tables without a primary key but with a unique index on a NOT NULL column report it is a primary key. When you run a MySQL console client and type in such a case for example:
SHOW columns FROM test;
You will discover "Key: PRI"

Any attempt of ALTER TABLE DROP PRIMARY KEY; results in:
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists
then.

As console client lets you type easily any MySQL query this is not such a big deal there. The real problem begins in GUIs, e. g. MySQL Administrator, where queries are generated automatically. If by coincidence it adds DROP PRIMARY KEY you will not be able to apply changes.

I am aware of:
http://bugs.mysql.com/bug.php?id=2361
and documentation
"DROP PRIMARY KEY drops the primary index. Note: In older versions of MySQL, if no primary index existed, DROP PRIMARY KEY would drop the first UNIQUE index in the table. This is not the case in MySQL 5.0, where trying to use DROP PRIMARY KEY on a table with no primary key results in an error."

If there is still a reason behind marking the first UNIQUE key as the PRIMARY KEY if no PRIMARY KEY specified explicitly, just make MySQL Administrator aware of it somehow.

How to repeat:
CREATE TABLE test (
  test INT NOT NULL,
  UNIQUE test (test)
);

SHOW columns FROM test;

and

ALTER TABLE DROP PRIMARY KEY;

Thank you for a bug report. Verified just as described with 5.0.26-BK on Linux:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.26-debug |
+--------------+
1 row in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test (
    ->   test INT NOT NULL,
    ->   UNIQUE test (test)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW COLUMNS FROM test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| test  | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                               |
+-------+----------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `test` int(11) NOT NULL,
  UNIQUE KEY `test` (`test`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table test DROP PRIMARY KEY;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists

I think, it is a bug, as SHOW COLUMNS results are misleading.

Cezary,

Thank you for the bug report.

The desired fix is to modify SHOW COLUMNS output (and also the relevant INFORMATION_SCHEMA values) to show "UNI" instead of "PRI" for all unique key columns, whether there is a PRIMARY KEY defined on the table or not.

This is not possible to do in 5.0, because certainly there are many people depending on the current behavior in scripts.  We feel it is OK to make this change in 5.1.

However, I would like to ask for more feedback about the MySQL Administrator behavior you reported.  It may be possible for MySQL Administrator to easily work around this, but I must confess that I don't understand just what the problem is.

Can you describe exactly what doesn't work with MySQL Administrator due to this bug?  With the steps to repeat it?

Thank you!

Timothy

I tried to redo my MySQL problem and I noticed that "DROP PRIMARY KEY" had not appeared in generated queries as often as it had seemed to me then. However, I can 
provide two examples of unwanted reaction:

1)
a) connect to a database
b) try to create the same table as with the described SQL queries, i. e. test (test INT NOT NULL, UNIQUE test (test)) and click apply
c) try to uncheck "Primary Key" in "Column Details" for column test
d) do anything else and "Apply"

2)
a) connect to a database
b) try to create the same table as with the described SQL queries, i. e. test (test INT NOT NULL, UNIQUE test (test)) and click apply
c) try to set "Auto Increment" for column test
d) do anything else and "Apply"

What version of MySQL Administrator do you use?

Earlier 1.2.3rc and now 1.2.4rc - the same. Should I be more precise in describing what I do/click/type to encounter the erroneous behaviour?

Cezary,

Let't this still be a verified server bug (as it is server bug, really). Please, report a separate MySQL Administrator bug also, in version 1.2.4rc. Describe what exactly you did/clicked/typed to encounter the erroneous behaviour there. As Timothy noted, GUI developers may be able to provide a workaround for this problem in MySQL Administrator.

Is this a Dupe of bug#11228 ?

Indeed Andrey is right. This is a duplicate of BUG#11228.