Error Code: 2013. Lost connection to MySQL server during query
I am using MySQL Workbench. Also, I am running a batch of inserts, about 1000 lines total (Ex. INSERT INTO mytable SELECT * FROM mysource1; INSERT INTO mytable SELECT * FROM mysource2;...mysource3...mysource4 multiplied 1000 times) Each batch takes a considerable amount of time, some of them, more than 600 seconds.
How can I configure workbench, to continue working overnight, without stopping and without losing the connection?
This question is related to
mysql
sql
mysql-workbench
connection-timeout
The answer is
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, 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.
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.
OK - so this issue has been driving me crazy - v 6.3.6 on Ubuntu Linux. None of the above solutions worked for me. Connecting to localhost mysql server previously always worked fine. Connecting to remote server always timed out - after about 60 seconds, sometimes after less time, sometimes more.
What finally worked for me was upgrading Workbench to 6.3.9 - no more dropped connections.
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 :(
I was getting this error 2013 and none of the above preference changes did anything to fix the problem. I restarted mysql service and the problem went away.
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
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 mysql-workbech 5.7 edit->preference-> SSH -> SSH Connect timeout (for SSH DB connection)
Source: Stackoverflow.com