| Bug #75941 | auto_increment value jumps from the max row value - 2 to max | ||
|---|---|---|---|
| Submitted: | 17 Feb 2015 22:11 | Modified: | 9 Mar 2015 21:26 |
| Reporter: | atsushi MAEDA | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6.16,5.5.40 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | auto_increment | ||
[18 Feb 2015 12:54]
Peter Laursen
I think you should read this carefully http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_m... There have been lots of similar reports here before (I posted such myself 4-5 years ago). There is no guarantee that there will not be 'holes' in autoincrement enumeration. This is not what 'auto_increment' means. It "increments", but is not guaranteed to always "increment by one". But if you set 'innodb_autoinc_lock_mode' to "0" it will rarely happen - if at all - that such 'holes' occur. -- Peter -- not a MySQL/Oracle person.
[20 Feb 2015 20:30]
Sveta Smirnova
Thank you for the report. Verified as described. Issue is repeatable with any value of innodb_autoinc_lock_mode. Here there is no rolled-backed transaction, no concurrency, no bulk inserts, all statements are sequential, so I don't see any reason for gap to occur.
[22 Feb 2015 8:40]
atsushi MAEDA
Thank you for comments and suggestions! I Sveta described above, I also still think this is a bug, and still checking a code and try to suggest code fix. I'm sorry to take time. (this is the first time to try fix on mysql)
[9 Mar 2015 16:14]
atsushi MAEDA
following fix seems to work well
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index b8f6351..6963569 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -1976,7 +1976,7 @@ innobase_next_autoinc(
ulonglong free = max_value - current;
- if (free < offset || free - offset <= block) {
+ if (free < offset || free - offset < block) {
next_value = max_value;
} else {
next_value = 0;
[9 Mar 2015 21:26]
atsushi MAEDA
Hi, I confirmed related test below pass after fix. [01:27:14 taka-h@taka-h mysql-server]$ ls -la mysql-test/suite/innodb/r/innodb-autoinc* -rw-r--r-- 1 taka-h staff 805 2 17 22:46 mysql-test/suite/innodb/r/innodb-autoinc-18274.result -rw-r--r-- 1 taka-h staff 722 2 17 23:12 mysql-test/suite/innodb/r/innodb-autoinc-44030.result -rw-r--r-- 1 taka-h staff 847 2 17 22:46 mysql-test/suite/innodb/r/innodb-autoinc-56228.result -rw-r--r-- 1 taka-h staff 321 2 17 22:46 mysql-test/suite/innodb/r/innodb-autoinc-optimize.result -rw-r--r-- 1 taka-h staff 36658 2 17 23:12 mysql-test/suite/innodb/r/innodb-autoinc.result
[16 Mar 2015 14:00]
atsushi MAEDA
innodb_bg75941.test
Attachment: innodb_bug75941.test (application/octet-stream, text), 401 bytes.
[16 Mar 2015 14:00]
atsushi MAEDA
innodb_bug75941.result
Attachment: innodb_bug75941.result (application/octet-stream, text), 364 bytes.
[16 Mar 2015 14:44]
atsushi MAEDA
innodb_bug75941.test
Attachment: innodb_bug75941.test (application/octet-stream, text), 443 bytes.
Description: auto_increment value jumps from the max row value - 2 to max For TINYINT(-128 to 127) column, auto_increment jumps from 125 to 127. mysql> select * from test.test order by id desc limit 3; +-----+ | id | +-----+ | 127 | | 125 | | 124 | +-----+ How to repeat: mysql> CREATE DATABASE IF NOT EXISTS test; Query OK, 1 row affected (0.00 sec) mysql> use test; mysql> CREATE TABLE test (id TINYINT AUTO_INCREMENT, PRIMARY KEY (id)); Query OK, 0 rows affected (0.01 sec) [00:22:00 taka-h@taka-h ~]$ for i in {1..124}; do mysql -u root -proot -e "INSERT INTO test.test SET id=NULL"; done mysql> select * from test.test order by id desc limit 3; +-----+ | id | +-----+ | 124 | | 123 | | 122 | +-----+ mysql> show table status like 'test'\G *************************** 1. row *************************** Name: test Engine: InnoDB Version: 10 Row_format: Compact Rows: 124 Avg_row_length: 132 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 125 Create_time: 2015-02-17 13:30:20 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> INSERT INTO test.test set id=NULL; Query OK, 1 row affected (0.00 sec) mysql> show table status like 'test'\G *************************** 1. row *************************** Name: test Engine: InnoDB Version: 10 Row_format: Compact Rows: 125 Avg_row_length: 131 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 127 Create_time: 2015-02-17 13:30:20 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> INSERT INTO test.test set id=NULL; Query OK, 1 row affected (0.00 sec) Suggested fix: I' checking the code.