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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.