Bug #12053 LOAD DATA INFILE ignores NO_AUTO_VALUE_ON_ZERO setting
Submitted: 20 Jul 2005 13:34 Modified: 23 Sep 2005 17:11
Reporter: Stanislav Malyshev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.11/4.1.14 BK OS:Linux (Linux)
Assigned to: Sergei Glukhov CPU Architecture:Any

[20 Jul 2005 13:34] Stanislav Malyshev
Description:
When NO_AUTO_VALUE_ON_ZERO sql mode is set, LOAD DATA INFILE still converts zero values from the file into next value in sequence for the auto_increment fields. This means that it is impossible to dump such table with SELECT OUTFILE and then reload it with LOAD DATA INFILE with any settings. 

How to repeat:
create table test_bug (id integer not null auto_increment primary key);

select @@sql_mode;
+-----------------------+
| @@sql_mode            |
+-----------------------+
| NO_AUTO_VALUE_ON_ZERO |
+-----------------------+

insert into test_bug values(0);
delete from test_bug;
SELECT * INTO OUTFILE '/tmp/test_bug' from test_bug;
load data infile '/tmp/test_bug' into table test_bug;
mysql> select * from test_bug;
+----+
| id |
+----+
|  1 |
+----+
delete from test_bug;
load data infile '/tmp/test_bug' into table test_bug;
select * from test_bug;
+----+
| id |
+----+
|  2 |
+----+

In /tmp/test_bug is:
# cat /tmp/test_bug
0
[20 Jul 2005 13:37] Stanislav Malyshev
correction - the right order of queries was:
insert into test_bug values(0);
SELECT * INTO OUTFILE '/tmp/test_bug' from test_bug;
delete from test_bug;
load data infile '/tmp/test_bug' into table test_bug;
[20 Jul 2005 14:16] MySQL Verification Team
mysql> select @@sql_mode;
+-----------------------+
| @@sql_mode            |
+-----------------------+
| NO_AUTO_VALUE_ON_ZERO |
+-----------------------+
1 row in set (0.00 sec)

mysql> create table test_bug (id integer not null auto_increment primary key);
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> insert into test_bug values(0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * INTO OUTFILE '/tmp/test_bug' from test_bug;
Query OK, 1 row affected (0.01 sec)

mysql> delete from test_bug;
Query OK, 1 row affected (0.00 sec)

mysql> load data infile '/tmp/test_bug' into table test_bug;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test_bug;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.14-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
miguel@hegel:~/work> cat /tmp/test_bug
0
[20 Sep 2005 8:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30091
[22 Sep 2005 7:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30193
[22 Sep 2005 7:47] Sergei Glukhov
Fixed in 4.1.15 tree
[23 Sep 2005 17:11] Paul DuBois
Noted in 4.1.15 changelog.