MYSQL 8 Hours Time out Problem

Question

I am using MYSQL 5.0 and Tomcat 5.5. After 8 hours , MYSQL closes by default closes all idle connections and so I am getting an SQL Exception .Any solution to this problem

Answer 1

Which connection pool are you using? Most allow a heartbeat command to be executed periodically to keep connections from idling out.

EDIT: Looking at the DBCP configuration page, I think you should set an appropriate (cheap) validationQuery. You could then either tell DBCP to test this when potentially evicting idle connections (testWhileIdle) or tell it to test just before returning the connection (testOnBorrow). The first would (hopefully!) keep your idle connections alive; the second wouldn't, but wouldn't return you broken connections (i.e. if would create a new one if necessary).

Answer 2

Do you mean that you keep the connections alive while they are idle? I am no expert on mySql, but in the databases I have been working with it's considered good practice to keep the connection open only while it's needed, and then close it.

Answer 3

I don't know how to increase the timeout but I guess there should be a setting.. Why don't you create the connection when you need it, and close it after using it other than depending on a created connection?