[mysql] Error Code: 2013. Lost connection to MySQL server during query

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?

This question is related to mysql sql database mysql-workbench

The answer is


Start the DB server with the comandline option net_read_timeout / wait_timeout and a suitable value (in seconds) - for example: --net_read_timeout=100.

For reference see here and here.


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


Three things to be followed and make sure:

  1. Whether multiple queries show lost connection?
  2. how you use set query in MySQL?
  3. how delete + update query simultaneously?

Answers:

  1. Always try to remove definer as MySQL creates its own definer and if multiple tables involved for updation try to make a single query as sometimes multiple query shows lost connection
  2. Always SET value at the top but after DELETE if its condition doesn't involve SET value.
  3. Use DELETE FIRST THEN UPDATE IF BOTH OF THEM OPERATIONS ARE PERFORMED ON DIFFERENT TABLES

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.


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).


SET @@local.net_read_timeout=360;

Warning: The following will not work when you are applying it in remote connection:

SET @@global.net_read_timeout=360;

check about

OOM on /var/log/messages ,
modify innodb_buffer_pool_size value ; when load data , use 50% of os mem ; 

Hope this helps


I had this error message due to a problem after of upgrade Mysql. The error appeared immediately after I tried to do any query

Check mysql error log files in path /var/log/mysql (linux)

In my case reassigning Mysql owner to the Mysql system folder worked for me

chown -R mysql:mysql /var/lib/mysql

I got the same issue when loading a .csv file. Converted the file to .sql.

Using below command I manage to work around this issue.

mysql -u <user> -p -D <DB name> < file.sql

Hope this would help.


I had the same problem - but for me the solution was a DB user with too strict permissions. I had to allow the Execute ability on the mysql table. After allowing that I had no dropping connections anymore


Just perform a MySQL upgrade that will re-build innoDB engine along with rebuilding of many tables required for proper functioning of MySQL such as performance_schema, information_schema, etc.

Issue the below command from your shell:

sudo mysql_upgrade -u root -p

Change "read time out" time in Edit->Preferences->SQL editor->MySQL session


If you are using SQL Work Bench, you can try using Indexing, by adding an index to your tables, to add an index, click on the wrench(spanner) symbol on the table, it should open up the setup for the table, below, click on the index view, type an index name and set the type to index, In the index columns, select the primary column in your table.

Do the same step for other primary keys on other tables.


Sometimes your SQL-Server gets into deadlocks, I've been in to this problem like 100 times. You can either restart your computer/laptop to restart server (easy way) OR you can go to task-manager>services>YOUR-SERVER-NAME(for me , it was MySQL785 something like this). And right-click > restart. Try executing query again.


If all the other solutions here fail - check your syslog (/var/log/syslog or similar) to see if your server is running out of memory during the query.

Had this issue when innodb_buffer_pool_size was set too close to physical memory without a swapfile configured. MySQL recommends for a database specific server setting innodb_buffer_pool_size at a max of around 80% of physical memory, I had it set to around 90%, the kernel was killing the mysql process. Moved innodb_buffer_pool_size back down to around 80% and that fixed the issue.


Check if the indexes are in place first.

SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = '<schema>'

This happened to me because my innodb_buffer_pool_size was set to be larger than the RAM size available on the server. Things were getting interrupted because of this and it issues this error. The fix is to update my.cnf with the correct setting for innodb_buffer_pool_size.


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.


Add the following into /etc/mysql/cnf file:

innodb_buffer_pool_size = 64M

example:

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
innodb_buffer_pool_size = 64M

There seems to be an answer missing here for those using SSH to connect to their MySQL database. You need to check two places not 1 as suggested by other answers:

Workbench Edit ? Preferences ? SQL Editor ? DBMS

Workbench Edit ? Preferences ? SSH ? Timeouts

My default SSH Timeouts were set very low and causing some (but apparently not all) of my timeout issues. After, don't forget to restart MySQL Workbench!

Last, it may be worth contacting your DB Admin and asking them to increase wait_timeout & interactive_timeout properties in mysql itself via my.conf + mysql restart or doing a global set if restarting mysql is not an option.

Hope this helps!


Go to Workbench Edit ? Preferences ? SQL Editor ? DBMS connections read time out : Up to 3000. The error no longer occurred.


Try please to uncheck limit rows in in Edit ? Preferences ?SQL Queries

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


I ran into this while running a stored proc- which was creating lots of rows into a table in the database. I could see the error come right after the time crossed the 30 sec boundary.

I tried all the suggestions in the other answers. I am sure some of it helped , however- what really made it work for me was switching to SequelPro from Workbench.

I am guessing it was some client side connection that I could not spot in Workbench. Maybe this will help someone else as well ?


Thanks!It's worked. But with the mysqldb updates the configure has became:

max_allowed_packet

net_write_timeout

net_read_timeout

mysql doc


I know its old but on mac

1. Control-click your connection and choose Connection Properties.
2. Under Advanced tab, set the Socket Timeout (sec) to a larger value.

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


In my case, setting the connection timeout interval to 6000 or something higher didn't work.

I just did what the workbench says I can do.

The maximum amount of time the query can take to return data from the DBMS.Set 0 to skip the read timeout.

On Mac Preferences -> SQL Editor -> Go to MySQL Session -> set connection read timeout interval to 0.

And it works


There are three likely causes for this error message

  1. Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently
  2. Sometimes the “during query” form happens when millions of rows are being sent as part of one or more queries.
  3. More rarely, it can happen when the client is attempting the initial connection to the server

For more detail read >>

Cause 2 :

SET GLOBAL interactive_timeout=60;

from its default of 30 seconds to 60 seconds or longer

Cause 3 :

SET GLOBAL connect_timeout=60;

This usually means that you have "incompatibilities with the current version of MySQL Server", see mysql_upgrade. I ran into this same issue and simply had to run:

mysql_upgrade --password The documentation states that, "mysql_upgrade should be executed each time you upgrade MySQL".


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.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to mysql-workbench

Authentication plugin 'caching_sha2_password' cannot be loaded Cannot connect to MySQL Workbench on mac. Can't connect to MySQL server on '127.0.0.1' (61) Mac Macintosh MySQL Workbench not displaying query results Can't connect to MySQL server on '127.0.0.1' (10061) (2003) How to unblock with mysqladmin flush hosts How to create localhost database using mysql? MySQL Workbench - Connect to a Localhost MySQL Workbench not opening on Windows How to view table contents in Mysql Workbench GUI? Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?