CODEWITHSUNDEEP
mysqlsql
meet
meet

Reputation: 166

Why mysql innodb table - auto increment column jumps by more that 1 number even if the variables like 'auto_increment_increment' are set as 1?

Why mysql innodb table - auto increment column jumps by more that 1 number even if the variables like 'auto_increment_increment' are set as 1 ?.

Note : no update or delete query is used on the table.

Upvotes: 1

Views: 201

Answers (3)

pryazhnikov
pryazhnikov

Reputation: 3779

Auto_increment value is incremented at INSERT queries too. The trick is that it can be incremented even if no real rows be added. There are several cases and I'll explain them using small demo table:

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (name)
) ENGINE = INNODB;

root@localhost/test> Show table status like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1 <-- Next auto_increment field value
    Create_time: 2016-06-06 14:39:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Case #1: INSERT IGNORE

root@localhost/test> INSERT IGNORE INTO test (name) VALUES ("One");
Query OK, 1 row affected (0.00 sec)

root@localhost/test> INSERT IGNORE INTO test (name) VALUES ("One");
Query OK, 0 rows affected (0.01 sec)

root@localhost/test> SELECT * FROM test ORDER BY id;
+----+------+
| id | name |
+----+------+
|  1 | One  |
+----+------+
1 row in set (0.00 sec)

root@localhost/test> Show table status like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 3
    Create_time: 2016-06-06 14:39:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Case #2: INSERT ... ON DUPLICATE KEY UPDATE

root@localhost/test> INSERT INTO test (name) VALUES ("One") ON DUPLICATE KEY UPDATE name=VALUES(name);
Query OK, 0 rows affected (0.00 sec)

root@localhost/test> SELECT * FROM test ORDER BY id;
+----+------+
| id | name |
+----+------+
|  1 | One  |
+----+------+
1 row in set (0.00 sec)

root@localhost/test> INSERT INTO test (name) VALUES ("One") ON DUPLICATE KEY UPDATE name=VALUES(name);
Query OK, 0 rows affected (0.00 sec)

root@localhost/test> Show table status like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 5
    Create_time: 2016-06-06 14:39:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Case #3: INSERT with errors

root@localhost/test> INSERT INTO test (name) VALUES ("One");
ERROR 1062 (23000): Duplicate entry 'One' for key 'name'

root@localhost/test> Show table status like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 6
    Create_time: 2016-06-06 14:39:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

You can read more about the reasons why these holes are occurred at this article: https://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/ or MySQL documentation: http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes

Upvotes: 2

Niko Hujanen
Niko Hujanen

Reputation: 733

Also INSERT IGNORE and INSERT ... ON DUPLICATE KEY will grow auto increment counter.

Upvotes: 0

Joni
Joni

Reputation: 111239

Rolling back transactions that would insert rows will make the auto increment counter skip values.

Upvotes: 0

Related Questions