As an update we are in the last phase of our testing and heading towards the defect against oncheck... I wil update you in a day or two...
Original Message:
Sent: Thu September 03, 2020 05:13 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
The situation of 'continuity' seems to be valid for your system only. The other day while trying few things i accidentally patched the flags in slot 4 of your partition page on your system (restored from L0) and found that i can control the inserting of duplicates by swapping them.
The reason i say this happens in your system only is that when i create similar table in fresh instance with the same flags as your's it does not allow me to insert duplicates.
For us to log in a defect we should have a reproducible test case. Problem reproduced with help of patching does not qualify as a test case. For this we need to know what actions or combination/seqence of actions (if i may say) happened on the table which resulted in the current state. We will try and see if we can reproduce the steps but in the meantime the solution seems to be to delete the duplicate records and then recreate the unique index.
Having said that, it doesn't mean we are done. We am still investigating and see why oncheck wouldn't report such things if we have duplicate records in any scenario.
My Next update on this will be next week!!!
------------------------------
Gaurav Kumar
Original Message:
Sent: Wed September 02, 2020 09:47 AM
From: TOM GIRSCH
Subject: Odd Index Issue
Gaurav:
The structure of the table itself has rarely been touched, however.
Also, do any of those APARs involve a scenario where it continues to be possible to add duplicate records despite the presence of the unique index? Because that's what we're seeing here.
It seems to me we may be dealing with a new bug here. At a minimum, oncheck should be able to detect the bad flag in the index header.
------------------------------
TOM GIRSCH
Original Message:
Sent: Wed September 02, 2020 06:50 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
Thanks for the history. Looking at the history if seems that the table is quite old and has seen many changes :-).
Having said this i looked into our bug database and found the below APAR's that existed in 11.7 and older versions of 12.10.
See the details below.
APAR-IC95692-Duplicate rows in table with unique index when rows result from insert triggers on ER-replicated tables.
Found in IDS-11.70.xC7
Fixed in IDS-11.70.xC8,IDS-12.10.xC3
APAR-IT08258-Duplicate rows can be inserted into a table with unique index defined.
found in IDS-11.70.xC8
fixed in IDS-11.70.xC9,IDS-12.10.xC6
There is one more defect for which i don't have APAR and unfortunately i can not share defect number here.
ER apply ignoring uniqueness imposed only by a unique index
Found in IDS-11.70.xC5
Fixed in IDS-11.70.xC8
I wonder if these could be the cause of the duplicates landing in your table. Over the years you upgraded the engine version but that did not affect the data inside the table. This could be a probable explanation of the table containing duplicate data.
------------------------------
Gaurav Kumar
Original Message:
Sent: Tue September 01, 2020 04:26 PM
From: TOM GIRSCH
Subject: Odd Index Issue
I'm not sure how relevant this is, but these databases all initially existed on AIX. Around 2015, we did a migration to Linux, and that was accomplished by building empty databases on the Linux side and then establishing ER (cdr) so that we could sync up all the data and run in parallel for a while. If my memory is correct, we were running 11.70 on AIX and 12.10 on Linux. I don't believe we ever ran a version older than 12.10.FC6 on our current platform. So for the tables we're working with today, that would be "ground zero."
------------------------------
TOM GIRSCH
Original Message:
Sent: Tue September 01, 2020 01:22 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
Again I am coming back to the history of that table.
1) You have told me version history from 12.10 FC12X1. So I would also be interested in knowing prior versions before XC12?
2) You also told me, table was created in Jan 2017, So what was your IDS version at that time?
3) When did you define cdr on this table? What was the IDS version at that time?
4) Can you please upload online.log from Jan 2017 onwards to see its history....
5) L0 Backup you have shared is from the Source database or target database?
------------------------------
Gaurav Kumar
Original Message:
Sent: Fri August 28, 2020 10:07 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
seems like you thought that i have concluded. But thats not the case yet!!! Or may be i can not put that in clear word. I am sorry for the confusion.
I worked on your case throughout the day and in the end i got to know it was flag which was wrong. Having said that it doesn't mean i have conclude. That's my findings from today's test and thought to tell you.
Allow me some more time. I will update you next week now.
------------------------------
Gaurav Kumar
Original Message:
Sent: Fri August 28, 2020 09:49 AM
From: TOM GIRSCH
Subject: Odd Index Issue
Gaurav:
I'd also ask that you compare notes with the support engineer (dsybes06) who's working TS004050227.
Thanks,
- TJG
------------------------------
TOM GIRSCH
Original Message:
Sent: Fri August 28, 2020 09:08 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Because it wasn't a bad index. So once informix think it was bad then oncheck should have thrown error .
And that's what i told you earlier that i can not tell you how it got changed. That's probably be a question for you to tell me what happned to your engine and this table in the past.
i am pretty sure something must have happened to this table in the past that's why unique index takes duplicate values.
Anyways now the best solution for you is to delete duplicated records and re-create this unique index.
------------------------------
Gaurav Kumar
Original Message:
Sent: Fri August 28, 2020 08:09 AM
From: TOM GIRSCH
Subject: Odd Index Issue
My next question is, why isn't oncheck finding this? There has to be a bug there somewhere.
------------------------------
TOM GIRSCH
Original Message:
Sent: Fri August 28, 2020 07:08 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
I think I have find the problem.
Seems like an issue with the flag on your index partition page of slot 4. I patched that in my system (with the value when I re-create a unique index on 6 columns after deleting duplicate values) and then it doesn't allow to insert more duplicate values.
And If I revert my patch, again I am able to insert duplicates.
Now I can not tell you how that flag got changed in your system.
So is that enough for you..or you need more details to it, knowing you have fixed the duplicates I hope by creating new table or new index?
------------------------------
Gaurav Kumar
Original Message:
Sent: Thu August 27, 2020 11:41 AM
From: TOM GIRSCH
Subject: Odd Index Issue
Advanced support has never connected to this system.
------------------------------
TOM GIRSCH
Original Message:
Sent: Thu August 27, 2020 10:30 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
just on a side note, i would like to know did anyone from IBM/HCL did tbzero on your system to bypass the recovery in the past??? Or anyother activity did by Advance Support
Regards
Gaurav
------------------------------
Gaurav Kumar
Original Message:
Sent: Thu August 27, 2020 07:32 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
Just an FYI..i am able to restore your L0 backup..
So far, I am able to insert duplicate data, but couldn't find any root cause... Partition page of that index looks good to me..
So ..still investigating.....Will update you in a day or two
------------------------------
Gaurav Kumar
Original Message:
Sent: Wed August 26, 2020 09:46 AM
From: TOM GIRSCH
Subject: Odd Index Issue
To make it easier for support to see the problem, I've restored the full instance to a test server, scaled back everything except the table in question and then done a Level-0 archive of that scaled back instance. The query I've already posted clearly shows the duplicates that shouldn't be there.
I also uploaded the onconfig and an onstat -d to the case to make restoring the instance on your test servers easier.
------------------------------
TOM GIRSCH
Original Message:
Sent: Wed August 26, 2020 04:49 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
Can you please dump the partition page of the unique index from both the tables (table having duplicate values and table with unique values)
How to find partition page of an index
oncheck pt aeglobal:g_ophours
and based on the Physical Address (first line) you can dump the partition page and share the data
for example: when you run the oncheck -pt , you will get partition number of indexes. So you need to dump that partition page
Index ix_g_ophours_16 fragment partition chaos_globidx4 in DBspace chaos_globidx4
Physical Address 4:40
using oncheck -pP 4 40
On a side note, I would like to know the history of this instance. Like previous corruption, Anytime you skipped logical recovery. And when did you upgrade to 12.10.FC14. What was your previous versions? and when did you created this table?
------------------------------
Gaurav Kumar
Original Message:
Sent: Tue August 25, 2020 08:58 AM
From: TOM GIRSCH
Subject: Odd Index Issue
I have uploaded a new onunload with the ticket; this time it's the table with the duplicates already in it. Based on your results above, I'm guessing the onload is going to fail.
------------------------------
TOM GIRSCH
Original Message:
Sent: Tue August 25, 2020 12:36 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
Thanks!!!
Now i am able to successfully load your binary dump ((1855548 rows)). But as i tried to load the dups.unl, it gives me error 239/100.
informix@COMP-1000-1:/data2/gaurav/1210FC14 $ echo "load from dups.unl insert into g_ophours"| dbaccess aeglobal
Database selected.
239: Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:ix_g_ophours_16). 100: ISAM error: duplicate value for a record with unique key.
847: Error in load file row 1.
Error in line 2
Near character position 0
Database closed.
Can you please give me another onunload on table having duplicate data? Because with the shared dump, I can not find anything.
Regards,
Gaurav
------------------------------
Gaurav Kumar
Original Message:
Sent: Mon August 24, 2020 08:37 AM
From: TOM GIRSCH
Subject: Odd Index Issue
Gaurav:
The table is in a space called chaos_globdbs1, which has 2K pages.
It has three indices in three different spaces: chaos_globidx4, chaos_globidx5 and chaos_globidx1; all of these have 16K pages.
------------------------------
TOM GIRSCH
Original Message:
Sent: Fri August 21, 2020 01:48 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
Can you help me the page size of the dbspaces in which that table and it's index is created....
------------------------------
Gaurav Kumar
Original Message:
Sent: Tue August 18, 2020 12:02 PM
From: TOM GIRSCH
Subject: Odd Index Issue
Figured it out. I had to touch the file AND provide a full path to that touched file. TAPEDEV points to a directory, not a file, and apparently onunload doesn't like that, even though ontape handles that just fine.
I'd whine that there should be STDIO and/or direct-to-file options, but since this is literally the first time I've ever had need of the utility, probably not worth the effort. ;)
------------------------------
TOM GIRSCH
Original Message:
Sent: Mon August 17, 2020 10:46 PM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
Can you please upload onunload of the table that has duplicate values to the case...
Regards,
Gaurav
------------------------------
Gaurav Kumar
Original Message:
Sent: Mon August 17, 2020 10:24 AM
From: TOM GIRSCH
Subject: Odd Index Issue
If you have TS system access, look at TS004050227 for details.
------------------------------
TOM GIRSCH
Original Message:
Sent: Mon August 17, 2020 06:27 AM
From: Gaurav Kumar
Subject: Odd Index Issue
Hi Tom,
May i know if you still have old table in place. If yes then, can you please share the dbschema -ss and some sample data which you are saying are duplicate.
------------------------------
Gaurav Kumar
Original Message:
Sent: Fri August 14, 2020 12:21 PM
From: TOM GIRSCH
Subject: Odd Index Issue
I have an existing table in 12.10.FC14, about 1.8 million records, that has three indexes on it:
- A unique index on a serial key
- A composite, six-column unique index
- A three-column composite index that allows dups
This table participates in ER in a send-only/receive-only relationship. When doing a cdr check, I noticed that the tables weren't syncing correctly.
After a bunch of investigation, I found that the main production table, on the send-only node, has duplicate records in it that violate the composite unique index. Yet an oncheck -CDI on the table shows no errors.
I was able to find, unload and delete the duplicates, but when I load them back up, they load right in without throwing a duplicate index violation error. Yes, I've triple-checked, and the index is enabled. Indeed, I need to force a sequential scan to find the dups, because if I query normally, it uses the index and finds only one.
As an extra sanity check, I grabbed the schema for the table to a file, using the -ss option, changed the table and index names in the new file and left everything else identical, built the new table, and populated it with the data from the original table, and in the new table, the unique index works exactly as expected.
An oncheck -cc against the database also returns clean.
I've never seen anything like this. I would expect the oncheck to produce an error, and I'd certainly expect that when I delete and re-add the duplicate rows, they should be rejected on the reload. It's as if it's just ignoring the word unique.
I have a ticket open, but I'm wondering if anyone here has seen something like this or has other suggestions.
Best,
- TJG
------------------------------
TOM GIRSCH
------------------------------
#Informix