True. Glad you found the precise cause, though! Index corruptions are extremely rare, and very hard to fix unless we can isolate and reproduce them.
Once in a while they occur because there was a recovery problem; for example the disk was full and the server crashed and recovery did not work properly. Although it's been a long time since I can remember one of those. bryan On Thu, Oct 24, 2019 at 1:43 AM Geraldine McCormack <geral...@ie.ibm.com> wrote: > > 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 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. > > > ------------------------------------------------------------------ > > > > > > 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? > > > > http://db.apache.org/derby/docs/10.15/devguide/cdevconcepts15366.html#cdevconcepts15366 > > > > 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. > > > > > > > > > ------------------------------------------------------------------ > > > > > > > > > >
