Today, morning I got 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.
/*Create table to test load*/
CREATE TABLE Employee.tbl_TestLoad (name varchar(50000));
/*Create procedure to test transaction load.*/
CREATE PROCEDURE Employee.usp_InsertLoad()
DECLARE Counter INT DEFAULT 0;
WHILE Counter < 10000000 DO
INSERT INTO Employee.tbl_TestLoad
SET Counter = Counter + 1;
After a few minutes I got an error that lost connection to MySQL, again, I tried and got the same error after a few minutes. I have tried few times and got error at every time, but during this I note time to generate an error. After every 10 minutes of execution this execution is stopped and give me error.
Now this is a key point for me that after every 10 minute query execution is stopped.
I searched this error on google and come with suitable solutions. I am using the workbench to execute this query so here found that there is default 600 second 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.
This are different cause for this error and I have taken this references knowledge from https://dev.mysql.com/doc/refman/5.0/en/error-lost-connection.html.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.