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

[8 Nov 2005 20:38] Michel Dumontier
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`;
[8 Nov 2005 20:46] Michel Dumontier
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?
[8 Nov 2005 21:40] MySQL Verification Team
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>