mysql-proxy-discuss team mailing list archive

[Diego Medina <diego@xxxxxxxxxxxxx>]

Hi Georg,

Thanks for al this information, could you open a bug report at
http://bugs.mysql.com/report.php
with the details you found?

Thanks

Diego


On Fri, Jul 24, 2009 at 8:13 AM, Georg Moritz<georg.moritz@xxxxxxxxxxxx> wrote:
> 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}
>



-- 
Diego Medina
Web Developer
http://www.fmpwizard.com