[
https://issues.apache.org/jira/browse/DERBY-4095?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12681958#action_12681958
]
Kathey Marsden commented on DERBY-4095:
---------------------------------------
Most of what I have found on this issue today is quite mysterious to me, but I
figure I will summarize it here for weekend contemplation and attack it again
on Monday.
Here is the little program I use to reproduce the issue. Unfortunately table
and trigger information I can't post here, except to say there is a after
delete statement trigger that inserts some rows into a table.
import org.apache.derby.jdbc.EmbeddedDataSource;
import java.sql.*;
public class ReproRSClosedLocal {
public static void main(String[] args) throws Exception {
EmbeddedDataSource ds = new EmbeddedDataSource();
ds.setDatabaseName("mydb");
Connection conn = ds.getConnection();
conn.setAutoCommit(false);
conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
Statement s = conn.createStatement();
s.executeUpdate("DELETE FROM XXX WHERE WSID=9");
conn.rollback();
conn.close();
}
}
Notice the program rolls back so there should be no change from one run to the
next in terms of what gets deleted and what trigger fires.
I am not sure yet, what version the user database was upgraded from, just that
with the database as I got it from the user, the trigger works fine up until
660483 where the version was bumped and we start getting the error. What I
have noticed is.
- With the failing and passing runs we get the same number of commits, so there
doesn't seem to be an extra commit.
- If I run the program at 660482 (before the version change) and *then* move up
to 660483 the trigger works fine. I have to unzip the db to get the error
again.
- If I drop and recreate the trigger, and then run the program it runs fine.
- If I connect with 10.4 with soft upgrade the program works fine.
> 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
> Reporter: Kathey Marsden
> Assignee: Kathey Marsden
>
> 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.
