| Bug #10359 | InnoDB in MySQL v5.0.4 doesn't lock auto increment reliably | ||
|---|---|---|---|
| Submitted: | 4 May 2005 16:00 | Modified: | 18 May 2005 16:06 |
| Reporter: | Aron Steg | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 5.0.4-beta-standard-log | OS: | Linux (Linux 2.4.20-021stab022.1.777-sm) |
| Assigned to: | Heikki Tuuri | CPU Architecture: | Any |
[4 May 2005 19:57]
Heikki Tuuri
Hi! I have to check the auto-inc code in ha_innodb.cc in 5.0. It has been changed since 4.1. Thank you for the bug report. Heikki
[6 May 2005 20:47]
Heikki Tuuri
Hi! A developer had removed code from ha_innodb.cc, and moved some functionality to an upper layer, to implement auto-inc where the increment is > 1. We must keep in mind the following: since the generation of auto-inc values must be deterministic for an SQL statement like INSERT INTO t SELECT * FROM s (for the binlog to work), we must keep a resource locked for the duration of the SQL statement. Deadlocks are then possible, and the resource must be protected by a transactional lock, like the AUTO-INC table lock in InnoDB. The resource cannot be protected by a mutex. I will probably need to move functionality from an upper layer back to ha_innodb.cc. Regards, Heikki
[17 May 2005 19:35]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/24989
[17 May 2005 19:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/24990
[17 May 2005 19:47]
Heikki Tuuri
Fixed in 5.0.6. Regards, Heikki
[18 May 2005 16:06]
Michael Widenius
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
Description: Hi, When multiple clients insert into my InnoDB tables at the same time, they are each allocated the same auto-increment number. Then, whichever hits the database second gets rejected with (1194697 is the auto generated id): PHP Fatal error: Database query failed: Duplicate entry '1194697' for key 1 in /home/pss/httpdocs/system/dblogic.php on line 22 I tested with MyISAM and found no problem. I rolled back to v4.1.11-standard-log with InnoDB and the problem went away. It doesn't matter what the structure of the table is. How to repeat: I run the following script (test.php) multiple times using: php test.php & php test.php & php test.php Two of the three fail every time. <?php require "dblogic.php"; $db = new DBLogic("localhost", "database", "username", "password"); for ($i=0; $i<10000; $i++) { $query = "insert into pss_log (id,transid,opid,queueid,queuecount,logtime,message,level) values (null, 1, 2, 3, 4, now(), 'this is a medium text field', 5)"; $db->executeInsert($query); } ?> Suggested fix: Don't know. some notes: - The server has 2 x HT CPU's (total 4 x CPU's). - mysql (and mysqld) are running within a Virtual Private Server (by Virtuozzo) with plenty of resources. The cpuinfo of one of the four is below: processor : 1 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Xeon(TM) CPU 3.06GHz stepping : 9 cpu MHz : 3048.967 cache size : 512 KB fdiv_bug : no hlt_bug : no f00f_bug : no coma_bug : no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm bogomips : 6094.84