MySQL Bugs: #55673: Lost connection during query (error 2013) while LOAD DATA LOCAL INFILE

Bug #55673	Lost connection during query (error 2013) while LOAD DATA LOCAL INFILE
Submitted:	1 Aug 2010 17:24	Modified:	7 Aug 2010 13:07
Reporter:	Daniil Kamenskiy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	5.0.67, 5.0.90	OS:	FreeBSD
Assigned to:		CPU Architecture:	Any
Tags:	lmysqlclient_r

Description:
Mysql Server (5.0.67) based on FreeBSD 7.0-STABLE-200807 port.
Client based on lmysqlclient_r (15.0.0), running on CentOS (5.1)
Client program runs thread (using posix threads), this thread connects to mysql-server and tries to load about 40Mb of data with

"load data local infile '/MY_FILENAME' into table 'MY_TABLE' fields terminated by ','".

mysql_query() returns with error, mysql_error returns "Lost connection to MySQL server during query", mysql_errno() returns 2013.

But future check of table 'MY_TABLE' shows that all data was loaded successfully.

How to repeat:
Load big data file with 'load data local infile' on a highload server with lmysqlclient_r. This bug appears from time to time, I've got it in approximately 5% of queries.

Thank you for the report.

But version 5.0.67 is old and many bugs were fixed since. Please upgrade to current version 5.0.91, try with it and if problem still exists send us output of SHOW VARIABLES LIKE '%timeout%' and server error log file.

Thank you for the answer.

I upgraded mysql-server to version 5.0.90 ( it's last version of mysql 5.0 in a freebsd repository), bug still appears.

Output of SHOW VARIABLES LIKE '%timeout%':

Server version: 5.0.90 FreeBSD port: mysql-server-5.0.90
mysql> SHOW VARIABLES LIKE '%timeout%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| connect_timeout            | 10     |
| delayed_insert_timeout     | 300    |
| innodb_lock_wait_timeout   | 50     |
| innodb_rollback_on_timeout | OFF    |
| interactive_timeout        | 864000 |
| net_read_timeout           | 30     |
| net_write_timeout          | 60     |
| slave_net_timeout          | 3600   |
| table_lock_wait_timeout    | 50     |
| wait_timeout               | 864000 |
+----------------------------+--------+
10 rows in set (0.00 sec)

Small note: Before connecting to mysql-server, application call's function mysql_options with parameter MYSQL_OPT_CONNECT_TIMEOUT, 
where timeout=60. So i think that variable connection_timeout will equall 60.

Thank you for the feedback.

Please also send us full MySQL server error log file.

full MySQL server error log file

Attachment: log.tar.gz (application/x-gzip, text), 25.69 KiB.

Log of mysql server was attached. Note: mysql-server was updated and restarted yesterday (02.08.2010), bug has appeared today (03.08.2010) too.

Thank you for the feedback.

Error log does not contain crash, so this is communication problem. 

Additionally I can not repeat the problem.

Please try to increase net_read_timeout and net_write_timeout and if this does not help send us example of C code which causes the problem.

Thank you for answer.

I increased net_read_time_out from 30 to 300, increased net_write_time_out from 60 to 600. After this action  bug has not appeared (one day). If you can, explain this effect please.

Thank you for the feedback.

This means client just processes file for long time not suitable for default values. For further details consult our user manual or use our forum and/or support contract. This bug database is for discussing bugs in MySQL code only, not about usage issues.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/