please help mysql datasource timeout problem

[jhudson]

Hello,

I am using jboss 4.0.2 with a MySQL datasource (shown below) that has the idle-timeout-minutes property set to 10. But, after 8 hours of server uptime, I receive the following error when using connections from this datasource:

Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
 at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
 at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)
 at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:296)
 at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:118)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:1239)
 at org.hibernate.loader.Loader.doQuery(Loader.java:374)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
 at org.hibernate.loader.Loader.loadEntity(Loader.java:1312)
 at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:116)
 at ...

I know that this error will occur if a connection in the pool has been idle for more than 8 hours [default setting] because the underlying connection will go stale.

My datasource file is shown below:

<datasources>
 <local-tx-datasource>
 <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
 <jndi-name>jdbc/MySQLDB</jndi-name>
 <connection-url>jdbc:mysql://localhost/db_name?autoReconnectForPools=true&dumpQueriesOnException=true&useServerPrepStmts=false</connection-url>
 <driver-class>com.mysql.jdbc.Driver</driver-class>
 <user-name>****</user-name>
 <password>****</password>
 <idle-timeout-minutes>10</idle-timeout-minutes>
 <min-pool-size>0</min-pool-size>
 <max-pool-size>25</max-pool-size>

<exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
 <metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>
 </local-tx-datasource>
</datasources>

I should never have a connection lasting longer than 10 minutes, right? I understand that it might be a bit longer than that because the checker runs on a time interval that is half of the lowest idle-timeout-minutes but, not 8 hours...

I would really appreciate any advice you might have to offer. I have searched and searched for a possible cause and have not found any. Thank you very much.

Joe Hudson

[vickyk]

MySQL closes connections after 8 hours of inactivity. You either need to use a connection pool that handles stale connections or use the "autoReconnect" parameter
For more details read here
http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html#qandaitem-24-3-5-3-4

Configure check-valid-connection in the -ds.xml file . For more details please refer here
http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJCADatabaseFailover

[jhudson]

Thank you very much for the reply. I have actually tried both of those things. The problem with the autoReconnectForPools is that you still hit an error once and then the connection reconnects.

I just don't understand why that would even be happening if I have:

<idle-timeout-minutes>10</idle-timeout-minutes>

Does this not mean what I think it means? I think it means that if a connection has been around and unused for more than 10 minutes, then close it. Do you have any idea why I would have connections sitting around idle for 8 hours? Thank you very much for your help.

Joe

[raja143chowdary]

Hi Joe, am also facing the issue in one of my application, do you find the answer ?, if so, please let me know: [e-mail]