error -285 Invalid cursor received by sqlexec.
A user on IDS 11.50.FC8 (AIX) hit error -285 "Invalid cursor received by sqlexec" on FOREACH in old 4GL programs after a bounce following an online-index/-710 problem; it only struck cursors with more than one host variable, cleared temporarily after rebuilding indexes, and returned. Update statistics and statement-cache changes didn't help; a PMR got no answer, and another restart made it stop. A second user with the same error found that preparing/declaring two cursors from identical SQL text triggered it, and making the second statement textually different (e.g. adding a dummy constant column) avoided it — a workaround, not an official fix.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi ,
IDS 11.50 FC8 , AIX 6.1
After a problem with creation online index , where the engine "lock" in some
way the internal structure of the table and don't stop to raise errors -710
for all tables with have foreign keys to table what the index was created...
we need to force a bounce/start on the instance to stop this and allow the
system company back functional.
This is a problem, what there is no direct relation with the error on the
subject of this message (I believed)
*After this restart* of the instance , some applications (4gl 7.32 / aix 5.3)
start abort with error -285 (Invalid cursor received by sqlexec) on the
foreach command.
Some of this applications are used daily for the users and they compilation
was from 2 months ago (so, 2 months working without problems).
The table what raise the error -285, there is any relationship with the table
what the indexes are created and raise the error -710
The curious is :
- The error occur only when the SQLs have more of one host variables :
declare xyz cursor select * from xyz where field1 = v_variable and field2 =
v_variable2 ## get error 285 on the foreach
declare xyz cursor select * from xyz where field1 = v_variable ## works
declare xyz cursor select * from xyz where field1 = v_variable and field2 =
'abc' ## works
- The problem appear occur always over the same tables (more of one program)
- After recreate the indexes of the tables involved (drop online/create
online), the error disappear... and back on the next day...
- There is no corrupt data / index (at least, is what "oncheck" says... )
- The error occur on the first "fetch" of the data (bellow , part of a stack
trace, from AF trap)
0x00000001000b76a8 (oninit)afstack
0x00000001000b97bc (oninit)afhandler
0x0000000100130b90 (oninit)check_traperror
0x00000001001faa94 (oninit)sqerr
0x0000000100447374 (oninit)sql_nfetch
0x00000001004474f4 (oninit)sq_nfetch
0x000000010022463c (oninit)sqmain
0x000000010037c160 (oninit)listen_verify
0x000000010037a608 (oninit)spawn_thread
0x0000000100dd528c (oninit)startup
This was confirmed by sqlidebug too.
Executing a dummy 4gl program, with one of the SQL what abort the programs, We
got the same error.
+----------------------------------------------------------------
| 2 main
| 3 define s_teste char(5)
| 4 define p_table RECORD LIKE tbl_table.*
| 5 define l_intcod integer
| 6
| 7 let s_teste ='cesar'
| 8 let l_intcod =72507
| 9 DECLARE c01 CURSOR FOR
| 10 SELECT *
| 11 FROM tbl_table
| 12 WHERE teste = s_teste AND
| 13 intcod = l_intcod
| 14 ORDER BY seqcod
| 15
| 16 FOREACH c01 INTO p_table.*
| 17 end foreach
| 18
| 19
| 20 end main
|
|
|
|(main.4gl:main)
+----------------------------------------------------------------
+----------------------------------------------------------------
| sqlawarn = " "
|end record
|$run
|Stopped in main at line 7 in module "main.4gl"
|$s
|Stopped in main at line 8 in module "main.4gl"
|$s
|Stopped in main at line 9 in module "main.4gl"
|$s
|Stopped in main at line 16 in module "main.4gl"
|$s
|Fatal error in main at line 16inmodule "main.4gl"
| -285: Invalid cursor received by sqlexec.
|$p sqlca
|global:sqlca = record
| sqlcode = -285
| sqlerrm = (null)
| sqlerrp = (null)
| sqlerrd = {1, 0, 0, 1, 17, 0}
| sqlawarn = " "
|end record
|$
+----------------------------------------------------------------
We cannot restart the instance again because of some business process what
can't stop any more... and this is something what I don't believed should be a
solution... This isn't a windows server ...
Anyone already have some situation similar with this kind of error? (-285)
Any tips?
Attempts already tried :
1) Execute a update statistics over the table
2) Disable the Statement cache (onmode -e off) , but this command appear not
working on the v11.50 FC8...
3) Recreate the indexes of the table (appear to works for a moment...and back
the problem, but this was done only once and we not try again).
Yes, is a very very odd situation.. but this is a production environment , we
can't play with stop/start of the instance...
Hmmm... I think the versions don't match... But:
- Do you have old 4GL clients connecting to that instance (7.20?) or old
CSDK (maybe 2.81?)
- Do the tables have decimal columns?
- Nothing more happens on the instance? AFs... bad query results?
Nevertheless you should open a PMR...
Regards
On Tue, Aug 2, 2011 at 10:18 PM, Cesar Inacio Martins <
cesar_inacio_martins@yahoo.com.br> wrote:
> Hi ,
>
> IDS 11.50 FC8 , AIX 6.1
>
> After a problem with creation online index , where the engine "lock" in
> some
> way the internal structure of the table and don't stop to raise errors -710
> for all tables with have foreign keys to table what the index was
> created...
> we need to force a bounce/start on the instance to stop this and allow the
> system company back functional.
>
> This is a problem, what there is no direct relation with the error on the
> subject of this message (I believed)
>
> *After this restart* of the instance , some applications (4gl 7.32 / aix
> 5.3)
> start abort with error -285 (Invalid cursor received by sqlexec) on the
> foreach command.
> Some of this applications are used daily for the users and they compilation
> was from 2 months ago (so, 2 months working without problems).
> The table what raise the error -285, there is any relationship with the
> table
> what the indexes are created and raise the error -710
>
> The curious is :
> - The error occur only when the SQLs have more of one host variables :
> declare xyz cursor select * from xyz where field1 = v_variable and field2 =
> v_variable2 ## get error 285 on the foreach
>
> declare xyz cursor select * from xyz where field1 = v_variable ## works
> declare xyz cursor select * from xyz where field1 = v_variable and field2 =
> 'abc' ## works
>
> - The problem appear occur always over the same tables (more of one
> program)
> - After recreate the indexes of the tables involved (drop online/create
> online), the error disappear... and back on the next day...
>
> - There is no corrupt data / index (at least, is what "oncheck" says... )
> - The error occur on the first "fetch" of the data (bellow , part of a
> stack
> trace, from AF trap)
>
> 0x00000001000b76a8 (oninit)afstack
>
> 0x00000001000b97bc (oninit)afhandler
>
> 0x0000000100130b90 (oninit)check_traperror
>
> 0x00000001001faa94 (oninit)sqerr
>
> 0x0000000100447374 (oninit)sql_nfetch
>
> 0x00000001004474f4 (oninit)sq_nfetch
>
> 0x000000010022463c (oninit)sqmain
>
> 0x000000010037c160 (oninit)listen_verify
>
> 0x000000010037a608 (oninit)spawn_thread
>
> 0x0000000100dd528c (oninit)startup
>
> This was confirmed by sqlidebug too.
>
> Executing a dummy 4gl program, with one of the SQL what abort the programs,
> We
> got the same error.
> +----------------------------------------------------------------
> | 2 main
> | 3 define s_teste char(5)
> | 4 define p_table RECORD LIKE tbl_table.*
>
> | 5 define l_intcod integer
> | 6
> | 7 let s_teste ='cesar'
> | 8 let l_intcod =72507
>
> | 9 DECLARE c01 CURSOR FOR
> | 10 SELECT *
> | 11 FROM tbl_table
> | 12 WHERE teste = s_teste AND
> | 13 intcod = l_intcod
> | 14 ORDER BY seqcod
> | 15
> | 16 FOREACH c01 INTO p_table.*
> | 17 end foreach
> | 18
> | 19
> | 20 end main
>
> |
> |
> |
> |(main.4gl:main)
>
> +----------------------------------------------------------------
> +----------------------------------------------------------------
> | sqlawarn = " "
> |end record
> |$run
> |Stopped in main at line 7 in module "main.4gl"
> |$s
>
> |Stopped in main at line 8 in module "main.4gl"
> |$s
> |Stopped in main at line 9 in module "main.4gl"
> |$s
>
> |Stopped in main at line 16 in module "main.4gl"
> |$s
> |Fatal error in main at line 16inmodule "main.4gl"
> | -285: Invalid cursor received by sqlexec.
> |$p sqlca
> |global:sqlca = record
> | sqlcode = -285
> | sqlerrm = (null)
>
> | sqlerrp = (null)
> | sqlerrd = {1, 0, 0, 1, 17, 0}
> | sqlawarn = " "
> |end record
>
> |$
> +----------------------------------------------------------------
>
> We cannot restart the instance again because of some business process what
> can't stop any more... and this is something what I don't believed should
> be a
> solution... This isn't a windows server ...
>
> Anyone already have some situation similar with this kind of error? (-285)
> Any tips?
>
> Attempts already tried :
> 1) Execute a update statistics over the table
> 2) Disable the Statement cache (onmode -e off) , but this command appear
> not
> working on the v11.50 FC8...
> 3) Recreate the indexes of the table (appear to works for a moment...and
> back
> the problem, but this was done only once and we not try again).
>
> Yes, is a very very odd situation.. but this is a production environment ,
> we
> can't play with stop/start of the instance...
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001485f6c822f0034504a98d52af
Hi Fernando,
The 4gl version is the 7.32 FC2 , the csdk used is 2.90 FC4 (version what
incoming with ids 10 fc4)
There is no other versions of 4GL running .
yes, have decimal columns on this table (not the columns used to filter)
Nothing of AFs,
Nothing of bad query (so far ... and I hope keep this )
At this moment have two things weird what I can talk about.
First, I believed I'm have mistake about the re-creation of the indexes and
all start to works fine. Today, during all work day, the problem back occur.
Just now , at night and with low concurrency over the table, testing ... all
appear works fine again. So, maybe the problem is the high concurrency. I will
need to wait tomorrow (wed) at rush time and with high concurrency to check if
the problem back occur , or just mysterious go way...
The second weird thing what happen, few hours after the restart of the
instance, some Java module try to started
and this never happen before, any java was configured and used on the database.
The PMR already open...
08/01/11 17:03:51 Booting Language <java> from module
<$INFORMIXDIR/extend/krakatoa/lmjava.so>
08/01/11 17:03:51 Loading Module <$INFORMIXDIR/extend/krakatoa/lmjava.so>
08/01/11 17:03:51 The C Language Module
</vulca/informix/extend/krakatoa/lmjava.so> loaded
08/01/11 17:03:51 Loading Module <informix.jvp.dbapplet.impl.JarHandler>
08/01/11 17:03:51 mt_bind_class: invalid argument
08/01/11 17:03:51 (-9799): ERROR: Routine execution VP switch failed -- new VP
<jvp>
08/01/11 17:04:37 Logical Log 30080 Complete, timestamp: 0xa098eb88.
08/01/11 17:04:38 Logical Log 30080 - Backup Started
08/01/11 17:04:39 Logical Log 30080 - Backup Completed
08/01/11 17:04:55 mt_bind_class: invalid argument
08/01/11 17:04:55 (-9799): ERROR: Routine execution VP switch failed -- new VP
<jvp>
08/01/11 17:07:02 mt_bind_class: invalid argument
08/01/11 17:07:02 (-9799): ERROR: Routine execution VP switch failed -- new VP
<jvp>
08/01/11 17:07:02 mt_bind_class: invalid argument
08/01/11 17:07:02 (-9799): ERROR: Routine execution VP switch failed -- new VP
<jvp>
08/01/11 17:07:03 mt_bind_class: invalid argument
08/01/11 17:07:03 (-9799): ERROR: Routine execution VP switch failed -- new VP
<jvp>
08/01/11 17:07:03 mt_bind_class: invalid argument
08/01/11 17:07:03 (-9799): ERROR: Routine execution VP switch failed -- new VP
<jvp>
08/01/11 17:08:23 Logical Log 30081 Complete, timestamp: 0xa0b82b12.
0
________________________________
De: Fernando Nunes <domusonline@gmail.com>
Para: ids@iiug.org
Enviadas: Terça-feira, 2 de Agosto de 2011 19:32
Assunto: Re: error -285 Invalid cursor received by sqlexec. [24501]
Hmmm... I think the versions don't match... But:
- Do you have old 4GL clients connecting to that instance (7.20?) or old
CSDK (maybe 2.81?)
- Do the tables have decimal columns?
- Nothing more happens on the instance? AFs... bad query results?
Nevertheless you should open a PMR...
Regards
On Tue, Aug 2, 2011 at 10:18 PM, Cesar Inacio Martins <
cesar_inacio_martins@yahoo.com.br> wrote:
> Hi ,
>
> IDS 11.50 FC8 , AIX 6.1
>
> After a problem with creation online index , where the engine "lock" in
> some
> way the internal structure of the table and don't stop$$ to raise errors -710
> for all tables with have foreign keys to table what the index was
> created...
> we need to force a bounce/start on the instance to stop this and allow the
> system company back functional.
>
> This is a problem, what there is no direct relation with the error on the
> subject of this message (I believed)
>
> *After this restart* of the instance , some applications (4gl 7.32 / aix
> 5.3)
> start abort with error -285 (Invalid cursor received by sqlexec) on the
> foreach command.
> Some of this applications are used daily for the users and they compilation
> was from 2 months ago (so, 2 months working without problems).
> The table what raise the error -285, there is any relationship with the
> table
> what the indexes are created and raise the error -710
>
> The curious is :
> - The error occur only when the SQLs have more of one host variables :
> declare xyz cursor select * from xyz where field1 = v_variable and field2 =
> v_variable2 ## get error 285 on the foreach
>
> declare xyz cursor select * from xyz where field1 = v_variable ## works
> declare xyz cursor select * from xyz where field1 = v_variable and field2 =
> 'abc' ## works
>
> - The problem appear occur always over the same tables (more of one
> program)
> - After recreate the indexes of the tables involved (drop online/create
> online), the error disappear... and back on the next day...
>
> - There is no corrupt data / index (at least, is what "oncheck" says... )
> - The error occur on the first "fetch" of the data (bellow , part of a
> stack
> trace, from AF trap)
>
> 0x00000001000b76a8 (oninit)afstack
>
> 0x00000001000b97bc (oninit)afhandler
>
> 0x0000000100130b90 (oninit)check_traperror
>
> 0x00000001001faa94 (oninit)sqerr
>
> 0x0000000100447374 (oninit)sql_nfetch
>
> 0x00000001004474f4 (oninit)sq_nfetch
>
> 0x000000010022463c (oninit)sqmain
>
> 0x000000010037c160 (oninit)listen_verify
>
> 0x000000010037a608 (oninit)spawn_thread
>
> 0x0000000100dd528c (oninit)startup
>
> This was confirmed by sqlidebug too.
>
> Executing a dummy 4gl program, with one of the SQL what abort the programs,
> We
> got the same error.
> +----------------------------------------------------------------
> | 2 main
> | 3 define s_teste char(5)
> | 4 define p_table RECORD LIKE tbl_table.*
>
> | 5 define l_intcod integer
> | 6
> | 7 let s_teste ='cesar'
> | 8 let l_intcod =72507
>
> | 9 DECLARE c01 CURSOR FOR
> | 10 SELECT *
> | 11 FROM tbl_table
> | 12 WHERE teste = s_teste AND
> | 13 intcod = l_intcod
> | 14 ORDER BY seqcod
> | 15
> | 16 FOREACH c01 INTO p_table.*
> | 17 end foreach
> | 18
> | 19
> | 20 end main
>
> |
> |
> |
> |(main.4gl:main)
>
> +----------------------------------------------------------------
> +----------------------------------------------------------------
> | sqlawarn = " "
> |end record
> |$run
> |Stopped in main at line 7 in module "main.4gl"
> |$s
>
> |Stopped in main at line 8 in module "main.4gl"
> |$s
> |Stopped in main at line 9 in module "main.4gl"
> |$s
>
> |Stopped in main at line 16 in module "main.4gl"
> |$s
> |Fatal error in main at line 16inmodule "main.4gl"
> | -285: Invalid cursor received by sqlexec.
> |$p sqlca
> |global:sqlca = record
> | sqlcode = -285
> | sqlerrm = (null)
>
> | sqlerrp = (null)
> | sqlerrd = {1, 0, 0, 1, 17, 0}
> | sqlawarn = " "
> |end record
>
> |$
> +----------------------------------------------------------------
>
> We cannot restart the instance again because of some business process what
> can't stop any more... and this is something what I don't b
08/01/11 17:04:55 (-9799): ERROR: Routine execution VP switch failed -- new VP
<jvp>
08/01/11 17:07:02 mt_bind_class: invalid argument
The above message shows up usually when you have not starte JVP's in your
Server Instance.
You can check with "onstat -g glo |grep -i jvp" if JVP is started in your
instance.
FWIW I am seeing the exact same problem so I'm interested in any followups to the PMR. We just installed 11.50.FC8 on AIX 5.3 (5300-07) two hours before this thread was started. The install/migration from 10.00.UC6 was flawless and most of several hundred modules in an ancient version of 4Js Genero (1.32) perform the way they did before, only faster. No decimal columns involved. A two-host-variable cursor, but there are dozens of others which are operating correctly. Simple two-table inner join. I am running on a test/development instance so if anyone has any theories I can test them and don't mind bouncing the server. Client SDK is 3.50.
Hi Bruce, For us, unfortunately this error started affect all billing of the company and we was forced to bounce the instance. After that, no more -285 appear at this moment and no answer from the PMR what we opened . ________________________________ De: BRUCE THELEN <bthelen@blarg.net> Para: ids@iiug.org Enviadas: Sexta-feira, 5 de Agosto de 2011 16:03 Assunto: Re: error -285 Invalid cursor received by sqlexec. [24548] FWIW I am seeing the exact same problem so I'm interested in any followups to the PMR. We just installed 11.50.FC8 on AIX 5.3 (5300-07) two hours before this thread was started. The install/migration from 10.00.UC6 was flawless and most of several hundred modules in an ancient version of 4Js Genero (1.32) perform the way they did before, only faster. No decimal columns involved. A two-host-variable cursor, but there are dozens of others which are operating correctly. Simple two-table inner join. I am running on a test/development instance so if anyone has any theories I can test them and don't mind bouncing the server. Client SDK is 3.50. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
OK, an update. I believe this error arises from client-side coding techniques. In my case I had prepared/declared an SQL from a string and then declared two separate cursors for it, one 'with hold' for updating and one as just a 'vanilla' cursor for display. This worked fine for several years. So I tried copying the string, and preparing/declaring two completely separate cursors (different names for the prepared SQL as well as the cursors) for the two copies. This 'fixed' the -285 in one screen but not another. Then I slightly modified the SQL in the second string to do the same select but with a constant on the end. So the first SQL was 'select a,b,c' and the second SQL was 'select a, b, c, "xx"'. This cures the -285 error. My off-the-wall conjecture is that the engine is realizing the cursors are really the same and is making some optimization which then leads to grief. Adding nonsense to the second select statement makes the cursors not-the-same.