MySQL Bugs: #30867: MySQL Proxy loses connections when R/W splitting with multiple users

Bug #30867	MySQL Proxy loses connections when R/W splitting with multiple users
Submitted:	6 Sep 2007 15:43	Modified:	14 Sep 2007 12:24
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Proxy: Core	Severity:	S1 (Critical)
Version:	0.6.0	OS:	Any
Assigned to:	MC Brown	CPU Architecture:	Any
Tags:	Connection, load balance, mysql-proxy

Description:
When MySQL Proxy is started with the rw-splitting script, it fails if two different users connect to the proxy.

When the second user connects, the Proxy returns an error message 
(read_query) /usr/local/share/mysql-proxy/rw-splitting.lua:261: attempt to index local 's' (a nil value)
network-mysqld-proxy.c.2841: I have no server backend, closing connection
network-mysqld.c.1104: plugin_call(CON_STATE_READ_QUERY) failed
[disconnect_client] 127.0.0.1:22221

As reported in the forums
http://forums.mysql.com/read.php?146,171137,171349#msg-171349

How to repeat:
(1) start the Proxy with at least one read-only backend server

/usr/local/sbin/mysql-proxy \
    --proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua \
    --proxy-backend-addresses=127.0.0.1:11000 \
    --proxy-read-only-backend-addresses=127.0.0.1:11001

(2) create two users, with equal privileges
    GRANT select,insert,delete,update on test.* to user1 
    identified by 'test';
    GRANT select,insert,delete,update on test.* to user2 
    identified by 'test';

(3) connect user1 and ask "SHOW TABLE FROM test";

(4) connect user2 and issue an INSERT statement.
    # connection fails here

Suggested fix:
none

a fix has been committed in [220]

------------------------------------------------------------------------
r220 | jkneschke | 2007-09-06 17:41:10 +0200 (Thu, 06 Sep 2007) | 27 lines

reworked the connection pool to have one pool for user (fixes #....)

before we had one connection pool which stored idling connections for
all users. In disconnect_client() we tried to shrink the pool to
max-idle and could specify for which user we want to close connections

Now this mgmt is move into the core and only the configuration is
external. To reflect this internal change the scripting-API changes too:

  proxy.backend[ndx].
    idling_connections

is replaced by

  proxy.backend[ndx].
    pool.
      min_idle_connections (rw)
      max_idle_connections (rw)
      users[username].
        cur_idle_connections (ro)

Another important change handles the order of getting connections from
the pool. Before we picked one randomly, now we pick the oldest in the
pool to reduce the impact of wait_timeout killing old, idling
connections

A note has been added to the 0.6.0 changelog: 

When using read/write splitting and the rw-splitting.lua example script, connecting a second user to 
the proxy returns an error message.

>>A note has been added to the 0.6.0 changelog

excuse me but why this bug has not be fixed in 0.6.1 release ?

i have the same bug on 0.6.1 !

the proxy use the last user to connect with the next database so the proxy write this error : can't change DB. (because the last user has not the rights on the next databse)

assume user1 has all privileges on database1 and user2 has all privileges on database2.
when i am connect with user1/database1 on the proxy, it works but after if i am connect with the user2/database2 the proxy try to use user1 and says cant't change DB and on the proxy.log its write access denied for user1 on database2 !!!

Arnaud,

I believe you describe a different issue than the one which has been fixed in this bug.

It would be helpful if you could open a new bug report and include detailed steps to reproduce your issue. That way we can ensure it gets attention.

Thanks,
Kay