MySQL Bugs: #55419: Lost connection / timeout to MySQL server during query after 900 seconds

Bug #55419	Lost connection / timeout to MySQL server during query after 900 seconds
Submitted:	20 Jul 2010 23:52	Modified:	21 Jul 2010 14:24
Reporter:	Orlando Colamatteo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.25 CE	OS:	Windows (64-bit 7.0 Professional)
Assigned to:		CPU Architecture:	Any
Tags:	Lost connection, timeout

Description:
I posted a question in the "MySQL Forums :: Workbench - SQL Browser, Editor, Refactor" and was asked to open this bug report by Edwin (http://forums.mysql.com/read.php?161,376274).

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

Environment:

- Server: "CentOS release 5.5 (Final)", kernel 2.6.18-128.4.1.el5
- Server: MySQL 5.1.45-51-log
- Client: 64-bit Windows 7 Professional
- Workbench 5.2.25 CE

Some DDL statements and select queries I run through Workbench can take more than 900 seconds to complete however when they take longer than 900 seconds I receive:

  Error Code: 2013
  Lost connection to MySQL server during query

Followed by...

  Error Code: 2006
  MySQL server has gone away

Is there a setting somewhere, either on the server or my driver or Workbench, that will allow these long running queries to complete without timing out?

Oddly enough there are times when long-running DDL queries (e.g. adding a column to a large table) timeout as described above but will continue to execute on the server (I can see them when I reconnect and run "show processlist;"). Eventually they do succeed in applying the DDL change I requested which is great, but Workbench should not time out. This behavior is not ideal but does work for DDL but does not help much for select queries since Workbench no longer has a connection leaving any results produced unhandled.

How to repeat:
select sleep(1000);

Suggested fix:
By default allow queries to run for an unlimited amount of time. Optionally expose a setting for users to set their own max timeout.

Thank you for the bug report. What is the behaviour with the mysql client tool with the same query?. Thanks in advance.

It times out after 900 seconds using my local mysql command line client as well. I logged into the server and used the local command line client there and it succeeded in executing the query totaling 1000 seconds of execution time. I guess that rules out the database server settings and brings us back to a client setting, or possibly network??? I am not sure this is a Workbench issue now. Any leads would however be appreciated. Thanks for reading.

Hello Orlando,

900 seconds == 15 minutes. This sounds like TCP timeout.

Anyway, this is not a bug at all and bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

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/

Thank you for your interest in MySQL.

I started this question in the forums as I did not assume it was a bug, but was asked to open a bug. I agree that this issue is likely not the fault of Workbench or any MySQL software. Thanks for the tip.

Does the MySQL client library allow to change the timeout on TCP connections?

Surely MySql workbench could at least give a meaningful error rather than just timing out - could it not say "TCP connection lost due to timeout" or something so people know what caused the problem?