| Bug #31574 | msql-proxy drops connections while using rw-splitting.lua | ||
|---|---|---|---|
| Submitted: | 13 Oct 2007 1:26 | Modified: | 9 May 2008 0:14 |
| Reporter: | Daniel Wharton | ||
| Status: | Verified | ||
| Category: | Proxy: Core | Severity: | S1 (Critical) |
| Version: | 0.6.0 and 0.6.1 | OS: | Any |
| Assigned to: | Jan Kneschke | Target Version: | |
| Tags: | mysql-proxy, rw-splitting.lua, rw-splitting | ||
| Triage: | D2 (Serious) | ||
[13 Oct 2007 1:26]
Daniel Wharton
[16 Oct 2007 7:37]
Daniel Wharton
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');
[16 Oct 2007 8:00]
Giuseppe Maxia
Thanks for an excellent bug report. Verified as described on Mac Os X and Linux.
[16 Oct 2007 18:02]
Jan Kneschke
... 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.
[8 May 2008 23:38]
Tiago Cruz
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
[9 May 2008 0:14]
Daniel Wharton
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
[13 Aug 2008 5:56]
David Konsumer
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.
[20 Mar 16:10]
Ed Rib
Same problem :
client default db: xxx
syncronizing
server default db:
client default db: xxx
syncronizing
Do you solve it ?
Thanks.
