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

[17 Feb 2015 22:11] atsushi MAEDA
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.
[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.