[DERBY-4095] Trigger fails with ERROR 38000: The exception 'java.sql.SQLException: ResultSet not open during VTIResultSet.getNextRowCore() - ASF JIRA

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.