Re: [Sqlrelay-discussion] how to ask sqlrelay if the database is not available

[David M. <dav...@fi...>]

Some database API's provide functions that return the state of the tcp
connection to the database and some don't.  For databases that do, the
server side uses that function when the client runs a ping().  For
databases that don't provide such a function, the server runs "select 1"
or a similar query and examines the error code if it fails.

If the client runs a query (even "select 1") and the database is down,
then SQL Relay will try to log back in to the database over and over.
When the database comes back, it will rerun the query.

However, if a client runs ping() and the database doesn't provide a
native method for testing state of the connection, the SQL Relay
connection daemon runs "select 1" against the database, but bypasses
the code that causes the server to try to log back in over and
over.  If the query fails with an error indicating that the database has
gone down, it returns that immediately.

I just tested it against postgresql with my development version of SQL
Relay and it worked as I expected.  In 0.35, if ping() doesn't return
immediately, then there may be a bug in 0.35 that has since been fixed.

Dave

On Fri, 25 Feb 2005 11:22:34 -0800
"Yulius Tjahjadi" <yu...@xf...> wrote:

> Hi David,
>=20
> >From looking at the code, it looks like ping() basically sets up
> >"select
> 1"=20
> as the sql query string and executes it.  When I call ping w/o a
> database
> connection, it stalls just like if I were to run a regular query.
>=20
> I'm using postgres 7.4 and 8.0 with unix sockets and running fedora
> core 2.
> Doesn't the sqlrelay server have the state of the tcp connection to
> the database?  Is there a way to get at that?
>=20
> Thanks.
>=20
> yulius
>=20
> -----Original Message-----
> From: sql...@li...
> [mailto:sql...@li...] On Behalf Of
> David Muse
> Sent: Friday, February 25, 2005 8:31 AM
> To: sql...@li...
> Subject: Re: [Sqlrelay-discussion] how to ask sqlrelay if the database
> is not available
>=20
>=20
> For the first one, you could do a ping() before running the query.  If
> the ping() returns false then the database is down and you could
> display the other page.
>=20
> For the second one, I'll have to look into that.  Thanks for finding
> it. Hopefully I'll get it sorted out for the next release.
>=20
> Dave
> dav...@fi...
>=20
> On Thu, 24 Feb 2005 15:43:59 -0800
> "Yulius Tjahjadi" <yu...@xf...> wrote:
>=20
> > Hi,
> >=20
> > I have a couple of issues I'm trying to figure out.
> >=20
> > 1. We would like to handle a graceful database downtime by having
> > php know
> >    that our postgres database is down and display another page.=20
> >    Right
>=20
> > now,
> >    0.36-pre2 stalls on sqlrcur_sendQuery if the database is
> >    shutdown.=20
> > Is
> >    there a way to not stall and figure out if sqlrelay is connected
> >    from
> > php?
> >=20
> > 2. On 0.36-pre2 if postgres runs out of connection, sqlrelay will=20
> > crash.
> >    To reproduce this, run postgres w/ 10 max connections, run
> >    sqlrelay
>=20
> > with
> >    a pool of greater than 10.  On startup, it will crash.  This is
> >    not a big deal for me since we have a set pool size, but it might
> >    be an
>=20
> > issue
> >    for other people.
> >=20
> > yulius
> >=20
> >=20
> > -------------------------------------------------------
> > SF email is sponsored by - The IT Product Guide
> > Read honest & candid reviews on hundreds of IT Products from real=20
> > users. Discover which products truly live up to the hype. Start=20
> > reading now. http://ads.osdn.com/?ad_ide95&alloc_id=14396&op=3Dclick
> > _______________________________________________
> > Sqlrelay-discussion mailing list=20
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >=20
>=20
>=20
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real
> users. Discover which products truly live up to the hype. Start
> reading now. http://ads.osdn.com/?ad_ide95&alloc_id=14396&op=3Dick
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>=20
>=20
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real
> users. Discover which products truly live up to the hype. Start
> reading now. http://ads.osdn.com/?ad_ide95&alloc_id=14396&op=3Dclick
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>=20