MySQL Bugs: #57643: InnoDB skips auto_increment values with LOAD DATA INFILE

Bug #57643	InnoDB skips auto_increment values with LOAD DATA INFILE
Submitted:	22 Oct 2010 2:47	Modified:	17 Dec 2011 18:44
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.41, 5.1.50, 5.5.20	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
InnoDB seems to allocate an extra auto-increment ID, so there is a gap in the sequence.

How to repeat:
Prepare source data:

[baron@ginger 5.1.41]$ cat > load.txt
1,foo,45
1,bar,45
[baron@ginger 5.1.41]$ cat load.txt 
1,foo,45
1,bar,45

Create table and load into the table:

drop table url;
CREATE TABLE `url` (
  `url` int(11) NOT NULL AUTO_INCREMENT,
  `client` smallint(5) unsigned NOT NULL,
  `dest` varchar(5),
  `employee` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`url`)
) ENGINE=InnoDB;
load data local infile 'load.txt' into table url fields terminated by ',' (client, dest, employee);
load data local infile 'load2.txt' into table url fields terminated by ',' (client, dest, employee);
select url from url;

Results:

mysql> drop table url;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE `url` (
    ->   `url` int(11) NOT NULL AUTO_INCREMENT,
    ->   `client` smallint(5) unsigned NOT NULL,
    ->   `dest` varchar(5),
    ->   `employee` smallint(5) unsigned NOT NULL,
    ->   PRIMARY KEY (`url`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> load data local infile 'load.txt' into table url fields terminated by ',' (client, dest, employee);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data local infile 'load2.txt' into table url fields terminated by ',' (client, dest, employee);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select url from url;
+-----+
| url |
+-----+
|   1 |
|   2 |
|   4 |
|   5 |
+-----+
4 rows in set (0.00 sec)

Expected results should be 1,2,3,4.  I have tried a variety of other table structures and I get 1,2,3,4.  Only with this table structure do I see the gap in the rows.

I'm sorry, I just realized that I copy-pasted the wrong thing from my terminal.  Here is the full source data in two files:

[baron@ginger 5.1.41]$ cat load.txt
1,foo,45
1,bar,45
[baron@ginger 5.1.41]$ cat load2.txt
1,boo,45
1,biz,45

Please, send the results of:

show global variables like 'innodb_autoinc_lock_mode';

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

We are experiencing this same bug. I ran the "SHOW GLOBAL VARIABLES LIKE  'innodb_autoinc_lock_mode'" command on our server.

innodb_autoinc_lock_mode	1

Brian

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.17-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >show variables like "innodb_autoinc_lock_mode";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 0     |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql 5.5 >CREATE TABLE `url` (
    ->   `url` int(11) NOT NULL AUTO_INCREMENT,
    ->   `client` smallint(5) unsigned NOT NULL,
    ->   `dest` varchar(5),
    ->   `employee` smallint(5) unsigned NOT NULL,
    ->   PRIMARY KEY (`url`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql 5.5 >load data infile 'c:/dbs/load.txt' into table url fields terminated by ',' (client, dest,
    -> employee);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql 5.5 >
mysql 5.5 >load data infile 'c:/dbs/load2.txt' into table url fields terminated by ',' (client, dest,
    -> employee);
Query OK, 2 rows affected (0.10 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql 5.5 >select url from url;
+-----+
| url |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
+-----+
4 rows in set (0.00 sec)

mysql 5.5 >

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc...

See http://bugs.mysql.com/bug.php?id=62612.

We still see this problem:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "innodb_autoinc_lock_mode";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `url` (
    ->   `url` int(11) NOT NULL AUTO_INCREMENT,
    ->   `client` smallint(5) unsigned NOT NULL,
    ->   `dest` varchar(5),
    ->   `employee` smallint(5) unsigned NOT NULL,
    ->   PRIMARY KEY (`url`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> load data infile '/Users/openxs/dbs/5.5/load.txt' into table url fields terminated by ',' (client, dest, employee);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile '/Users/openxs/dbs/5.5/load2.txt' into table url fields terminated by ',' (client, dest, employee);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from url;
+-----+--------+------+----------+
| url | client | dest | employee |
+-----+--------+------+----------+
|   1 |      1 | foo  |       45 |
|   2 |      1 | bar  |       45 |
|   4 |      1 | boo  |       45 |
|   5 |      1 | biz  |       45 |
+-----+--------+------+----------+
4 rows in set (0.00 sec)

While setting innodb_autoinc_lock_mode to 0 is a workaround for some cases, value 1 should still give us consecutive values:

"This lock mode ensures that, in the presence of INSERT statements where the number of rows is not known in advance (and where auto-increment numbers are assigned as the statement progresses), all auto-increment values assigned by any “INSERT-like” statement are consecutive, and operations are safe for statement-based replication. "

I don't believe this is a bug, but an implementation detail. Let's see if I can explain...

InnoDB skips values to the next highest power of 2 up to an unknown point - this appears to be true for loading data file performance reasons.

The above comments show an insert of 2 rows, the next hihger power of 2 is 4, and that's exactly what the next auto-increment value shows.

The data suggest that the developers pre-allocate auto-increment ids in successively larger blocks (powers of 2) up to some value as the data is loaded.

My quick testing shows that loading 332 rows the first time you run load data into an empty table, the next auto-increment value for that table will be 512, further testing shows that loading 16385 (2^14 + 1) rows into an empty table sets the next auto-increment to 32768 (2^15) as expected.

I'm not sure that auto-increment is guaranteed to be packed sequential - just that it is to be unique.

it's still happening in 

mysql  Ver 14.14 Distrib 5.5.29, for debian-linux-gnu (x86_64) using readline 6.2

the only workaround is to use innodb_autoinc_lock_mode=0.

Is it going to be fixed at all?

I suggest that it is "not a bug".  I believe that there is nothing saying that AUTO_INCREMENT values must avoid wasting ids.  Sure, it is annoying.

There are many other cases of 'burned' ids:
* Rolled back Transaction.
* INSERT IGNORE with multiple rows (and other INSERTs/REPLACEs).

An un-burned id that is annoying in a different direction:
1. DELETE the row with MAX(id)
2. Restart the server
3. Now that id will be reused (InnoDB only?).

>I suggest that it is "not a bug".  I believe that there is nothing saying that >AUTO_INCREMENT values must avoid wasting ids.  Sure, it is annoying.

>There are many other cases of 'burned' ids:
>* Rolled back Transaction.
>* INSERT IGNORE with multiple rows (and other INSERTs/REPLACEs).

I did not use any of the above.

>An un-burned id that is annoying in a different direction:
>1. DELETE the row with MAX(id)
>2. Restart the server
>3. Now that id will be reused (InnoDB only?).

It's not really useful to restart server each time, especially if it's used by applications and have large number of dirty_pages.

I think the behavior of AUTO_INCREMENT in innodb_autoinc_lock_mode=1 if there is only 1 client accessing the table should be the same as on innodb_autoinc_lock_mode=0, otherwise 'burned' ids situation is completely normal I think.

Btw, there is another workaround is to alter table AUTO_INCREMENT COUNTER to 1, so it resets to the maximum number of records:

ALTER TABLE table AUTO_INCREMENT=1

after that table's AUTO_INCREMENT resets to MAX+1 value of PRIMARY KEY

Testing MySQL old version with mysql 5.0 series as belown:

-bash-3.1# more /tmp/a.txt
"aa","11","aa1"
"bb","22","bb2"
 
ruochen 0:12:43 
root@localhost:test 00:10:47>show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(10) default NULL,
  `id1` int(11) NOT NULL,
  `name1` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@localhost:test 00:10:51>load data infile '/tmp/a.txt' into table a columns terminated by ',' enclosed by '"' lines terminated by '\n'(name,id1,name1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

root@localhost:test 00:10:55>show create table a\G                              *************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(10) default NULL,
  `id1` int(11) NOT NULL,
  `name1` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@localhost:test 00:10:57>select * from a;
+----+------+-----+-------+
| id | name | id1 | name1 |
+----+------+-----+-------+
|  1 | aa   |  11 | aa1   |
|  2 | bb   |  22 | bb2   |
|  3 | aa   |  11 | aa1   |
|  4 | bb   |  22 | bb2   |
|  5 | aa   |  11 | aa1   |
|  6 | bb   |  22 | bb2   |
+----+------+-----+-------+
6 rows in set (0.00 sec)

root@localhost:test 00:11:01>select version();
+-----------+
| version() |
+-----------+
| 5.0.77    |
+-----------+
1 row in set (0.00 sec)

root@localhost:test 23:55:57>show global variables like '%mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

root@localhost:test 23:55:59>show global variables like '%inc%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 1     |
| auto_increment_offset       | 1     |
| div_precision_increment     | 4     |
| innodb_autoextend_increment | 8     |
+-----------------------------+-------+
4 rows in set (0.00 sec)

The problem is still repeatable with recent 5.6:

mysql> CREATE TABLE `url` (
    ->   `url` int(11) NOT NULL AUTO_INCREMENT,
    ->   `client` smallint(5) unsigned NOT NULL,
    ->   `dest` varchar(5),
    ->   `employee` smallint(5) unsigned NOT NULL,
    ->   PRIMARY KEY (`url`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.26 sec)

mysql> load data local infile '/tmp/load.txt' into table url fields terminated by ',' (client, dest, employee);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data local infile '/tmp/load2.txt' into table url fields terminated by ',' (client, dest, employee);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from url;
+-----+--------+------+----------+
| url | client | dest | employee |
+-----+--------+------+----------+
|   1 |      1 | foo  |       45 |
|   2 |      1 | bar  |       45 |
|   4 |      1 | boo  |       45 |
|   5 |      1 | biz  |       45 |
+-----+--------+------+----------+
4 rows in set (0.00 sec)

mysql> select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> \! cat /tmp/load.txt
1,foo,45
1,bar,45
mysql> \! cat /tmp/load2.txt
1,boo,45
1,biz,45