MySQL communication link failure

[Stijn de Witt]

Hi,
We have developed a web application using OJB. All seems to work fine, 
but after some hours of being deployed we run into a problem.

We get the exception shown below (lots of lines snipped for brevity).
       at 
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown
       at 
org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(Unknown
       at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown 
Source)
       at 
org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(Unk
       at 
org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQue
       at 
org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery
       at 
org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(
       ... 89 more
Caused by: java.sql.SQLException: Communication link failure: 
java.io.EOFException: Underlying cause: null

** BEGIN NESTED EXCEPTION **
java.io.EOFException
STACKTRACE:
java.io.EOFException
       at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1394)
       at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1538)
       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929)
       at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
       at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
       at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
       at 
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
       at 
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown
       at 
org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(Unknown
      .........
       at 
nl.bergland.shop.impl.ShopServiceImpl.getShop(ShopServiceImpl.java:10
       at 
nl.bergland.shop.impl.portlets.ShopPortlet.doView(ShopPortlet.java:48
       at javax.portlet.GenericPortlet.doDispatch(GenericPortlet.java:247)
      .........
       at 
org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:866)
       at 
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP
       at java.lang.Thread.run(Thread.java:534)

** END NESTED EXCEPTION **
I have done some investigating on the internet and seen many posts of 
people with this exception. It happens with Hibernate too, so it is not 
an OJB problem, but maybe you know how to fix it using OJB.

In short, the problem seems to be caused by MySQL dropping idle 
connections after eight hours. The OJB connection pool attempts to use 
such a dropped connection, causing the exception.

I have seen a number of solutions mentioned:
1)  Specify the MySQL autoReconnect="true" property in the connection 
string:

jdbc:mysql://localhost:3306/<database name>?autoReconnect=true
( http://212.26.206.162/roller/page/jakkovos/20040815 )
2) Make sure the used pool drops idle connections before MySQL does
3) Make sure the connection is validated before being used with a validation 
query
4) Disable pooling with ConnectionFactoryNotPooledImpl
5) Use a DataSource managed by the AppServer (Tomcat / JBoss) i.c.w JNDI lookup
Now I am getting a bit lost in all these options, and testing them is 
very hard. Does anyone here have experience with this issue and know of 
an easy way to fix it? Option 1) with autoReconnect seems most 
attractive to me, but how should I set such a connection parameter using 
OJB? Is it even possible?

Thanks for any help,
Stijn de Witt
Bergland IT
http://www.bergland-it.nl/

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]