Tim Dudgeon <[email protected]> writes: > On 02/09/2014 13:37, Knut Anders Hatlen wrote: >> Tim Dudgeon <[email protected]> writes: >> >>> No joy. Seems like nothing extra is being logged. >> >> From the stack trace, cleanupOnError() seems to be called from this >> catch block in GenericPreparedStatement.executeStatement(): >> >> } catch (StandardException se) { >> /* Cann't handle recompiling SPS action recompile here */ >> if >> (!se.getMessageId().equals(SQLState.LANG_STATEMENT_NEEDS_RECOMPILE) >> || spsAction) >> throw se; >> statementContext.cleanupOnError(se); >> continue recompileOutOfDatePlan; >> >> } >> >> It looks like the INSERT statement got a recompilation request. That's >> not really an error, but an exception is raised to signal the condition >> internally. The statement is supposed to be recompiled and retried >> automatically after cleaning up the execution state. Unfortunately, the >> trigger execution context doesn't seem to be prepared for this scenario, >> and it fails with a NullPointerException in cleanup(). >> >> I've managed to reproduce the NPE in my environment, and I've filed >> https://issues.apache.org/jira/browse/DERBY-6724 to track the issue. >> >> Thanks, >> >> > > Interestingly I'm seeing a similar (but not identical) error with > another trigger. This time the stack trace is this: > > Tue Sep 02 13:39:09 BST 2014 Thread[SQLExecution,1,system] (XID = > 62693), (SESSIONID = 1), (DATABASE = > C:/Users/timbo/Documents/IJCProjects/mini-regs/Vanilla > Oracle/.config/derby-minireg-01-sep/db), (DRDAID = null), Failed > Statement is: UPDATE samples SET sample_code = 'S123456' WHERE > sample_id = CAST > (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getNewRow().getObject(1) > AS INTEGER) > java.lang.NullPointerException > at > org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getTriggerActionString(Unknown > Source) > at > org.apache.derby.iapi.sql.dictionary.TriggerDescriptor.getActionSPS(Unknown > Source) > at > org.apache.derby.impl.sql.execute.GenericTriggerExecutor.getAction(Unknown > Source) > at > org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown > Source) > at > org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown > Source) > at > org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTriggers(Unknown > Source) > at org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown > Source) > at > org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown > Source) > at > org.apache.derby.impl.sql.GenericPreparedStatement.executeSubStatement(Unknown > Source) > at > org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown > Source) > at > org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown > Source) > at > org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown > Source) > at > org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown > Source) > at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown > Source) > at > org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown > Source) > at > org.apache.derby.impl.sql.GenericPreparedStatement.execute(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) > > > The trigger definition is this: > > CREATE TRIGGER samples_code_trg > AFTER INSERT ON samples > REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL > UPDATE samples SET sample_code = 'S123456' > WHERE samples.sample_id = newrow.sample_id;
Is there also a trigger that is "AFTER UPDATE ON samples"? From the stack trace, it looks like the error happens when an INSERT statement triggers an UPDATE that again causes some other AFTER trigger to fire. > Unfortunately I'm unable to turn this into a simple reproducible test case. Is it reproducible in your environment? If so, there might be more clues (at least line numbers in the stack trace) if you run with debug-enabled jar files. They're available from the download page, look for the downloads named *-lib-debug.zip or *-lib-debug.tar.gz. It might be worth a bug report even if you don't have a simple reproducible test case, so that we have a place to collect information about the bug. > This is with Derby 10.10.1.1 Some trigger bugs were found and fixed in 10.11 during the work on DERBY-534 (WHEN clause in triggers). Most of them were backported and included in the 10.10.2.0 release. I don't recall any bugs with the stack trace you're seeing, though. > On a related front, is it possible to do this with BEFORE INSERT > trigger (the actual value for sample_code needs to come from a > sequence - the value 'S123456' is just there to simplify things)? I don't think so. Derby doesn't allow UPDATE statements in BEFORE triggers. -- Knut Anders
