MySQL Bugs: #1370: ERROR 2013: Lost connection to MySQL server during query.

Bug #1370	ERROR 2013: Lost connection to MySQL server during query.
Submitted:	21 Sep 2003 22:27	Modified:	16 Oct 2003 22:01
Reporter:	Farley Inglis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	4.0.15	OS:	Windows (Windows)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Client MYSQL.EXE 4.0.15 reports:  'ERROR 2013: Lost connection to MySQL server during query.' on queries lasting more than 30 seconds.

Client MYSQL.EXE 4.0.13 does not have this issue.

Until there is a fix, I am using client MYSQL.EXE 4.0.13 to communicate with service MYSQLD-NT.EXE 4.0.15.

How to repeat:
Run any query from MYSQL.EXE that requires longer than 30 seconds to complete.

Suggested fix:
Temporarily use a copy of the client MYSQL.EXE version 4.0.13.

Between now and when there is a fix:

   Look to see what was changed in the MYSQL-CLIENT-SOURCE-CODE between versions 4.0.13 and 4.0.15 to determined what was changed that would cause this issue and correct it as soon a possible.

Was introduced from 4.0.15 code for to make works the TCP/IP connections
time out:

C:\mysql\bin>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.15-max-nt

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

mysql> show variables like "%timeout%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| connect_timeout          | 5     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 28800 |
| net_read_timeout         | 30    |
| net_write_timeout        | 60    |
| slave_net_timeout        | 3600  |
| wait_timeout             | 28800 |
+--------------------------+-------+
8 rows in set (0.01 sec)

how you can see above net_read_timeout = 30. So please make a test
starting the server with a suitable time out e.g.:

C:\mysql\bin>mysqld-max-nt --standalone --console --net_read_timeout=100
030922  3:37:51  InnoDB: Started
mysqld-max-nt: ready for connections.
Version: '4.0.15-max-nt'  socket: ''  port: 3306

mysql> show variables like "%timeout%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| connect_timeout          | 5     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 28800 |
| net_read_timeout         | 100   |
| net_write_timeout        | 60    |
| slave_net_timeout        | 3600  |
| wait_timeout             | 28800 |
+--------------------------+-------+
8 rows in set (0.00 sec)

If the above works then you need to set the time outs according
with your necessities.

Miguel Solorzano,

     I had already tried setting the [net_read_timeout] to exactly 100 for an 86 second query on an MYISAM-Table BEFORE posting this issue.

My exact settings at that time were as follows:

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| connect_timeout          | 12     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 7200  |
| net_read_timeout         | 100   |
| net_write_timeout        | 60    |
| slave_net_timeout        | 3600  |
| wait_timeout             | 7200  |
+--------------------------+-------+

Farley

Please do the following test from the mysql client before to
run your query:

mysql> SET @@NET_READ_TIMEOUT=200;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%timeout%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| connect_timeout          | 5     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 28800 |
| net_read_timeout         | 200   |
| net_write_timeout        | 60    |
| slave_net_timeout        | 3600  |
| wait_timeout             | 28800 |
+--------------------------+-------+
8 rows in set (0.01 sec)

Miguel

     I have just completed your instructions for:

SET @@NET_READ_TIMEOUT=200;

     I also verified that the 'net_read_timeout' was in fact set to the expected 200 seconds with

SHOW VARIABLES;

     The 86 second query that I try to run through the MYSQL-CLIENT (MYSQL.EXE) continues to loose connection at the 30 second mark, just as before.

     I do notice that the MYSQL-Service (MYSQLD-NT.EXE) continues running the query to completion after the MYSQL-CLIENT (MYSQL.EXE) looses connection at the 30-second mark, but is unable to display the results of the query.

Thank You For Trying,

Farley

Ok. thanks you for the tests performed. Now I need to create a test case
like of your and try to debug this issue.

Miguel,

     Try using:  SELECT BENCHMARK(1500000000, 3.3 * 3.145);

I am using recompiled source-code targeted for an Intel P4.  My machine is a P4 at 2.4 GHz and this SELECT-QUERY requires 36 seconds to run on my machine.

    This will certainly save you the time trying to find a table with 3,000,000 records or more.

Farley

Farley,

I think all of us that run production servers on Win32 with even trivially large (<70mb) databases are experiencing this issue on 4.0.15.  You will see in the changelog for this release the %timeout% vars are now working on Windows (or not working as the case may be).  Once a night our database is exported from BTree Filer .DAT files into a tab delimited output (around 300mb large).  Following, when doing a LOAD DATA INFILE command, I receive the error you are speaking of at the 30 second mark.  HOWEVER, the client seems to auto reconnect and pick up where it left off without a hitch (which I can verify with a query of the data - the values are correct).  I also noticed this issue while doing an ALTER TABLE - DROP COLUMN command ... the mysql client lost its connection (but the drop column still worked).

This is a rather annoying bug, but it doesn't seem to cause any problems.  Hopefully this will be resolved by the next release.

Clint

I am working on a database containing tables ranging from 400,000 to 10,000,000 rows and want to benchmark MySQL against some other products. So a comment such as:

"This is a rather annoying bug, but it doesn't seem to cause any problems. 
Hopefully this will be resolved by the next release."

let me flabbergasted because I just can't do anything with that bug. Even if I ws able to construct my tables and run queries (the server keeping working after the connection is lost), I don't have the times it requires and so am unable to benchmark.

Hoping "next release" will show up soon,

Olivier

Thank you for the bug report, this is issue was already fixed with the
ChangeSet
  1.1573 03/10/15 21:41:13 monty@mashka.mysql.fi +10 -0
  Extend default timeout on windows clients to 1 year (to avoid timeout problems)

in the latest BK tree