This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Error code:2013
Today, morning I got an error like, Error code: 2013 Lost connection to MySQL Server during query. I was testing some load of transaction in while loop.
Below is my sample function to insert 10000000 dummy names using while loop.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/*Create table to test load*/ CREATE TABLE Employee.tbl_TestLoad (name varchar(50000)); /*Create procedure to test transaction load.*/ DELIMITER // CREATE PROCEDURE Employee.usp_InsertLoad() BEGIN DECLARE Counter INT DEFAULT 0; WHILE Counter < 10000000 DO INSERT INTO Employee.tbl_TestLoad VALUES (CONCAT('abc',Counter)); SET Counter = Counter + 1; END WHILE; END // DELIMITER; |
After a few minutes, I got an error that lost connection to MySQL, and again, I tried and got the same error after a few minutes.
I tried few times and got error at every time, but I note the time and found that error was generating after every 10 minutes.
Now, this is a key point for me that after every 10 minutes, query execution stopped.
I searched this error on google and come with suitable solutions.
I am using the workbench to execute the query, so here found that there is default 600 seconds time out has been set in workbench preferences.
Now time to change this default time out values.
Please go to workbench Edit Menu -> Preferences -> SQL Editor, here I change the default value of 600 second to 84000 second.
Please stop all running queries and restart your workbench.
Below is a reference image.
This is what I have found and fixed for my workbench, but all the time this is not solution because this error has few more cases.
Please read below mainly three types of reason for this error.
Basically, this error occurs when million of transactions is going to execute in one batch. This is also indicated that there is some problem with the network. You should change net_read_timeout and net_write_timeout parameter value in config file. This parameter defines number wait second to perform read and write for a particular connection. This timeout value applies only for TCP/IP Connections. The default value for this parameter is 30 to 60 seconds and you can change this value as per requirements.
Initial connection timeout, this also causes for this error. When a client is going to connect server and at the time connection timeout is set very few second. You can solve by increasing connect_timeout parameter. You can also check aborted connection by server using command SHOW GLOBAL STATUS LIKE ‘Aborted_connects’.
Maximum packet size, this might be another cause if your data size is very large and defined packet size is small, so at this time you require to change parameter max_allowed_packet.
For different causes, I took reference from here.
