Re: Problem with triggers on 10.12

[Alex <al...@zoho.com>]

Hi Rick,
Just tried SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS() - doesn't 
help. I'm dropping the trigger, then calling the procedure, then trying 
to create the same trigger - same error message. Same result when trying 
to create another test trigger on that table (trigger with that name 
never existed in DB).

The database is a couple years old and started out as 10.10.1.1. I 
created a test DB with 10.10.1.1, ran all the DDL on it, upgraded to 
10.11.1.1, then to 10.12.1.1 - trigger drop/creation works just fine. 
This is not an exact replication of how DB evolved - some DDL was added 
while on 10.11.1.1.
Any ideas so far?
Exact replication of upgrade steps would take much more time. I should 
have made more descriptive commit messages in VCS...

/--Regards, Alex/

------------------------------------------------------------------------

*From:* Rick Hillegas
*Sent:* Monday, November 30, 2015 8:59PM
*To:* Derby-dev
*Cc:* Alex
*Subject:* Re: Problem with triggers on 10.12
> On 11/30/15 9:27 AM, Alex wrote:
>> Hello developers,
>> I have a database that was recently upgraded from 10.11 to 10.12 via 
>> "upgrade=true" connection attribute.
>> After dropping a trigger on a table I can't create any new triggers 
>> on that table, getting this error:
>>
>> Error: StoredPreparedStatement '19ba803c-014e-b216-6d98-00000650b418' 
>> does not exist.
>> SQLState:  42X94
>> ErrorCode: 30000
>>
>> It was working when DB was on 10.11 version. I found this ID in 2 
>> system tables; attaching their exports. They were made before 
>> dropping the trigger - the marked lines are not there anymore after 
>> the drop. I never manipulated system tables or database files of course.
>>
>> What I need help with is how to boil it down to a test case to create 
>> a JIRA issue. I tried setting up 10.11 database with the exact same 
>> DDL, upgrading it to 10.12 and then dropping/creating the trigger - 
>> it works fine. A database backup and full DDL history are available.
>> Any hints?
>> -- 
>> /--Regards, Alex/
> Hi Alex,
>
> Does it help if you run 
> SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS() (see 
> http://db.apache.org/derby/docs/10.12/ref/rrefsyscsinvalidatestoredstmts.html)?
>
> There were some trigger (re)compilation problems in older releases. In 
> scripting the problem, you might try creating the database with an old 
> Derby version like 10.8.3.0.  Do you know how old the original 
> database is and what version of Derby was used to create it initially?
>
> Thanks,
> -Rick