StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTomcat-Mysql connections in a Connection Pool timing out after idle time
text
Body
copied!<p>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.</p> <p><a href="http://people.apache.org/~fhanik/tomcat/jdbc-pool.html" rel="nofollow noreferrer">http://people.apache.org/~fhanik/tomcat/jdbc-pool.html</a></p> <p>Everything works fine as long as I am using it.</p> <p>If I leave it idle for a few hours then I am not able to execute any queries. I get the following exception.</p> <pre><code>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) </code></pre> <p>I saw another person on stack overflow the same question but with not answer. <a href="https://stackoverflow.com/questions/7095687/database-connections-timing-out-connection-pooling-on-tomcat">Database Connections Timing Out (Connection Pooling On Tomcat)</a></p> <p>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.</p> <pre><code>https://groups.google.com/group/ctjug-forum/browse_thread/thread/c326d3595d0c91af </code></pre> <p>Is there any way I can solve this problem without having to resort to deploying third party jars/apps.</p> <p>I have already tried the options such as autoreconnect, test connection before and after connection was accessed and all other options.</p> <p>This is my current setup in conext.xml</p> <pre><code> <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"/> </code></pre> <p>What is the best practice solution in this case?</p> <p>I couldn't find anything in the mysql logs.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload