Re: [Sqlrelay-discussion] ping() sendQuery() problem during database failure handling

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

Are you sure ping() returns false even when the database has come back
up?  I just tested it and it works for me:

[mused@localhost ~]$ sqlr-start -id mysqltest

Starting listener:
  sqlr-listener -id mysqltest
-config /usr/local/firstworks/etc/sqlrelay.conf
Warning: could not change group to nobody
Warning: could not change user to nobody

Starting 1 connections to db :
  sqlr-connection-mysql -id mysqltest -connectionid db
-config /usr/local/firstworks/etc/sqlrelay.conf
Warning: could not change group to nobody
Warning: could not change user to nobody

Starting cache manager:
  sqlr-cachemanager
Warning: using default id.


Thanks to MP3.com for sponsoring:
        Clustered/Replicated database support.
        Perl API.
[mused@localhost ~]$ sqlrsh -id mysqltest
SQLRShell - Version 0.22
        Connected to: localhost:8006 as mysqltest

        type help; for a help.

0> select * from testtable;
col1
====
1
        Rows Returned   : 1
        Fields Returned : 1
        System time     : 20000

(here I shut the db down in another window)

0> ping;
        The database is down.

(here I brought it back up)

0> ping;
        The database is up.
0> select * from testtable;
col1
====
1
        Rows Returned   : 1
        Fields Returned : 1
        System time     : 20000

0> quit
1> ;


I tried a couple of other combinations, but ping() appears to work
properly to me, at least with MySQL.

Dave
dav...@fi...

On Fri, 2006-01-20 at 16:10 +0600, Kumara S wrote:
> Hi,
> 
> We are develpoping a web application that connects to MySQL thru
> sqlrelay...
> 
> We thought of using PHP pear DB classes to abstract the
> database. My requirement is to connect to the database server and check
> if it can handle further queries successfully. When the MySQL server
> goes down, sqlrelay sendQuery() keeps retrying infinitely and returns
> only after the DB comes up. Since i can't allow my page to wait till
> everything becomes normal, i thought of using ping() to check the status
> of MySQL before querying. but, even when the DB recovers later, ping()
> returns false. Following ealier discussions with Maciej Wisniowski,
> ping() just gives the status of the connection that went to CLOSE_WAIT
> state and does not attempt to reconnect to DB. It is only the first
> sendQuery that reconnects.
> 
> So, using ping() before sendQuery() does
> not allow my application to recover even when MySQL is up.
> Calling 
> sendQuery() directly sends my application to infinite loop in case of DB
> failure.
> 
> The problem is simple. Connect to DB when it is available in
> a single try, return false in case of a DB failure. API calls like
> mysql_connect gives me just that. but, sqlrelay either gives me infinite
> retries(when DB goes down) or no try at all (when it
> recovers).
> 
> Please give me a workaround.
> 
> Thanks!
> 
> Kumara
> Sundaram
>