MySQL Bugs: #72290: LOAD DATA INFILE converts PK value of ZERO to ONE

Bug #72290	LOAD DATA INFILE converts PK value of ZERO to ONE
Submitted:	9 Apr 2014 13:20	Modified:	9 May 2014 17:45
Reporter:	J. Go	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.6.12-log	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	LOAD DATA INFILE

Description:
If a file that is imported using "LOAD DATA INFILE" has a primary key with the value ZERO, it is inserted as the value ONE, causing the importing of the next row, (likely) containing the PK value of ONE, to raise the following error:

ERROR 1062 (23000) at line ...: Duplicate entry '1' for key 'PRIMARY'

This is a problem as "SELECT INTO ... OUTFILE" produces PK values of ZERO, if this PK value exist in the table. For this reason exports cannot be imported without manual fixing (removing the record with PK value of zero)

How to repeat:
Create a simple table:

CREATE TABLE IF NOT EXISTS `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msgid` int(11) NOT NULL,
  `memberid` int(11) NOT NULL,
  `moment` int(11) NOT NULL,
  `contents` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ID_MSG` (`ID_MSG`,`ID_MEMBER`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

Create XYZ.infile containing:
0	238300	12098	1342731000	Test Topic A
1	238301	12099	1342731001	Test Topic B

Run these queries:
TRUNCATE TABLE `my_table`;
LOAD DATA INFILE 'XYZ.infile' INTO TABLE `my_table`;

FYI: when the PK values are always non-zero, it works just fine:
1	238300	12098	1342731000	Test Topic A
2	238301	12099	1342731001	Test Topic B

TRUNCATE TABLE `my_table`;
LOAD DATA INFILE 'XYZ.infile' INTO TABLE `my_table`;

There is an SQL_mode controlling this. 

Refer:
http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_auto_value_on_zero

I forgot my usual signature here:

-- Peter
-- not a MySQL/Oracle person

Please check http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_auto_value_on_zero how Peter pointed. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".