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
• Status: Closed
• 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.