[Sqlrelay-discussion] failover and starting sqlr with unavailable connections

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

Hi all,

General summary, are the following use cases supported:

1 - starting sqlrelay with some connections unavailable?

2 - having a server fail, then when clients attempt to retrieve a
connection, they will not receive a connection  routed to that server?

Here's my config:

        <instance id="pool" port="9000" dbase="mysql" connections="2"
maxconnections="4" maxqueuelength="0" growby="1" ttl="60"
maxsessioncount="1000" endofsession="commit" sessiontimeout="600"
runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener"
handoff="pass" deniedips="" allowedips="" debug="yes"
maxquerysize="65536" maxstringbindvaluelength="4000"
maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
listenertimeout="5" reloginatstart="yes" timequeriessec="-1"
timequeriesusec="500">
                <users>
                        <user user="me" password="pw"/>
                </users>
                <connections>
                        <connection connectionid="server1"
string="user=me;password=pw;db=test;host=server1;fakebinds=no;"
metric="1" behindloadbalancer="no"/>
                        <connection connectionid="server2"
string="user=me;password=pw;db=test;host=server2;fakebinds=no;"
metric="1" behindloadbalancer="no"/>                </connections>
        </instance>


Ideally, if server1 goes down, clients that attempt to connect will go
to server2 automatically (I realize that failed connection aren't
going to be repooled to the server2).  The behavior I see is this:

If server1 and server2 are up when I start sqlr and I later stop
server1, then every other client connection attempt fails.  So I still
have 2 connections up and running, but the one connected to the
stopped server just throws an error when a client tries to use it
(cannot connect to server1), instead of this connection being marked
invalid by sqlrelay.

If server1 is down, server2 up, when I start sqlr, the system appears
to start normally:


-bash-3.2$ sudo ./sqlr-status -id pool
Warning: using default connectionid.
  Open   Server Connections:  2
  Opened Server Connections:  2

  Open   Client Connections:  0
  Opened Client Connections:  0

  Open   Server Cursors:      10
  Opened Server Cursors:      10

  Times  New Cursor Used:     0
  Times  Cursor Reused:       0

  Total  Queries:             0
  Total  Errors:              0

  Forked Listeners:           0

Scaler's view:
  Connections:                2
  Sessions:                   0

Semaphores:
  +---------------------------------------------+
  | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |  10 |
  +---+---+---+---+---+---+---+---+---+---+-----+
  | 0 | 1 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 1 |   0 |
  +---------------------------------------------+


But all client connection attempts fail like so:

-bash-3.2$ ruby sqlrelaytest.rb
Couldn't connect to the listener.


Any ideas?

Thanks,
Dave