Tomcat-Mysql connections in connection pool after idle timeout
I am currently using tomcat 6 and MySQL 5.1.56. It uses Mysql Connector-j to get DB connections and use them. I have set up a connection pool from this link.
http://people.apache.org/~fhanik/tomcat/jdbc-pool.html
Everything works fine as long as I use it.
If I leave it dormant for a few hours, I cannot fulfill any requests. I am getting the following exception.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: **No operations allowed after connection closed.**
java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: **Communications link failure**
The last packet successfully received from the server was 31,858,914 milliseconds ago. The last packet sent successfully to the server was 11 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3090)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3520)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281)
I saw another person on the stack overflowing the same question, but didn't answer. Synchronizing database connections (connection pooling in Tomcat)
I found a solution here, but I cannot use it as it directs me to use another third party app that I don't have to do this.
https://groups.google.com/group/ctjug-forum/browse_thread/thread/c326d3595d0c91af
Is there a way to solve this problem without resorting to deploying third party banners / apps.
I've already tried options like autoreconnect, test connection before and after connection and all other options.
This is my current setting in the conext.xml file
<Resource name="jdbc/appdb" auth="Container"
type="javax.sql.DataSource"
username="**********"
password="**********"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/appdb?autoReconnect=true"
maxActive="15" maxIdle="3"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="false"
validationQuery="SELECT 1"
validationInterval="30000"
timeBetweenEvictionRunsMillis="30000"/>
What's the best solution in this case?
I couldn't find anything in the mysql logs.
source to share
You shouldn't leave any connection open for hours.
You must have a persistence layer that gets the connection from the pool, performs the operation (s), commits or rolls back, and closes the connection to bring it back to the pool in the largest possible scope. Your application will be much more scalable if you don't limit yourself to one connection per session. Your application will be responsive enough because the pool will amortize the cost of opening each connection across all your requests.
Some pools have a configuration that allows you to test the connection before releasing it from the pool and provide a new one if it gets old. See how to do this with your pool / application server.
source to share
This configuration has been working for me for a while, but I am connecting to an Oracle database. I find that 30 seconds is too short to validate the Interval and timeBetweenEvictionRunsMillis, but that means the tests will run more often.
Also, I set numTestsPerEvictionRun to "-1", which means it will check all idle connections. Now the doc clearly states that this property is not being used, so it really is impossible to tell if it will help. You will have to try this to find out.
numTestsPerEvictionRun = "- 1"
source to share