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. > > > > > > ------------------------------------------------------------------ > > > >
