Bug #12911 LOAD DATA INFILE anomally changes data

• Submitted: 31 Aug 2005 15:02
• Modified: 5 Sep 2005 15:42
• Reporter: Jaroslav Uher
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.13, 4.1.14
• OS: Windows (Windows XP)
• CPU Architecture: Any

[31 Aug 2005 15:02] Jaroslav Uher

Description:
I used LOAD DATA INFILE for loading huge amount of data from csv files. I found anomally changed data  (varchar and datetime fields). Repeated LOAD DATA INFILE command (into empty table) resulted in different changes of data. Error makes itself felt in adding of $ or ` characters at end of text fields, odd values stored in datetime fields (eg. 2000-89-52 05:40:88).

Symptoms:
Odd values appears in max. 5-10 records of 3mil records file.
This bug disappeared, when I  make loaded files less than 50MB.
Bug not detected in 4.1.10a release.
LOAD DATA INFILE into table with changed datetime field to varchar cause crashing of file: SELECT show 'Got error 127 from storage engine' 

How to repeat:
CREATE TABLE `history` (
  `BATCH` char(8) NOT NULL default '',
  `TA_CARRIER_ID_NO` int(11) NOT NULL default '0',
  `FACTORY_UNIT_CODE` char(8) NOT NULL default '',
  `LOCATION_TYPE` char(8) NOT NULL default '',
  `LOCATION_CODE` char(8) NOT NULL default '',
  `TA_TECHNIQUE_CODE` char(4) default NULL,
  `REASON_CODE` char(4) default NULL,
  `QTY_PCS` int(11) default NULL,
  `QTY_LF` int(11) default NULL,
  `SCRAPPED_PCS` int(11) default NULL,
  `SCRAPPED_LF` int(11) default NULL,
  `DATE_DELIVERED` datetime default NULL,
  `USER_ID` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
LOAD DATA INFILE 'history.csv' INTO TABLE history FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n';

typical wrong records:
select * from history where month(date_delivered) > 12

[31 Aug 2005 22:35] MySQL Verification Team

Please provide your file at:

ftp://ftp.mysql.com:/pub/mysql/upload

Thanks in advance.

[31 Aug 2005 22:35] MySQL Verification Team

Please provide your file at:

ftp://ftp.mysql.com:/pub/mysql/upload

Thanks in advance.

[1 Sep 2005 9:32] Jaroslav Uher

uploaded file mysql_bug#12911.zip

Regards

[1 Sep 2005 17:54] MySQL Verification Team

Sorry I was unable for to find the file you said uploaded.
Could you please try again, using just the name: bug12911.zip ?

Thanks

[2 Sep 2005 23:00] Jaroslav Uher

File with test data uploaded sucessfully: ftp.mysql.com/pub/mysql/upload/bug12911.zip

[5 Sep 2005 15:42] MySQL Verification Team

Thank you for the feedback.
I was unable for to repeat the behavior reported, all datetime columns
were valid values on my side:

mysql> select * from history where month(date_delivered) > 12;
Empty set (5.33 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.38 sec)