Bug #27405 Partitioning InnoDB auto_increment bug
Submitted: 23 Mar 2007 18:01 Modified: 4 Sep 2007 9:03
Reporter: Reinis Rozitis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.16 OS:Linux (OpenSuse 10.2)
Assigned to: Alexey Botchkov
Tags: auto_increment, innodb, partitioning

[23 Mar 2007 18:01] Reinis Rozitis
Description:
MySQL fails to generate/populate or retrieve autoincrement key (next autoindex value) when there is partitioning on table and primary key with AUTO_INCREMENT.

I couldn't reproduce the issue on MyISAMI so seems InnoDB is affected. Also when running one thread the problem doesnt appear.

How to repeat:
Create table:

CREATE TABLE `content_test` ( 
`cid` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`uid` int(11) unsigned NOT NULL, 
PRIMARY KEY (`cid`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
PARTITION BY RANGE (cid) ( 
PARTITION p0 VALUES LESS THAN (10000), 
PARTITION p1 VALUES LESS THAN (20000) 
) 

Launch few paralel threads (from some aplication (I use php for example)):

INSERT INTO content_test SET uid = 123;

Majority of the queries fail with:
Can't write; duplicate key in table 'content_test'
[26 Mar 2007 9:44] Sveta Smirnova
test case

Attachment: testcase.c (text/plain), 5.42 KiB.

[26 Mar 2007 9:45] Sveta Smirnova
Thank you for the report.

Verified as described.
[24 Apr 2007 14:43] 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/commits/25285

ChangeSet@1.2567, 2007-04-24 18:42:46+05:00, holyfoot@mysql.com +1 -0
  bug #27405 (Partitioning InnoDB auto_increment bug)
  InnoDB engine changes internal auto_increment counter only after
  ha_innodb::write_row, so two threads can't simultaneously
  operate between ha_innodb::update_autoincrement and
  ha_innodb::write_row.
  So concurrent execution of ha_partition::write_row prevented
[12 Jun 2007 20:25] Yuriy Maksimov
Do you have any assumptions when this bug will be fixed? It still exists in 5.1.19
(Windows Server 2003). Please advise.

Best Regards,
Yuriy
[16 Jun 2007 11:04] Jon Stephens
Yuri,

"Patch pending" status = fix has been written, and is now waiting for approval by a different developer assigned to review it.

The fix could be ready in time to go into 5.1.20. Otherwise (unless the reviewer finds a really big problem with the proposed patch), it will almost certainly be in 5.1.21.
[24 Aug 2007 7:20] Bugs System
Pushed into 5.1.22-beta
[31 Aug 2007 17:05] Paul Dubois
This was pushed to 5.1.23, not 5.1.22.
[4 Sep 2007 9:03] Jon Stephens
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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Changed category to Partitioning, added tags.

Documented fix in 5.1.23 changelog.
[1 Sep 2008 6:55] Kristofer Pettersson
Obtaining the TABLE_SHARE::mutex causes deadlocks:
http://bugs.mysql.com/bug.php?id=38804
which has a solution in 
http://bugs.mysql.com/bug.php?id=33479