Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Odd Index Issue

Gaurav KumarWed August 26, 2020 12:46 AM

  • 1.  Odd Index Issue

    Posted Fri August 14, 2020 12:21 PM
    Edited by TOM GIRSCH Fri August 14, 2020 12:28 PM

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


  • 2.  RE: Odd Index Issue

    Posted Mon August 17, 2020 06:28 AM
    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
    ------------------------------



  • 3.  RE: Odd Index Issue

    Posted Mon August 17, 2020 10:25 AM
    If you have TS system access, look at TS004050227 for details.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 4.  RE: Odd Index Issue

    Posted Mon August 17, 2020 10:46 PM
    Hi Tom,

    Can you please upload onunload of the table that has duplicate values to the case...


    Regards,
    Gaurav

    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 5.  RE: Odd Index Issue

    Posted Tue August 18, 2020 11:16 AM
    Unfortunately, I can't get onunload to work. It keeps telling me to mount a tape and complaining when I hit enter. It's not recognizing the -FILE option listed in the documentation, and no matter what I specify for TAPEDEV, it fails with either errno=2 or errno=13.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 6.  RE: Odd Index Issue

    Posted Tue August 18, 2020 11:18 AM
    Did you pre-create (touch) the unload device?

    ------------------------------
    Andreas Legner
    ------------------------------



  • 7.  RE: Odd Index Issue

    Posted Tue August 18, 2020 11:41 AM
    Tom:

    Just to get the "dumb stuff" out of the way, errno 2 is no such file and errno 13 is no permissions. So, does the TAPEDEV file exist and are the privileges correct?

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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.








  • 8.  RE: Odd Index Issue

    Posted Tue August 18, 2020 11:43 AM
    Oh, and the -FILE=<filepath> option is for a file containing environment variables like INFORMIXDIR and INFORMIXSERVER so they don't have to be in the actual environment. That was added to most of the on* tools in v14.10. It doesn't specify the output file for onunload.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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.








  • 9.  RE: Odd Index Issue

    Posted Tue August 18, 2020 12:02 PM
    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
    ------------------------------



  • 10.  RE: Odd Index Issue

    Posted Fri August 21, 2020 01:49 AM
    Hi Tom,

    Can you help me the page size of the dbspaces in which that table and it's index is created....

    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 11.  RE: Odd Index Issue

    Posted Mon August 24, 2020 08:37 AM

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



  • 12.  RE: Odd Index Issue

    Posted Tue August 25, 2020 12:36 AM
    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
    ------------------------------



  • 13.  RE: Odd Index Issue

    Posted Tue August 25, 2020 08:59 AM
    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
    ------------------------------



  • 14.  RE: Odd Index Issue

    Posted Wed August 26, 2020 12:46 AM
    Edited by Gaurav Kumar Wed August 26, 2020 01:10 AM
    .


  • 15.  RE: Odd Index Issue

    Posted Wed August 26, 2020 04:50 AM
    Edited by Gaurav Kumar Wed August 26, 2020 05:58 AM
    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
    ------------------------------



  • 16.  RE: Odd Index Issue

    Posted Wed August 26, 2020 09:24 AM

    I'll try to grab the partition page. But here's the query you need to run to "see" the duplicates:

    SELECT {+FULL(g_ophours)} ophrserial,ophrrtype,ophrmofrom,ophrmothru,ophrdyfrom,ophrdythru,COUNT(*) AS nrows
    FROM g_ophours
    GROUP BY ophrserial,ophrrtype,ophrmofrom,ophrmothru,ophrdyfrom,ophrdythru
    HAVING COUNT(*) > 1



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 17.  RE: Odd Index Issue

    Posted Wed August 26, 2020 09:26 AM
    $ oncheck -pP 23 2336 addr stamp chksum nslots flag type frptr frcnt next prev 23:2336 -1930885629 83dd 5 2 PARTN 340 16020 0 0 slot ptr len flg 1 24 136 0 2 160 44 0 3 204 0 0 4 204 116 0 5 320 20 0 slot 1: 0: 61 0 70 1 2 8 0 0 1c 0 0 0 0 0 1 0 a.p............. 16: 1 0 0 40 95 f9 90 58 1 0 0 0 70 56 0 0 ...@.y.X....pV.. 32: 48 11 0 0 72 13 0 0 c0 11 0 0 0 0 0 0 H...r...@....... 48: ff ff ff ff 41 0 f0 0 1 0 0 0 0 0 0 0 ....A.p......... 64: ba b 13 0 0 0 0 0 0 0 0 0 0 0 0 0 :............... 80: 83 25 b 0 0 0 0 0 0 0 0 0 1 0 0 0 .%.............. 96: 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 ................ 112: 1 0 0 0 a 0 0 0 50 f0 a5 b 0 0 0 0 ........Pp%..... 128: 0 0 0 0 9b 61 46 5f .....aF_........ slot 2: 0: 61 65 67 6c 6f 62 61 6c 0 69 6e 66 6f 72 6d 69 aeglobal.informi 16: 78 0 69 78 5f 67 5f 6f 70 68 6f 75 72 73 5f 31 x.ix_g_ophours_1 32: 36 0 65 6e 5f 55 53 2e 38 31 39 0 6.en_US.819..... slot 3: slot 4: 0: 98 2 0 0 6 0 12 0 0 0 0 0 41 0 f0 0 ............A.p. 16: 1 0 0 0 0 0 4 0 2 0 0 0 0 0 0 0 ................ 32: 0 0 0 0 4 0 2 0 0 0 0 0 0 0 0 0 ................ 48: 0 0 0 0 6 0 4 0 2 0 0 0 0 0 0 0 ................ 64: 0 0 0 0 a 0 4 0 2 0 0 0 0 0 0 0 ................ 80: 0 0 0 0 e 0 2 0 1 0 0 0 0 0 0 0 ................ 96: 0 0 0 0 10 0 2 0 1 0 0 0 0 0 0 0 ................ 112: 0 0 0 0 ................ slot 5: 0: 0 0 0 0 0 17 0 b f6 60 0 0 13 72 0 0 ........v`...r.. 16: 0 0 0 0 ................ ​

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 18.  RE: Odd Index Issue

    Posted Wed August 26, 2020 09:30 AM
    As to the history, the table has been around for years. We upgraded from FC12X1 to FC13 to FC14 to FC14X3 earlier this year in increments. The FC14 upgrade happened on May 24. It's possible that the problem has existed for a long time and went unnoticed. I only found it when tracking down a cdr check issue.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 19.  RE: Odd Index Issue

    Posted Wed August 26, 2020 09:59 AM

    Ok thanks Tom for the info.

     Right now my shift was over and i'll look into your oncheck output in the morning and will let you know.and i hope you have shared partition page of indexes from both the tables (table which contain duplicates and table which doesnt have duplicates)

    and one more thing. As you siad you were there on 12.10 FC12.   So does that mean you have started using informix directly from FC12 or that table was created when you were on FC12 



    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 20.  RE: Odd Index Issue

    Posted Wed August 26, 2020 09:46 AM

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



  • 21.  RE: Odd Index Issue

    Posted Thu August 27, 2020 07:32 AM
    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
    ------------------------------



  • 22.  RE: Odd Index Issue

    Posted Thu August 27, 2020 10:31 AM
    Edited by Gaurav Kumar Thu August 27, 2020 10:32 AM
    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
    ------------------------------



  • 23.  RE: Odd Index Issue

    Posted Thu August 27, 2020 11:42 AM
    Advanced support has never connected to this system.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 24.  RE: Odd Index Issue

    Posted Fri August 28, 2020 07:08 AM
    Edited by Gaurav Kumar Fri August 28, 2020 07:12 AM
    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
    ------------------------------



  • 25.  RE: Odd Index Issue

    Posted Fri August 28, 2020 08:10 AM
    My next question is, why isn't oncheck finding this? There has to be a bug there somewhere.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 26.  RE: Odd Index Issue

    Posted Fri August 28, 2020 09:09 AM

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



  • 27.  RE: Odd Index Issue

    Posted Fri August 28, 2020 09:24 AM
    I'm not sure I follow you, unless we're using different definitions of "bad." To me, a unique index that does not enforce uniqueness is bad by definition. If the problem is with the index, oncheck -cI should find that. If it is elsewhere, then where else is it?

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 28.  RE: Odd Index Issue

    Posted Fri August 28, 2020 09:38 AM
    I agree with Tom. Oncheck should check for inconsistent flags like this one that if this flag is set ON in a UNIQUE index that is a problem! Also someone should be searching the code for any place where that flag might be set, figure out why it might have been set incorrectly on this one index on Tom's server, and fix that code! 

    Alternatively someone should figure out why the flag being set permits dups to be inserted into a table under the control of a UNIQUE index and fix that if it is not a legitimate reason. The only thing I can think of from the outside is that this flag was used to debug something and was never removed from the code, but that's no more than a guess.

    I will grant that there may be a legitimate reason for this flag and its effects, but it is not clear what that might be.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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.








  • 29.  RE: Odd Index Issue

    Posted Fri August 28, 2020 09:45 AM
    This iteration of the table was created on January 31, 2017 and there have been no advanced support operations against it, nor any kind of system catalog shenanigans (against this or any other table). I'd love to know how this got corrupted (or whatever is wrong with it). Now I feel like I need to validate every unique index in the system.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 30.  RE: Odd Index Issue

    Posted Fri August 28, 2020 09:49 AM

    Gaurav:

    I'd also ask that you compare notes with the support engineer (dsybes06) who's working TS004050227.

    Thanks,

    - TJG



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 31.  RE: Odd Index Issue

    Posted Fri August 28, 2020 10:07 AM

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



  • 32.  RE: Odd Index Issue

    Posted 29 days ago
    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
    ------------------------------



  • 33.  RE: Odd Index Issue

    Posted 28 days ago

    Best guess based on ticket history is that we were on 12.10.FC8W1 in January of 2017. I seriously doubt I have online.log going that far back.

    cdr replication dates back to 11.10 (prior to my arrival at this company), but the replication has been completely dismantled and rebuild several times in the interim. Most recently just a few months ago.

    The L0 I shared is from the send-only database. However, to create something you could easily work with, I restored the full thing to a test server, then dropped all of the other databases and tables leaving just the relevant table. Then I took an L0 backup of that and sent that. If I sent you the whole thing, it would have been about a TB.



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 34.  RE: Odd Index Issue

    Posted 28 days ago
    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
    ------------------------------



  • 35.  RE: Odd Index Issue

    Posted 28 days ago
    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
    ------------------------------



  • 36.  RE: Odd Index Issue

    Posted 28 days ago

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



  • 37.  RE: Odd Index Issue

    Posted 27 days ago
    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
    ------------------------------