172: ISAM error: Unexpected internal error

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

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 www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ 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.

This an issue for tech support.

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

Looks to be a know defect in 12.10.xC11 (no APAR, sorry, also no workaround) -> fixed from xC12 onwards. ------------------------------ Andreas Legner ------------------------------

Interesting, thanks for the share. I didn't realize customers could request a special build. Nice to know.

Thanks Andreas. Will give the FC12 migration/upgrade a go.

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