[DERBY-6876] Can't create triggers on a table - error 42X94

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Environment: Linux x86_64, Java 1.8.0_74
• Bug behavior facts: Data corruption
• Assignee: Unassigned
• Reporter: Aleksei Kovura
• Created: 14/Mar/16 08:18
• Updated: 12/Nov/16 10:06
• Resolved: 12/Nov/16 10:06

Description

I previously shared this on dev mailing list - http://thread.gmane.org/gmane.comp.apache.db.derby.devel/115427.

I'm reworking triggers in my database and getting this message while trying to create one:
------------
Error: StoredPreparedStatement '19ba803c-014e-b216-6d98-00000650b418' does not exist.
SQLState: 42X94
ErrorCode: 30000
------------
This database is a couple years old, started out as 10.10.1.1, was upgraded to stable releases as they appeared (10.11.1.1 -> 10.12.1.1). Some DDL was changed in between upgrades, unfortunately I can't track which ones and when.
SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS() doesn't help.
I tried my best to create a replicable test case through creating database on earlier versions, upgrading and playing with DDL - no such luck, so I'm attaching compressed database (with data deleted and tables compressed). Bug replication procedure is as follows:

1) Unpack attached file (it has a "trigger_bug" root directory);
2) Boot the db_trigger database in embedded mode, include "trigger_bug" directory in the classpath - there is one Java class that is referenced in Stored Procedure;
3) Try to run this SQL:
CREATE TRIGGER APP."test"
AFTER UPDATE OF description ON APP."ACTIONS"
REFERENCING NEW ROW AS updated_row
FOR EACH ROW
UPDATE APP."ACTIONS" SET description = 'testing'
WHERE id=updated_row.id
4) Get a 42X94 error.

I'm hoping someone with enough knowledge can poke around in system tables and figure out what's going on.

Issue Links

• is related to: DERBY-6882 Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT