db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Commented: (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.
Date Tue, 17 Mar 2009 20:51:50 GMT

    [ 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.


Mime
View raw message