MySQL Bugs: #27586: Wrong autoinc value assigned by LOAD DATA in the NO_AUTO_VALUE_ON

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

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.

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.

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.

Pushed into 5.1.18-beta

Pushed into 5.0.40

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.