MySQLNonTransientConnectionException in JDBC program at run time

Question

I have a JDBC MySQL connection in Java. My program works fine for simple execution of query.

If I run the same program for more than 10 hours and execute a query then I receive the following MySQL exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: 
Connection.close() has already been called. Invalid operation in 
this state.
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(
  Native Method)
  com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
  No operations allowed after statement closed.
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(
  Native Method)

I have not used close() method anywhere. I created database connection and opened it forever and always executed query. There is no place where I explicitly mentioned timeout for connection. I am unable to identify the problem.

Here is the code I use for the database connection:

 String driver = PropertyReader.getDriver();
 String url = dbURLPath;
 Class.forName(driver);
 connectToServerDB = DriverManager.getConnection(url);
 connectToServerDB.setAutoCommit(false);

What causes that exception?

Answer 1

You have to make a change in the configuration file or increase the timeout period of your database. If database remains idle for more than 8 hours it is closed by default.

Thanks

Answer 2

MySQL terminates a connection after 8 hour timeout. You can modify the timeout by setting wait_timeout variable in MySQL.

Yet, generally it is not such a good idea for an application to hold a connection for such a long time. A better approach is to set up a connection pool using a pooling API such as Apache DBCP and retrieve connections from the pool rather than directly though a driver. A connection pool will also take care about re-establishing a pooled connection if it dies for some reason, including timeouts.