InnoDb Transaction timeout

[Olivier Poitrey]

Hello,

I'm having a problem with Connector/J (stable and beta versions) and
InnoDb Transactions.

I'm trying to lock on a single row for update before doing some long
time stuff (XML-RPC call) and updating then commit. The problem is
that after the XML-RPC call, the SQL connection seems closed, and I'm
getting an "java.sql.SQLException: Communication link failure:
java.io.EOFException, underlying cause: null".

It's really easy to reproduce it by replacing the XML-RPC call by
a Thread.sleep(10000) for instance.

I didn't mentione that my SQL code works well without the slow code
part (or the sleep), so it really looks like a timeout
problem... In that direction, I tried raising all MySQL timeouts
variables like innodb_lock_wait_timeout or net_*_timeout but nothing
seems to works...

Here is the code:

Connection connection = null;

try
{
    connection = Vdatabase.getConnection();
    connection.setAutoCommit(false);

    PreparedStatement lock_stmt =
        connection.prepareStatement("SELECT flag FROM table WHERE field=? FOR UPDATE");
    lock_stmt.setString(1, "something");
    ResultSet rs = lock_stmt.executeQuery();

    // replacing the code with a sleep illustrates the fact
    // that the code is not the cause of the problem here
    Thread.sleep(10000);

    PreparedStatement updt_stmt =
        connection.prepareStatement("UPDATE table SET flag=1 WHERE field=?");
    updt_stmt.setString(1, "something");
    updt_stmt.execute();

    connection.commit();
}
finally
{
    if(connection != null)
    {
        try
        {
           connection.close();
        }
        catch(Exception e)
        {
           // ignore
        }
    }
}

Then the full exception:

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:1388)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1532)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1923)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1340)
        at
org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
        at xxxx.MyClass.method(MyClass.java:228)
        at xxxx.MyClassSOAPBinding.method(MyClassSOAPBinding.java:84)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:324)
        at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:402)
        at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:309)
        at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:333)
        at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:71)
        at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:150)
        at org.apache.axis.SimpleChain.invoke(SimpleChain.java:120)
        at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:481)
        at org.apache.axis.server.AxisServer.invoke(AxisServer.java:323)
        at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:854)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
        at
org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:339)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
        at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
        at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
        at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
        at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
        at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
        at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
        at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
        at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
        at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700)
        at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584)
        at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
        at java.lang.Thread.run(Thread.java:534)


** END NESTED EXCEPTION **


-- 
______________________________________________________________________
 O  l  i  v  i  e  r    P  o  i  t  r  e  y