db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bruehlicke <bruehli...@gmail.com>
Subject Re: Moving from 10.5.3.0 -> 10.12.1.1 SQLNonTransientConnectionException but 10.5.3.0 ->10.7.1.1 is working perfectly exactly same code, 10.5.3.0 -> 10.8.1.2 fails similar
Date Fri, 30 Sep 2016 18:47:16 GMT
I found the problem and suggested a fix to the code. See comments in
https://issues.apache.org/jira/browse/DERBY-6726

On Wed, Sep 28, 2016 at 2:03 PM, bruehlicke <bruehlicke@gmail.com> wrote:

> I have managed to crystallize out the error into a standalone JUnit 4 test
> and uploaded a NetBeans project with this to JIRA under DERBY-6726. Hope
> this will allow us to hunt down the issue which really was introduced with
> 10.8.
>
> On Tue, Sep 27, 2016 at 9:18 PM, bruehlicke <bruehlicke@gmail.com> wrote:
>
>> Thanx I will see if I can narrow it down.  I do rebuild the entire sets
>> of Tables and triggers and hence I would be able to make this reproducable.
>> I will work on making a small test app and see if I can make it fail. The
>> interesting bit is that the UPDATE works if I paste the SQL statement
>> directly into the SQL editor in NetBeans - even with the trigger (!) using
>> the jars after 10.8. Mysterious.
>>
>>
>> The table involved CURVE_NUMBER and the TRIGGER on those are created as
>> following when the project starts. (see below).
>>
>> Just tested:  If I do NOT create the Trigger (shown below) on the Table -
>> all works fine in 10.12.1.1. So it is for sure related to the Update
>> Trigger.
>>
>>
>>
>>             s.execute("CREATE TABLE CURVE_DATA_NUMBER (" +
>>                     "CURVE_ID BIGINT NOT NULL,"
>> +
>>                     "SEQ_NUM BIGINT NOT NULL," +
>>                     "MDEPTH DOUBLE," +
>>
>>                     "CVALUE DOUBLE DEFAULT -999.25"
>> +
>>                     ")");
>>
>>             s.execute("ALTER TABLE CURVE_DATA_NUMBER ADD CONSTRAINT
>> CURVE_DATA_NUMBER_CURVE_ID_FK FOREIGN KEY (CURVE_ID) REFERENCES CURVE (ID)
>> ON DELETE CASCADE");
>>             s.execute("ALTER TABLE CURVE_DATA_NUMBER ADD CONSTRAINT
>> CURVE_DATA_NUMBER_UN UNIQUE (CURVE_ID, SEQ_NUM)");
>>             // Create Database Index to speed up - we do NOT need an
>> Index on the CURVE_ID since it already gets one being a Foreign Key
>>             s.execute("CREATE INDEX CURVE_DATA_NUMBER_SEQ_NUM_INDEX on
>> CURVE_DATA_NUMBER (SEQ_NUM)");
>>             // Add trigger to update CURVE in case curve data is modyfied
>> which may impact top/bottom attributes
>>             s.execute("CREATE TRIGGER CURVE_DATA_NUMBER_TRIG_UP AFTER
>> UPDATE OF CURVE_ID, SEQ_NUM, MDEPTH, CVALUE " +
>>                     "ON CURVE_DATA_NUMBER REFERENCING OLD AS UPDATEDROW
>> FOR EACH ROW UPDATE CURVE SET " +
>>                     "TOP_DEPTH=(SELECT MIN(MDEPTH) FROM CURVE_DATA_NUMBER
>> WHERE CURVE_ID=UPDATEDROW.CURVE_ID AND MDEPTH<>-999.25)," +
>>                     "BOTTOM_DEPTH=(SELECT MAX(MDEPTH) FROM
>> CURVE_DATA_NUMBER WHERE CURVE_ID=UPDATEDROW.CURVE_ID AND MDEPTH<>-999.25),"
>> +
>>                     "MINCVAL=(SELECT MIN(CVALUE) FROM CURVE_DATA_NUMBER
>> WHERE CURVE_ID=UPDATEDROW.CURVE_ID AND CVALUE<>-999.25)," +
>>                     "MAXCVAL=(SELECT MAX(CVALUE) FROM CURVE_DATA_NUMBER
>> WHERE CURVE_ID=UPDATEDROW.CURVE_ID AND CVALUE<>-999.25) " +
>>                     "WHERE ID=UPDATEDROW.CURVE_ID");
>>
>>
>>
>>
>>
>>
>> On Tue, Sep 27, 2016 at 8:47 PM, Bryan Pendleton <
>> bpendleton.derby@gmail.com> wrote:
>>
>>> 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)
>>>>
>>>
>>> This definitely looks like it could be DERBY-6726
>>>
>>>         https://issues.apache.org/jira/browse/DERBY-6726
>>>
>>> From the notes in that bug report we were unable to reproduce
>>> the problem, and the original reporter noted that
>>>
>>>          I rebuilt all the tables and triggers and now the problem
>>> doesn't happen
>>>
>>> It would sure be nice to fix this problem, but if we can't reproduce
>>> it we may need a lot of help from you to try to fix it.
>>>
>>> Alternatively, if you have a way to backup your tables, and then drop
>>> and re-load your data, it seems like the problem may go away?
>>>
>>> The fact that you seemed to be able to narrow down the introduction
>>> of this problem to Derby 10.8 makes me wonder if DERBY-5121 is related?
>>>
>>> At the least, it would be nice if you could put some comments into
>>> DERBY-6726
>>> and include as much information as you can about the particulars of your
>>> situation.
>>>
>>> thanks,
>>>
>>> bryan
>>>
>>>
>>
>

Mime
View raw message