MySQL Bugs: #38872: Auto-increment for Innodb does not work for mixed and statement based formats

Bug #38872	Auto-increment for Innodb does not work for mixed and statement based formats
Submitted:	18 Aug 2008 18:44	Modified:	26 Feb 2009 20:10
Reporter:	Hema Sridharan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	mysql-6.0-backup, mysql-6.0, 5.1 bzr	OS:	Linux
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	regression

Description:
1) I create database(ptr_ob1) and Innodb table (ptr_ob1.t3)with auto increment column in it.
2) I create trigger that will fire into that table.
3) The auto increment doesn't work properly for innodb tables and shows gaps in the numbers for "Mixed" and "Statement" based binary logging format.

Please see the example below:

How to repeat:
--source include/have_innodb.inc
--source include/have_log_bin.inc

SET BINLOG_FORMAT='MIXED';
CREATE DATABASE IF NOT EXISTS ptr_ob1;
USE ptr_ob1;
CREATE TABLE ptr_ob1.t1(id INT, a INT, b CHAR(5))ENGINE=INNODB;
CREATE TABLE ptr_ob1.t3(srno INT AUTO_INCREMENT, PRIMARY KEY(srno), name VARCHAR(30) NOT NULL, old_a INT)ENGINE=INNODB;

delimiter ||;
CREATE TRIGGER ptr_ob1.trg1 BEFORE INSERT ON ptr_ob1.t1 FOR EACH ROW
BEGIN
 INSERT INTO ptr_ob1.t3 VALUES(NULL, 't1', new.a);
END;||
delimiter ;||

INSERT INTO ptr_ob1.t1 VALUES(1,20, 'bb1'), (2,50, 'bb2'),(3,80,'bb3');
SELECT * FROM ptr_ob1.t1;
SELECT * FROM ptr_ob1.t3;
INSERT INTO ptr_ob1.t3 VALUES(NULL,'t1', 20),(NULL,'t1',50),(NULL,'t1',80);
SELECT * FROM ptr_ob1.t3;

RESULTS
=======

SELECT * FROM ptr_ob1.t1;
id      a       b
1       20      bb1
2       50      bb2
3       80      bb3
SELECT * FROM ptr_ob1.t3;
srno    name    old_a
1       t1      20
2       t1      50
4       t1      80
INSERT INTO ptr_ob1.t3 VALUES(NULL,'t1', 20);
SELECT * FROM ptr_ob1.t3;
srno    name    old_a
1       t1      20
2       t1      50
4       t1      80
6       t1      20

From the above example if you notice, the ptr_ob1.t3 has gaps in the srno column(i.e auto increment column). After numeric 2 we see 4 instead of 3. Also after 4, 5 is missing and we see 6.

Note: This does not occur with Row based binary logging format and for Myisam, Falcon storage engines. Also this behavior is not seen if the inserts are done 
separately.

Thank you for the report.

Verified as described. Looks like duplicate of bug #31612. And this is not repeatable with 5.0 as latter.

Test case for seperate inserts

Attachment: test1.txt (text/plain), 1.93 KiB.

Test case using Row based format

Attachment: test2.txt (text/plain), 1.70 KiB.

Lars Thalmann wrote:
"We do not guarantee that the autoinc numbers are
consecutive without gaps, only that they are 
strictly increasing.  Peter G should confirm this before the
bug gets closed."
Unfortunately we do guarantee that. The MySQL Reference Manual says
"innodb_autoinc_lock_mode = 0 ... assures that the auto-increment values
assigned by any given statement are consecutive (although "gaps"
can exist within a table if a transaction that generated
auto-increment values is rolled back, as discussed later)."
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
Bug#38872 isn't about rollback.
The word "consecutive" generally means "without gaps".
And other places on that page imply the same thing.

So I'd suggest a wait until Bug#31612 is fixed,
then test again with innodb_autoinc_lock_mode=0.

In the long term MySQL should try to abandon this wording.
Other DBMSs tend to avoid consecutiveness guarantees, see
http://web.archive.org/web/20030401185932/www.dbazine.com/gulutzan4.html

Verifier, since BUG#31612 is now fixed, and that bug 
was suggested as a possible reason why this bug appeared, 
can you please re-verify this bug?

Lars,

thank you for the feedback.

Bug is not repeatable anymore since version 5.1.29.