I am posting a solution to an issue I’ve seen thousands trying to fix while scouring forums online.  It seems that a “random” error and lost connection was occuring with various versions of MySQL database and people using IBatis database ORM framework.  We too faced the same dilemma and spent countless hours, database upgrades, config param changes, etc. trying to get it fixed.

The error that seemed to be random we discovered happened every time a certain insert query was called.  After further testing we could repeat the error and analyzed the java and SQL syntax.  We had used the <select key> tag to nest a select query within the insert query to return the id of the inserted record.  That was the cause of this mysterious error, not a connection timeout or pooling issue.  We removed the select key tag and instead made a second call to our DAO to retrieve the latest record id after the insert and everything worked.

I hope this helps others solve the issue that literally took weeks to discover.  Embarassingly it seemed random and the error logs displayed the following which did not imply a code error but instead a configuration error and lost connection to the database:

ERROR LOG FOR IBATIS CONNECTION LOST ISSUE:

Last packet sent to the server was 7 ms ago.
04:45:46,389 DEBUG [SimpleDataSource] Connection 12764831 is BAD: 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:3170)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3099)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1169)
at com.ibatis.common.jdbc.SimpleDataSource.pingConnection(SimpleDataSource.java:695)
at com.ibatis.common.jdbc.SimpleDataSource.access$000(SimpleDataSource.java:52)
at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.isValid(SimpleDataSource.java:791)
at com.ibatis.common.jdbc.SimpleDataSource.popConnection(SimpleDataSource.java:627)
at com.ibatis.common.jdbc.SimpleDataSource.getConnection(SimpleDataSource.java:222)
at com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransaction.init(JdbcTransaction.java:48)
at com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:89)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:565)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:540)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:84)
at com.goomzee.webservices.connect.security.Authorizer.getAccountType(Authorizer.java:296)
at com.goomzee.webservices.connect.security.Authorizer.checkAccess(Authorizer.java:75)
at com.goomzee.webservices.connect.GoomzeeConnectServiceSkeleton.GetInfo(GoomzeeConnectServiceSkeleton.java:595)
at com.goomzee.webservices.connect.GoomzeeConnectServiceMessageReceiverInOut.invokeBusinessLogic(GoomzeeConnectServiceMessageReceiverInOut.java:243)
at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.receive(AbstractInOutSyncMessageReceiver.java:37)
at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:454)
at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:284)
at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:136)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:595)

** END NESTED EXCEPTION **

(8 votes, average: 4.38 out of 5)

 Loading ...

This entry was posted on Saturday, April 7th, 2007 at 4:56 pm and is filed under Knowledge. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site. This post has been viewed 3,158 times.