derbylang/procedureInTrigger: not able to create trigger due to an open ResultSet

• Key: DERBY-1585
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Daniel John Debrunner
• Reporter: Henri van de Scheur
• Component/s: SQL
• Affects Version/s: 10.2.1.6
• Fix Version/s: 10.3.3.0, 10.4.1.3

Environment

------------------ Java Information ------------------
Java Version: 1.6.0-rc
Java Vendor: Sun Microsystems Inc.
Java home: /usr/local/java/jdk1.6.0_b91/jre
Java classpath:
test/junit.jar:test/dbprocedures.jar:test/jmxremote.jar:test/javadbtests.jar: test/Dots.jar:test/mail.jar:test/activation.jar:test/Perfmon.jar: test/jakarta-oro-2.0.8.jar:test/commons-logging.jar:test/jagops.jar: test/jdmkrt.jar:test/db2jcc_license_c.jar:test/jet.jar:test/jetbatch.jar: test/db2jcc.jar:test/jag.jar:test/jagclient.jar:test/jagutils.jar:test/GenCfg.jar: test/jmxremote_optional.jar:test/jmx.jar:test/hadbjdbc4.jar:derbyTesting.jar: derby.jar:derbyLocale_zh_TW.jar:derbytools.jar:derbyLocale_ko_KR.jar: derbyLocale_zh_CN.jar:derbyLocale_es.jar:derbyLocale_de_DE.jar:derbyLocale_ja_JP.jar: derbynet.jar:derbyLocale_pt_BR.jar:derbyclient.jar:derbyLocale_fr.jar:derbyrun.jar: derbyLocale_it.jar:

OS name: Linux
OS architecture: i386
OS version: 2.6.9-34.ELsmp
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derby.jar] 10.2.0.4 alpha - (423199)
[/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbytools.jar] 10.2.0.4 alpha - (423199)
[/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbynet.jar] 10.2.0.4 alpha - (423199)
[/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/derbyclient.jar] 10.2.0.4 alpha - (423199)
[/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/db2jcc_license_c.jar] 2.4 - (17)
[/export/home/tmp/jagtmp/autoderbyN_regression/install/lib/test/db2jcc.jar] 2.4 - (17)
------------------------------------------------------
----------------- Locale Information -----------------
Current Locale : [English/United States [en_US]]
Found support for locale: [de_DE]
version: 10.2.0.4 alpha - (423199)
Found support for locale: [es]
version: 10.2.0.4 alpha - (423199)
Found support for locale: [fr]
version: 10.2.0.4 alpha - (423199)
Found support for locale: [it]
version: 10.2.0.4 alpha - (423199)
Found support for locale: [ja_JP]
version: 10.2.0.4 alpha - (423199)
Found support for locale: [ko_KR]
version: 10.2.0.4 alpha - (423199)
Found support for locale: [pt_BR]
version: 10.2.0.4 alpha - (423199)
Found support for locale: [zh_CN]
version: 10.2.0.4 alpha - (423199)
Found support for locale: [zh_TW]
version: 10.2.0.4 alpha - (423199)
------------------------------------------------------

Description

********* Diff file derbyall/derbylang/procedureInTrigger.diff
*** Start: procedureInTrigger jdk1.6.0-rc derbyall:derbylang 2006-07-19 13:52:20 ***
714a715,730
> ERROR X0X95: Operation 'CREATE TRIGGER' cannot be performed on object 'T1' because there is an open ResultSet dependent on that object.
> ij> --- delete a row. check that trigger is fired - procedure should be called once
> delete from t1 where i=10;
> 1 row inserted/updated/deleted
> ij> --- check delete is successful
> select * from t1;
> I |B
> ---------------------------
> 5 |two
> 6 |four
> 8 |eight
> ij> drop trigger select_from_trig_table;
> ERROR 42X94: TRIGGER 'SELECT_FROM_TRIG_TABLE' does not exist.
> ij> --- use procedures which alter/drop trigger table and some other table
> create trigger alter_table_trig AFTER delete on t1
> for each STATEMENT mode db2sql call alter_table_proc();
716,732d731
< ij> --- delete a row. check that trigger is fired - procedure should be called once
< delete from t1 where i=10;
< selectRows - 1 arg - 1 rs
< 1 row inserted/updated/deleted
< ij> --- check delete is successful
< select * from t1;
< I |B
< ---------------------------
< 5 |two
< 6 |four
< 8 |eight
< ij> drop trigger select_from_trig_table;
< 0 rows inserted/updated/deleted
< ij> --- use procedures which alter/drop trigger table and some other table
< create trigger alter_table_trig AFTER delete on t1
< for each STATEMENT mode db2sql call alter_table_proc();
< 0 rows inserted/updated/deleted
Test Failed.
*** End: procedureInTrigger jdk1.6.0-rc derbyall:derbylang 2006-07-19 13:52:34 ***

Comments

[Kathey Marsden]

I will put this in component test for now. I am not sure ultimately if it should be test or sql

[Myrna van Lunteren]

I see a similar diff with wctme5.7/j9 2.2:
*** Start: procedureInTrigger jdk1.3.1 subset - 2.2 2006-07-28 11:45:34 ***
700a701,716
> ERROR X0X95: Operation 'CREATE TRIGGER' cannot be performed on object 'T1' because there is an open ResultSet dependent on that object.
> ij> --- delete a row. check that trigger is fired - procedure should be called once
> delete from t1 where i=10;
> 1 row inserted/updated/deleted
> ij> --- check delete is successful
> select * from t1;
> I |B
> ---------------------------
> 5 |two
> 6 |four
> 8 |eight
> ij> drop trigger select_from_trig_table;
> ERROR 42X94: TRIGGER 'SELECT_FROM_TRIG_TABLE' does not exist.
> ij> --- use procedures which alter/drop trigger table and some other table
> create trigger alter_table_trig AFTER delete on t1
> for each STATEMENT mode db2sql call alter_table_proc();
702,718d717
< ij> --- delete a row. check that trigger is fired - procedure should be called once
< delete from t1 where i=10;
< selectRows - 1 arg - 1 rs
< 1 row inserted/updated/deleted
< ij> --- check delete is successful
< select * from t1;
< I |B
< ---------------------------
< 5 |two
< 6 |four
< 8 |eight
< ij> drop trigger select_from_trig_table;
< 0 rows inserted/updated/deleted
< ij> --- use procedures which alter/drop trigger table and some other table
< create trigger alter_table_trig AFTER delete on t1
< for each STATEMENT mode db2sql call alter_table_proc();
< 0 rows inserted/updated/deleted
Test Failed.
*** End: procedureInTrigger jdk1.3.1 subset - 2.2 2006-07-28 11:46:08 ***

In other words, wctme5.7 barks earlier than jdk16.

[Deepa Remesh]

Attaching a patch 'derby1585_v1.diff' to resolve the intermittent failure seen in the test lang/procedureInTrigger.sql.

The intermittent failure was happening when a result set returned by a procedure called from a trigger remains open. Then when we create another trigger on the same table, we get following exception:
ERROR X0X95: Operation 'CREATE TRIGGER' cannot be performed on object 'T1' because there is an open ResultSet dependent on that object.

The purpose of the test is only to test after/before triggers that call a procedure which executes a select statement. As we are calling the procedure from a trigger, there is no need to use a procedure that returns a result set. The result set cannot be accessed from the trigger. So I changed this test to call a new procedure which executes a select, uses the result set and closes it. I believe this will solve the intermittent failure.

This patch modifies only tests. I ran the changed test multiple times on jdk142, jdk15, and j9_22 vm successfully. I modified the master file for jdk16 but have some trouble with jdk16 environment on my machine. So I haven't run the test on jdk16.

Please take a look at this patch and commit if okay.

[Daniel John Debrunner]

Does this mean that there is a bug?
Either:

When a procedure is called from a trigger any ResultSet's returned are not explictly closed by the trigger calling mechanism?

or

procedures that return result sers should not be allowed in triggers.

Might need to look at the standard to see what is expected here. For the first case the question would be should the
returned dynamic result sets simply be closed, or should each row be fetched and then closed. The fetching of rows
could have functions that produce side effects the application is expecting to occur.

[Deepa Remesh]

Pasting the link to my previous comment to derby-dev for reference.
http://www.nabble.com/Re%3A--jira--Commented%3A-%28DERBY-1585%29-derbylang-procedureInTrigger%3A-not-able-to-create-trigger-due-to-an-open-ResultSet-p6235128.html

In the code, the result set returned from executing the trigger action statement is closed. GenericTriggerExecutor.executeSPS fetches all rows and closes the result set. So the problem seems to be elsewhere.

[Rick Hillegas]

Moving to 10.2.2.0.

[Deepa Remesh]

I had earlier said:
"In the code, the result set returned from executing the trigger action statement is closed. GenericTriggerExecutor.executeSPS fetches all rows and closes the result set. So the problem seems to be elsewhere."

What I had previously stated is not fully correct. Result set returned by executing the trigger action statement (in this case the call statement) is closed. But the result set which is closed is the internal CallStatementResultSet.
The result set returned by the procedure is not closed. It may get closed if it is garbage collected. However, if the result set remains open, a subsequent trigger creation will fail with following error:
ERROR X0X95: Operation 'CREATE TRIGGER' cannot be performed on object 'T1' because there is an open ResultSet dependent on that object.

[Øystein Grøvlen]

What is the status on this bug fix? It would be nice to get this
fixed since I think it is one of the more common intermittent in the
nightly tests. This week it has also happened twice in the tinderbox
test.

[Henri van de Scheur]

I agree it would be nice to get this one fixed, but I do not have the
resources to follow up bug fixes....
After Deepa assigned this bug, I don't think there has been so much
activity......
I can send a copy of this mail to Rick, maybe it starts itching...... ;-)

Henri


--

With regards,



Henri van de Scheur, Database Technology Group,
Sun Microsystems, Trondheim, Norway

[Deepa Remesh]

After the initial analysis, I hadn't spent much time on this bug. Thanks for bringing this up. I'm unassigning now if anyone else wants to pick this up.

[Rick Hillegas]

Move to 10.2.3.0.

[Andrew McIntyre]

Unsetting Fix Version for unassigned issues.

[Knut Anders Hatlen]

I have a machine where this test (actually, it's the new ProcedureInTriggerTest) fails more or less consistently (running on OpenSolaris and Sun JDK 6). The attached patch makes the test pass, but it's more of a hack than a fix.

The patch makes GenericTriggerExecutor call close() on any dynamic result sets returned by the trigger action, and on their statements. However, the calls to close() are no-ops because setting up the context stack fails and the error is silently ignored. Therefore, I also had to make EmbedPreparedStatement.close() call markUnused() on its activation if setupContextStack() failed. The same thing should probably have been done with singleUseActivation in EmbedResultSet.close() in case the ResultSet didn't come from a PreparedStatement, but that wasn't necessary for this particular issue.

Ugly, I know, but at least it's a starting point... No other tests were run.

[Daniel John Debrunner]

I see the intent in closing the result sets but this (in edge cases) can lead to exceptions being thrown in a trigger but calling the procedure directly would not throw those exceptions.
That would be when the close() fails on a ResultSet that would be ignored by EmbedStatement.processDynamicResults.
Also if multiple result sets are returned and an exception is thrown on close, then the remaining result sets would not be closed, thus leaving the potential for
the bug to resurface.

It's also strange that the close calls are failing, is it because the result sets are in a limbo between the old closed nested connection and the user connection?
I.e. if this was a plain CALL statement then the ResultSets would be linked into the valid open user connection, which obviously does not (and should not) happen here.

Not sure I have good ideas here on the best approach, but I don't understand why the close() is failing or how the change in EmbedPreparedStatement.close() achieves.
Also the comment about singleUseActivation in EmbedResultSet.close() is not clear to me.

[Kristian Waagan]

There were problems with the line-breaking in the environment field. I removed the prefix '/export/home/tmp/jagtmp/autoderbyN_regression/install/lib' from the Java classpath and broke the lines manually.

[Knut Anders Hatlen]

close() doesn't actually fail, but EmbedResultSet.close() and EmbedPreparedStatement.closeActions() have this piece of code:

try {
setupContextStack(); // make sure there's context
} catch (SQLException se) {
// we may get an exception here if this is part of an XA transaction
// and the transaction has been committed
// just give up and return
return;
}

The exception that is swallowed is a no-current-connection error, so your limbo theory could very well be correct.

With my comment about singleUseActivation I meant to say that the attached patch only fixes the problem if the stored procedure used a PreparedStatement (since ResultSet.close() was a no-op here and I only fixed/hacked EmbedPreparedStatement.closeActions()). If the stored procedure had used a Statement, we would have to find another way to mark the activation as unused.

I guess the main problem is that we only have access to the dynamic results through their JDBC interface, and since we're in the SQL execution layer, we cannot simply call some package private methods to do the required magic.

Anyway, the patch is not meant for commit. Only food for thought.

[Mike Matrigali]

I believe the following junit test failure is the same bug, if not let me know and I will file it as a separate case. it also seems intermittent. It followed on this date in the nightly poster runs in only one enviroment and passed in the others. It failed in
http://dbtg.thresher.com/derby/test/Daily/jvm1.6/testing/testlog/sles/531517-suitesAll_diff.txt

There was 1 error:
1) testTriggerNegative(org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest)java.sql.SQLException: Operation 'CREATE TRIGGER' cannot be performed on object 'T1' because there is an open ResultSet dependent on that object.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.testTriggerNegative(ProcedureInTriggerTest.java:396)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:88)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
at junit.extensions.TestSetup.run(TestSetup.java:25)
Caused by: java.sql.SQLException: Operation 'CREATE TRIGGER' cannot be performed on object 'T1' because there is an open ResultSet dependent on that object.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.verifyNoOpenResultSets(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.prepareToInvalidate(Unknown Source)
at org.apache.derby.impl.sql.depend.BasicDependencyManager.coreInvalidateFor(Unknown Source)
at org.apache.derby.impl.sql.depend.BasicDependencyManager.invalidateFor(Unknown Source)
at org.apache.derby.impl.sql.execute.CreateTriggerConstantAction.executeConstantAction(Unknown Source)
at org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
... 32 more

[Dag H. Wanvik]

See also my experience/run-in with this test in DERBY-1947. Relying
on explicitly running System.gc and System.runFinalization
is not a sure way to make this work (cf
GenericLanguageConnectionContext#verifyNoOpenResultSets),
so intermittent occurences would be expected.

[Daniel John Debrunner]

Section 4.27.5 of the TECHNICAL CORRIGENDUM 1 to the SQL 2003 Standard details what happens to dynamic result sets in detail, the SQL 2003 foundation document is missing these details.

If the dynamic result sets are unreachable, which they will be for a CALL statement as a trigger's single action statement, then they must be destroyed, which is ResultSet.close() in JDBC terms.

Thus I think the general idea of the close.diff is correct, but I think a cleaner implementation would be to encapsulate the logic in the close of the activation for the CALL statement. (also need to ensure that all result sets are closed, regardless of any exceptions).

[Daniel John Debrunner]

Patch that closes dynamic result sets in the CallStatementResultSet.close(), thus encapsulating the logic rather than putting CALL specific code in the general trigger path. Code will also work for any other situations where a CALL statement will be executed and the dynamic results inaccessible (ie. potential future changes).

CallStatementResultSet.close() ensures all dynamic ResultSets for the current connection are closed and thus mimics the logic when dynamic results sets are processed in EmbedStatement (through code sharing).

Patch does close result sets in ProcedureInTrigger test that were not closed before, so I assume it will fix the bug, but I haven't be able to make the test fail without the change.

Will run tests on the patch before any commit

[Knut Anders Hatlen]

I have a machine on which I'm able to reproduce this failure when I use an insane build. I'm afraid it still fails after applying the patch.

[Daniel John Debrunner]

Thanks Knut Anders, I neglected the information you found earlier that the close of the result set did nothing. I'll submit a new patch, again I can't seem to make this test fail.

[Daniel John Debrunner]

Patch that ensures the close on the dynamic results does in fact close the language ResultSet and activation.

[Knut Anders Hatlen]

Hi Dan,

I'm not able to reproduce the failure with the v2 patch, so +1 to commit from me. I found the javadoc comment for EmbedConnectionContext.processInaccessibleDynamicResult() a bit unclear, so you may consider rewriting it. (It talks about converting a ResultSet, but I can't see that any conversion is performed. The interface's javadoc comment says "Process the resultSet as a dynamic result for closure" which sounds clearer to me.)

[Daniel John Debrunner]

Revisions 602991 (trunk) closes inaccessible dynamic result sets from a CALL statement, which was causing the procedureInTriggerTest to fail.

Thanks to Knut Anders for confirming the test no longer failed with the patch.

[Knut Anders Hatlen]

Thanks for fixing this, Dan.

By the way, I noticed this incomplete comment in EmbedStatement:

+ * <P>
+ * if b

Perhaps you could elaborate a bit more on what happens if b... ;)

[Ole Solberg]

We still see this on 10.3: http://dbtg.thresher.com/derby/test/stats.html .
Could the 602991(trunk) fix be merged to 10.3?

[Dag H. Wanvik]

I attach a a repro (Main.java) that shows changed behavior after svn 602991
(the patch committed for this issue). It seems a regression:

An explicit commit inside a stored procedure makes a dynamic result sets passed out unavailable, even if the commit is executed *prior* to the result set; as in the repro.
Is this the expected behavior or not? Reopening.

[Daniel John Debrunner]

issue shown by Main.java attachment is a new issue, since it does not involve triggers in any way. Clearer to have a separate issue.

[Daniel John Debrunner]

To record back-port to 10.3 branch

[Daniel John Debrunner]

Revision: 611274 merges trunk revisions 602991 and 603030 to the 10.3 branch.

[Henri van de Scheur]

Not seen anymore after week 2 when this was fixed