Description:
In some cases, a table's AUTO_INCREMENT counter value will be set to [previous value + 1] when auto_increment_increment is set to larger than one.
See test case below, which has comments for each case, first test shows that inserting a value explicitly sets it incorrectly, second test shows that resetting it to auto detected value (alter table auto_increment=1) also fails, third test shows that it updates correctly when letting the sequence generate the value on a new insert (the main expected operation).
This is lower severity because actual inserts do check and pick the next valid value to write if the one in the sequence counter is wrong, as shown by the write between cases 2 and 3, but anything reading table metadata will get this odd impossible value for its auto increment counter.
How to repeat:
mysql> select @@global.auto_increment_offset,@@global.auto_increment_increment;
+--------------------------------+-----------------------------------+
| @@global.auto_increment_offset | @@global.auto_increment_increment |
+--------------------------------+-----------------------------------+
| 3 | 10 |
+--------------------------------+-----------------------------------+
1 row in set (0.00 sec)
mysql> create table test (id int unsigned not null auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test set id=10000+@@global.auto_increment_offset;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+-------+
| id |
+-------+
| 10003 |
+-------+
1 row in set (0.00 sec)
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> /* that should say AUTO_INCREMENT=10013 */
mysql>
mysql>
mysql> update test set id=5000+@@global.auto_increment_offset;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> alter table test auto_increment=1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test;
+------+
| id |
+------+
| 5003 |
+------+
1 row in set (0.00 sec)
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5004 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> /* that should say AUTO_INCREMENT=10013 */
mysql>
mysql>
mysql> insert into test set id=null;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+
| id |
+------+
| 5003 |
| 5013 |
+------+
2 rows in set (0.00 sec)
mysql> /* the insert does get the correct value vs using the table's counter, which is good */
mysql>
mysql>
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5023 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> /* that time it is correct */
mysql>
mysql>
Suggested fix:
for anything that updates the counter, ensure it sets a possible value, by including increment/offset the way normal inserts do.
Description: In some cases, a table's AUTO_INCREMENT counter value will be set to [previous value + 1] when auto_increment_increment is set to larger than one. See test case below, which has comments for each case, first test shows that inserting a value explicitly sets it incorrectly, second test shows that resetting it to auto detected value (alter table auto_increment=1) also fails, third test shows that it updates correctly when letting the sequence generate the value on a new insert (the main expected operation). This is lower severity because actual inserts do check and pick the next valid value to write if the one in the sequence counter is wrong, as shown by the write between cases 2 and 3, but anything reading table metadata will get this odd impossible value for its auto increment counter. How to repeat: mysql> select @@global.auto_increment_offset,@@global.auto_increment_increment; +--------------------------------+-----------------------------------+ | @@global.auto_increment_offset | @@global.auto_increment_increment | +--------------------------------+-----------------------------------+ | 3 | 10 | +--------------------------------+-----------------------------------+ 1 row in set (0.00 sec) mysql> create table test (id int unsigned not null auto_increment primary key); Query OK, 0 rows affected (0.01 sec) mysql> insert into test set id=10000+@@global.auto_increment_offset; Query OK, 1 row affected (0.00 sec) mysql> select * from test; +-------+ | id | +-------+ | 10003 | +-------+ 1 row in set (0.00 sec) mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> /* that should say AUTO_INCREMENT=10013 */ mysql> mysql> mysql> update test set id=5000+@@global.auto_increment_offset; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> alter table test auto_increment=1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from test; +------+ | id | +------+ | 5003 | +------+ 1 row in set (0.00 sec) mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5004 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> /* that should say AUTO_INCREMENT=10013 */ mysql> mysql> mysql> insert into test set id=null; Query OK, 1 row affected (0.00 sec) mysql> select * from test; +------+ | id | +------+ | 5003 | | 5013 | +------+ 2 rows in set (0.00 sec) mysql> /* the insert does get the correct value vs using the table's counter, which is good */ mysql> mysql> mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5023 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> /* that time it is correct */ mysql> mysql> Suggested fix: for anything that updates the counter, ensure it sets a possible value, by including increment/offset the way normal inserts do.