| Bug #47933 | auto_increment | ||
|---|---|---|---|
| Submitted: | 9 Oct 2009 8:53 | Modified: | 20 Oct 2009 19:20 |
| Reporter: | Xu Faniu | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.1.39 | OS: | Linux |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | auto_increment | ||
[9 Oct 2009 9:44]
Valeriy Kravchuk
This is NOT a bug. You should just use proper LINES TERMINATED BY clause. Look:
mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)
mysql> load data local infile "c:\\tmp\\work\\47933.txt" ignore into table test
lines terminated by '\n';
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | content |
+----+---------+
|| abc
|| xyz
|| www
| | http
|5 | thank
|| bye
+----+---------+
6 rows in set (0.02 sec)
mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)
mysql> load data local infile "c:\\tmp\\work\\47933.txt" ignore into table test
lines terminated by '\r\n';
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | content |
+----+---------+
| 1 | abc |
| 2 | xyz |
| 3 | www |
| 4 | http |
| 5 | thank |
| 6 | bye |
+----+---------+
6 rows in set (0.00 sec)
You can check that id's are generated properly by:
select id from test;
after loading. So, proper descrition of line endings matters. Read http://dev.mysql.com/doc/refman/5.1/en/load-data.html.
[9 Oct 2009 10:50]
Xu Faniu
[root@localhost ~]# cat /tmp/data.txt 0 abc 0 xyz 0 www 0 http 0 thank 0 bye mysql> create table test(id int auto_increment primary key, content varchar(20)) engine = InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> load data local infile "/tmp/data.txt" ignore into table test lines terminated by '\r\n'; Query OK, 6 rows affected (0.00 sec) Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from test; +----+---------+ | id | content | +----+---------+ | 1 | abc | | 2 | xyz | | 3 | www | | 4 | http | | 5 | thank | | 6 | bye | +----+---------+ 6 rows in set (0.00 sec) mysql> load data local infile "/tmp/data.txt" ignore into table test lines terminated by '\r\n'; Query OK, 6 rows affected (0.04 sec) Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from test; +----+---------+ | id | content | +----+---------+ | 1 | abc | | 2 | xyz | | 3 | www | | 4 | http | | 5 | thank | | 6 | bye | | 8 | abc | | 9 | xyz | | 10 | www | | 11 | http | | 12 | thank | | 13 | bye | +----+---------+ 12 rows in set (0.01 sec) ****************************************************************** ****************************************************************** There are 12 rows, but the max id is 13. I can't find the row where id is 7, is it a bug?
[9 Oct 2009 11:54]
Valeriy Kravchuk
Now I've got your point. Indeed, this looks like a bug:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.39-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table test;
Query OK, 0 rows affected (0.25 sec)
mysql> create table test(id int auto_increment primary key, content
-> varchar(20)) engine = InnoDB;
Query OK, 0 rows affected (0.58 sec)
mysql> load data local infile "c:\\tmp\\work\\47933.txt" ignore into table test
lines terminated by '\r\n';
Query OK, 6 rows affected (0.39 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | content |
+----+---------+
| 1 | abc |
| 2 | xyz |
| 3 | www |
| 4 | http |
| 5 | thank |
| 6 | bye |
+----+---------+
6 rows in set (0.09 sec)
mysql> load data local infile "c:\\tmp\\work\\47933.txt" ignore into table test
lines terminated by '\r\n';
Query OK, 6 rows affected (0.03 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | content |
+----+---------+
| 1 | abc |
| 2 | xyz |
| 3 | www |
| 4 | http |
| 5 | thank |
| 6 | bye |
| 8 | abc |
| 9 | xyz |
| 10 | www |
| 11 | http |
| 12 | thank |
| 13 | bye |
+----+---------+
12 rows in set (0.00 sec)
Now, let's compare to MyISAM:
mysql> alter table test engine=MyISAM;
Query OK, 12 rows affected (0.41 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)
mysql> load data local infile "c:\\tmp\\work\\47933.txt" ignore into table test
lines terminated by '\r\n';
Query OK, 6 rows affected (0.05 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | content |
+----+---------+
| 1 | abc |
| 2 | xyz |
| 3 | www |
| 4 | http |
| 5 | thank |
| 6 | bye |
+----+---------+
6 rows in set (0.00 sec)
mysql> load data local infile "c:\\tmp\\work\\47933.txt" ignore into table test
lines terminated by '\r\n';
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | content |
+----+---------+
| 1 | abc |
| 2 | xyz |
| 3 | www |
| 4 | http |
| 5 | thank |
| 6 | bye |
| 7 | abc |
| 8 | xyz |
| 9 | www |
| 10 | http |
| 11 | thank |
| 12 | bye |
+----+---------+
12 rows in set (0.00 sec)
mysql> exit
Bye
And, finally, this is a regression bug, as 5.0.x works as expected:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.0.85-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table test;
Query OK, 0 rows affected (0.13 sec)
mysql> create table test(id int auto_increment primary key, content
-> varchar(20)) engine = InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql> load data local infile "c:\\tmp\\work\\47933.txt" ignore into table test
lines terminated by '\r\n';
Query OK, 6 rows affected (0.05 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | content |
+----+---------+
| 1 | abc |
| 2 | xyz |
| 3 | www |
| 4 | http |
| 5 | thank |
| 6 | bye |
+----+---------+
6 rows in set (0.00 sec)
mysql> load data local infile "c:\\tmp\\work\\47933.txt" ignore into table test
lines terminated by '\r\n';
Query OK, 6 rows affected (0.03 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | content |
+----+---------+
| 1 | abc |
| 2 | xyz |
| 3 | www |
| 4 | http |
| 5 | thank |
| 6 | bye |
| 7 | abc |
| 8 | xyz |
| 9 | www |
| 10 | http |
| 11 | thank |
| 12 | bye |
+----+---------+
12 rows in set (0.00 sec)
[9 Oct 2009 12:15]
Peter Laursen
@Valeriy .. are you sure. I thought the same when I reported: http://bugs.mysql.com/47933 Sveta directed me to: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html .. but I won't claim I understand this. Probably nobody does if he is not a MySQL developer. MySQL documentation at its worst (and that is bad!). However LOAD DATA and BULK INSERTS behave different, what I do not find documented: TRUNCATE TABLE test; INSERT INTO test VALUES (0,'a'), (0,'b'); INSERT INTO test VALUES (0,'x'), (0,'y'); SELECT * FROM test; /* 3 is not skipped here - result is: id content ------ ------- 1 a 2 b 3 x 4 y */
[9 Oct 2009 12:33]
Valeriy Kravchuk
Yeah, this is all because of innodb_autoinc_lock_mode=1 by default now in 5.1, so semantics is different comparing to 5.0 :( This can be easily demonstrated:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table test;
Query OK, 0 rows affected (0.03 sec)
mysql> create table test(id int auto_increment primary key, content
-> varchar(20)) engine = InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql> load data local infile "c:\\tmp\\work\\47933.txt" ignore into table test
lines terminated by '\r\n';
Query OK, 6 rows affected (0.03 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | content |
+----+---------+
| 1 | abc |
| 2 | xyz |
| 3 | www |
| 4 | http |
| 5 | thank |
| 6 | bye |
+----+---------+
6 rows in set (0.00 sec)
mysql> load data local infile "c:\\tmp\\work\\47933.txt" ignore into table test
lines terminated by '\r\n';
Query OK, 6 rows affected (0.03 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | content |
+----+---------+
| 1 | abc |
| 2 | xyz |
| 3 | www |
| 4 | http |
| 5 | thank |
| 6 | bye |
| 7 | abc |
| 8 | xyz |
| 9 | www |
| 10 | http |
| 11 | thank |
| 12 | bye |
+----+---------+
12 rows in set (0.00 sec)
mysql> show variables like '%autoinc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 0 |
+--------------------------+-------+
1 row in set (0.00 sec)
I think http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html should be clarified and illusrated with proper examples.
[10 Oct 2009 2:27]
Xu Faniu
mysql> show variables like '%autoinc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 0 | +--------------------------+-------+ When innodb_autoinc_lock_mode is 0, it performances right. While is 1, some auto-increment values are lost. Is it a bug? I want to know why some auto-increment values are lost. Please help me, thank you!
[14 Oct 2009 9:39]
Stefan Hinz
It's on Paul's list to rework the AUTO_INCREMENT documentation. Since this is on his short-term agenda I'm setting this bug report to "to be fixed later", and will leave it to Paul to close it once the reworking has been done. Here's what he will do: AUTO_INCREMENT coverage in the Reference Manual has some shortcomings. Right now it's mostly buried in a section in the tutorial chapter. A better place would be the data types chapter. This material should provide more information. It should cover how auto-increment value generation works for different storage engines: which ones support it at all, which ones allow multiple-column indexes containing an AUTO_INCREMENT column, whether values get reused after deletion from the top of the sequence, that kind of thing.
[16 Oct 2009 18:16]
Paul DuBois
From Sunny Bains:
This is expected behavior. With lock mode 1 and 2 InnoDB will allocate values
in blocks and update the global counter at the time of the allocation. For
lock mode 0 the global counter is updated at the end of the statement (or
when the autoinc lock is released). In the former case, the number of values
allocated doesn't necessarily reflect the exact number of values that are
required. The number of values to reserve actually comes from MySQL, see
below:
UNIV_INTERN
void
ha_innobase::get_auto_increment(
/*============================*/
ulonglong offset, /*!< in: table autoinc offset */
ulonglong increment, /*!< in: table autoinc increment */
ulonglong nb_desired_values, /*!< in: number of values reqd */
ulonglong *first_value, /*!< out: the autoinc value */
ulonglong *nb_reserved_values) /*!< out: count of reserved values
For certain types of statements MySQL can only guess and LOAD DATA is one
such type of statement. The gaps are as a result of that.
[20 Oct 2009 19:20]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
Description: I have found that if a table has a auto_increment column, load data operation will skip some auto increment values: /tmp/data.txt 0 abc 0 xyz 0 www 0 http 0 thank 0 bye mysql> create table test(id int auto_increment primary key, content varchar(20)) engine = InnoDB; Query OK, 0 rows affected (0.04 sec) mysql> load data local infile "/tmp/data.txt" ignore into table test; Query OK, 6 rows affected (0.00 sec) Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from test; +----+---------+ | id | content | +----+---------+ || abc || xyz || www | | http |5 | thank | 6 | bye | +----+---------+ 6 rows in set (0.00 sec) mysql> load data local infile "/tmp/data.txt" ignore into table test; Query OK, 6 rows affected (0.00 sec) Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from test; +----+---------+ | id | content | +----+---------+ || abc || xyz || www | | http |5 | thank | 6 | bye | || abc || xyz || www | | http |2 | thank | 13 | bye | +----+---------+ I can't find some id in table test. In addition, I have insert 12 rows, but now the max id is not 12, but 13. why? Who can help me? How to repeat: You can find this problme when the engine is InnoDB.