Bug #10483 'LOAD DATA' does not support to traditional mode
Submitted: 9 May 2005 15:34 Modified: 6 Nov 2005 5:55
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.5-beta-nt OS:Windows (window xp)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[9 May 2005 15:34] Disha 
Description:
Create a table using 'datetime' datatype. If inserted the data using INSERT statement the data gets rejected properly. But if used 'LOAD DATA' statement to insert the data , data get inserted with warnings. 

Create a file named 'checkdate.txt' at 'C:\' which contains the data
as follows,

0000-00-00

How to repeat:
1. delimiter//
2. create database chkdate//
3. use chkdate
4. set @@sql_mode='TRADITIONAL'//
5. create table t1 (d1  datetime )// 
6. LOAD DATA LOCAL INFILE 'c://checkdate.txt' INTO TABLE t1//
7. Select * from t1//
8. The execution of step (7) displays the result as,
		+------------+
		| d1              |
		+------------+
		| 0000-00-00 00:00:00|
		+------------+
[9 May 2005 16:34] MySQL Verification Team 
Verified on Linux Bk source.
[31 Aug 2005 7:08] Ramil Kalimullin 
From the manual (http://dev.mysql.com/doc/mysql/en/load-data.html):

 "If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you don't specify either option, the behavior depends on whether or not the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation."