Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4095

Trigger fails with ERROR 38000: The exception 'java.sql.SQLException: ResultSet not open during VTIResultSet.getNextRowCore()

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.1.1
    • Fix Version/s: 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.1.1
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Patch Available
    • Bug behavior facts:
      Regression

      Description

      I don't have a reproduction without the user database yet, but it seems that recompiling the trigger stored prepared statements on upgrade is causing a result set to get closed and is causing the following error when firing a trigger in a global transaction. The holdability in an XA transaction is CLOSE_CURSORS_AT_COMMIT so it may be that there is a commit occurring as part of the process that is causing the problem. I haven't tried yet with a regular transaction and using default holdability CLOSE_CURSORS_AT_COMMIT. The error comes on a delete which fires an after delete statement trigger to insert some values into another table.

      Here is the stack trace:

      2009-03-13 14:10:49.375 GMT Thread[main,5,main] (XID = 1853834), (SESSIONID = 1), (DATABASE = derby/wpsdb), (DRDAID = null), Cleanup action starting

      2009-03-13 14:10:49.375 GMT Thread[main,5,main] (XID = 1853834), (SESSIONID = 1), (DATABASE = derby/wpsdb), (DRDAID = null), Failed Statement is: DELETE FROM XXX WHERE WSID=9

      ERROR 38000: The exception 'java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.' was thrown while evaluating an expression.

      at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:294)

      at org.apache.derby.iapi.error.StandardException.unexpectedUserException(StandardException.java:554)

      at org.apache.derby.impl.sql.execute.VTIResultSet.getNextRowCore(VTIResultSet.java:326)

      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:255)

      at org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(NestedLoopJoinResultSet.java:116)

      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:255)

      at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java:186)

      at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:127)

      at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:496)

      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:370)

      at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:173)

      at org.apache.derby.impl.sql.execute.StatementTriggerExecutor.fireTrigger(StatementTriggerExecutor.java:80)

      at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:278)

      at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(DeleteResultSet.java:479)

      at org.apache.derby.impl.sql.execute.DeleteResultSet.open(DeleteResultSet.java:167)

      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:370)

      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1203)

      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:596)

      at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:176)

      at org.apache.derby.iapi.jdbc.BrokeredStatement.executeUpdate(BrokeredStatement.java:113)

      at ReproRSClosed.main(ReproRSClosed.java:20)

      Caused by: java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.

      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)

      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:88)

      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:94)

      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:173)

      at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(EmbedConnection.java:2244)

      at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(ConnectionChild.java:151)

      at org.apache.derby.impl.jdbc.EmbedResultSet.checkIfClosed(EmbedResultSet.java:4280)

      at org.apache.derby.impl.jdbc.EmbedResultSet.checkExecIfClosed(EmbedResultSet.java:4292)

      at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:404)

      at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:388)

      at org.apache.derby.impl.sql.execute.VTIResultSet.getNextRowCore(VTIResultSet.java:308)

      ... 18 more

      Caused by: java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.

      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)

      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:135)

      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)

      ... 28 more

      ============= begin nested exception, level (1) ===========

      java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.

      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)

      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:88)

      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:94)

      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:173)

      at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(EmbedConnection.java:2244)

      at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(ConnectionChild.java:151)

      at org.apache.derby.impl.jdbc.EmbedResultSet.checkIfClosed(EmbedResultSet.java:4280)

      at org.apache.derby.impl.jdbc.EmbedResultSet.checkExecIfClosed(EmbedResultSet.java:4292)

      at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:404)

      at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:388)

      at org.apache.derby.impl.sql.execute.VTIResultSet.getNextRowCore(VTIResultSet.java:308)

      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:255)

      at org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(NestedLoopJoinResultSet.java:116)

      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:255)

      at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java:186)

      at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:127)

      at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:496)

      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:370)

      at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:173)

      at org.apache.derby.impl.sql.execute.StatementTriggerExecutor.fireTrigger(StatementTriggerExecutor.java:80)

      at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:278)

      at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(DeleteResultSet.java:479)

      at org.apache.derby.impl.sql.execute.DeleteResultSet.open(DeleteResultSet.java:167)

      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:370)

      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1203)

      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:596)

      at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:176)

      at org.apache.derby.iapi.jdbc.BrokeredStatement.executeUpdate(BrokeredStatement.java:113)

      at ReproRSClosed.main(ReproRSClosed.java:20)

      Caused by: java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.

      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)

      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:135)

      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)

      ... 28 more

      ============= end nested exception, level (1) ===========

      ============= begin nested exception, level (2) ===========

      java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.

      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)

      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:135)

      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)

      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:88)

      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:94)

      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:173)

      at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(EmbedConnection.java:2244)

      at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(ConnectionChild.java:151)

      at org.apache.derby.impl.jdbc.EmbedResultSet.checkIfClosed(EmbedResultSet.java:4280)

      at org.apache.derby.impl.jdbc.EmbedResultSet.checkExecIfClosed(EmbedResultSet.java:4292)

      at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:404)

      at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:388)

      at org.apache.derby.impl.sql.execute.VTIResultSet.getNextRowCore(VTIResultSet.java:308)

      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:255)

      at org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(NestedLoopJoinResultSet.java:116)

      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:255)

      at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java:186)

      at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:127)

      at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:496)

      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:370)

      at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:173)

      at org.apache.derby.impl.sql.execute.StatementTriggerExecutor.fireTrigger(StatementTriggerExecutor.java:80)

      at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:278)

      at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(DeleteResultSet.java:479)

      at org.apache.derby.impl.sql.execute.DeleteResultSet.open(DeleteResultSet.java:167)

      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:370)

      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1203)

      at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:596)

      at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:176)

      at org.apache.derby.iapi.jdbc.BrokeredStatement.executeUpdate(BrokeredStatement.java:113)

      at ReproRSClosed.main(ReproRSClosed.java:20)

      ============= end nested exception, level (2) ===========

      Cleanup action completed

        Attachments

        1. ReproDerby4095Local.java
          2 kB
          Katherine Marsden
        2. derby-4095_diff2.txt
          6 kB
          Katherine Marsden
        3. derby-4095_diff.txt
          6 kB
          Katherine Marsden

          Activity

            People

            • Assignee:
              kmarsden Katherine Marsden
              Reporter:
              kmarsden Katherine Marsden
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: