Informix

 View Only
  • 1.  172: ISAM error: Unexpected internal error

    Posted Tue July 21, 2020 07:35 AM
    Hi all,

    I have an existing table, which is populated. When I attempt to ALTER TABLE to ADD a column (a simple CHAR(1) with no constraints) I get the following error:
    959: The current transaction has been rolled back due to an internal error.
    172: ISAM error: Unexpected internal error

    From the online log:
    02:00:25 Assert Failed: No Exception Handler
    02:00:25 IBM Informix Dynamic Server Version 12.10.FC11
    02:00:25 Who: Session(46, informix@foobar, 1031, 0x6b8bfba8)
    Thread(250, sqlexec, 6b880108, 8)
    File: mtex.c Line: 508
    02:00:25 Results: Exception Caught. Type: MT_EX_OS, Context: mem
    02:00:25 Action: Please notify IBM Informix Technical Support.
    (note: source file and line number reference - mtex.c:508)

    There appear to be 2 separate stack traces (?) from the dump log:
    0x000000000141d4b9 (oninit) afstack
    0x0000000001420fa1 (oninit) mt_ex_throw_sig
    0x000000000142be13 (oninit) afsig_handler
    0x00007f199049b8a0 (Linux) <signal frame>
    0x00000000009a41d7 (oninit) getcolp
    0x00000000009f072d (oninit) ddistdrp
    0x00000000009f42b9 (oninit) perform_alter
    0x00000000009f7217 (oninit) ddalter
    0x00000000006ee00d (oninit) aud_ddalter
    0x00000000005fdae9 (oninit) excommand
    0x0000000000a46eac (oninit) sq_execute
    0x0000000000b0a161 (oninit) sqmain
    0x000000000154a03b (oninit) spawn_thread
    0x000000000140efd0 (oninit) th_init_initgls
    0x00000000014547f8 (oninit) startup
    0x0000000001423d78 (oninit) mt_system
    0x000000000141fb69 (oninit) afhandler
    0x0000000001420af2 (oninit) afcrash_interface
    0x0000000001421018 (oninit) mt_ex_throw_sig
    0x000000000142be13 (oninit) afsig_handler
    0x00007f199049b8a0 (Linux) <signal frame>
    0x00000000009a41d7 (oninit) getcolp
    0x00000000009f072d (oninit) ddistdrp
    0x00000000009f42b9 (oninit) perform_alter
    0x00000000009f7217 (oninit) ddalter
    0x00000000006ee00d (oninit) aud_ddalter
    0x00000000005fdae9 (oninit) excommand
    0x0000000000a46eac (oninit) sq_execute
    0x0000000000b0a161 (oninit) sqmain
    0x000000000154a03b (oninit) spawn_thread
    0x000000000140efd0 (oninit) th_init_initgls
    0x00000000014547f8 (oninit) startup
    Which seems to imply the signal is caught twice by the same signal handler, but, for some reason follows a different processing path with the 2nd trap. However, the thread for both stacks is apparently the same ("253 sqlexec").

    In any event, the error still occurs, even after:
    • All indexes on the table are dropped
    • All external constraints referencing the table are dropped
    • All rows are deleted via DELETE TABLE
    However, if I TRUNCATE the table, then try to add the same column, it works without error!?

    I'm not sure if PDQ is set or not (unless someone can confirm that perhaps it is from the above), and I'm also not sure if any bad/stale caching needs to be cleared/reset/disabled (assuming that is remotely related to the issue).

    There is a work-around, which involves truncating the entire table, and then reloading the data.
    But, this is the least-preferable option, given the problem is happening with several other tables, with the odd table being rather large, and having to rebuild multiple indexes for them something we'd prefer to avoid.

    If anyone has any ideas as to finding a better solution, or some kind of work-around that allows us to keep the table populated while adding a column, it would be greatly appreciated.

    TIA.
    #Informix


  • 2.  RE: 172: ISAM error: Unexpected internal error

    IBM Champion
    Posted Tue July 21, 2020 08:30 AM
    Daniil:

    Have you tried running an "oncheck -cDI" on one of the offending tables? Results?

    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.








  • 3.  RE: 172: ISAM error: Unexpected internal error

    Posted Tue July 21, 2020 09:09 AM

    This an issue for tech support. 

     

     

     

     






  • 4.  RE: 172: ISAM error: Unexpected internal error

    Posted Tue July 21, 2020 12:18 PM
    Edited by System Fri January 20, 2023 04:34 PM
    Hi Daniil,
    My customer also had an assert failture and 959/172 error as below when adding a column in version 12.10.FC11.
    [SQL]
    ALTER TABLE CONF_ONT_AID ADD CARDTYPE int;
    
    [Error Code] -959 & -172
    -959    The current transaction has been rolled back due to an internal error.
    -172    ISAM error: Unexpected internal error.
    
    [online.log]
    06/19/19 14:59:15  Assert Failed: No Exception Handler
    06/19/19 14:59:15  IBM Informix Dynamic Server Version 12.10.FC11
    06/19/19 14:59:15   Who: Session(22610, vdsl@provmon1, 29196, 1da10f0e8)
                    Thread(22849, sqlexec, 1da0ddc98, 1)
                    File: mtex.c Line: 508
    06/19/19 14:59:15   Results: Exception Caught. Type: MT_EX_OS, Context: mem
    06/19/19 14:59:15   Action: Please notify IBM Informix Technical Support.
    06/19/19 14:59:15   See Also: /informix/tmp/af.5d29cf32​

    The workaround is to create a new table schema and then load the existing data, but as you mentioned, it is not suitable for large tables.
    You need to open the case to IBM for cause analysis, but it is likely a defect. My customer applied version 12.10.FC13.

    Another story, but on 12.10.FC13, a 959/172 error occurred when a merge statement with a subquery was executed. So, I have requested special build.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 5.  RE: 172: ISAM error: Unexpected internal error

    Posted Wed July 22, 2020 04:33 AM
    Interesting, thanks for the share.

    I didn't realize customers could request a special build. Nice to know.


  • 6.  RE: 172: ISAM error: Unexpected internal error

    IBM Champion
    Posted Tue July 21, 2020 01:49 PM
    Looks to be a know defect in 12.10.xC11 (no APAR, sorry, also no workaround) -> fixed from xC12 onwards.

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



  • 7.  RE: 172: ISAM error: Unexpected internal error

    Posted Wed July 22, 2020 04:35 AM
    Thanks Andreas.

    Will give the FC12 migration/upgrade a go.


  • 8.  RE: 172: ISAM error: Unexpected internal error

    IBM Champion
    Posted Wed July 22, 2020 04:38 AM
    Well, nobody obliging you to use each and every interim fixpack (FC14 would be the latest, FC12 having some other serious or nasty flaws) ;-)

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