stored procedures and informix crash
Poster reported IDS 9.21 crashing (Assert Failed: Exception Caught, MT_EX_OS 'mem', recursive exception, server PANIC) under load when using three levels of nested SPL functions returning rows row-by-row via FOREACH ... RETURN WITH RESUME. His workaround was to flatten the design so the client calls the appropriate procedure directly (one level), plus replacing LVARCHAR with CHAR(2048), which made it stable. An IBM support engineer said this is a bug, not a limitation, and urged opening a support case; another user reported a similar nested-SPL panic on Solaris 8 / IDS 9.30.UC4 (case 355228), and a third saw comparable crashes from a huge generated SQL statement. No actual fix or patch is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
After some serious investigation I have concluded that
there are serious limitation to using nested stored procedures
with foreach clause.
In our application, the client calls a stored procedure with
appropriate parameters. Within the called stored procedure,
based on parameters, it calls different stored procedures.
In the second level stored procedure , based on parameter,
a third level stored procedure is called. All of them
return row by row in foreach.
For e.g.
application call
execute bk('aaa','bbb','ccc) in a cursor.
create function bk(....) returning char(2048)if parameter = 'aaa' then
foreach function bk_2() into retstr
return retstr with resume ;
end foreach ;
end if
end function
create function bk_2 returning char(2048)if ... then
foreach function bk_3() into retstr
return retstr with resume ;
end foreach;
end if ;
end function ;
create function bk_3 returning char(2048)foreach select blah blah blah
into retstr
return retstr with resume ;
end foreach;
end function ;
This approach has been taken to maintain a single level calling
interface to the client program. However, when there is heavy
usage , the server crashes.
=====================================
16:24:53 Booting Language <spl> from module <>
16:24:53 Loading Module <SPLNULL>
16:34:07 Assert Failed: Exception Caught. Type: MT_EX_OS, Context: mem
16:34:07 Informix Dynamic Server 2000 Version 9.21.UC4XE
16:34:07 Who: Session(37, dba@webqa, 1328, 403967912)
Thread(90, sqlexec, 18119fe8, 5)
File: mtex.c Line: 356
"/opt/informix.org/logs/online.log" 759 lines, 14068 characters
Thread(90, sqlexec, 18119fe8, 5)
File: mtex.c Line: 356
16:34:07 Action: Please notify Informix Technical Support.
16:34:07 stack trace for pid 1191 written to /tmp/af.442edcf
16:34:07 See Also: /tmp/af.442edcf
16:34:08 Assert Failed: No Exception Handler
16:34:08 Informix Dynamic Server 2000 Version 9.21.UC4XE
16:34:08 Who: Session(37, dba@webqa, 1328, 403967912)
Thread(90, sqlexec, 18119fe8, 1)
File: mtex.c Line: 405
16:34:08 Results: Exception Caught. Type: MT_EX_OS, Context: mem
16:34:08 Action: Please notify Informix Technical Support.
16:34:08 stack trace for pid 1187 written to /tmp/af.442edcf
16:34:08 See Also: /tmp/af.442edcf
16:34:08 mtex.c, line 405, thread 90, proc id 1187, No Exception Handler.
16:34:08 Recursive Exception - Server exiting
16:34:08 The Master Daemon Died
16:34:08 PANIC: Attempting to bring system down
16:34:09 semctl: errno = 22
16:34:09 semctl: errno = 22
16:34:09 semctl: errno = 22
16:34:09 semctl: errno = 22
================================
Once I changed the calling level to only one (which means client program has
to call appropriate stored procedue), this problem vanished.
In other words, informix craps out in three level of nested procedures.
Lame..
very lame.
Plus, LVARCHAR is inherently unreliable variable. We changed it to
char(2048)
and it became stable.
Ravi.
Ravi,
Based on the fact that the engine crashes I would not say it is a product
limitation, but a product bug.
You should open a case via the proper support channels to have the bug
identifed so a fix can be
developed if one does not exist already.
JMM
John Michael Magie
Advanced Support Engineer
IBM Data Management Solutions, Software Group
Tel : 1-800-274-8184 Fax : 913-599-7185
internet : jmagie@us.ibm.com
http://www-4.ibm.com/software/data/informix
"rkusenet "
<rkusenet@sympati To: ids@iiug.org
co.ca> cc:
Sent by: Subject: stored procedures and informix crash [741]
forum.subscriber@
iiug.org
03/17/2003 10:39
AM
Hi,
After some serious investigation I have concluded that
there are serious limitation to using nested stored procedures
with foreach clause.
In our application, the client calls a stored procedure with
appropriate parameters. Within the called stored procedure,
based on parameters, it calls different stored procedures.
In the second level stored procedure , based on parameter,
a third level stored procedure is called. All of them
return row by row in foreach.
For e.g.
application call
execute bk('aaa','bbb','ccc) in a cursor.
create function bk(....) returning char(2048)if parameter = 'aaa' then
foreach function bk_2() into retstr
return retstr with resume ;
end foreach ;
end if
end function
create function bk_2 returning char(2048)if ... then
foreach function bk_3() into retstr
return retstr with resume ;
end foreach;
end if ;
end function ;
create function bk_3 returning char(2048)foreach select blah blah blah
into retstr
return retstr with resume ;
end foreach;
end function ;
This approach has been taken to maintain a single level calling
interface to the client program. However, when there is heavy
usage , the server crashes.
=====================================
16:24:53 Booting Language <spl> from module <>
16:24:53 Loading Module <SPLNULL>
16:34:07 Assert Failed: Exception Caught. Type: MT_EX_OS, Context: mem
16:34:07 Informix Dynamic Server 2000 Version 9.21.UC4XE
16:34:07 Who: Session(37, dba@webqa, 1328, 403967912)
Thread(90, sqlexec, 18119fe8, 5)
File: mtex.c Line: 356
"/opt/informix.org/logs/online.log" 759 lines, 14068 characters
Thread(90, sqlexec, 18119fe8, 5)
File: mtex.c Line: 356
16:34:07 Action: Please notify Informix Technical Support.
16:34:07 stack trace for pid 1191 written to /tmp/af.442edcf
16:34:07 See Also: /tmp/af.442edcf
16:34:08 Assert Failed: No Exception Handler
16:34:08 Informix Dynamic Server 2000 Version 9.21.UC4XE
16:34:08 Who: Session(37, dba@webqa, 1328, 403967912)
Thread(90, sqlexec, 18119fe8, 1)
File: mtex.c Line: 405
16:34:08 Results: Exception Caught. Type: MT_EX_OS, Context: mem
16:34:08 Action: Please notify Informix Technical Support.
16:34:08 stack trace for pid 1187 written to /tmp/af.442edcf
16:34:08 See Also: /tmp/af.442edcf
16:34:08 mtex.c, line 405, thread 90, proc id 1187, No Exception Handler.
16:34:08 Recursive Exception - Server exiting
16:34:08 The Master Daemon Died
16:34:08 PANIC: Attempting to bring system down
16:34:09 semctl: errno = 22
16:34:09 semctl: errno = 22
16:34:09 semctl: errno = 22
16:34:09 semctl: errno = 22
================================
Once I changed the calling level to only one (which means client program
has
to call appropriate stored procedue), this problem vanished.
In other words, informix craps out in three level of nested procedures.
Lame..
very lame.
Plus, LVARCHAR is inherently unreliable variable. We changed it to
char(2048)
and it became stable.
Ravi.
rkusenet
wrote:
>
> Hi,
>
> After some serious investigation I have concluded that
> there are serious limitation to using nested stored procedures
> with foreach clause.
>
> In our application, the client calls a stored procedure with
> appropriate parameters. Within the called stored procedure,
> based on parameters, it calls different stored procedures.
> In the second level stored procedure , based on parameter,
> a third level stored procedure is called. All of them
> return row by row in foreach.
>
> For e.g.
>
> application call
> execute bk('aaa','bbb','ccc) in a cursor.
>
> create function bk(....) returning char(2048)> if parameter = 'aaa' then
> foreach function bk_2() into retstr
> return retstr with resume ;
> end foreach ;
> end if
> end function
>
> create function bk_2 returning char(2048)> if ... then
> foreach function bk_3() into retstr
> return retstr with resume ;
> end foreach;
> end if ;
> end function ;
>
> create function bk_3 returning char(2048)> foreach select blah blah blah
> into retstr
> return retstr with resume ;
> end foreach;
> end function ;
>
> This approach has been taken to maintain a single level calling
> interface to the client program. However, when there is heavy
> usage , the server crashes.
>
> =====================================
> 16:24:53 Booting Language <spl> from module <>
> 16:24:53 Loading Module <SPLNULL>
> 16:34:07 Assert Failed: Exception Caught. Type: MT_EX_OS, Context: mem
> 16:34:07 Informix Dynamic Server 2000 Version 9.21.UC4XE
> 16:34:07 Who: Session(37, dba@webqa, 1328, 403967912)
> Thread(90, sqlexec, 18119fe8, 5)
> File: mtex.c Line: 356
> "/opt/informix.org/logs/online.log" 759 lines, 14068 characters
> Thread(90, sqlexec, 18119fe8, 5)
> File: mtex.c Line: 356
> 16:34:07 Action: Please notify Informix Technical Support.
> 16:34:07 stack trace for pid 1191 written to /tmp/af.442edcf
> 16:34:07 See Also: /tmp/af.442edcf
> 16:34:08 Assert Failed: No Exception Handler
> 16:34:08 Informix Dynamic Server 2000 Version 9.21.UC4XE
> 16:34:08 Who: Session(37, dba@webqa, 1328, 403967912)
> Thread(90, sqlexec, 18119fe8, 1)
> File: mtex.c Line: 405
> 16:34:08 Results: Exception Caught. Type: MT_EX_OS, Context: mem
> 16:34:08 Action: Please notify Informix Technical Support.
> 16:34:08 stack trace for pid 1187 written to /tmp/af.442edcf
> 16:34:08 See Also: /tmp/af.442edcf
> 16:34:08 mtex.c, line 405, thread 90, proc id 1187, No Exception Handler.
> 16:34:08 Recursive Exception - Server exiting
> 16:34:08 The Master Daemon Died
> 16:34:08 PANIC: Attempting to bring system down
> 16:34:09 semctl: errno = 22
>
> 16:34:09 semctl: errno = 22
>
> 16:34:09 semctl: errno = 22
>
> 16:34:09 semctl: errno = 22
>
> ================================
>
> Once I changed the calling level to only one (which means client program has
> to call appropriate stored procedue), this problem vanished.
Hi Ravi,
today we logged a case because of a PANIC in a case, which looks
very similar. Here also nested SPL is involved, but here the
System Error is 27, not 22.
Our support case number is 355228.
Maybe this is of help to someone
dic_k
rkusenet
wrote:
>
> Hi,
>
> After some serious investigation I have concluded that
> there are serious limitation to using nested stored procedures
> with foreach clause.
>
> In our application, the client calls a stored procedure with
> appropriate parameters. Within the called stored procedure,
> based on parameters, it calls different stored procedures.
> In the second level stored procedure , based on parameter,
> a third level stored procedure is called. All of them
> return row by row in foreach.
>
> For e.g.
>
> application call
> execute bk('aaa','bbb','ccc) in a cursor.
>
> create function bk(....) returning char(2048)> if parameter = 'aaa' then
> foreach function bk_2() into retstr
> return retstr with resume ;
> end foreach ;
> end if
> end function
>
> create function bk_2 returning char(2048)> if ... then
> foreach function bk_3() into retstr
> return retstr with resume ;
> end foreach;
> end if ;
> end function ;
>
> create function bk_3 returning char(2048)> foreach select blah blah blah
> into retstr
> return retstr with resume ;
> end foreach;
> end function ;
>
> This approach has been taken to maintain a single level calling
> interface to the client program. However, when there is heavy
> usage , the server crashes.
>
> =====================================
> 16:24:53 Booting Language <spl> from module <>
> 16:24:53 Loading Module <SPLNULL>
> 16:34:07 Assert Failed: Exception Caught. Type: MT_EX_OS, Context: mem
> 16:34:07 Informix Dynamic Server 2000 Version 9.21.UC4XE
> 16:34:07 Who: Session(37, dba@webqa, 1328, 403967912)
> Thread(90, sqlexec, 18119fe8, 5)
> File: mtex.c Line: 356
> "/opt/informix.org/logs/online.log" 759 lines, 14068 characters
> Thread(90, sqlexec, 18119fe8, 5)
> File: mtex.c Line: 356
> 16:34:07 Action: Please notify Informix Technical Support.
> 16:34:07 stack trace for pid 1191 written to /tmp/af.442edcf
> 16:34:07 See Also: /tmp/af.442edcf
> 16:34:08 Assert Failed: No Exception Handler
> 16:34:08 Informix Dynamic Server 2000 Version 9.21.UC4XE
> 16:34:08 Who: Session(37, dba@webqa, 1328, 403967912)
> Thread(90, sqlexec, 18119fe8, 1)
> File: mtex.c Line: 405
> 16:34:08 Results: Exception Caught. Type: MT_EX_OS, Context: mem
> 16:34:08 Action: Please notify Informix Technical Support.
> 16:34:08 stack trace for pid 1187 written to /tmp/af.442edcf
> 16:34:08 See Also: /tmp/af.442edcf
> 16:34:08 mtex.c, line 405, thread 90, proc id 1187, No Exception Handler.
> 16:34:08 Recursive Exception - Server exiting
> 16:34:08 The Master Daemon Died
> 16:34:08 PANIC: Attempting to bring system down
> 16:34:09 semctl: errno = 22
>
> 16:34:09 semctl: errno = 22
>
> 16:34:09 semctl: errno = 22
>
> 16:34:09 semctl: errno = 22
>
> ================================
>
Sorry, all
I forgot to mention:
The enviroment to our support case 355228
is Solaris 8 & IFX 9.30.UC4
dic_k
>today we logged a case because of a PANIC in a case, which looks >very similar. Here also nested SPL is involved, but here the >System Error is 27, not 22. >Our support case number is 355228. >Maybe this is of help to someone > Sorry, all > I forgot to mention: > The enviroment to our support case 355228 > is Solaris 8 & IFX 9.30.UC4 So this is not fixed even in 9.30 ?????.
Hello,
From our experience it seems it is not the limitation of stored procedures
only.
Namely we had the similar problem:
sun4u sparc SUNW,Ultra-60
Informix Dynamic Server 2000 Version 9.21.UC3
online.log:
11:43:17 Recursive Exception - Server exiting
11:43:17 Recursive Exception - Server exiting
11:43:17 Fatal error in ADM VP at mt.c:11380
11:43:17 Unexpected virtual processor termination, pid = 12939, exit =
0x100
11:43:17 PANIC: Attempting to bring system down
11:43:17 semctl: errno = 22
11:43:17 semctl: errno = 22
11:43:17 semctl: errno = 22
11:43:17 semctl: errno = 22
from af* file at that time onstat -g sql:
select count(read_num) from articles where dis_id = ? and not ((read_num
between ? and ?) or (read_num between ? and ?) or (read_num between ?
and ?)
or (read_num between ? and ?) or (read_num between ? and ?) or (read_num
between ...
and then for 1320 lines (over 2000 between clauses).
This piece of sql was constructed in and called from cgi-bin script.
I rewrote it (entire section which sql above was in) using 4 nested
procedures and replaced 'not (( .. between )or( between) or( between))'.
And now the main stored procedure handles the case that priviously crashed
the database and one foreach in one stored procedure calls another stored
procedure over 10000 times.
Hope this helps in some way. I would be very interested in the resolution as
I didn't have time to test the limits newly written stored procedures.
Thanks
Ljubica
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of rkusenet
Sent: 17 March 2003 16:40
To: ids@iiug.org
Subject: stored procedures and informix crash [741]
Hi,
After some serious investigation I have concluded that
there are serious limitation to using nested stored procedures
with foreach clause.
In our application, the client calls a stored procedure with
appropriate parameters. Within the called stored procedure,
based on parameters, it calls different stored procedures.
In the second level stored procedure , based on parameter,
a third level stored procedure is called. All of them
return row by row in foreach.
For e.g.
application call
execute bk('aaa','bbb','ccc) in a cursor.
create function bk(....) returning char(2048)if parameter = 'aaa' then
foreach function bk_2() into retstr
return retstr with resume ;
end foreach ;
end if
end function
create function bk_2 returning char(2048)if ... then
foreach function bk_3() into retstr
return retstr with resume ;
end foreach;
end if ;
end function ;
create function bk_3 returning char(2048)foreach select blah blah blah
into retstr
return retstr with resume ;
end foreach;
end function ;
This approach has been taken to maintain a single level calling
interface to the client program. However, when there is heavy
usage , the server crashes.
=====================================
16:24:53 Booting Language <spl> from module <>
16:24:53 Loading Module <SPLNULL>
16:34:07 Assert Failed: Exception Caught. Type: MT_EX_OS, Context: mem
16:34:07 Informix Dynamic Server 2000 Version 9.21.UC4XE
16:34:07 Who: Session(37, dba@webqa, 1328, 403967912)
Thread(90, sqlexec, 18119fe8, 5)
File: mtex.c Line: 356
"/opt/informix.org/logs/online.log" 759 lines, 14068 characters
Thread(90, sqlexec, 18119fe8, 5)
File: mtex.c Line: 356
16:34:07 Action: Please notify Informix Technical Support.
16:34:07 stack trace for pid 1191 written to /tmp/af.442edcf
16:34:07 See Also: /tmp/af.442edcf
16:34:08 Assert Failed: No Exception Handler
16:34:08 Informix Dynamic Server 2000 Version 9.21.UC4XE
16:34:08 Who: Session(37, dba@webqa, 1328, 403967912)
Thread(90, sqlexec, 18119fe8, 1)
File: mtex.c Line: 405
16:34:08 Results: Exception Caught. Type: MT_EX_OS, Context: mem
16:34:08 Action: Please notify Informix Technical Support.
16:34:08 stack trace for pid 1187 written to /tmp/af.442edcf
16:34:08 See Also: /tmp/af.442edcf
16:34:08 mtex.c, line 405, thread 90, proc id 1187, No Exception Handler.
16:34:08 Recursive Exception - Server exiting
16:34:08 The Master Daemon Died
16:34:08 PANIC: Attempting to bring system down
16:34:09 semctl: errno = 22
16:34:09 semctl: errno = 22
16:34:09 semctl: errno = 22
16:34:09 semctl: errno = 22
================================
Once I changed the calling level to only one (which means client program has
to call appropriate stored procedue), this problem vanished.
In other words, informix craps out in three level of nested procedures.
Lame..
very lame.
Plus, LVARCHAR is inherently unreliable variable. We changed it to
char(2048)
and it became stable.
Ravi.