Archive for the ‘mysql’ Category

MySQL Error 2013

Tuesday, July 31st, 2007

Of all MySQL errors, Error Number 2013 is most frustrating and annoying.

MySQL Errors like 1062(Duplicate Entry), 2002(Server is not started), 2006(Server has gone away) are easy to identify and solve. 2013 signifies lost connection to server in midst of query execution.

2013 is frustrating because, you can’t produce it again for same sequence of actions. So you never know when it will come or when it won’t.

Finally, I have come up with few points to look for when MySQL Error 2013 occurs.

1. check for MySQL Status
shell> mysql -u root -p -e “\s” | grep Uptime

It will give uptime of MySQL Server. If it is less than 1 min, then server must have gone down and started again. So, you have look for Status Variable wait_timeout. By default, wait-timeout will be 28800 sec (8 Hours). Developer can change it while starting a server(change has to be done in my.cnf file) or by setting parameter “CLIENT_INTERACTIVE” in “mysql_real_connect()”. If Uptime is not less than a min, then there is a problem with Query.

Query can be too long or it is syntactically correct but not semantically.

Example: I am running a stored procedure spLogin(uname, pword, status). Here uname and pword are input parameters and status output parameter. To supply input to this stored procedure, I use user defined variables.

So, order of queries will be
mysql > SET @user = ‘sarma’, @passwd = ‘sarma’;
mysql > call spLogin(@username, @password, @status);
There is a chance that it will give an error 2013, as query is syntactically correct, but values passed to query are garbage.

Remedy will be
mysql> call spLogin(@user, @passwd, @status);
query Success(if stored procedure is correct).

But as I said earlier, it is tough to reproduce Error 2013. Care should be taken in passing inputs to either Queries or Stored Procedures and wait_timeout should be atleast 4 Hrs.