Bug #7357 ERROR 1205 in InnoDB with double unique key on same column

• Submitted: 16 Dec 2004 18:55
• Modified: 12 Feb 2005 13:40
• Reporter: Martin Rode
• Status: No Feedback
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 4.0.22
• OS: Linux (Linux / Debian)
• Assigned to: Assigned Account
• CPU Architecture: Any

[16 Dec 2004 18:55] Martin Rode

Description:
After starting a transaction a simple "delete from " results in an error 1205. although there is only on user logged on to the database.

It is probably because I have two unique key indeces on the same column. 

To drop the extra index, i had to restart the mysql daemon. 

How to repeat:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM pf_file WHERE (reference = 'mail_media_b2e46e2d7210d854e736fc439a58693b_2383');

ERROR 1205: Lock wait timeout exceeded; Try restarting transaction
mysql>
mysql> select * from pf_file WHERE (reference = 'mail_media_b2e46e2d7210d854e736fc439a58693b_2383');
Empty set (0.00 sec)

CREATE TABLE `pf_file` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `mimetype` varchar(60) NOT NULL default 'application/octet-stream',
  `name` varchar(120) NOT NULL default '',
  `size` bigint(20) unsigned NOT NULL default '1024',
  `filedate` datetime NOT NULL default '0000-00-00 00:00:00',
  `info` text,
  `md5sum` varchar(32) default NULL,
  `reference` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `reference` (`reference`),
  UNIQUE KEY `reference_2` (`reference`)
) TYPE=InnoDB

[16 Dec 2004 18:56] Martin Rode

Try to change the Synopsis to:
ERROR 1205 in InnoDB with double unique key on same column

[16 Dec 2004 19:48] Heikki Tuuri

Martin,

please print

SHOW INNODB STATUS\G

during that lock wait.

Are there any FOREIGN KEYs involved? If yes, please print SHOW CREATE TABLE ... for all tables that are referenced, or reference the table in question.

Regards,

Heikki

[17 Dec 2004 6:38] Jan Lindström

I could not repeat this problem using table definition given and following example data:

mysql> insert into pf_file values(null,'application/octet-stream','puppu',1024, '2004-01-01 10:00:00', 'data',null,'mail_media_b2e46e2d7210d854e736fc439a58693b_2383');
Query OK, 1 row affected (0.00 sec)

mysql> insert into pf_file values(null,'application/octet-stream','puppu',1024, '2004-01-01 10:00:00', 'data',null,'mail_media_b2e46e2d7210d854e736fc439a58693b_2384');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from pf_file WHERE (reference =
    -> 'mail_media_b2e46e2d7210d854e736fc439a58693b_2383');
+----+--------------------------+-------+------+---------------------+------+--------+--------------------------------------------------+
| id | mimetype                 | name  | size | filedate            | info |md5sum | reference                                        |
+----+--------------------------+-------+------+---------------------+------+--------+--------------------------------------------------+
|  1 | application/octet-stream | puppu | 1024 | 2004-01-01 10:00:00 | data |NULL   | mail_media_b2e46e2d7210d854e736fc439a58693b_2383 |
+----+--------------------------+-------+------+---------------------+------+--------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM pf_file WHERE (reference =
    -> 'mail_media_b2e46e2d7210d854e736fc439a58693b_2383');
Query OK, 1 row affected (0.00 sec)

mysql>                      

Please provide result from show innodb status.

[12 Feb 2005 13:40] Heikki Tuuri

No feedback in 3 months.