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<[email protected]> 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<[email protected]> >> 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 _______________________________________________ Mailing list: https://launchpad.net/~mysql-proxy-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~mysql-proxy-discuss More help : https://help.launchpad.net/ListHelp
