CODEWITHSUNDEEP
mysqlsqlmysql-workbenchconnection-timeout
Omar Juvera
Omar Juvera

Reputation: 12307

MySQL Workbench: How to keep the connection alive

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?

Upvotes: 258

Views: 379958

Answers (9)

jidulberger
jidulberger

Reputation: 401

In 5.2.47 (at least on Mac), the location of the related preferences is:

MySQL Workbench > 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.

Upvotes: 19

kimbaudi
kimbaudi

Reputation: 15615

I had a similar problem where CREATE FULLTEXT timed out after 30 seconds:

error

Setting DBMS connection read timeout interval to 0 under Edit -> Preferences -> SQL Editor fixed the issue for me:

fix error

Also, I did not have to restart mysql workbench for this to work.

Upvotes: 31

Omer Anisfeld
Omer Anisfeld

Reputation: 1302

in mysql-workbech 5.7 edit->preference-> SSH -> SSH Connect timeout (for SSH DB connection) enter image description here

Upvotes: 2

Ignacio
Ignacio

Reputation: 5730

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.

Upvotes: 522

Chris Dav
Chris Dav

Reputation: 71

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.

Upvotes: 2

Abelgo
Abelgo

Reputation: 772

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

enter image description here

Upvotes: 12

Ljubitel
Ljubitel

Reputation: 951

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 :(

Upvotes: 50

Neal Garrett
Neal Garrett

Reputation: 185

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.

Upvotes: 2

Tuncay Göncüoğlu
Tuncay Göncüoğlu

Reputation: 1767

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.

Upvotes: 2

Related Questions