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.开发者_如何学Pythonreflect.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?
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.
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
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!!
精彩评论