Bug #5262 mysqld hangs and takes 100% CPU when dropping PK
Submitted: 27 Aug 2004 18:33 Modified: 28 Aug 2004 5:36
Reporter: Stanislas Renan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:4.1.2 OS:Windows (windows XP)
Assigned to: CPU Architecture:Any

[27 Aug 2004 18:33] Stanislas Renan
Description:
Create a new table in innodb format, with a PK.
If you try to delete this PK (possibly twice), then, mysqld hangs and takes all the CPU.
Nothing is logged but the query in mysql.log.
Nothing special is logged in mysql.err.

If I start the server in a console :
mysqld-max.exe --console

nothing special is logged after :
Version: '4.1.2-alpha-max-log'  socket: ''  port: 3306

This bug has been discovered because DBDesigner 4.0.5.6 beta does this sort of queries in order to synchronize the local schema with the database. DBD4 is obviously bugged by doing it twice.

How to repeat:
This little script has been able to reproduce the bug :

CREATE TABLE `double_pk` (
  `id` int(11) NOT NULL default '0',
  `champ` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

alter table double_pk drop primary key ;
alter table double_pk drop primary key ;
[27 Aug 2004 19:03] MySQL Verification Team
I tested with latest BK windows server and I wasn't to repeat the
issue:

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.4-gamma-debug

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

mysql> CREATE TABLE `double_pk` (
    ->   `id` int(11) NOT NULL default '0',
    ->   `champ` varchar(20) NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.27 sec)

mysql>
mysql> alter table double_pk drop primary key ;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

I will test that behavior for 4.1.3 which is the today release at our
web site.

Thanks.
[27 Aug 2004 21:49] Stanislas Renan
just to be sure : have you tried a third time to drop the PK ?

I have double checked my inputs, and noticed that I incorreclty reported 2 drops (because I've done it many times in a row to find a small test case).

Here is the exact mysql client log to reproduce again the bug, after having dropped the PK (hence I must add it back first) :

===
mysql> alter table double_pk add primary key (id);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 0 rows affected (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table double_pk drop primary key ;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table double_pk drop primary key ;
ERROR 1091 (42000): Can't DROP 'PRIMARY'. Check that column/key exists
mysql> alter table double_pk drop primary key ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
===

The error 2013 is due to me breaking mysqld with ctrl-c in the command window from where I've started it.

Sorry for the original mistake.
[28 Aug 2004 4:13] MySQL Verification Team
This issue is already fixed in version 4.1.3.

Thank you for the bug report.
[28 Aug 2004 5:36] MySQL Verification Team
Ok. I did several times:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.3b-beta-nt

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

mysql> CREATE TABLE `double_pk` (
    ->   `id` int(11) NOT NULL default '0',
    ->   `champ` varchar(20) NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.24 sec)

mysql> alter table double_pk drop primary key ;
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

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