I would like to keep a MySQL database open for long periods of time without opening and closing it regularly (reason: it runs under Apache Tomcat, and for each request to the servlet, the servlet gets a request number from a MySQL database and table).
However, if left idle for a long period (i.e., no requests to the servlet for a long time), the database connection fails.
One solution is to open and close the database for each request, but I would think that would be expensive.
I'm wondering if a long idle connection failure is a common problem, and I am seeking advice on how best to use MySQL in this way.
Joined: Nov 09, 2009
The more I think about this problem I have, the more I think it can be solved by simply opening and closing the database, as I suggested, but with the cavaet that a "block" of request numbers be issued and dispensed.
I have implemented such a solution, so there is no need to respond to this ticket.
Yes, MySQL will notice after several hours that a connection hasn't been used, and drop its end of the connection. That of course makes it useless for the client, as you observed.
But you're concerned that it would be "expensive" to repeatedly create new connections. That might be the case, but doing an "expensive" operation (what does it take? half a second?) every few hours shouldn't be a concern. I think you are prematurely optimizing here. Just do this: if you try to use the connection, and it's broken, then close the database, open it again, and create a new connection.