MySQL Bugs: #31574: msql-proxy drops connections while using rw-splitting.lua

Bug #31574	msql-proxy drops connections while using rw-splitting.lua
Submitted:	12 Oct 2007 23:26	Modified:	9 Jan 2015 16:01
Reporter:	Daniel Wharton	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Proxy: Core	Severity:	S1 (Critical)
Version:	0.6.0 and 0.6.1	OS:	Any
Assigned to:	Jan Kneschke	CPU Architecture:	Any
Tags:	mysql-proxy, rw-splitting, rw-splitting.lua

Description:

Even though this only happens with a lua script enabled, I'm hesitant to say it is actually the lua script behind the issue.

after moderate use, mysql-proxy begins to drop seemingly every other query (not exactly, but close) from the command line and frequently drops them from the app.

I see this in behavior in 0.6.0 binaries, 0.6.1 code checked out today, and 0.6.1 code checked out today with the recommendation from Bug #31423 applied.

command line options:

export LUA_PATH="/usr/local/share/mysql-proxy/?.lua"

/usr/local/sbin/mysql-proxy \
    --proxy-address=10.xx.xx.241:3306 \
    --proxy-backend-addresses=10.xx.xx.215:3306 \
    --proxy-read-only-backend-addresses=10.xx.xx.36:3306 \
    --proxy-lua-script=/etc/mysql-proxy/rw-splitting.lua \
    --pid-file=/var/run/mysql-proxy/mysql-proxy.pid \
    --daemon \
    > /var/log/mysql-proxy.log 2>&1

I plan to do a pretty large multi-tier deploy of mysql-proxy if I don't have to fall back to another solution.  If you are into music and/or social networking, here is an invite to our private beta:

http://www.jango.com/?r=403417197

How to repeat:

do a bunch of rapid simple selects from a rw-splitting.lua implementation of mysql-proxy.  You should notice this happening very quickly (once it actually starts using the read-only pool):

[root@ad01 ~]# mysql -h 10.xx.xx.241 -u xxx -pxxx -e "select * from sessions limit 1" database
ERROR 2013 (HY000): Lost connection to MySQL server during query
[root@ad01 ~]# mysql -h 10.xx.xx.241 -u xxx -pxxx -e "select * from sessions limit 1" database
+----+----------------------------------+--------------------------------------------------------------------------------------------------------+---------------------+
| id | session_id                       | data                                                                                                   | updated_at          |
+----+----------------------------------+--------------------------------------------------------------------------------------------------------+---------------------+
|  1 | fb48d9a82316993a93b928c34d1e1827 | BAh7BzoMdXNlcl9pZGkCYV4iCmZsYXNoSUM6J0FjdGlvbkNvbnRyb2xsZXI6
OkZsYXNoOjpGbGFzaEhhc2h7AAY6CkB1c2VkewA=
 | 2007-08-06 23:52:29 |
+----+----------------------------------+--------------------------------------------------------------------------------------------------------+---------------------+
[root@ad01 ~]# mysql -h 10.xx.xx.241 -u xxx -pxxx -e "select * from sessions limit 1" database
ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
not sure yet.... sorry I don't have more time ATM to figure it out.  I plan to circle back on this soon, but wanted to fire this off to you guys for now.

I've narrowed this bug down to a specific mysql option.  This occurs only when the password for the user you are authenticating with is created with old_passwords=ON on the master server.

You can easily reproduce it by following these steps:

--

on your master and slave DB servers (actually, creating a < mysql-4.1 compatible password on just the master server will do the trick):

mysql> set @@old_passwords=ON;
mysql> GRANT USAGE,SELECT ON *.* TO 'proxytest'@'%' IDENTIFIED BY 'proxytest';

--

launch mysql-proxy with approximately the following options:

export LUA_PATH="/usr/local/share/mysql-proxy/?.lua"

/usr/local/sbin/mysql-proxy \
    --proxy-address=xx.xx.xx.xx:3306 \
    --proxy-backend-addresses=aa.aa.aa.aa:3306 \
    --proxy-read-only-backend-addresses=bb.bb.bb.bb:3306 \
    --proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua \
    --pid-file=/var/run/mysql-proxy/mysql-proxy.pid \
    --daemon \
    > /var/log/mysql-proxy.log 2>&1

--

run a script like so against your mysql-proxy:

#!/bin/bash

#query count
qc=1

while mysql -u proxytest -h xx.xx.xx.xx -pproxytest -e "select * from user limit 1" mysql > /dev/null; 
do
        echo "$qc: PASSED"
        qc=$[$qc+1]
        sleep 1
done

echo "$qc: FAILED"

--

if you have the default rw-splitting.lua, it will fail on the 11th attempt; it works out to something like failure on 2n + 3 (where n=min_idle_connections).

--

quick fix:

execute the following on your master mysql server:

mysql> set @@old_passwords=OFF;
mysql> SET PASSWORD FOR 'proxytest'@'%' = PASSWORD('proxytest');

Thanks for an excellent bug report.
Verified as described on Mac Os X and Linux.

... looks like in the case of connection reuse we mix up the packet-id:

11:36:07.056979 recv(11, "\1\0\0\1", 4, 0) = 4
11:36:07.057027 recv(11, "\376", 1, 0)  = 1
11:36:07.057090 send(8, "\1\0\0\2\376", 5, 0) = 5

We are using COM_CHANGE_USER and havn't taken old-passwords into account.

Daniel,

Your quick-fix solve this problem too:
http://bugs.mysql.com/bug.php?id=30304

But I will need to to this in all users/ passwords that I have?

I'm using SVN version, #369

Thanks

Tiago,

You will have to modify any accounts which will be authenticated by mysql-proxy.  Accounts that mysql-proxy does not utilize do not matter.  If you have legacy applications that require the old-style password, you can always create a user just for mysql-proxy.

daniel

I am still having this problem, after the password fix.

I start mysqll-proxy with this:
mysql-proxy --proxy-address=127.0.0.1:3306 --admin-address=127.0.0.1:4401 --proxy-backend-addresses=10.x.x.166:3306 --pid-file=/var/run/mysql-proxy.pid --daemon --proxy-read-only-backend-addresses=10.x.x.143:3306 --proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua

It works fine if I leave out the "proxy-lua-script" but then it just proxies for the master (10.x.x.166)

I get timeouts, and eventually the system gets really slow.

in the term where I started it, I see this:
    client default db: xxx
    syncronizing
    server default db: 
    client default db: xxx
    syncronizing
    server default db: 
    client default db: xxx
    syncronizing

where xxx is the name of the database.

Same problem :

client default db: xxx
    syncronizing
    server default db: 
    client default db: xxx
    syncronizing

Do you solve it ?
Thanks.

Same problem mysql-proxy-0.7.2

I cannot turn off old_passwords.

I need to scale with mysql-proxy...

Any fix?

Bug #56789 was marked as duplicate of this one.