You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Kathey Marsden (JIRA)" <ji...@apache.org> on 2009/03/18 01:13:50 UTC
[jira] Updated: (DERBY-4095) After upgrading from 10.3.3.0 -
(660482) to 10.3.3.1 - (660483) trigger in global transaction fails with
ERROR 38000: The exception 'java.sql.SQLException: ResultSet not open.
[ https://issues.apache.org/jira/browse/DERBY-4095?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-4095:
----------------------------------
Attachment: ReproDerby4095Local.java
So yes, the failing case was using a different plan which reopened the VTI multiple times, which just happened to change on upgrade. I was able to construct a reproduction (Derby4095Local.java) by using optimizer directives in the trigger definition.
I think the CLOSE_CURSORS_AT_COMMIT was a red herring. I was able to reproduce with the original user case without setting CLOSE_CURSORS_AT_COMMIT, so that was not really part of it.
Anyway, here is the trunk stack trace.
>java Derby4095Local
Exception in thread "main" java.sql.SQLException: The exception 'java.sql.SQLException: ResultSet not open. Operation 'n
ext' not permitted. Verify that autocommit is OFF.' was thrown while evaluating an expression.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java:223)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:398)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175)
at ReproDerby4095Local.main(ReproDerby4095Local.java:14)
Caused by: java.sql.SQLException: The exception 'java.sql.SQLException: ResultSet not open. Operation 'next' not permitt
ed. Verify that autocommit is OFF.' was thrown while evaluating an expression.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:11
9)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
... 10 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:11
9)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:93)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:172)
at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(EmbedConnection.java:2951)
at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(ConnectionChild.java:151)
at org.apache.derby.impl.jdbc.EmbedResultSet.checkIfClosed(EmbedResultSet.java:4275)
at org.apache.derby.impl.jdbc.EmbedResultSet.checkExecIfClosed(EmbedResultSet.java:4287)
at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:387)
at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:371)
at org.apache.derby.impl.sql.execute.VTIResultSet.getNextRowCore(VTIResultSet.java:338)
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.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:127)
at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1058)
at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:495)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeSubStatement(GenericPreparedStatement.java:286)
at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:159)
at org.apache.derby.impl.sql.execute.StatementTriggerExecutor.fireTrigger(StatementTriggerExecutor.java:80)
at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:269)
at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(DeleteResultSet.java:465)
at org.apache.derby.impl.sql.execute.DeleteResultSet.open(DeleteResultSet.java:158)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
I think I will pursue the fix of having TriggerOldTransitionRows reconstruct the ResultSet on executeQuery. Please let me know if that does not sound acceptable.
> After upgrading from 10.3.3.0 - (660482) to 10.3.3.1 - (660483) trigger in global transaction fails with ERROR 38000: The exception 'java.sql.SQLException: ResultSet not open.
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4095
> URL: https://issues.apache.org/jira/browse/DERBY-4095
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.3.1, 10.4.3.0, 10.5.0.0
> Reporter: Kathey Marsden
> Assignee: Kathey Marsden
> Attachments: ReproDerby4095Local.java
>
>
> 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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.