db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aleksei Kovura (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6876) Can't create triggers on a table - error 42X94
Date Tue, 29 Mar 2016 17:13:25 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15216352#comment-15216352
] 

Aleksei Kovura commented on DERBY-6876:
---------------------------------------

Is there a way to retain values of generated column somehow when restoring data (with foreignViews
or otherwise)?

Using foreignViews turned out to be trickier than I expected due to the fact that tables have
auto-generated IDs and refer to each other by them. So importing data will result in changed
IDs for many objects (there are counter gaps in "corrupted" database due to deletes), which
will wreak havoc in relationships between tables; it's a lot of work to sort out all that
data...

If you gentlemen had any bright ideas on repairing this DB, that would be most welcome :)

> Can't create triggers on a table - error 42X94
> ----------------------------------------------
>
>                 Key: DERBY-6876
>                 URL: https://issues.apache.org/jira/browse/DERBY-6876
>             Project: Derby
>          Issue Type: Bug
>         Environment: Linux x86_64, Java 1.8.0_74
>            Reporter: Aleksei Kovura
>         Attachments: trigger_bug.zip
>
>
> 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.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message