| 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: | |
| 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 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.
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.