Intermittent failures when executing UPDATE trigger statements

• Key: DERBY-388
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: A B
• Reporter: A B
• Component/s: SQL
• Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0
• Fix Version/s: 10.0.2.2, 10.1.2.1, 10.2.1.6

Description

If I define an UPDATE trigger and then I repeatedly perform a table update that fires the trigger over many rows, I occasionally see intermittent failures of two kinds: 1) a null pointer exception, and/or 2) a syntax error.

The NPE stack trace is this:

java.lang.NullPointerException
at org.apache.derby.impl.sql.GenericPreparedStatement.makeValid(GenericPreparedStatement.java:808)
at org.apache.derby.impl.sql.GenericPreparedStatement.rePrepare(GenericPreparedStatement.java:229)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:342)
at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:166)
at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowTriggerExecutor.java:108)
at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:296)
at org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTriggers(UpdateResultSet.java:824)
at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:292)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:366)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1100)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1271)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:259)

The syntax error stack trace is this:

ERROR 42X01: Syntax error: org.apache.derby.iapi.db.Factory::getTriggerExecutionContext.
at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:311)
at org.apache.derby.impl.sql.compile.MethodCallNode.resolveMethodCall(MethodCallNode.java:666)
at org.apache.derby.impl.sql.compile.StaticMethodCallNode.bindExpression(StaticMethodCallNode.java:455)
at org.apache.derby.impl.sql.compile.NonStaticMethodCallNode.bindExpression(NonStaticMethodCallNode.java:170)
at org.apache.derby.impl.sql.compile.NonStaticMethodCallNode.bindExpression(NonStaticMethodCallNode.java:170)
at org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(JavaToSQLValueNode.java:250)
at org.apache.derby.impl.sql.compile.CastNode.bindExpression(CastNode.java:224)
at
[snip]
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:247)
at org.apache.derby.impl.sql.compile.UpdateNode.bind(UpdateNode.java:507)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:332)
at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107)
at org.apache.derby.impl.sql.GenericPreparedStatement.makeValid(GenericPreparedStatement.java:811)
at org.apache.derby.impl.sql.GenericPreparedStatement.rePrepare(GenericPreparedStatement.java:229)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:342)
at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:166)
at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowTriggerExecutor.java:108)
at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:296)
at org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTriggers(UpdateResultSet.java:824)
at org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSet.java:292)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:366)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1100)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1271)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:259)


In both cases, it looks like the error happens when Derby decides (internally) to recompile the trigger-action statement in the middle of trigger processing--depending on the state of the underlying Statement object that performs the trigger action, the result will be an NPE or a syntax error.

I'm working on writing up a simplified reproduction and will post soon...

Comments

[A B]

Attaching a sample program to reproduce the behavior described in this bug. Note that while this particular repro is contrived, this problem CAN show up in a normal application environment as a result of the fact that Derby sometimes chooses to recompile triggers automatically. If it does so while a trigger is firing, the result will be the same as what is witnessed by this program.

The "intermittent" nature of the bug as first reported was because Derby didn't always choose to recompile the trigger statements, and hence the problem didn't always occur. I'm not sure how Derby goes about deciding when to automatically recompile the trigger, but in any event, I've written the repro to be more consistent: it forces a table alteration while running, which makes it mandatory for the trigger to be recompiled, and thus the problem is more easily reproduced.

I think I have a patch for this problem, but would like to run derbyall (tonight) before posting.

[A B]

Attaching patch to fix this issue. I ran derbyall with these changes and all tests passed. If someone who's familiar with trigger execution and/or statement recompilation could look at this patch, I'd be grateful. Thanks.

[A B]

Oh, I should mention that the patch I created is against the 10.0 branch. I think we'll want it to be applied to all branches (10.0, 10.1, and trunk) but I don't know if there's a certain order that's supposed to be done. So I created the patch w.r.t the 10.0 branch and have marked the "Fixin" as 10.0.2.2". I'll work on adding the repro for this problem to one of the existing tests and will submit another patch for that later.

[Satheesh Bandaram]

I looked at this patch... While it seems to solve the problem, I wonder if the recompile of the trigger action could use prepareStorable() method, instead of the newly extended prepare() method with internalSQL flag added. I think it is desirable to not have prepare() method with internalSQL flag, if possible, since this is mostly used directly for JDBC methods. These should ideally not need internalSQL option.

[A B]

Thanks for looking at this Satheesh. Per your suggestion, I looked to see if I could use prepareStorable() instead of adding the new version of prepare(), and the answer was Yes, I could. However, after doing so, I noticed that once a recompilation was determined to be necessary, we would end up recompiling it EVERY time the trigger was fired thereafter (at least, that was the case with the repro I've attached to this issue). So I went back to square one to see how the trigger-action is compiled to begin with (upon first execution), and realized that we can and should be following that same codepath when we try to recompile the statement in mid-trigger processing.

Long story short, it turns out that this patch has become even simpler than it was before--all I had to do was replace my old changes with a single "if" statement in one class and the problem was solved. I'm attaching a new patch that does this; see the comments in the patch for an explanation of why this simple if-statement does the trick.

So thanks again for the review--it's made for a much simpler patch, and one that seems like it's generally more correct--at least to me. If anyone else out there sees a problem with my approach, please comment.

I'm attaching the new patch (based on 10.0 codeline) after running "derbylang" on Windows 2000 with Sun jdk 1.4.2 and seeing no failures. I will run the full "derbyall" suite tonight.

[A B]

In looking more closely at the latest patch I posted, I decided to make a change that, while in theory should do the same thing in a more robust manner, is nonetheless enough to warrant a new run of "derbyall" before posting. So I have removed the old patch and will post a new one tomorrow, after my derbyall suite has completed.

[A B]

Attaching a new patch (this is the 3rd version). Patch is against the 10.0 codeline and I'm hoping it can be applied to that branch (that's where my " itch" is). Ran "derbyall" on Windows 2000 with IBM jdk 1.4.2 with no new failures.

[Daniel John Debrunner]

patch v3 looks good to me.

[Satheesh Bandaram]

I ported this fix from 10.0 branch into 10.1 branch and trunk. I noticed a test case has been added for this checkin. I will follow up with Army to get a test case added to each of the branches to make sure we don't break this fix in the future.

[A B]

Attached is a patch to add a test case for this issue to the lang/triggerGeneral.sql test. If this is deemed acceptable, we should probably
port this test back to the 10.1 branch, as well.

[Satheesh Bandaram]

Submitted this change to trunk. Will merge into 10.1 after the release is done.

Sending java\testing\org\apache\derbyTesting\functionTests\master\triggerGeneral.out
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\triggerGeneral.sql
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\userDefMethods.java
Transmitting file data ...
Committed revision 226896.

[Satheesh Bandaram]

Merged this test case addition into 10.1 release.

Sending java\testing\org\apache\derbyTesting\functionTests\master\triggerGeneral.out
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\triggerGeneral.sql
Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\userDefMethods.java
Transmitting file data ...
Committed revision 227116.

[A B]

Fix for this issue was checked into the 10.0 (svn 201659), 10.1 (svn 219113) and trunk (svn 219115) branches. A test case was also added and committed to 10.1 (svn 227116) and the trunk (svn 226896) branch. Thus, I'm resolving and closing this issue.

[Deepa Remesh]

reopening to fix fix version

Assigning to SQL component.