MySQL Workbench: How to keep the connection alive

P

9

246

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?

Pinero answered 29/3, 2013 at 22:40 Comment(0)

T

499

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.

Thitherto answered 29/3, 2013 at 22:49 Comment(7)

There is a bug in all version of MySQL Workbench beyond 6.0.x on Mac OS : https://mcmap.net/q/118948/-mysql-workbench-drops-connection-when-idle – Infidel 17/6, 2016 at 20:48

This worked for me, but had to restart the editor to take effect. – Batholith 3/4, 2017 at 15:30

@Infidel Is this bug fixed? – Otherwise 19/10, 2017 at 23:50

A restart of the editor is needed for this to take effect after changing the values. – Uncertainty 13/6, 2019 at 14:6

You can set it to 0 to just skip read timeout – Hacking 10/2, 2021 at 23:8

No need to restart mysql workbench in recent versions. At least version 8.0.26 don't require a restart, I assume the same is true for alot of earlier version. – Bair 19/4, 2022 at 11:20

This helped me too. Suddenly one bigger query would time out when running in MySQL Workbench. Turns out it just crept over 30 seconds. I did have to restart though, for it to take effect. Thanks for taking the time, over 10 years ago ;) – Neu 7/3 at 10:11

V

50

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

Verdaverdant answered 13/11, 2015 at 20:2 Comment(6)

Why are you repeating an answer? – Encephalo 13/11, 2015 at 20:29

@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... – Verdaverdant 14/11, 2015 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. – Power 30/8, 2016 at 14:12

Could you please add an example of value to use in your answer? Do your recommend something like 1800 seconds? – Elery 19/11, 2018 at 10:15

@Elery 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. – Verdaverdant 20/11, 2018 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 – Commorancy 20/3, 2019 at 21:36

M

26

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.

Misname answered 7/7, 2019 at 7:21 Comment(0)

B

19

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.

Bernadettebernadina answered 20/6, 2013 at 23:53 Comment(2)

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? – Fong 16/8, 2016 at 12:15

you need to restart Workbench after changes – Esta 23/8, 2022 at 6:48

C

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

Caterpillar answered 7/12, 2015 at 5:43 Comment(1)

In 6.3 Advanced has been renamed as Others. – Leptospirosis 26/2, 2016 at 15:27

S

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.

Statics answered 6/8, 2014 at 11:30 Comment(0)

W

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.

Winzler answered 2/10, 2014 at 12:58 Comment(0)

C

2

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.

Courtnay answered 21/6, 2017 at 10:51 Comment(0)

C

2

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

Carbine answered 23/4, 2019 at 9:58 Comment(0)

Recommended topics

Hot tags