forcing a duplicate value on a unique index ✓ Answered: green (solid confidence) — Art Kagel's diagnosis (SERIAL wraparound plus an HDR failover losing the sequence position, compounded by likely index corruption) is directly confirmed by the original poster, who agrees the duplicate was due to index corruption and adds detail on how the primary/secondary swap occurred.

Advisory only.

Hello, It happened again few days ago: - All indexes & constraint were enabled. - An unload of log_evenements table was performed in 'single user' mode - The file was loaded on another server in a 'plain' table (no indexes at all). - After the load was finished, I tried to recreate the index & the primary key BUT I found a duplicate value: le_id le_date 6410 2011-01-26 04:06:20 6410 2011-01-25 17:04:21 [the unload was started after '2011-01-26 04:10:00'] The full story is: - a HDR environment, primary node being a Sun cluster, secondary a stand alone machine [IDS 11.50FC2 on Solaris 10] - on the primary, the 'le_id' reached the maximum value and was cycled to 1 at '2011-01-25 16:12:24' (so the first '6401' was inserted on primary) - at about '2011-01-26 02:34:28' the system was swapped to secondary; at that moment, current value of le_id was 188572 - after the swap, the current value of the serial data type was lost on secondary and le_id was revert to probably 1 => a lot of 'duplicate value' errors start being raised - finding 'small' gaps within the le_id, 103 new records were 'sneaked' in log_evenements; the duplicate '6401' was the 102-th record inserted [gaps occurred on primary due to rollback - 28 seconds after the '6401' insertion, a new valid value was inserted. - few minutes after this the system was 'unplugged', the table was unloaded and then truncated [after this, the activity was restored in good conditions] <table defs> create table log_evenements (le_id SERIAL not null, le_date DATETIME YEAR TO SECOND not null, ...) fragment by round robin in datadbs, datadbs1, datadbs2, datadbs3, datadbs4, datadbs5, datadbs6, datadbs7 extent size 200000next size 300000 lock mode row; create unique index le_id_ix on log_evenements (le_id asc) using btree fragment by expression (mod(le_id , 8 ) = 0 ) in indexdbs , (mod(le_id , 8 ) = 1 ) in indexdbs1 , (mod(le_id , 8 ) = 2 ) in indexdbs2 , (mod(le_id , 8 ) = 3 ) in indexdbs3 , (mod(le_id , 8 ) = 4 ) in indexdbs4 , (mod(le_id , 8 ) = 5 ) in indexdbs5 , (mod(le_id , 8 ) = 6 ) in indexdbs6 , remainder in indexdbs7; alter table log_evenements add constraint primary key (le_id) constraintpk_log_evenements; </table defs>

OK, three separate problems: 1. SERIAL column values wrapping causing grief. 2. Duplicate SERIAL column value permitted into the table. 3. New primary does not maintain the next serial value that the original primary was using. I'm assuming that the new primary had been an HDR or RSS secondary prior to switching roles with the primary. Note that inserts, deletes, and updates applied on a secondary are actually applied by primary and replicated to the secondary so it would have been the old secondary/new primary that got confused about the current serial value. This one is probably a bug, report it - though now that you have wrapped the value around, IBM may have difficulty duplicating the problem. The solution to the first one, since you are running 11.50, is to ALTER the le_id column from SERIAL to BIGSERIAL so it will not wrap any longer (BIGSERIAL holds 64bit integer so 2^63-1 is the maximum value). The second one may have been caused by a corrupted index, but it's probably too late to determine that now that the table has been truncated. Still, it would not hurt to run "oncheck -cDI" on the table to check out the possibility that the index is still corrupted. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 2, 2011 at 6:29 AM, EMANUEL IONESCU <elionescu@yahoo.com> wrote: > Hello, > > It happened again few days ago: > - All indexes & constraint were enabled. > - An unload of log_evenements table was performed in 'single user' mode > - The file was loaded on another server in a 'plain' table (no indexes at > all). > - After the load was finished, I tried to recreate the index & the primary > key > BUT I found a duplicate value: > > le_id le_date > 6410 2011-01-26 04:06:20 > 6410 2011-01-25 17:04:21 > > [the unload was started after '2011-01-26 04:10:00'] > > The full story is: > - a HDR environment, primary node being a Sun cluster, secondary a stand > alone > machine [IDS 11.50FC2 on Solaris 10] > - on the primary, the 'le_id' reached the maximum value and was cycled to 1 > at > '2011-01-25 16:12:24' (so the first '6401' was inserted on primary) > - at about '2011-01-26 02:34:28' the system was swapped to secondary; at > that > moment, current value of le_id was 188572 > - after the swap, the current value of the serial data type was lost on > secondary and le_id was revert to probably 1 => a lot of 'duplicate value' > errors start being raised > - finding 'small' gaps within the le_id, 103 new records were 'sneaked' in > log_evenements; the duplicate '6401' was the 102-th record inserted > [gaps occurred on primary due to rollback > - 28 seconds after the '6401' insertion, a new valid value was inserted. > - few minutes after this the system was 'unplugged', the table was unloaded > and then truncated [after this, the activity was restored in good > conditions] > > <table defs> > create table log_evenements (le_id SERIAL not null, le_date DATETIME YEAR> TO > SECOND not null, ...) > fragment by round robin in datadbs, datadbs1, datadbs2, datadbs3, datadbs4, > datadbs5, datadbs6, datadbs7 > extent size 200000 > next size 300000 > lock mode row; > > create unique index le_id_ix on log_evenements (le_id asc) using btree > fragment by expression> > (mod(le_id , 8 ) = 0 ) in indexdbs , > > (mod(le_id , 8 ) = 1 ) in indexdbs1 , > > (mod(le_id , 8 ) = 2 ) in indexdbs2 , > > (mod(le_id , 8 ) = 3 ) in indexdbs3 , > > (mod(le_id , 8 ) = 4 ) in indexdbs4 , > > (mod(le_id , 8 ) = 5 ) in indexdbs5 , > > (mod(le_id , 8 ) = 6 ) in indexdbs6 , > > remainder in indexdbs7; > > alter table log_evenements add constraint primary key (le_id) constraint> pk_log_evenements; > </table defs> > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517503b08f95cfd049b4e8dcb

Hello Art, Thank you for your answer! My main concern was about the duplicate value that was inserted on the secondary machine - but I believe this has to be credited to an index corruption. The scenario I posted was a little incomplete: the swap from primary to secondary was done by 'breaking' the replication - the 'primary' was put to 'standard' before switching the (other application) services to 'secondary'. Based on a previous experience, I can say that in a replication scenario (HDR), also sequences are not replicated in 'real time'. Regards, E.Ionescu