While working on a project with Tomcat server and MySQL database, I got an exception saying something like "Lost connection to MySQL server".
This problem I faced every morning at the start of a day.
i.e. If I use my application before leaving for a day and if server was left RUNNING, on the next morning when I try to use application again, above exception was thrown.
Problem here was, MySQL's variable "interactive_timeout".
Definition of "interactive_timeout" from MySQL manual is as follows -
"The number of seconds the server waits for activity on an interactive connection before closing it."
This variable's default value is 28800 i.e. 8 hrs.
So when I kept my server running all the night, no interaction was there on connection and server was closing the connection.
This connection was tried to be used by my application and hence above exception was thrown.
Some of the solutions to this problem are
1. If you are using connection pooling, keep the connection interactive so that it is never idle for ${interactive_timeout} seconds
2. Change the value of variable on MySQL server as per your requirement (This solution, I think, is not the best solution)
3. Before firing any query to server, check whether connection is active or not
Drop a comment to let me know whether this helped you in solving the problem.
Thanks.
Subscribe to:
Post Comments (Atom)
6 comments:
hi,
hey that seems to be a good piece of advice!! Thanks, my project seems to have a similiar error and we were wondering the reason for it...
I'll check out the workaround ad let you know...
Thanks
Hey, hi
As i said we would check out the solution and get back to you - here i am!
Firstly, the trick did not work for me. :( When i did set interactive_timeout to 0 the server would timeout instantly (the '0' in the timeout).
Well, here are a couple of investigations I did to find out why interactive_timeout may not work,
http://dev.mysql.com/doc/refman/5.0/en/cj-configuration-properties.html says that "Set the CLIENT_INTERACTIVE flag, which tells MySQL to timeout connections based on INTERACTIVE_TIMEOUT instead of WAIT_TIMEOUT"
Maybe that hint, that the choice of the value depends on the CLIENT_INTERACTIVE variable, which implies that the trick MAY not work ALWAYS.
Also, http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html says " interactive_timeout
The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout"
and "wait_timeout
The number of seconds the server waits for activity on a non-interactive connection before closing it.
On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.
"
For now the solution that I've fallen backto is that I've set the wait_timeout and interactive_timeout to 7 days (604800)
Thats an interesting observation. In windows, I think CLIENT_INTERACTIVE flag is set to true by default.
Did you try setting up value of interactive_timeout to something other than 0? Try using some positive value.
Link is useful in knowing all the properties that we can set for a connection while obtaining connection using DriverManager.getConnection(String url, Properties info);
http://galileo.gamepoint.com.au/pub/mysql/doc/connector/j/en/
Setting CLIENT_INTERACTIVE flag is explained in that article. As parameters we specify is on connection basis, we need not set properties for server.
Good recommendations and analysis.
One can also ping on a mysql object to bring it alive (if the connect is dead) before performing any query on it. E.g.
mysql_ping(mysql);
if(mysql_query(mysql, query) != 0)
{
printf("mysql_query failed: %s\n",query);
exit(0);
}
hi,
4 years after.. would you mind explaining solution 1 a bit further? maybe with some code snippet to put in my hibernate.cfg.xml ?
thanks a lot!
You are right, after 4 years, I don't have the code with me.
Will try to get the basic code and paste it here.
Post a Comment