Re: [Sqlrelay-discussion] Fail over for SQL relay - release 0.45

[David D. <dav...@gm...>]

I had the same problem about a year ago, asked basically the same question,
and got no response.

Good luck to you though.


On Wed, May 30, 2012 at 4:43 AM, findjaimin <fin...@gm...> wrote:

> Hi,
>   I am using SQL relay between my php app and Mysql database*s*.
> Following is the configuration file.
>
> <?xml version="1.0"?>
> <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
>
> <instances>
>
>         <!-- Regular SQL Relay Instance -->
>         <instance id="example" port="7000"
> socket="/home/Jaimin/relay.sock" dbase="mysql" connections="10"
> maxconnections="25" maxqueuelength="5" growby="5" ttl="1"
> maxsessioncount="10000" endofsession="commit" sessiontimeout="600"
> runasuser="Jaimin" runasgroup="Jaimin" cursors="5" maxcursors="10"
> cursors_growby="10" authtier="listener" handoff="pass" deniedips=""
> allowedips="" debug="none" maxquerysize="65536"
> maxstringbindvaluelength="4000" maxlobbindvaluelength="71680"
> idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0"
> reloginatstart="no" timequeriessec="-1" timequeriesusec="-1"
> fakeinputbindvariables="no" translatebindvariables="no"
> isolationlevel="read committed" ignoreselectdatabase="no">
>                 <users>
>                         <user user="jaimin" password="password"/>
>                 </users>
>                 <connections>
>                         <connection connectionid="db1"
> string="user=jaimin;password=password;db=schema;host=172.29.5.169"
> metric="1" behindloadbalancer="no"/>
>                         <connection connectionid="db2"
> string="user=jaimin;password=password;db=schema;host=172.29.5.169;port=3308"
> metric="1" behindloadbalancer="no"/>
>                 </connections>
>         </instance>
> </instances>
>
>  My problem is : When mysql referenced by connectionid db2 goes down,
> every second request sent by php gets error "Can't connect to MySQL server
> on '172.29.5.169' (111)" . However as mentioned in this (
> http://sqlrelay.sourceforge.net/sqlrelay/loadbalfailover.html ) link it
> should just *mark* connection(s) of db2 as *unavailable to clients*.
> Means, my php app will never get error..instead it will have smaller pool
> of connections so may be some performance impact. I am using release # 0.45.
>
> Am I missing something in achieving following behaviour as mentioned in
> http://sqlrelay.sourceforge.net/sqlrelay/loadbalfailover.html ? :
> "Currently, if an SQL Relay connection daemon notices that the database
> server it is connected to has gone down it will mark itself unavailable to
> clients, log out and loop, attempting to re-connect to that database
> server. If that connection daemon is configured with the *
> behindloadbalancer* attribute set to "no", then it will also raise a flag
> and all connection daemons connected to that database server mark
> themselves unavailable to clients, close their connections and loop,
> attempting to re-connect to that database server. When the database server
> comes back up, as each connection daemon successfully re-connects to the
> database server, it marks itself available to clients again. While one
> database server is down, client sessions are still distributed over the
> servers that are still up, albiet through a smaller pool of persistent
> database connections."
>
>
> Thanks,
> Jaimin
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>