ERROR 54038: "Maximum depth of nested triggers was exceeded" occurs when trigger fires after upating 10.1.2.5 jars to 10.1.3.1

• Key: DERBY-1603
• Type: Bug
• Status: Closed
• Resolution: Invalid
• Priority: Critical
• Assignee: Yip Ng
• Reporter: Kathey Marsden
• Component/s: SQL
• Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1
• Fix Version/s: 10.2.1.6

Description

trigger fails after upgrading Cloudscape version 10.1.3.1
10.1 repro with the embedded driver.

Execute the following in ij with version 10.1.2.5 - (409283)
CREATE TABLE "TEST" (
     
"TESTID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START
WITH 1,
INCREMENT BY 1),
     
"INFO" INTEGER NOT NULL,
     
"TIMESTAMP" TIMESTAMP NOT NULL DEFAULT
'1980-01-01-00.00.00.000000'
);
     

CREATE TRIGGER UPDATE_TEST
 AFTER UPDATE ON TEST
 REFERENCING OLD AS OLD
 FOR EACH ROW MODE DB2SQL
 UPDATE TEST SET TIMESTAMP = CURRENT_TIMESTAMP WHERE
 TESTID = OLD.TESTID;

INSERT INTO TEST (INFO) VALUES
(1),
(2),
(3);

Exit ij

Replace derby jars with version 10.1.3.2.424154

Execute the following in ij:

UPDATE TEST SET INFO = 1 WHERE TESTID = 2;
ERROR 54038: Maximum depth of nested triggers was exceeded.


Rajesh confirmed.
I tried different combinations and found that the issue exists between versions and is present in older versions dating back to 10.0.
10.0.2.2 - (349072) database -> booted in 10.1.1.0 - (208786) jars
10.1.1.0 - (208786) database -> booted in 10.1.2.4 - (394241) jars
10.1.2.4 - (394241) database -> booted in 10.1.2.5 - (413784) jars
10.1.2.5 - (413784) database -> booted in 10.1.3.1 - (417277) jars (Attached is a derby.log that shows the stack trace using sane jars.)

This (self) trigger works fine if it was created and used in the same version of jars.

Below is the trace:
        at
org.apache.derby.iapi.error.StandardException.newException(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.
pushT
riggerExecutionContext(Unknown Source)
        at
org.apache.derby.impl.sql.execute.InternalTriggerExecutionContex
t.<in
it>(Unknown Source)
        at
org.apache.derby.impl.sql.execute.GenericExecutionFactory.getTri
ggerE
xecutionContext(Unknown Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.<init
>(Unknow
n Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireBeforeTrig
gers(
Unknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.sql.execute.GenericTriggerExecutor.execute
SPS(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger
(Unkn
own Source)
        at
org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEv
ent(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTrigg
ers(U
nknown Source)
        at
org.apache.derby.impl.sql.execute.UpdateResultSet.open(Unknown
Source
)
        at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unkno
wn So
urce)
        at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unkno
wn So
urce)
        at
org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown
Source)
        at
org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown
Source)
        at
org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown
Source)
        at
org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown
Source)
        at org.apache.derby.impl.tools.ij.utilMain.go(Unknown
Source)
        at org.apache.derby.impl.tools.ij.Main.go(Unknown
Source)
        at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown
Source)
        at org.apache.derby.impl.tools.ij.Main14.main(Unknown
Source)
        at org.apache.derby.tools.ij.main(Unknown Source)


SYSINFO:
------------------ Java Information ------------------
Java Version: 1.4.2
Java Vendor: IBM Corporation
Java home: C:\Cloudscape_10.1\ibm-jre-n142p\jre
Java classpath:
C:\Cloudscape_10.1\lib\derby.jar;C:\Cloudscape_10.1\lib\derbytools.jar;C:\Cloudscape_10.1\lib\db2jcc_license_c.jar;
OS name: Windows 2000
OS architecture: x86
OS version: 5.0
Java user home: C:\Documents and Settings\Administrator
Java user dir: C:\Documents and Settings\Administrator
java.specification.name: Java Platform API Specification
java.specification.version: 1.4
--------- Derby Information --------
JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
[C:\Cloudscape_10.1\lib\derby.jar] 10.1.3.2 - (424154)
[C:\Cloudscape_10.1\lib\derbytools.jar] 10.1.3.2 - (424154)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------

Comments

[Kathey Marsden]

attachment with sane build trace. I have not verified on 10.2 yet between snapshots.

[Rajesh Kartha]

I tried the scenario on a 10.2.0.4 alpha - (418118) database with 10.2.0.5 alpha - (424456) jars and the issue exists.

[Rajesh Kartha]

Bumping the Priority and Urgency. This issue occurs between versions, hence can be expected when users move their 10.1 database to 10.2.

[Yip Ng]

The problem is not the upgrade right? The update statement above throws SQLSTATE 54038 as it self triggers to the max depth and thus is reporting the error correctly. The issue here is why self trigger works fine with the same version. It should have thrown an error in this case.

[Rajesh Kartha]

Yes, I agree this self trigger should have thrown the error everytime it was executed and there should not be any special cases. After the upgrade, things work as expected, hence the trigger creation seems to be issue here.

I also think the title of the issue also need to change to reflect that self trigger does not throw ERROR 54038 in some cases.

[Yip Ng]

Found the problem, will post the patch up for review after running some initial tests.

[Kathey Marsden]

It was determined that the problem is not that the exception is thrown after upgrade, but rather that it the exception is not thrown before upgrade.

The reframed issue has been filed as DERBY-1652. See that issue for more details.

[Kathey Marsden]

This issue is being closed as invalid and a new issue DERBY-1652 filed with the correct description. The actual problem is that the exception is not being thrown before upgrade.