Error Code: 2013. Lost connection to MySQL server

Question

I got the Error Code: 2013. Lost connection to MySQL server during query error when I tried to add an index to a table using MySQL Workbench. I noticed also that it appears whenever I run long query.

Is there away to increase the timeout value?

Answer 1

Go to:

Edit -> Preferences -> SQL Editor

In there you can see three fields in the "MySQL Session" group, where you can now set the new connection intervals (in seconds).

Answer 2

Turns out our firewall rule was blocking my connection to MYSQL. After the firewall policy is lifted to allow the connection i was able to import the schema successfully.

Answer 3

If your query has blob data, this issue can be fixed by applying a my.ini change as proposed in this answer:

[mysqld]
max_allowed_packet=16M

By default, this will be 1M (the allowed maximum value is 1024M). If the supplied value is not a multiple of 1024K, it will automatically be rounded to the nearest multiple of 1024K.

While the referenced thread is about the MySQL error 2006, setting the max_allowed_packet from 1M to 16M did fix the 2013 error that showed up for me when running a long query.

For WAMP users: you'll find the flag in the [wampmysqld] section.

Answer 4

If you experience this problem during the restore of a big dump-file and can rule out the problem that it has anything to do with network (e.g. execution on localhost) than my solution could be helpful.

My mysqldump held at least one INSERT that was too big for mysql to compute. You can view this variable by typing show variables like "net_buffer_length"; inside your mysql-cli. You have three possibilities:

• increase net_buffer_length inside mysql -> this would need a server restart
• create dump with --skip-extended-insert, per insert one line is used -> although these dumps are much nicer to read this is not suitable for big dumps > 1GB because it tends to be very slow
• create dump with extended inserts (which is the default) but limit the net-buffer_length e.g. with --net-buffer_length NR_OF_BYTES where NR_OF_BYTES is smaller than the server's net_buffer_length -> I think this is the best solution, although slower no server restart is needed.

I used following mysqldump command: mysqldump --skip-comments --set-charset --default-character-set=utf8 --single-transaction --net-buffer_length 4096 DBX > dumpfile

Answer 5

You should set the 'interactive_timeout' and 'wait_timeout' properties in the mysql config file to the values you need.

Answer 6

I faced this same issue. I believe it happens when you have foreign keys to larger tables (which takes time).

I tried to run the create table statement again without the foreign key declarations and found it worked.

Then after creating the table, I added the foreign key constrains using ALTER TABLE query.

Hope this will help someone.