[
https://issues.apache.org/jira/browse/DERBY-4095?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12682793#action_12682793
]
Kathey Marsden commented on DERBY-4095:
---------------------------------------
In the failing case we go through VTIResultSet.reopenCore during execution of
the trigger and close the ResultSet associated with the
TriggerOldTransitionRows VTI. Here is the trace when we go through this code.
java.lang.Exception: TemporaryRowHolderResultSet.close()
at
org.apache.derby.impl.sql.execute.TemporaryRowHolderResultSet.close(TemporaryRowHolderResultSet.java:574)
at
org.apache.derby.impl.jdbc.EmbedResultSet.close(EmbedResultSet.java:596)
at
org.apache.derby.impl.sql.execute.VTIResultSet.reopenCore(VTIResultSet.java:258)
at
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.reopenCore(ProjectRestrictResultSet.java:212)
at
org.apache.derby.impl.sql.execute.JoinResultSet.openRight(JoinResultSet.java:266)
at
org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(NestedLoopJoinResultSet.java:147)
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:81)
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 ReproRSClosedLocal.main(ReproRSClosedLocal.java:16)
In VTIResultSet reopenCore we have:
/**
* If the VTI is a version2 vti that does not
* need to be instantiated multiple times then
* we simply close the current ResultSet and
* create a new one via a call to
* PreparedStatement.executeQuery().
*
* @see NoPutResultSet#openCore
* @exception StandardException thrown if cursor finished.
*/
public void reopenCore() throws StandardException
{
if (reuseablePs)
{
/* close the user ResultSet.
*/
if (userVTI != null)
{
try
{
--> We close the RS here userVTI.close();
userVTI = userPS.executeQuery();
/* Save off the target VTI */
if (isTarget)
{
activation.setTargetVTI(userVTI);
}
} catch (SQLException se)
{
throw
StandardException.unexpectedUserException(se);
}
}
}
else
{
close();
openCore();
}
}
The TriggerOldTransitionRows VTI seems to have reusablePs set to true, so we
close the ResultSet and call executeQuery() on the VTI prepared statement, but
our TriggerOldTransitionRows.executeQuery() method only returns the existing
resultSet, even if it has been closed and this the issue. It is not really a
reusable prepared statement. If I change executeQuery to rebuild the resultSet
like:
public ResultSet executeQuery() throws SQLException {
TriggerExecutionContext tec = Factory.getTriggerExecutionContext();
resultSet = tec.getOldRowSet();
return resultSet;
}
instead of just returning the existing ResultSet, it works ok, but I am not
sure that this is the right fix.
In the working case we don't seem to go through this reopenCore code. I'm not
entirely sure why, perhaps a different plan?
Also not clear to me is what CLOSE_CURSORS_AT_COMMIT has to do with this,
because it doesn't seem to be a commit that is closing the result set.
I will debug more and try to find answers to those questions but thought I
would post my findings so far.
> 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.
