Bug #27586 Wrong autoinc value assigned by LOAD DATA in the NO_AUTO_VALUE_ON_ZERO mode
Submitted: 2 Apr 2007 11:28 Modified: 11 Apr 2007 2:17
Reporter: Evgeny Potemkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0-bk OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any

[2 Apr 2007 11:28] Evgeny Potemkin
Description:
In the NO_AUTO_VALUE_ON_ZERO may assign wrong value to an auto increment field.

create table t1(f1 int);
insert into t1 values(1),(null);
create table t2(f2 int auto_increment primary key);

-- Correct
mysql> load data infile '/tmp/t1' into table t2;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t2;
+----+
| f2 |
+----+
|  1 | 
|  2 | 
+----+
2 rows in set (0.00 sec)

-- Wrong
mysql> set sql_mode=NO_AUTO_VALUE_ON_ZERO;  
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/tmp/t1' into table t2;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t2;
+----+
| f2 |
+----+
|  0 | 
|  1 | 
+----+
2 rows in set (0.01 sec)

How to repeat:
See description.
[6 Apr 2007 14:42] 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/commits/23988

ChangeSet@1.2432, 2007-04-06 18:40:27+04:00, evgen@moonbone.local +3 -0
  Bug#27586: Wrong autoinc value assigned by LOAD DATA in the
  NO_AUTO_VALUE_ON_ZERO mode.
  
  The table->auto_increment_field_not_null variable wasn't reset after
  reading a row which may lead to inserting a wrong value to the auto-increment
  field to the following row.
  
  The table->auto_increment_field_not_null variable is reset now right after a
  row is being written in the read_fixed_length() and the read_sep_field()
  functions.
[6 Apr 2007 20:15] 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/commits/24005

ChangeSet@1.2432, 2007-04-07 00:13:27+04:00, evgen@moonbone.local +3 -0
  Bug#27586: Wrong autoinc value assigned by LOAD DATA in the
  NO_AUTO_VALUE_ON_ZERO mode.
  
  The table->auto_increment_field_not_null variable wasn't reset after
  reading a row which may lead to inserting a wrong value to the auto-increment
  field to the following row.
  
  The table->auto_increment_field_not_null variable is reset now right after a
  row is being written in the read_fixed_length() and the read_sep_field()
  functions.
  Removed wrong setting of the table->auto_increment_field_not_null variable in
  the read_sep_field() function.
[9 Apr 2007 12:42] Bugs System
Pushed into 5.1.18-beta
[9 Apr 2007 12:43] Bugs System
Pushed into 5.0.40
[11 Apr 2007 2:17] Paul Dubois
Noted in 5.0.40, 5.1.18 changelogs.

With NO_AUTO_VALUE_ON_ZERO SQL mode enabled, LOAD DATA operations
could assign incorrect AUTO_INCREMENT values.