CODEWITHSUNDEEP
javamysqljdbc
Sunil Kumar Sahoo
Sunil Kumar Sahoo

Reputation: 53687

MySQLNonTransientConnectionException in JDBC program at run time

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?

Upvotes: 12

Views: 42040

Answers (3)

Vivek
Vivek

Reputation: 3613

I faced the same problem too. It's because of connection timeout by mysql and generally its not a good practice to extent the timeout in mysql as it serves for many other applications. It's good to reconnect the database on timeouts (i.e when this exception occurs) or to use some open source libraries for connection pooling like Apache DBCP as @slava suggested. ORM Frameworks takes care of this by default. Cheers!!

Upvotes: 2

Sunil Kumar Sahoo
Sunil Kumar Sahoo

Reputation: 53687

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

Upvotes: 6

Slava Imeshev
Slava Imeshev

Reputation: 1400

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.

Upvotes: 23

Related Questions