MySQL Bugs: #41392: mysql-proxy is unable to change users within already established connections

Bug #41392	mysql-proxy is unable to change users within already established connections
Submitted:	11 Dec 2008 12:22	Modified:	11 Dec 2008 14:52
Reporter:	Lars Volker	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Proxy	Severity:	S3 (Non-critical)
Version:	0.6.1-2.56	OS:	Linux (centos)
Assigned to:		CPU Architecture:	Any

Description:
mysql-proxy is used with rw-splitting.lua script. 

When a client requests to use a database on connect, which all previously connected users are unable to access, the following error is thrown to the client:

ERROR 1105 (07000) at line 1: can't change DB db2 to on slave 127.0.0.1:3306

"db2" is the database the client requested.

How to repeat:
* Create two databases, db1 and db2. Each has a table t with two entries:
mysql> select * from db1.t;
+----+--------+
| id | name   |
+----+--------+
|  1 | table1 | 
|  2 | table1 | 
+----+--------+
* db2 has "table2" in this table.

* Start mysql-proxy with one master, one slave.
* Connect so often as user1, that all connection-slots are filled. I used the following snippet to open connections:

[root@vps102 ~]# echo "select name from t where id='2'" | mysql --host=127.0.0.1 --port=4040 --user=user1 --password=pw1 db1

* After 11 times, run the same statement for user2:

[root@vps102 ~]# echo "select name from t where id='2'" | mysql --host=127.0.0.1 --port=4040 --user=user2 --password=pw2 db2
ERROR 1105 (07000) at line 1: can't change DB db2 to on slave 127.0.0.1:3306

The database does not get changed here as user1 was used at login time and is not allowed to access the db of user2.

Suggested fix:
mysql-proxy shall detect s.user ~= c.user and act upon it by resetting the username or reestablishing a connection.

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #36962

I'm not convinced, that this is a duplicate of #36962. I had read #36962 before posting already.

In #36962 the described behaviour relates to different databases used by the clients. The fix described there is already included in my version rw-splitting.lua. 

In the meantime i tried the following which did not work:

if c.username and c.username ~= s.username then
                print("    server username: " .. s.username)
                print("    client username: " .. c.username)
                print("    syncronizing")
                proxy.queries:prepend(3, string.char(proxy.COM_CHANGE_USER) .. c.username)
end

I think however, this is a duplicate of #35013.

Thank you for the feedback.

Closed as duplicate of bug #35013. If you find this is new case feel free to reopen the report.