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_OK was set to 318901 for 4066 rows. Later it is updated to a different value. But derby seems to remember 318901 aswell 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 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. ------------------------------------------------------------------
