mysql-proxy-discuss team mailing list archive

[Georg Moritz <georg.moritz@xxxxxxxxxxxx>]

Hello Diego, hello team,

after poking around a bit and adding debug print statements, I found out
that rw-splitting.lua only works with non-authenticated connections.

Running the loop

    bash# for i in `seq 1 11`; do
    > echo "select count(ip) from awl;" | \
    > mysql -h 10.1.2.24 --port=4040 -u amavis -ppass sa_bayes
    > done

the mysql-proxy on the master node 10.1.2.20 to which the mysql-proxy
on 10.1.2.24 connects, chokes with the 11th connection:
   
---8<---
[connect_server] 10.1.2.24:57708
  [1].connected_clients = 0
  [1].pool.cur_idle     = 5
  [1].pool.max_idle     = 8
  [1].pool.min_idle     = 4
  [1].type = 1
  [1].state = 1
  [2].connected_clients = 0
  [2].pool.cur_idle     = 0
  [2].pool.max_idle     = 8
  [2].pool.min_idle     = 4
  [2].type = 2
  [2].state = 1
  [2] idle-conns below min-idle
[read_query] 10.1.2.24:57708
  current backend   = 0
  client default db = sa_bayes
  client username   = amavis
  query             = select count(ip) from awl
  statement = TK_SQL_SELECT
   BACKEND_TYPE_RO s.state = 1
conns.cur_idle_connections = 5
***** choosing backend 2
  sending to backend : 127.0.0.1:3306
    is_slave         : true
    server default db: sa_bayes
    server username  : amavis
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 10.1.2.24:57708
  current backend   = 0
  client default db = sa_bayes
  client username   = amavis
  (QUIT) current backend   = 0
[disconnect_client] 10.1.2.24:57708

[connect_server] 10.1.2.24:57710
  [1].connected_clients = 0
  [1].pool.cur_idle     = 5
  [1].pool.max_idle     = 8
  [1].pool.min_idle     = 4
  [1].type = 1
  [1].state = 1
  [2].connected_clients = 0
  [2].pool.cur_idle     = 5
  [2].pool.max_idle     = 8
  [2].pool.min_idle     = 4
  [2].type = 2
  [2].state = 1
  [1] taking master as default      <==== here
  using pooled connection from: 1   <==== it chokes
[disconnect_client] 10.1.2.24:57710
---8<---

The "taking master as default" message from rw-splitting.lua is printed
in function connect_server() if the last branch of the if-condition in
the backends-loop is met:

---8<---
                elseif s.type == proxy.BACKEND_TYPE_RW and
                       s.state ~= proxy.BACKEND_STATE_DOWN and
                       rw_ndx == 0 then
                        rw_ndx = i
                end
        end

        if proxy.connection.backend_ndx == 0 then
                if is_debug then
                        print("  [" .. rw_ndx .. "] taking master as default")
                end
                proxy.connection.backend_ndx = rw_ndx
        end

        -- pick a random backend
        --
        -- we someone have to skip DOWN backends

        -- ok, did we got a backend ?

        if proxy.connection.server then
                if is_debug then
                        print("  using pooled connection from: " .. proxy.connection.backend_ndx)
                end

                -- stay with it
                return proxy.PROXY_IGNORE_RESULT
        end
---8<---

After the "return proxy.PROXY_IGNORE_RESULT", the mysql proxy on the master
node gets a MYSQLD_PACKET_EOF which triggers the g_error() at line 468 in function
network_mysqld_proto_get_query_result() in file src/network-mysqld-packet.c:

 441 int network_mysqld_proto_get_query_result(network_packet *packet, network_mysqld_con *con) {
 442         guint8 status;
 443         int is_finished = 0;
 444         int err = 0;
 445         network_mysqld_eof_packet_t *eof_packet;
 446 
 447         err = err || network_mysqld_proto_skip_network_header(packet);
 448         if (err) return -1;
 449 
 450         /* forward the response to the client */
 451         switch (con->parse.command) {
 452         case COM_CHANGE_USER:
 453                 /**
 454                  * - OK
 455                  * - ERR (in 5.1.12+ + a duplicate ERR)
 456                  */
 457                 err = err || network_mysqld_proto_get_int8(packet, &status);
 458                 if (err) return -1;
 459 
 460                 switch (status) {
 461                 case MYSQLD_PACKET_ERR:
 462                         is_finished = 1;
 463                         break;
 464                 case MYSQLD_PACKET_OK:
 465                         is_finished = 1;
 466                         break;
 467                 default:
 468                         g_error("%s.%d: COM_(0x%02x) should be (ERR|OK), got %02x",
 469                                         __FILE__, __LINE__,
 470                                         con->parse.command, status);
 471                         break;
 472                 }
 473                 break;
 474         case COM_INIT_DB:


It seems that this is related to the statement at
 http://forge.mysql.org/wiki/MySQL_Proxy_RW_Splitting

"As the authentication protocol never transmits the real password nor anything we
can use to authenticate against another server, we need a internal storage of
credentials to open connections to the backend-servers when we are short of
connections in the pool."

best regards,
0--gg-

>From the keyboard of Diego Medina [20.07.09,16:06]:

> Hi Georg,
> 
> Are you using a mysql proxy "in between" the replication between your
> master and slave? Or are you using a proxy in between your web
> application and the database?
> 
> In either case, could you identify what query is causing the error?
> 
> Thanks.
> 
> Diego
> 
> On Sat, Jul 18, 2009 at 5:44 PM, Georg Moritz<georg.moritz@xxxxxxxxxxxx> wrote:
> >
> > IMHO MySQL-Proxy shold handle an EOF gracefully. If I omit the rw-splitting.lua
> > script, all queries are sent to the MySL Master Proxy - that works like a charm;
> > but I need read/write splitting...
> >
> > Any clues?
> >
> > 0--gg-

-- 
_($_=" "x(1<<5)."?\n".q·/)Oo.  G°\        /
                              /\_¯/(q    /
----------------------------  \__(m.====·.(_("always off the crowd"))."·
");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}