Bug #71122 auto_increment doesn't update after changing auto_increment_increment/offset
Submitted: 10 Dec 2013 0:27 Modified: 19 Nov 2015 16:33
Reporter: Nahuel Sotelo Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:5.5.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: auto_increment, auto_increment_increment, auto_increment_offset

[10 Dec 2013 0:27] Nahuel Sotelo
Description:
After changing the auto_increment_increment (AII) and/or auto_increment_offset (AIO) on the server, the tables doesn't return well the next auto_increment value. This happens with MyISAM engine.

E.g: you have a table A (tA) with a "id" field and table B (tB) with fields "id" and "tablea_id" which contains ids related to tA.

At the moment you use AII and AIO as default, so the auto_increment goes one by one. But then, for applying replications-options, you need to keep those ids but to get the new entries by the new AII and AIO options. Auto_increment is tA works fine, BUT in the process of creating the tB entries, when I query to the information_schema of tA the returned auto_increment is not the right one based on the new AII and AIO settings but it is the common "one by one" auto_increment. That causes that when I get the auto_increment from tA -which is wrong- and save that value on tB I lost the relation between entries from tB and the main entrie from tA.

How to repeat:
For the repeat, just need table_a. So...

----
0 - auto_increment_increment/offset with default values.
----

----
1 - Create the schema and the table_a
----

DROP SCHEMA IF EXISTS `test2`;

CREATE SCHEMA `test2`;

USE `test2`;

CREATE TABLE `table_a` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `table_b` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_b` INT NULL,
  PRIMARY KEY (`id`));

INSERT INTO table_a(name) VALUES("Foo"), ("Bar"), ("John"), ("Jesus"), ("Oh"), ("My"), ("Gosh");

----
2 - See the table entries
----

SELECT * FROM table_a

id	name
1	Foo
2	Bar
3	John
4	Jesus
5	Oh
6	My
7	Gosh

----
3 - Check the AI by checking information_schema
----

SELECT AUTO_INCREMENT FROM `information_schema`.`tables` WHERE TABLE_SCHEMA = "test2" AND TABLE_NAME = "table_a";

AUTO_INCREMENT
8

----
4 - Show table a status (just to be sure)
----

SHOW TABLE STATUS LIKE 'table_a'

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
table_a	InnoDB	10	Compact	7	2340	16384	0	0	5242880	8	"2013-12-09 21:08:11"	NULL	NULL	latin1_swedish_ci	NULL		

Again, auto_increment 8

----
5 - Change auto_increment_increment to 10 and auto_increment_offset to 1.
6 - Reset mysql service
7 - By doing this, should be desirable that table_a auto_increment be 11.
----

----
8 - Do 3 and 4 again. You'll get the same results as before.
9 - You can think that it's okay, that table_a would need to be repaired, so you do...

REPAIR TABLE table_a

10 - Check again. Same results, auto_increment stills being 8.

11 - Well, maybe adding an entry, the auto_increment get fixed. So...

INSERT INTO table_a(name) VALUE("new");

12 - The entry is inserted fine, with the right auto_increment value. Just check it:

SELECT * FROM table_a

id	name
1	Foo
2	Bar
3	John
4	Jesus
5	Oh
6	My
7	Gosh
11	new

----

13 - But if you want to get the next auto_increment -that should be 21-, you now will get 12 (11 + 1 and not 11+10).
So, if you repeat 3 and 4, you'll see auto_increment 12.

----

This is a problem when you want to create other entries in other tables that depends on the table_a.id. A quick solution could be to insert the entry in the table_a first, but I read in many places that this is not recommended.
I look for this problem over the internet and even in the bug forums here, but nothing related.

Hope you can help me and this can be fixed.

Suggested fix:
To return the correct auto_increment, based on the new auto_increment_increment and auto_increment_offset configurations.
[10 Dec 2013 19:19] MySQL Verification Team
This two variables affect tables only at table creation. Later changes will only affect new tables that are yet to be created.

If you find that this is not functional, please provide a test case.
[10 Dec 2013 19:29] Nahuel Sotelo
I've already provided a test case! haha
[11 Dec 2013 19:14] MySQL Verification Team
What I wrote that this would be a bug if you provided a test case where table would be created AFTER auto_increment_increment/_offset were changed.
[11 Dec 2013 19:16] Nahuel Sotelo
So, this is not a bug?

What is it then?

A feature request?

It would be desirable that the AUTO_INCREMENT would return automatically the right value...

I don't say to be done automatically after the my.cnf is changed but maybe through performing a REPAIR TABLE or something like that.

What do you say about that?
[16 Dec 2013 17:21] Nahuel Sotelo
Hey guys, I have a database with relational ids and so, I cannot re-create the tables again. That would be a very risky job. It would be easier if the auto_increment option of the table would update automatically to the new increment/offser without having to re-create the tables...
[19 Nov 2015 16:33] Georgi Kodinov
Thank you for the reasonable feature request to take into account these two variables when calculating the I_S values.