Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 9.0pe
    • Fix Version/s: 9.0pe
    • Component/s: jdbc
    • Labels:
      None
    • Environment:

      Operating System: All
      Platform: Linux

    • Issuezilla Id:
      942

      Description

      By default MySQL has a 8 hour timeout on idle connections, when SJAS 9.0 FCS
      tries to reconnect (for EJB3 entities) the following exception occurs (with
      paritial stack trace):

      System Exception
      com.sun.enterprise.resource.PoolingException:
      javax.resource.spi.LocalTransactionException: 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:3118)
      at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:5215)
      at
      com.mysql.jdbc.jdbc2.optional.ConnectionWrapper.setAutoCommit(ConnectionWrapper.java:109)
      at com.sun.gjc.spi.LocalTransaction.begin(LocalTransaction.java:61)
      at
      com.sun.enterprise.resource.ConnectorXAResource.getResourceHandle(ConnectorXAResource.java:199)
      at
      com.sun.enterprise.resource.ConnectorXAResource.start(ConnectorXAResource.java:106)
      at
      com.sun.enterprise.distributedtx.J2EETransactionManagerOpt.enlistResource(J2EETransactionManagerOpt.java:146)
      at
      com.sun.enterprise.resource.SystemResourceManagerImpl.enlistResource(SystemResourceManagerImpl.java:87)
      at
      com.sun.enterprise.resource.PoolManagerImpl.getResource(PoolManagerImpl.java:214)
      at
      com.sun.enterprise.connectors.ConnectionManagerImpl.internalGetConnection(ConnectionManagerImpl.java:312)
      at
      com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:176)
      at
      com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:152)
      at
      com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:145)
      at com.sun.gjc.spi.DataSource.getConnection(DataSource.java:93)
      at oracle.toplink.essentials.jndi.JNDIConnector.connect(JNDIConnector.java:130)
      at
      oracle.toplink.essentials.sessions.DatasourceLogin.connectToDatasource(DatasourceLogin.java:167)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatasourceAccessor.connect(DatasourceAccessor.java:218)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.connect(DatabaseAccessor.java:227)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatasourceAccessor.reconnect(DatasourceAccessor.java:421)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.reconnect(DatabaseAccessor.java:1152)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatasourceAccessor.incrementCallCount(DatasourceAccessor.java:205)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:464)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
      at
      oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.executeCall(UnitOfWorkImpl.java:1355)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:270)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:600)
      at
      oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2108)
      at
      oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2074)
      at
      oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:774)
      at
      oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:609)
      at
      oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:677)
      at
      oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:731)
      at
      oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2211)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:909)
      at
      oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:342)
      at
      oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:452)

      This is using MySQL 4.1.20 (5.0.18 has same problem) and MySQL Connector/J
      5.0.3, Mark Matthews (Client Connectivity Manager for MySQL) has always stated
      that this is not a JDBC driver but a lack of SQL exception handling by the
      client. Using autoReconnect results in an immediate connection failure anyway
      with latest driver - and I don't think they have plans to fix it.

      Enabling "Connection Validation" and "Close All Connections on Failure" has no
      effect, surely there must be a way round this problem within the app server with
      having to increase MySQL's connection timeout.

      Thanks

      Dan

        Activity

        Hide
        danielrhoades added a comment -

        "...a way round this problem within the app server with
        having to increase MySQL's connection timeout."

        should read:

        "...a way round this problem within the app server without
        having to increase MySQL's connection timeout.

        Show
        danielrhoades added a comment - "...a way round this problem within the app server with having to increase MySQL's connection timeout." should read: "...a way round this problem within the app server without having to increase MySQL's connection timeout.
        Hide
        Jagadish added a comment -

        Increasing timeout at mysql is not needed.
        1) switching on connection validation must solve this issue.
        2) With a recent bug fix, no idle-timeout (a con. pool property) connections
        will be left in the pool. They will be cleaned & refreshed periodically
        (=idle-time property of con pool)

        for case 1)
        switching on con. validation: default validation type : autocommit

        autocommit, metadata mode of validation may not work because, jdbc-drivers may
        cache this data and hence no actual database interaction will happen during
        connection validation.
        source : http://docs.sun.com/source/819-0076/jdbc.html#wp1019535

        To be safer, validation type need to be "table" and provide any existing table-name

        So, validation will actually will talk to the database. If con. is invalid, it
        will be dropped (exceptions will be logged anyways), recreated and this new
        connection will be given to application.

        Closing this as this works for me.
        Please re-open the issue if it is reproducible with validation type as "table"

        Show
        Jagadish added a comment - Increasing timeout at mysql is not needed. 1) switching on connection validation must solve this issue. 2) With a recent bug fix, no idle-timeout (a con. pool property) connections will be left in the pool. They will be cleaned & refreshed periodically (=idle-time property of con pool) for case 1) switching on con. validation: default validation type : autocommit autocommit, metadata mode of validation may not work because, jdbc-drivers may cache this data and hence no actual database interaction will happen during connection validation. source : http://docs.sun.com/source/819-0076/jdbc.html#wp1019535 To be safer, validation type need to be "table" and provide any existing table-name So, validation will actually will talk to the database. If con. is invalid, it will be dropped (exceptions will be logged anyways), recreated and this new connection will be given to application. Closing this as this works for me. Please re-open the issue if it is reproducible with validation type as "table"
        Hide
        danielrhoades added a comment -

        Sorry about the LATE response. Table validation solves the problem for me

        Show
        danielrhoades added a comment - Sorry about the LATE response. Table validation solves the problem for me

          People

          • Assignee:
            lancea
            Reporter:
            danielrhoades
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: