MySQL Bugs: #48900: Autoincrement fault on temporary table

Bug #48900	Autoincrement fault on temporary table
Submitted:	19 Nov 2009 11:59	Modified:	21 Nov 2009 15:45
Reporter:	KREME LANDRY KOSSONOU	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	MySQL 5.1.40-Commuity via TCP/IP	OS:	Windows (XP version 2002 SP3)
Assigned to:		CPU Architecture:	Any
Tags:	autoincrement, temporary table

Description:
The autoincrementation of a field in a temporary table dont work correctly.
After incrementation work correctly from 1 to 9 it make a hope (do not increment to 10) to 11.

Further i can see the same problem at any position, it isn't regular.

How to repeat:
Any time i execute my stored procedure, the probleme rises.

Suggested fix:
None

Thank you for the problem report. Please, send the code of your procedure and SHOW CREATE TABLE results for the temporary table used.

What is your default storage engine?

My default storage engine is InnoDB

So, temporary table is created as InnoDB one. Please, send the results of:

show variables like 'innodb_autoinc%';

So, you have:

"innodb_autoinc_lock_mode","1"

Please, check if setting it to 0 will help to get the results you expect. Read http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html and http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_m... for the details.

After configuration of the auto_inc_lock_mode option to zero (0) all the autoincrement work correctly (without gap) since there is no rollback.

Thank you for the help.

The configured option is innodb_autoinc_lock_mode = 0
Thank

So, this was problem was not related to any bug, but was a result of a (documented) change in default behavior of auto_increment columns of InnoDB tables in 5.1.x.