Hi Brian
Thanks for the advice. No triggers, no constraints, and Isolation level
makes no difference.
But your suggestion on the index was a good one!
An index is scan is used
Index Scan ResultSet for RE_GROUPS using
index RE_GROUPS_POKIDX at read committed isolation level using exclusive
row locking chosen by the optimizer
Number of opens = 1
Rows seen = 4066
And when I drop the index the issue resolves!
ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK =
7777 WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 318901 ;
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result
of a query is an empty table.
I know this still means there is an issue in derby.
Kind Regards,
Geraldine.
-----------------------------------------------------------------------------------------------------------------------
Geraldine McCormack
L3 Team Lead - TBSM & Netcool/Impact
IBM Ireland
Visit the IBM Support Portal to open a case, download fixes, access
product documentation, education and training materials.
Visit our devcenters (applications and operations) for blogs, docs and
resources brought to you by the technical people who create our offerings.
------------------------------------------------------------------
From: Bryan Pendleton <bpendleton.de...@gmail.com>
To: derby-dev@db.apache.org
Date: 24/10/2019 02:03
Subject: [EXTERNAL] Re: Derby remembers old value of a column ?
Some other thoughts:
1) When the problem strikes, does it ever "go away"? That is, do
things subsequently revert to giving the correct results, and if they
do, what seems to be the trigger for that return-to-normal?
2) Try looking at the query plan for your sql, is it a question of
whether an index is or isn't involved?
3) Are there constraints involved? If so, are they deferrable? Maybe
you're seeing a deferred constraint somehow?
4) Are there triggers on the table?
Sorry I'm just sort of speculating, not really sure I have any good
theories.
On Wed, Oct 23, 2019 at 5:33 PM Bryan Pendleton
<bpendleton.de...@gmail.com> wrote:
>
> Does the behavior change if you use the SQL SET ISOLATION statement?
>
>
https://urldefense.proofpoint.com/v2/url?u=http-3A__db.apache.org_derby_docs_10.15_devguide_cdevconcepts15366.html-23cdevconcepts15366&d=DwIFaQ&c=jf_iaSHvJObTbx-siA1ZOg&r=6J-7mvgmPnzqRS1eOee5rO5TAG8hsUfFeSGjSJ7lrDk&m=YH-y4JYdz0VjNfPJNQ7bRXD3YaTcQ1KxAiLMHk3TWHg&s=zOcFyD9pUgptDAeNyXqZzTwgFEJdcFwuAno4Yz9JiBM&e=
>
> Also, have you tried your reproduction with different versions of Derby?
>
> thanks,
>
> bryan
>
> On Wed, Oct 23, 2019 at 2:07 PM Geraldine McCormack
<geral...@ie.ibm.com> wrote:
> >
> > Hi derby group,
> >
> > I am hitting a very strange issue on derby 10.14 where an update
statement seems to be "remembering" an old column value. I cannot recreate
this on all systems but the SQL below shows the issue.
> >
> > At one point in the past, the integer column PATTERN_IS_OKwas set to
318901for 4066 rows. Later it is updated to a different value. But derby
seems to remember 318901aswell as knowing about the new value.
> >
> > See the pieces in red below where 4066 rows are updated even though
the PATTERN_IS_OK column has a different value.
> >
> > ij> select count(*),PATTERN_IS_OK,CONFIGNAME from
RELATEDEVENTS.RE_GROUPS WHERE CONFIGNAME = 'ACCESS_CEMS' group by
CONFIGNAME ,PATTERN_IS_OK;
> > 1 |PATTERN_IS&|CONFIGNAME
> >
--------------------------------------------------------------------------------------------------------------------------------------------------------
> > 208 |3 |ACCESS_CEMS
> > 4066 |5555 |ACCESS_CEMS
> >
> > 2 rows selected
> > ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK
= 4444 WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 318901 ;
> > 4066 rows inserted/updated/deleted
> >
> > ij> select count(*),PATTERN_IS_OK,CONFIGNAME from
RELATEDEVENTS.RE_GROUPS WHERE CONFIGNAME = 'ACCESS_CEMS' group by
CONFIGNAME ,PATTERN_IS_OK;
> > 1 |PATTERN_IS&|CONFIGNAME
> >
--------------------------------------------------------------------------------------------------------------------------------------------------------
> > 208 |3 |ACCESS_CEMS
> > 4066 |4444 |ACCESS_CEMS
> >
> > 2 rows selected
> > ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK
= 8888 WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 318901 ;
> > 4066 rows inserted/updated/deleted
> >
> > ij> select count(*),PATTERN_IS_OK,CONFIGNAME from
RELATEDEVENTS.RE_GROUPS WHERE CONFIGNAME = 'ACCESS_CEMS' group by
CONFIGNAME ,PATTERN_IS_OK;
> > 1 |PATTERN_IS&|CONFIGNAME
> >
--------------------------------------------------------------------------------------------------------------------------------------------------------
> > 208 |3 |ACCESS_CEMS
> > 4066 |8888 |ACCESS_CEMS
> >
> > ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK
= 2222 WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 8888 ;
> > 4066 rows inserted/updated/deleted
> >
> > ij> select count(*),PATTERN_IS_OK,CONFIGNAME from
RELATEDEVENTS.RE_GROUPS WHERE CONFIGNAME = 'ACCESS_CEMS' group by
CONFIGNAME ,PATTERN_IS_OK;
> > 1 |PATTERN_IS&|CONFIGNAME
> >
--------------------------------------------------------------------------------------------------------------------------------------------------------
> > 208 |3 |ACCESS_CEMS
> > 4066 |2222 |ACCESS_CEMS
> >
> > 2 rows selected
> > ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK
= 2222 WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 1111;
> > 0 rows inserted/updated/deleted
> > WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the
result of a query is an empty table.
> >
> > ij>
> > ij> update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK
= 8888 WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 318901 ;
> > 4066 rows inserted/updated/deleted
> >
> > ij>
> > update RELATEDEVENTS.RE_GROUPS SET PATTERN = null , PATTERN_IS_OK =
8888 WHERE CONFIGNAME = 'ACCESS_CEMS' AND PATTERN_IS_OK = 8888;
> > 4066 rows inserted/updated/deleted
> >
> >
> > The table was an existing table for which the column PATTERN_IS_OK
column was added with default value of -2. This was updated to 318901 and
ever after derby seems to remember this value. Restarting derby does not
help. The issue persists. I cannot reproduce this on all systems or even
consistently on this system - I have tried adding other columns like this
one and the issue does not happen.
> >
> >
> > describe RELATEDEVENTS.RE_GROUPS;
> > COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
> >
------------------------------------------------------------------------------
> > GROUPID |BIGINT |0 |10 |19 |GENERATED&|NULL |NO
> > GROUPNAME |VARCHAR |NULL|NULL|256 |NULL |512 |NO
> > INSTANCES |BIGINT |0 |10 |19 |NULL |NULL |NO
> > CREATIONTIME |BIGINT |0 |10 |19 |NULL |NULL |NO
> > VALIDATIONTIME |TIMESTAMP|9 |10 |29 |NULL |NULL |NO
> > CONFIGNAME |VARCHAR |NULL|NULL|256 |NULL |512 |NO
> > GROUPTTL |BIGINT |0 |10 |19 |NULL |NULL |NO
> > PROFILE |VARCHAR |NULL|NULL|128 |NULL |256 |NO
> > TIMESFIRED |BIGINT |0 |10 |19 |NULL |NULL |NO
> > TIMESFIREDMONTHLY |BIGINT |0 |10 |19 |NULL |NULL |NO
> > TIMESFIREDRENEW |BIGINT |0 |10 |19 |NULL |NULL |NO
> > LASTEVENTOCCURRENCE&|DOUBLE |NULL|2 |52 |NULL |NULL |NO
> > LASTEVENTOCCURRENCE&|DOUBLE |NULL|2 |52 |NULL |NULL |NO
> > LASTEVENTOCCURRENCE&|DOUBLE |NULL|2 |52 |NULL |NULL |NO
> > LASTFIRED |TIMESTAMP|9 |10 |29 |NULL |NULL |YES
> > UNIQUEEVENTS |BIGINT |0 |10 |19 |NULL |NULL |NO
> > TOTALEVENTS |BIGINT |0 |10 |19 |NULL |NULL |NO
> > REVIEWED |BOOLEAN |NULL|NULL|1 |NULL |NULL |NO
> > TYPE |VARCHAR |NULL|NULL|10 |NULL |20 |NO
> > EVENTIDENTITIES |VARCHAR |NULL|NULL|1024 |NULL |2048 |NO
> > ALGVERSION |INTEGER |0 |10 |10 |NULL |NULL |YES
> > PATTERNACTION |VARCHAR |NULL|NULL|500 |NULL |1000 |YES
> > PATTERN |VARCHAR |NULL|NULL|4028 |NULL |8056 |YES
> > IS_CONFLICT |BOOLEAN |NULL|NULL|1 |FALSE |NULL |YES
> > CONFLICT_GROUP |VARCHAR |NULL|NULL|1024 |'' |2048 |YES
> > EVENTSCOUNT |INTEGER |0 |10 |10 |0 |NULL |YES
> > ACKNOWLEDGED_NON_ZE&|DECIMAL |0 |10 |31 |NULL |NULL |YES
> > NODE_EXAMPLE |VARCHAR |NULL|NULL|4112 |NULL |8224 |YES
> > SUMMARY_EXAMPLE |VARCHAR |NULL|NULL|2048 |NULL |4096 |YES
> > ORIGINALSEVERITY_MAX|DECIMAL |0 |10 |31 |NULL |NULL |YES
> > ALERTGROUP_EXAMPLE |VARCHAR |NULL|NULL|4112 |NULL |8224 |YES
> > ALARMSOURCE_EXAMPLE |VARCHAR |NULL|NULL|4112 |NULL |8224 |YES
> > ALERTKEY_EXAMPLE |VARCHAR |NULL|NULL|4112 |NULL |8224 |YES
> > PATTERN_IS_OK |INTEGER |0 |10 |10 |-2 |NULL |YES
> > GROUP_IS_OK |INTEGER |0 |10 |10 |-2 |NULL |YES
> >
> >
> > Has anyone come across something like this before ? There are no
errors in the derby log. Is there any logging I can turn on which would
shed some light on what is happening?
> >
> > Kind Regards,
> > Geraldine
> >
> >
-----------------------------------------------------------------------------------------------------------------------
> >
> > Geraldine McCormack
> > L3 Team Lead - TBSM & Netcool/Impact
> > IBM Ireland
> >
> > Visit the IBM Support Portalto open a case, download fixes, access
product documentation, education and training materials.
> >
> > Visit our devcenters (applications and operations) for blogs, docs and
resources brought to you by the technical people who create our offerings.
> >
> >
> > ------------------------------------------------------------------
> >
> >
