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

[<kum...@si...>]

David,

Ping does return false even after the DB comes up, after failure. similar condition has been reported in http://sourceforge.net/mailarchive/message.php?msg_id=14211078

As already quoted, when the DB goes down, the TCP connection between sqlr-connection and MYSQL server goes to CLOSE_WAIT state from ESTABLISHED state. When the DB comes up, the earlier connection is no longer valid. sqlr-connection must try out a new TCP connection with DB, which is not the case with ping(). AFAIK, only sendQuery() attempts to reconnect.

Kumara Sundaram

Quoting  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
> 



-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems?  Stop!  Download the new AJAX search engine that makes
searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Sqlrelay-discussion mailing list
Sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion