MySQL Bugs: #27405: Partitioning InnoDB auto

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	CPU Architecture:	Any
Tags:	auto_increment, innodb, partitioning

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'

test case

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

Thank you for the report.

Verified as described.

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

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

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.

Pushed into 5.1.22-beta

This was pushed to 5.1.23, not 5.1.22.

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.

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