Menu
To prevent these connections being automatically closed, the connector can be configured to keep the connection alive by submitting a simple SELECT statement (actually SELECT 'KEEP_ALIVE';) periodically to ensure that the MySQL timeout is not reached and the connection closed.
Can I adjust the timeout? Yes, go to Preferences, SQL Editor, and adjust the DBMS connection read time out option that defaults to 600 seconds. This sets the maximum amount of time (in seconds) that a query can take before MySQL Workbench disconnects from the MySQL server.
The error above commonly happens when you run a long or complex MySQL query that runs for more than a few seconds. To fix the error, you may need to change the timeout-related global settings in your MySQL database server.
From the now unavailable internet archive:
Go to Edit -> Preferences -> SQL Editor and set to a higher value this parameter: DBMS connection read time out (in seconds). For instance: 86400.
Close and reopen MySQL Workbench. Kill your previously query that probably is running and run the query again.
If you are using a "Standard TCP/IP over SSH" type of connection, under "Preferences"->"Others" there is "SSH KeepAlive" field. It took me quite a while to find it :(
In 5.2.47 (at least on mac), go the location of the preferences is: MySQLWorkbench->Preferences->SQL Editor
Then you'll see both:
DBMS connection keep-alive interval (in seconds): DBMS connection read time out (in seconds):
The latter is where you'll want to up the limit from 600 to something a bit more.
In my case after trying to set the SSH timeout on the command line and in the local server settings. @Ljubitel solution solved the issue form me.
One point to note is that in Workbench 6.2 the setting is now under advanced
I had a similar problem where CREATE FULLTEXT timed out after 30 seconds:
Setting DBMS connection read timeout interval to 0 under Edit -> Preferences -> SQL Editor fixed the issue for me:
Also, I did not have to restart mysql workbench for this to work.
If you are using a "Standard TCP/IP over SSH" type of connection, it might be the ssh server that keeps timing out, in which case, you would have to edit TCPKeepAlive related settings in /etc/ssh/sshd_config on your server.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With