Connectivity recovery problem
Poster's application server (Informix 7.2 on Solaris 2.5.1) detects SQLCODE -1803 after an engine crash and tries to CONNECT again; once the engine is restarted by cron, the reconnect appears to succeed but every subsequent PREPARE returns SQLCODE 100 until the application is restarted. The logic had worked when the app was single-threaded; it is now POSIX-threaded with serialized DB access through a wrapper holding one connection. Replies note connections/prepared statements cannot survive a lost connection, question why the engine keeps crashing (blamed on mirroring), and suggest upgrading to at least 7.24. No resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm seeing a strange problem where a server that communicates with the Informix database can't recover its connection when Informix crashes and is subsequently re-started. Basically, the server checks the SQLCODE after every SQL statement. If the value = -1803, it attempts to re-establish the connection with the CONNECT statement and try again. If second attempt fails, the Server returns an an error to the client making the database request ... it will then attempt to service another request. It is hoped that if Informix ever goes down, the server will eventually re-establish a connection when Informix is restarted. This is indeed what we see when it is stopped, then restarted manually. However, what I'm seeing in practice when Informix crashes is ... Request n is read by the server. It queries Informix, receives a -1803, attempts to reconnect, receives another -1803 then returns an error for the request. Request n+1.... is read by the server. This time, Informix returns a 100. This will continue for each server request thereafter, until the server is restarted. Some time during the above, Informix is automatically re-started by a cron job. No matter how long we let the server run, however, it continues to receive 100's from Informix. We are using version 7.2 of Informix running on Solaris 2.5.1 Thanks in advance, Rich Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.
rgarabed@tripod.com wrote: > I'm seeing a strange problem where a server that communicates with the > Informix database can't recover its connection when Informix crashes and > is subsequently re-started. > > Basically, the server checks the SQLCODE after every SQL statement. If > the value = -1803, it attempts to re-establish the connection with the > CONNECT statement and try again. If second attempt fails, the Server > returns an an error to the client making the database request ... it > will then attempt to service another request. It is hoped that if > Informix ever goes down, the server will eventually re-establish a > connection when Informix is restarted. This is indeed what we see when > it is stopped, then restarted manually. > > However, what I'm seeing in practice when Informix crashes is ... > > Request n is read by the server. It queries Informix, receives a -1803, > attempts to reconnect, receives another -1803 then returns an error for > the request. > > Request n+1.... is read by the server. This time, Informix returns a > 100. This will continue for each server request thereafter, until the > server is restarted. > > Some time during the above, Informix is automatically re-started by a > cron job. No matter how long we let the server run, however, it > continues to receive 100's from Informix. > > We are using version 7.2 of Informix running on Solaris 2.5.1 > > Thanks in advance, > > Rich > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't. I would be more concerned why your database connections keeps crashing. Hans -----------
rgarabed@tripod.com wrote: > I'm seeing a strange problem where a server that communicates with > the Informix database can't recover its connection when Informix > crashes and is subsequently re-started. Connections and prepared statements etc are not, and cannot be, preserved across separate connections, and when your connection is terminated (whether under program control or because the server crashes is immaterial), reconnecting (even with the same connection name in your ESQL/C code) is establishing a new, independent connection. Why is your version of Informix crashing? > Basically, the server checks the SQLCODE after every SQL statement. If > the value = -1803, it attempts to re-establish the connection with the > CONNECT statement and try again. If second attempt fails, the Server > returns an an error to the client making the database request ... it > will then attempt to service another request. It is hoped that if > Informix ever goes down, the server will eventually re-establish a > connection when Informix is restarted. This is indeed what we see when > it is stopped, then restarted manually. > > However, what I'm seeing in practice when Informix crashes is ... > > Request n is read by the server. It queries Informix, receives a -1803, > attempts to reconnect, receives another -1803 then returns an error for > the request. > > Request n+1.... is read by the server. This time, Informix returns a > 100. This will continue for each server request thereafter, until the > server is restarted. > > Some time during the above, Informix is automatically re-started by a > cron job. Why? > No matter how long we let the server run, however, it > continues to receive 100's from Informix. > > We are using version 7.2 of Informix running on Solaris 2.5.1 Unless you are using 7.24.xxx, it is probably time to upgrade. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>
Well, I am ... but I'm not working on that problem. My focus is need to make sure my server will continue to work - with out human intervention - if that database happens to crash and then gets restarted. Rich In article <3758C891.98B80B59@tusc.com.au>, Hans Siebrand <jhs@tusc.com.au> wrote: > > I would be more concerned why your database connections keeps crashing. > > Hans > > ----------- > > Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.
> Connections and prepared statements etc are not, and cannot be, > preserved across separate connections, and when your connection is > terminated (whether under program control or because the server > crashes is immaterial), reconnecting (even with the same connection > name in your ESQL/C code) is establishing a new, independent connection. This is pretty much what I thought. What I can't understand, however, is an -1803 means there is no connection, so when I do another CONNECT, I *should* either 1)fail wile the database is still down, or 2) connect to the newly running instance of Informix. This is *kind of* what happens, although like I said before, the newly estanblished connection keeps returning an SQLCODE of 100 for EVERY time a statement is PREPARED. I should of mentioned before, this simple error processing algorithm worked before .... when the server we wrote was single threaded. Now its multi-threaded (using POSIX threads). Access to the database, however, is still single-threaded. I basically have a wrapper around the Informix calls that all threads use - basically forcing the threads to access Informix serially. The wrapper opens a single connection at program start, and continues to use that single connection through out program life. > > Why is your version of Informix crashing? I think its load related .... and now that I think of it, I'm not sure our sys-admins know. I'll ask them again ... > > > Some time during the above, Informix is automatically re-started by a > > cron job. > > Why? Well, its because the Informix database is the foundation for a very busy web site. If Informix goes down, then people can't access the web site, they get mad, our advertisers get mad, etc. Restarting it automatically keeps the web site alive. > > > > > We are using version 7.2 of Informix running on Solaris 2.5.1 > > Unless you are using 7.24.xxx, it is probably time to upgrade. Yeah, not sure its in our budget at this point. Thank you for your comments. Rich Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.
> > Why is your version of Informix crashing? > Sys admins tell me it was likely the result of trying ot mirror the database. It doesn't crash that often ... but when it does it makes a mess ! Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.
> > Why is your version of Informix crashing? > Sys admins tell me it was likely the result of trying ot mirror the database. It doesn't crash that often ... but when it does it makes a mess ! Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.