Error Code 2013 Lost connection to MySQL server during query

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

User · Answer

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

User · Answer

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

User · Answer

Three things to be followed and make sure    Whether multiple queries show lost connection  how you use set query in MySQL  how delete   update query simultaneously    Answers    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 Always SET value at the top but after DELETE if its condition doesn t involve SET value  Use DELETE FIRST THEN UPDATE IF BOTH OF THEM OPERATIONS ARE PERFORMED ON DIFFERENT TABLES

User · Answer

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

User · Answer

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 gt services gt YOUR-SERVER-NAME for me   it was MySQL785 something like this   And right-click  gt  restart  Try executing query again

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

check about   OOM on  var log messages   modify innodb buffer pool size value   when load data   use 50  of os mem      Hope this helps

User · Answer

Thanks It s worked  But with the mysqldb updates the configure has became   max allowed packet net write timeout net read timeout  mysql doc

User · Answer

There are three likely causes for this error message   Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently Sometimes the    during query    form happens when millions of rows are being sent as part of one or more queries  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

User · Answer

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  gt  dumpfile

User · Answer

New versions of MySQL WorkBench have an option to change specific timeouts   For me it was under Edit   Preferences   SQL Editor   DBMS connection read time out  in seconds   600  Changed the value to 6000   Also unchecked limit rows as putting a limit in every time I want to search the whole data set gets tiresome

User · Answer

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

User · Answer

You should set the  interactive timeout  and  wait timeout  properties in the mysql config file to the values you need

User · Answer

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

User · Answer

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

User · Answer

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  lt user gt  -p -D  lt DB name gt   lt  file sql   Hope this would help

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Check if the indexes are in place first   SELECT   FROM INFORMATION SCHEMA STATISTICS WHERE TABLE SCHEMA     lt schema gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  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

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

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to mysql-workbench