MySQL Bugs: #65225: InnoDB miscalculates auto-increment after changing auto_increment

Bug #65225	InnoDB miscalculates auto-increment after changing auto_increment_increment
Submitted:	7 May 2012 16:39	Modified:	10 Feb 2014 14:23
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
The manual http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html#sysvar_auto_increme... says about calculating auto-increment value when auto_increment_increment is changed on fly: "the next value inserted is the least value in the series that is greater than the maximum existing value in the AUTO_INCREMENT column." The example provided there confirms it.

It works this way with MyISAM, but not quite so with InnoDB. In the test case provided in 'how to repeat' section, the first two auto-increment values are inserted while auto_increment_increment=300, then we change it to 50 and insert two more values. The value of auto_increment_offset remains default 1. 

According to the algorithm described in the manual, first inserted values would be 1,301 -- that's all right.
After the change, the sequence is 1,51,101,151,201,251,301,351,... and the first inserted value would be 351 -- the first value in the sequence that is bigger than the maximum of existing column values. 
However, InnoDB starts with 601 -- that is, it applies the increment 300 once more (or uses a previously generated value), and only after that takes into account the new auto_increment_increment:

CREATE TABLE t ( i INT AUTO_INCREMENT, KEY(i) ) ENGINE=InnoDB;
SET auto_increment_increment = 300;
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL);
SELECT * FROM t;
i
1
301
SET auto_increment_increment = 50;
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL);
SELECT * FROM t;
i
1
301
601
651

Using innodb-autoinc-lock-mode=0 does not change it. 

With MyISAM it works as expected:

CREATE TABLE t ( i INT AUTO_INCREMENT, KEY(i) ) ENGINE=MyISAM;
SET auto_increment_increment = 300;
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL);
SELECT * FROM t;
i
1
301
SET auto_increment_increment = 50;
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL);
SELECT * FROM t;
i
1
301
351
401

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

CREATE TABLE t ( i INT AUTO_INCREMENT, KEY(i) ) ENGINE=InnoDB;
SET auto_increment_increment = 300;
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL);
SELECT * FROM t;
SET auto_increment_increment = 50;
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL);
SELECT * FROM t;
DROP TABLE t;

Thank you for the report.

Verified as described. Problem is not repeatable with version 5.0.

Fixed as of 5.5.37, 5.6.17, 5.7.4 and here's the changelog entry:

Decreasing the "auto_increment_increment" value would have no affect on
the next auto-increment value. 

Thank you for the bug report.

5.5$ bzr log -r 4596 -n0
------------------------------------------------------------
revno: 4596
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.5
timestamp: Mon 2014-02-10 10:13:35 +0530
message:
  Bug #14049391   INNODB MISCALCULATES AUTO-INCREMENT AFTER DECREASING
                          AUTO_INCREMENT_INCREMENT
  Problem:
  =======
  When auto_increment_increment system variable decreases,
  immediate next value of auto increment column is not affected.
  
  Solution:
  ========
  	Get the previous inserted value of auto increment column by
  subtracting the previous auto_increment_increment from next
  auto increment value. After that calculate the current autoinc value
  using newly changed auto_increment_increment variable.
  
  	Approved by Sunny [rb#4394]