MySQL Bugs: #14771: HANG on LOAD DATA INFILE

Bug #14771	HANG on LOAD DATA INFILE
Submitted:	8 Nov 2005 20:38	Modified:	8 Nov 2005 21:40
Reporter:	Michel Dumontier	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When I try to load a particular data set into a table using either the client or by C API, the "LOAD DATA INFILE", the server appears to hang. As such, no errors or warnings are provided. This worked fine on MySQL 4.1.x.

How to repeat:

CREATE TABLE IF NOT EXISTS `gene_history` (
`taxid` int( 11 ) NOT NULL default '0',
`geneid` int( 11 ) NOT NULL default '0',
`geneid_old` int( 11 ) NOT NULL default '0',
`symbol_old` varchar( 50 ) NOT NULL default '',
KEY `taxid` ( `taxid` ),
KEY `geneid` ( `geneid` ),
KEY `geneid_old` ( `geneid_old` ),
KEY `symbol_old` ( `symbol_old` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 COMMENT = 'GeneIDs that are no longer current';

test.txt
9	-	1246494	repA1
9	-	1246495	repA2
9	-	1246496	leuA
9	-	1246497	leuB
9	-	1246498	leuC
9	-	1246499	leuD
9	-	1246506	yqhA
9	-	1246507	repA2
9	-	1246508	repA1

mysql> LOAD DATA INFILE 'test.txt' INTO TABLE `gene_history`;

Well, I changed the int types to varchar (50), owing to the badly formed file i was trying to import.  This seems to have done the trick... but still, we should get some kind of error?

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

I was able to repeat the behavior reported (the mysql client hangs). This happens
due to the sql_mode which was wrote by the Windows installer with server
5.0.15. However that behavior was already fixed on source. The correct
server behavior is to launch the below error message.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set @@sql_mode='traditional';
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE IF NOT EXISTS `gene_history` (
    -> `taxid` int( 11 ) NOT NULL default '0',
    -> `geneid` int( 11 ) NOT NULL default '0',
    -> `geneid_old` int( 11 ) NOT NULL default '0',
    -> `symbol_old` varchar( 50 ) NOT NULL default '',
    -> KEY `taxid` ( `taxid` ),
    -> KEY `geneid` ( `geneid` ),
    -> KEY `geneid_old` ( `geneid_old` ),
    -> KEY `symbol_old` ( `symbol_old` )
    -> ) ENGINE = MYISAM DEFAULT CHARSET = utf8 COMMENT = 'GeneIDs that are no longer
    '> current';
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> LOAD DATA INFILE 'c:/0/test.txt' INTO TABLE `gene_history`;
ERROR 1264 (22003): Out of range value adjusted for column 'geneid' at row 1

mysql> set @@sql_mode=''
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE 'c:/0/test.txt' INTO TABLE `gene_history`;
Query OK, 9 rows affected, 9 warnings (0.00 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 9

mysql>