MySQL Bugs: #60103: Workbench drops connection during long running query

Bug #60103	Workbench drops connection during long running query
Submitted:	10 Feb 2011 19:45	Modified:	20 Jun 2011 18:58
Reporter:	luke libraro	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.2.31 CE	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
When I run a query that takes over 600 seconds in Workbench, I will lose my connection to MySql server. I get error messages:

Error Code: 2013 Lost connection to MySQL server during query 600.495 sec
Error Code: 2006 MySQL server has gone away

When I downgraded to Workbench version 5.2.28 as suggested in the comments for bug #57449, the problem goes away, and queries over 10 minutes are able to be run with no problems or loss of connection on the same server installation. Server version is 5.5.8 running on Amazon RDS, I also tested against Amazon RDS 5.1.50 with the same result.

I am able to reproduce this bug in Workbench 5.2.29 and 5.2.30 as well.

How to repeat:
Run any single query that takes longer than 600 seconds.

Suggested fix:
I've attempted to edit the mysql_rdbms_info.xml with no success. Added this section, which was suggested in the comments for bug #57449, then changed the timeout value to be much higher.

          <value type="object" struct-name="db.mgmt.DriverParameter" id="com.mysql.rdbms.mysql.driver.native.paramx">
            <value type="string" key="caption">Read Timeout.</value>
            <value type="string" key="defaultValue">60000</value>
            <value type="string" key="description">OPT_READ_TIMEOUT</value>
            <value type="int" key="layoutAdvanced">1</value>
            <value type="int" key="layoutRow">-1</value>
            <value type="int" key="layoutWidth">318</value>
            <value type="string" key="lookupValueMethod"></value>
            <value type="string" key="lookupValueModule"></value>
            <value type="string" key="name">OPT_READ_TIMEOUT</value>
            <link type="object" key="owner">com.mysql.rdbms.mysql.driver.native</link>
            <value type="string" key="paramType">int</value>
            <value type="dict" content-type="string" key="paramTypeDetails"/>
            <value type="int" key="required">0</value>
          </value>

No change in behavior (restarted workbench after this change).

Does a query like select sleep(600) also cause the disconnection even after increasing the timeout value?

Good day,

Im also having the same problem as Luke. It doesn't disconnect when you do select sleep(600) after increasing timeout, but it does whenever the query passes the 600 sec threshold, more exactly the 600.500 sec mark that is stated in the Duration/Fetch column in the Output tab of the workbench (tried with select sleep(601) and select sleep(600.501) which in effect provoked the disconnection).

On my version, with the changes made to my config file or the stock config file, "select sleep(601);" does not time out, but instead returns 0.

Are you connecting local or remote, please provide your my.ini/my.cnf file. Thanks in advance.

I've also been encountering same issue when trying to execute a query over 600 sec. I use MySQL workbench 5.2.31 and for internal connection only.

Someone find a workaround?

I cannot provide the my.ini/cnf because our database is hosted on Amazon RDS and those files are modified through a web based control panel instead. Are there specific values you're looking for?

I just upgrade from 5.2.31 to version 5.2.32 and it still has the same problem; it stops working after 600 seconds

I have been seeing the same issue when connecting to Amazon RDS, I am using 5.2.33 CE Revision 7506.  In my preferences the timeout is set to 1200 seconds.

I'm having the same problem with Workbench version 5.2.33b:

Error Code: 2013 Lost connection to MySQL server during query 600.526 sec
Error Code: 2006 MySQL server has gone away

But the query continue running on MySql Server, the connection is lost only in the Workbench. If you´re doing an INSERT query, it will continue an finish the INSERT (you can monitor in Server Administration on Workbench).

I also am having connections timeout at 600 seconds with this version.  My connection is SSH and I cannot set "read timeout" on the "advanced" tab of "manage DB connections". I've tried replacing the mysql_rdbms_info.xml file as described in bug (57449) http://bugs.mysql.com/bug.php?id=57449 and setting a higher read timeout, but no joy.  Using Windows Vista.

I'm having exactly the same problem. The connection is lost after 600.5 seconds, although the query continues to run just fine on the server side. Would LOVE to have a fix for this.

Just to be sure,  please execute the command:

show variables like 'net%timeout' ;

and paste the output here.

net_read_timeout, 30
net_write_timeout, 60

net_read_timeout, 30
net_write_timeout, 60

I'm still getting this bug, except I've seen it on both 5.2.31 CE (Windows Server 2008), and after just upgrading to 5.2.33. My net_*_timeout variables are the same as above, and when I run:

select sleep(610);

I get:
Error Code: 2013 Lost connection to MySQL server during query | 600.541

It seems really, really suspicious at this point that the Workbench default is 600s, and even after setting it to 1800 within the Workbench UI, I'm still seeing timeouts at that exact time.

Hi All !

I, too, am having this same annoying problem, as reported in bug 58560. I run Workbench 5.2.31 CE on Windoze XP. I really hope this gets fixed soon.

Some more info on this issue can be found on the forums: http://forums.mysql.com/read.php?161,391544,391681#msg-391681

Apparently, the timeout value is hard coded.  :(  It would be great if we could set this as an option, as 10 minutes is really not long enough for a lot of applications.

Same story here: 600.5 seconds.  Need to be able to run a 45 minute long query.  5.2.33 CE revision 7508.

Bug #61183 was marked as a duplicate of this one.

Noted in 5.2.34 changelog.

Workbench lost the connection to the server for long-running queries
(more than 600 seconds).

The 600.5 second timeout (still) occurs in Workbench 5.2.37 CE Revision 8576 under Windows 7, e.g. when using ALTER TABLE to add indexes to a very large table; client and server on the same PC.

Timeouts don't occur when the same commands are issued under the command-line client.

Please, please make this a configuration option for workbench.

Is there a fix for this? This happens on RDS with 5.2.31CE

Hi All, 
I'm running Workbench 5.2.37 and experiencing the same problem. But my OS is OSX 10.6.8. 
My time outs are ...
'net_read_timeout', '30'
'net_write_timeout', '60'

select sleep(610) LIMIT 0, 1000	Error Code: 2013. Lost connection to MySQL server during query 600.015 sec

Any progress on this?

Hi All, 
I'm running Workbench 5.2.37 and experiencing the same problem. But my OS is OSX 10.6.8. 
My time outs are ...
'net_read_timeout', '30'
'net_write_timeout', '60'

select sleep(610) LIMIT 0, 1000	Error Code: 2013. Lost connection to MySQL server during query 600.015 sec

Any progress on this?

Also experiencing this in MySQL Workbench CE 5.2.38 8753 on Ubuntu.

Can someone re-open this bug? The bug still exists in 5.2.37CE.

Can someone re-open this bug? The bug still exists in 5.2.37CE.

This is not the best fix, but I lost two days last week with the described problem. 

It seems it was coming from mysql 5.5 x itself- not workbench (I also had the same issue with using Navicat). After multiple desperate attempts, I actually moved up to the developer version 5.6x and used workbench. Never had the problem since. I also read of people moving down to the 5.1 trunk and it fixing it.

This is not the best fix, but I lost two days last week with the described problem. 

It seems it was coming from mysql 5.5 x itself- not workbench (I also had the same issue with using Navicat). After multiple desperate attempts, I actually moved up to the developer version 5.6x and used workbench. Never had the problem since. I also read of people moving down to the 5.1 trunk and it fixing it.

I am using trunk 5.1 (5.1.40) and have the problem. So there may be more to it.

I am using trunk 5.1 (5.1.40) and have the problem. So there may be more to it.

I am using trunk 5.1 (5.1.40) and have the problem. So there may be more to it.

MySQL WorkBench 5.2.40

Go to Preferences -> SQL Editor and set to a bigger value this parameter:
DBMS connection read time out (in seconds)

Then close and re-open MySQL WorkBench. Eventually kill your previously query which probably is running, and run the query again.

Best
alberto

Thank you very much for this answer!

Thanks!

I wish MySQL Workbench, when throwing this error message, hinted to the change of preference parameter Alberto Termanini pointed out (Preferences -> SQL Editor and set to a bigger value this parameter). It would be a timesaver for many users.

I am running into this bug on version 6.1 Windows 7. I did not have this issue on version 5 in XP. 

The databases I am having the problems in are MySQL version 5.1.61 on two different Linux servers, CentOS 6.3 and 6.4. They are generally Magento schemas but we have WordPress schemas and even some custom schemas, one of which has just one table with nothing special about it but we having the same Error 2013 on any of them randomly but it is most consistent when using right-click Select Rows (1000); if we select a schema then type in a SELECT statement everything runs fine after that. It does not happen on a CentOS 6.4 MySQL 5.1.66 server that is used primarily for WordPress schemas and some custom schemas.

I have already tried setting DBMS read time out to 6000 but it worked only temporarily so I have set it back to 600 (it doesn't even seem like it applies as the bug happens within seconds of logging in).

I would like to know any other troubleshooting steps or workarounds; I've looked in the Workbench logfile and the error is: exception in grt execute_task, continuing: Exception: Access denied for user, however, again this error doesn't happen when typing in the query manually, after which the right-click select works fine until next log in. There must be something else the Workbench is doing that is causing the error.

I am using SSH tunnel and everything else works fine.

I'm having the same issue.  

Windows 7, connecting to VPS over SSH MySQL v5.6.