CODEWITHSUNDEEP
javamysqltomcatdatabase-connectionconnection-pooling
kensen john
kensen john

Reputation: 5519

Tomcat-Mysql connections in a Connection Pool timing out after idle time

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 setup a connection pool as per this link.

http://people.apache.org/~fhanik/tomcat/jdbc-pool.html

Everything works fine as long as I am using it.

If I leave it idle for a few hours then I am not able to execute any queries. I get 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 stack overflow the same question but with not answer. Database Connections Timing Out (Connection Pooling On Tomcat)

I found a solution here but I cannot use it as it directs me to use another third party application which I do not have permission to do so.

https://groups.google.com/group/ctjug-forum/browse_thread/thread/c326d3595d0c91af

Is there any way I can solve this problem without having to resort to deploying third party jars/apps.

I have already tried the options such as autoreconnect, test connection before and after connection was accessed and all other options.

This is my current setup in conext.xml

 <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 is the best practice solution in this case?

I couldn't find anything in the mysql logs.

Upvotes: 3

Views: 4328

Answers (3)

duffymo
duffymo

Reputation: 308733

You should not leave any connection open for hours.

You should have a persistence layer that gets a connection from a pool, performs the operation(s), commits or rolls back, and closes the connection to return it back to the pool in the smallest scope possible. Your application will be far more scalable if you don't limit yourself to one connection per session. Your app will be responsive enough, because the pool will amortize the cost of opening each connection over all your requests.

Some pools have configuration that allows you to check a connection before letting it out of the pool and provide a fresh one if it's stale. See how to do it with your pool/app server.

Upvotes: 2

souser
souser

Reputation: 6100

I have this configuration working for a while but I connect to an Oracle database. I feel 30 seconds is too short for the validationInterval and timeBetweenEvictionRunsMillis but it only means that the tests will run more frequently.

Apart from that, I have set numTestsPerEvictionRun to "-1" which means that it will test all the idle connections. Now, the document clearly states that this property is not in use, so really cannot tell whether it will help. You will have to try it to find out.

numTestsPerEvictionRun="-1"

Upvotes: 1

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7282

Add following attributes to your resource tag

validationQuery="select 1"
testOnBorrow="true"
validationInterval="YOUR_VALIDATON_INTERVAL"

and tomcat will validate the connection when borrowing it from connection pool.

Upvotes: 2

Related Questions