MySQL Workbench: How to keep the connection alive

Question

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?

score 389 · Accepted Answer · edited Nov 13 '17 at 14:51

389

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.

edited Nov 13 '17 at 14:51

Benny Bottema

9,605
10
59
80

answered Mar 29 '13 at 22:49

Ignacio

4,400
1
13
10

6

There is a bug in all version of MySQL Workbench beyond 6.0.x on Mac OS : http://stackoverflow.com/a/37890150/1014813 – lepix Jun 17 '16 at 20:48
8

This worked for me, but had to restart the editor to take effect. – cucu8 Apr 03 '17 at 15:30
@lepix Is this bug fixed? – posfan12 Oct 19 '17 at 23:50
4

A restart of the editor is needed for this to take effect after changing the values. – philip oghenerobo balogun Jun 13 '19 at 14:06
@lepix @philip-oghenerobo-balogun Did you make it past the first paragraph? OP mentions that is a REQUIREMENT, That's if you READ the SECOND paragraph of course: `Close and reopen MySQL Workbench. Kill your previously query that probably is running and run the query again.` – Omar Sep 22 '20 at 20:22
You can set it to 0 to just skip read timeout – Ollie Feb 10 '21 at 23:08

score 47 · Answer 2 · answered Nov 13 '15 at 20:02

47

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

answered Nov 13 '15 at 20:02

Ljubitel

801
7
6

Why are you repeating an answer? – Jan Doggen Nov 13 '15 at 20:29
20

@JanDoggen I don't see any other answer that points where in MySQL Workbench you can change "SSH KeepAlive" which by default is 0 => disabled. I only see an answer that suggest to change server's ssh settings... – Ljubitel Nov 14 '15 at 21:27
Thanks for pointing this out, as the setting here overrides the client config files ~/.ssh/config and /etc/ssh/ssh_config This is an absolute must for mobile broadband. – Rodney Aug 30 '16 at 14:12
Could you please add an example of value to use in your answer? Do your recommend something like `1800` seconds? – A.L Nov 19 '18 at 10:15
1

@A.L it depends on your context. In my case, the SSH connection was expiring in 3 minutes (180 seconds) so I set my SSH KeepAlive to 30 seconds. – Ljubitel Nov 20 '18 at 20:24
Thank you for pointing this out, for me I don't see "DBMS connection read time out" in Mysql workbench 6.3 – JohnnyHuo Mar 20 '19 at 21:36

score 16 · Answer 3 · answered Jun 20 '13 at 23:53

16

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.

answered Jun 20 '13 at 23:53

jidulberger

371
2
6

I have setted my DBMS connection read timeout to 86400 second, after around 7200 seconds of running some query I still get error 2013. Does anyone know anything about it? – IndexOutOfDevelopersException Aug 16 '16 at 12:15

Abelgo · Answer 4 · 2016-02-10T06:48:41.630

12

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

edited Feb 10 '16 at 06:48

answered Dec 07 '15 at 05:43

Abelgo

772
7
7

2

In 6.3 Advanced has been renamed as Others. – Richard Feb 26 '16 at 15:27

score 2 · Answer 5 · answered Aug 06 '14 at 11:30

2

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.

answered Aug 06 '14 at 11:30

Tuncay Göncüoğlu

1,514
13
18

score 2 · Answer 6 · answered Oct 02 '14 at 12:58

2

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.

answered Oct 02 '14 at 12:58

Neal Garrett

165
1
5

score 2 · Answer 7 · answered Jun 21 '17 at 10:51

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.

score 2 · Answer 8 · answered Jul 07 '19 at 07:21

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.

score 0 · Answer 9 · answered Apr 23 '19 at 09:58

0

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

answered Apr 23 '19 at 09:58

junior_software

1,016
7
19

MySQL Workbench: How to keep the connection alive

9 Answers9

Linked

Related