MySQL Bugs: #15239: Problem with LOAD DATA Infile

Bug #15239	Problem with LOAD DATA Infile
Submitted:	25 Nov 2005 1:21	Modified:	27 Nov 2005 14:08
Reporter:	Friedrich Stockebrand	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Windows (windows xp)
Assigned to:		CPU Architecture:	Any

Description:
Dependent from type of column the LOAD DATA Infile provided an error, if the field in the infile is empty.

How to repeat:
The Infile.txt
==========

Belegdatum<TAB>Belegnummer<TAB>Buchungstext<TAB>Buchungsbetrag<TAB>Sollkonto<TAB>Habenkonto<TAB>Waehrung
03.01.2004<TAB>19<TAB>Fa. xxxx<TAB>42.95<TAB><TAB>01600<TAB>EUR

!!!    Change "<TAB>" to the tab character

--------------------------------------------------------------

The LoadDataInfile_1.sql
==================

DROP TABLE
IF EXISTS  	journal;

CREATE TABLE   	journal
		(
		Belegdatum		VARCHAR(20)			NULL,
		Belegnummer		INT UNSIGNED			NULL,
		Buchungstext		VARCHAR(100)			NULL,
		Buchungsbetrag		DECIMAL(19, 2)			NULL,
		Sollkonto		INT UNSIGNED			NULL,
		Habenkonto		INT UNSIGNED			NULL,
		Waehrung		VARCHAR(3)			NULL
		)
		ENGINE = INNODB;

LOAD DATA  
INFILE 		'Infile.txt' 
INTO TABLE 	journal
FIELDS 		TERMINATED BY '\t' 
		ENCLOSED BY '' 
		ESCAPED BY '\\'
LINES 		TERMINATED BY '\r\n' 
		STARTING BY ''
IGNORE 		1 LINES;

--------------------------------------------------------------

The LoadDataInfile_2.sql
==================

DROP TABLE
IF EXISTS  	journal;

CREATE TABLE   	journal
		(
		Belegdatum		VARCHAR(20)			NULL,
		Belegnummer		INT UNSIGNED			NULL,
		Buchungstext		VARCHAR(100)			NULL,
		Buchungsbetrag		DECIMAL(19, 2)			NULL,
		Sollkonto		VARCHAR(20)			NULL,
		Habenkonto		VARCHAR(20)			NULL,
		Waehrung		VARCHAR(3)			NULL
		)
		ENGINE = INNODB;

LOAD DATA  
INFILE 		'Infile.txt' 
INTO TABLE 	journal
FIELDS 		TERMINATED BY '\t' 
		ENCLOSED BY '' 
		ESCAPED BY '\\'
LINES 		TERMINATED BY '\r\n' 
		STARTING BY ''
IGNORE 		1 LINES;

--------------------------------------------------------------

The difference between LoadDataInfile_1.sql and LoadDataInfile_2.sql is the type of columns Sollkonto and Habenkonto. 

LoadDataInfile_1.sql provided an error: 1264;
LoadDataInfile_2.sql provided not error.

Correct should be no error!

!!!  I have not checked all kinds of types.   !!!

Thank you for a problem report. What exact 5.0.x version do you use, by the way?

Is '.' OK in your value 42.95 for the decimal separator according to your locale settings? Don't you need to use ',' instead?

The problem is empty (<TAB><TAB>) value for your Sollconto column. This problem is documented (http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html):

"When reading data with LOAD DATA INFILE, empty or missing columns are updated with ''. If you want a NULL value in a column, you should use \N in the data file. The literal word “NULL” may also be used under some circumstances. See Section 13.2.5, “LOAD DATA INFILE Syntax”."

So, you get '' from your empty field in the file, and it is not good for InnoDB table in the (default) STRICT_TRANS_TABLES mode on Windows:

mysql> show variables like 'sql%';
+---------------+----------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 |
| sql_notes     | ON |
| sql_warnings  | ON |
+---------------+----------------------------------------------------------------+
3 rows in set (0.06 sec)

mysql> drop table tttt;
Query OK, 0 rows affected (0.17 sec)

mysql> create table tttt (c1 int unsigned null);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into tttt values('');
ERROR 1264 (22003): Out of range value adjusted for column 'c1' at row 1

As explained in http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html:

"Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a column that has no explicit DEFAULT clause in its definition.

For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back."

So, I believe, it is not a bug. Note, that '' (empty string) is OK for VARCHAR columns, as demonstrated by your second test case. And, in MySQL, '' is not the same as NULL.