aspose file tools*
The moose likes JDBC and the fly likes MySQL database connection fails after long idle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL database connection fails after long idle" Watch "MySQL database connection fails after long idle" New topic
Author

MySQL database connection fails after long idle

Dave Anderson
Greenhorn

Joined: Nov 09, 2009
Posts: 29
This is a MySQL question.

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.

David
Dave Anderson
Greenhorn

Joined: Nov 09, 2009
Posts: 29
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.

David
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18127
    
    8

But I have a comment anyway.

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQL database connection fails after long idle
 
Similar Threads
mysql connection
How to get connection at each request?
on servlets and jdbc
JDBC in JSP/Servlet question
Apache commons dbcp - SocketException: Too many open files