| Bug #45960 | LOAD DATA generates incorrect auto-increment counter on InnoDB | ||
|---|---|---|---|
| Submitted: | 6 Jul 2009 7:11 | Modified: | 11 Sep 2009 5:13 |
| Reporter: | Mikiya Okuno | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1.36 | OS: | Any |
| Assigned to: | Sunny Bains | CPU Architecture: | Any |
[6 Jul 2009 13:32]
Mikhail Izioumtchenko
please provide MySQL version and the contents of your my.cnf file
[6 Jul 2009 13:40]
MySQL Verification Team
Hi Michael, I updated the version field on this bug, and my.cnf for the system which I verified the bug, a test system, follows: [mysqld] datadir=/var/lib/mysql port=3306 socket=/tmp/mysql.sock key_buffer=8M slow-query-log log-bin=mysql-bin log_warnings=2 lower_case_table_names=2 expire-logs-days=1 thread_cache_size=5 max_connections=1200 table_open_cache=2400 character_set_server=utf8 core-file open_files_limit=1000 sysdate-is-now innodb_flush_log_at_trx_commit=2 innodb_buffer_pool_size=100M innodb_status_file=1 innodb_log_file_size=64M innodb_log_files_in_group=2 Kind regards, -- Mikiya
[20 Jul 2009 16:52]
Martin Karm
I am experiencing a similar bug, but the affected column is NOT NULL. Version: 5.1.34-community To recreate: I use the following script along with a text file containing two columns of tab spaced data. DROP TABLE IF EXISTS test; CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data1` varchar(50) DEFAULT NULL, `data2` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ); LOAD DATA LOCAL INFILE 'X://data1.txt' into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (`data1`, `data2`); LOAD DATA LOCAL INFILE 'X://data1.txt' into table test FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (`data1`, `data2`); I noticed that for every subsequent LOAD DATA statement the `id` field continues to jump. The amount it jumps may depend on the number of records being inserted. Some values I've found: 11 records resulted in a jump of 5. 280 records resulted in a jump of 232. This is my first comment on a bug report, so please let me know if more information is needed.
[11 Sep 2009 5:13]
Sunny Bains
Hi,
When doing BULK inserts using the test provided, MySQL first requests 1 value
followed by 2 values in the second request. With the faster :
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
setting, InnoDB always updates the next level counter before the insert. This
will result in some padding towards the end. However as the documentation states
it will not result in gaps during the bulk load if there are not other
interleaved autoinc requests.
See GDB output below and nb_desired_values argument.
Breakpoint 1, ha_innobase::get_auto_increment (this=0x17f1220, offset=1,
increment=1, nb_desired_values=1, first_value=0x46dbfc10,
nb_reserved_values=0x46dbfc08) at handler/ha_innodb.cc:8635
8635 ulonglong autoinc = 0;
(gdb) c
Continuing.
Breakpoint 1, ha_innobase::get_auto_increment (this=0x17f1220, offset=1,
increment=1, nb_desired_values=2, first_value=0x46dbfc10,
nb_reserved_values=0x46dbfc08) at handler/ha_innodb.cc:8635
8635 ulonglong autoinc = 0;
Regards,
-sunny
Description: If AUTO_INCREMENT column is NULL, InnoDB badly increments auto-inc counter after LOAD DATA command. How to repeat: 1. Create a file contains the following two lines: shell> cat data.txt \N \N 2. Read this file using LOAD DATA command like below: mysql> create table inctest (inc_col bigint unsigned not null primary key auto_increment) engine innodb; Query OK, 0 rows affected (0.06 sec) mysql> load data infile 'inctest.txt' into table inctest; Query OK, 2 rows affected (0.01 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> show create table inctest\G *************************** 1. row *************************** Table: inctest Create Table: CREATE TABLE `inctest` ( `inc_col` bigint(20) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`inc_col`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) You see AUTO_INCREMENT=4 instead of 3! OTOH the table contains values like below: mysql> select * from inctest; +---------+ | inc_col | +---------+ | 1 | | 2 | +---------+ 2 rows in set (0.00 sec) Suggested fix: Please fix the above and prevent auto-inc counter from increasing unnecessarily.