MySQL Bugs: #35013: mysql-proxy and rw-splitting.lua don't work with many users

Bug #35013	mysql-proxy and rw-splitting.lua don't work with many users
Submitted:	3 Mar 2008 21:22	Modified:	10 Jan 2009 15:53
Reporter:	Gonono Gonono	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Proxy	Severity:	S1 (Critical)
Version:	0.6.1	OS:	Linux (centos5 )
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	change user, mysql-proxy, rw-splitting

Description:
i use mysql-proxy 0.6.1 and the rw-splitting.lua script.

the mysql-proxy don't change the user on a next request.

How to repeat:
I set all privileges on database1 to user1 and all privileges on database2 to user2

i am connect to the proxy with user1/database1 and it works,
but when i want to connect with user2/database2 the proxy try to use user1 and say "can't change DB". the log say : access denied for user1 on database2.

(if i set all privileges on database2 to user1 the proxy works but it's a security risk to have all users with all privileges on all database on a frontend application...)

Thank you for the report.

How do you start mysql-proxy? Please provide full command line you use.

here is the start command : 
`LUA_PATH="/mysql-proxy-0.6.1/lib/?.lua" /mysql-proxy-0.6.1/src/mysql-proxy \
--proxy-backend-addresses=master:3306 \
--proxy-read-only-backend-addresses=slave1:3306 \
--proxy-read-only-backend-addresses=slave2 \
--proxy-lua-script=/mysql-proxy-0.6.1/lib/rw-splitting.lua \
--proxy-address=192.168.xx:4040 \
--admin-address=192.168.xx:4041 \
--pid-file=/tmp/mysql-proxy.pid --daemon

Thank you for the feedback.

I can not repeat described behavior:

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql50 -e "create database db1;"

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql50 -e "create database db2;"

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql50 -e " grant all on db1.* to u1@'127.0.0.1';"

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql50 -e " grant all on db2.* to u2@'127.0.0.1';"

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql51 -e "create database db1;"
ERROR 1007 (HY000) at line 1: Can't create database 'db1'; database exists

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql51 -e "create database db2;"
ERROR 1007 (HY000) at line 1: Can't create database 'db2'; database exists

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql51 -e " grant all on db1.* to u1@'127.0.0.1';"

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql51 -e " grant all on db2.* to u2@'127.0.0.1';"

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$ ./src/mysql-proxy --proxy-lua-script=/Users/apple/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1/lib/rw-splitting.lua -proxy-backend-addresses=127.0.0.1:3306 --proxy-read-only-backend-addresses=127.0.0.1:3350 --proxy-read-only-backend-addresses=127.0.0.1:3351 --daemon
Неизвестный параметр -proxy-backend-addresses=127.0.0.1:3306

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$ ./src/mysql-proxy --proxy-lua-script=/Users/apple/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1/lib/rw-splitting.lua --proxy-backend-addresses=127.0.0.1:3306 --proxy-read-only-backend-addresses=127.0.0.1:3350 --proxy-read-only-backend-addresses=127.0.0.1:3351 --daemon

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql -h127.0.0.1 -u1 db1
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'db1'

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql -h127.0.0.1 -uu1 db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5322
Server version: 5.0.51a-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user(), current_user();
+--------------+----------------+
| user()       | current_user() |
+--------------+----------------+
| u1@localhost | u1@127.0.0.1   | 
+--------------+----------------+
1 row in set (0.11 sec)

mysql> \q
Bye

apple@apple ~/Documents/src/repos/mysql-proxy/tags/mysql-proxy-0.6.1
$mysql -h127.0.0.1 -uu2 db2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5323
Server version: 5.0.51a-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user(), current_user();
+--------------+----------------+
| user()       | current_user() |
+--------------+----------------+
| u2@localhost | u2@127.0.0.1   | 
+--------------+----------------+
1 row in set (0.00 sec)

Please provide exact steps needed to repeat the problem.

Hello

Exactly the same problem. (Gentoo [2.6.23-r8] + mysql-5.0.54 + mysql-proxy 0.6.1 + lua-5.1.2-r2)

mysql-proxy \
--proxy-backend-addresses=xx.xx.49.171:3306 \
--proxy-read-only-backend-addresses=:3307 \
--proxy-read-only-backend-addresses=xx.xx.49.190:3307 \
--proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua \
--proxy-fix-bug-25371

In my case it happens only in php applications. I could not replicate the problem on console.

i also use mysql with php .... and i think that the bug is on the mysql-proxy because i have no problem on no-mysql-proxy
Mysql-team, have you an idea ?

i also use mysql with php .... and i think that the bug is on the mysql-proxy because i have no problem on no-mysql-proxy
Mysql-team, have you an idea ?

Thank you for the feedback.

Please indicate MySQL library version PHP linked with.

To check client library you should run function phpinfo() and find "Client API version" under section "mysql" if you use mysql, or "Client API library version" and "Client API header version" if you use mysqli.

the version is 5.0.24a (mysqli and mysql)
:-)

Thank you for the feedback.

I still can not repeat the problem on my side.

Could you please provide version of MySQL server you use and if it is not latest upgrade and check. Also please upgrade client libraries PHP linked with.

mysql:
Client API version 	5.0.54
MYSQL_MODULE_TYPE 	external
MYSQL_SOCKET 	/var/run/mysqld/mysqld.sock
MYSQL_INCLUDE 	-I/usr/include/mysql
MYSQL_LIBS 	-L/usr/lib64 -lmysqlclient

mysqli:

Client API library version 	5.0.54
Client API header version 	5.0.54

MySQL proxy version 0.7.0 from actual SVN.

here the log of mysql (not mysql-proxy) :

first test : a php application try to access to database with the user1 who have only all privileges to database1 -->OK

second test : a php application try to access to database2 with the user2 who have only all privileges to database2 -->KO because mysql-proxy use the user1 connection (see the log)

the log :
19097 Init DB     Access denied for user 'user1'@'%' to database 'database2'

(perhapps mysql-proxy don't create a new connection for each request but try to use an existing thread)

if i use a user who have all privileges on all databases it works successfully...

Hello,

I'm having the same problem, is there any progress on this issue?

Thanks.
JS

no there is no no issue at the actual time, the only way is to use a super user as root or a an equal user. But this solution is not good in production.

i suppose that the solution in on the lua script. this script will be modified to force the connection with the user who request, but this language (lau) is unknow for me and i did''nt find any web site for a new lua rw-splitting script....

the mysql-proxy with the rw-splitting re-lay to all my performances bug, so i wait and see!! 

Big up to the man who resolve this ! (excuse my bad english)

Whenever the client user is not the same as the user used in the already established connection between mysql proxy and mysql, it tries to switch users.
It does it by invoking the COM_CHANGE_USER command defined in the commands.lua file. The problem is that in this case it enters an else condition that doesn't do anything and prints out the message "[debug] (command) unhandled type COM_CHANGE_USER".

so it would be say : case not implemented
what say mysql team about this command commit change user

Which version of Lua do you use? (Output of `lua -v`)

version 5.1.2

Could you also please provide PHP script which demomstrates problem. I use following script and still not able to repeat the issue:

$php -r 'mysql_connect("127.0.0.1:4040", "u1", ""); mysql_query("show tables from db1"); var_dump(mysql_error());'
string(0) ""

$php -r 'mysql_connect("127.0.0.1:4040", "u2", ""); mysql_query("show tables from db1"); var_dump(mysql_error());'
string(57) "Access denied for user 'u2'@'127.0.0.1' to database 'db1'"

I cannot reproduce this with a php script.

But my setup is pretty simple.
I have 2 mysql boxes, 1 master 1 slave. The master has mysqlproxy running and delivering writes to itself and reads to the slave.
I have a 3rd box with Glassfish application server installed (Server 9.1_01). I have a connection pool configured:
 - datasource classname: com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
 - Resource Type: javax.sql.ConnectionPoolDatasource
 - Properties: serverName, databaseName, user, password, portNumber

In the connection pool definition screen there's a ping button to test the connection. After a few clicks on the ping button I get the error "Access denied for user '<user>'@'<host>' (using password: YES)".

hello Sveta Smirnova 

your test is not good
the first script you try to connect with u1 to db1 : ok
the second script you try to connect with u2 to db1 : not good. you should be connect with u2 to db2 and see if mysql-proxy use really u2 because the bug is that mysql proxy use u1 in the place of u2. The bug doesn't appear in the first time so you need to check the script a lot of time.

I work to create a test script that reproduct this!

on the mysql-master i create u1 with all privileges to db1 and u2 with all privileges to db2

mysql> create database db1;
Query OK, 1 row affected (0,02 sec)

mysql> grant all privileges on db1.* to 'u1'@'%' identified by 'pwd';
Query OK, 0 rows affected (0,06 sec)

mysql> create database db2;
Query OK, 1 row affected (0,01 sec)

mysql> grant all privileges on db2.* to 'u2'@'%' identified by 'pwd';
Query OK, 0 rows affected (0,00 sec)

on the mysql i verify that the replication have create user and database :

mysql> show databases like 'db%';
+----------------+
| Database (db%) |
+----------------+
| db1            |
| db2            |
+----------------+
2 rows in set (0,00 sec)

its good also for user creation !

-----------------------------------------
first script 

[root@opn-mrtg root]# php -r 'mysql_connect("192.168.46.44:3306", "u1", "pwd"); mysql_query("show tables from db1");
var_dump(mysql_error());'
string(0) ""

-->its good

second script 

[root@opn-mrtg root]# php -r 'mysql_connect("192.168.46.44:3306", "u2", "pwd"); mysql_query("show tables from db2");
var_dump(mysql_error());'
string(49) "Access denied for user 'u1'@'%' to database 'db2'"

-->not good

see, the mysql-proxy want to use u1 ??? 

but i can not reproduct this a second time or when i want......

just for information : the 192.168.46.44:3306 is the mysql-proxy (no a mysql server, i just start the proxy with the same port as mysql server) mysql-master and mysql-slave run on other server.

Hi.

I also have the same problem in FreeBSD 7.0 using mysql-proxy 0.6.1

Client API version	5.0.51a
PHP Version 5.2.5

It is exactly the same problem as described before.
If i run as a super user or root it works flawless but when starting to use individual username with access to certain databases i also get the error. can't change db on slave.

It seems like the rw-splitting.lua need some work so that new users don't use other users existing connections.

Is there any case of this yet or you still think it's user related errors?

Hello
I tested mysql-proxy with rw-splitting.lua on several LAMP systems and every time the same results. On higher load it tries to use "random" user connections to access the database you requested with your correct username/password.

perhapps it is a variable of mysql who has to be much set...

I run CentOS 5.1 with MySQL 5.0.51a and it seems that is you use php PDO driver to connect to DB via MySQL-Proxy it get's this error..

I can confirm this bug.
Using MySQL-Proxy 0.6.1 on several Debian Etch servers

Thank you for the feedback.

Bug is still not repeatable in my environment. Please provide debug output from MySQL Proxy in time when error occurs and general log file: I want to see queries which were issued before error.

I just did a reinstall on a virtual server on freebsd 7.

I just have to set up 2 php files that does a simple select * from table; pointing to 2 diffrent db's with diffent users then run a benchmark agains the php files at the same time.

once the proxy is "online" with user1 i't cant switch to user2. Need load on the sql to reproduce the error.
Need to fill up the first session with querries so that the proxy and sql are working then try to stick another user to the proxy then it tries to use a already open session with user1 to acces db2 and *paf* you got the error :)

Sorry for my bad explination and english but i'm tired and i think you catch my drift :-)

Christian,

thank you for the feedback. Verified as described.

To repeat start Proxy as described in comment "[5 Mar 10:59] Gonono Gonono" and create users as described in comment "[5 May 10:46] Gonono Gonono", then create 2 scripts.

$cat u1.sh:
#!/bin/bash

while (true); do php -r 'mysql_connect("127.0.0.1:4040", "u1", "pwd");mysql_select_db("db1");for($i=1; $i<=65536; $i++) {mysql_query("select id from t1 where id=$i");var_dump(mysql_error());}'; done

$cat u2.sh 
#!/bin/bash

while (true); do php -r 'mysql_connect("127.0.0.1:4040", "u2", "pwd");mysql_query("show tables from db2");var_dump(mysql_error());'; done

Modify host, port and queries to valid for your environment. (I created table for test:  create table t1(id int not null auto_increment primary key); and filled it with 65536 records).

Run u1.sh, be sure no error occured, then run u2.sh in parallel, wait error.

Another way to simulate 'the bug' is to install a phpbb instance for example. It creates several idle connections. Once you try to use different db with other user and make a simple select, you will see it.

If anyone succeed in writing a possible workaround, please submit it here

i post just to put this bug on up because i didn't find any ways to resolv this problem.....
all suggests are welcome ....

I've worked on this bug with Boyan Dimitrov, trying to code around it with Lua but without success. This is not to say it can't be done, but we were not successful in finding a scriptable solution.

I would consider this a high priority as it limits the usage of Proxy to one set of credentials. We have stopped moving forward with production use until this has been addressed.

We love ya, Jan -- please address this issue soon! :)

Hi all,

we had the same problem switching users on different databases.
We are working with "mysql-proxy 0.6.1" and Debian Linux. 

We really, really like to use the Proxy, so we thought about a work-around for this problem until the problem is solved by mysql-proxy-Team.

We started multiple mysql-proxy-daemons with (of course) different Ports.
Every Daemon-Process is dedicated to only one database-user.

So there is no need any more to switch a user within one mysql-proxy-process.
I know, its not the golden solution, but it works without any problems.

Hope i could give you a help out there.

Cheers, Frank

Frank Weiser
www.it-consult-wiesbaden.de
weiser@it-consult-wiesbaden.de

Hi, same problem here. I tried the following without any luck:

        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

However Jan mentions the usage of COM_CHANGE_USER here: http://jan.kneschke.de/2008/1/25/mysql-proxy-reusing-connections

I was not able though to find the keepalive-tutorial version he refers to.

Still i'd love to see this issue fixed.

Please try SVN version located at http://svn.mysql.com/svnpublic/mysql-proxy

There is duplicate bug #41392

Thanks for the help, sveta.

I tried the svn version without success. It took me some time to get the included rw-splitting.lua running as of bug #38419 getting in my way. Having that fixed, i still get the old:

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

regard, lars

This here is the commit-log from revision 221. Maybe it gives one of you a hint on how to proceed with this issue:

=====BEGIN=====
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
=====END=====

Lars,

I meant in SVN version you can find tutorial-keepalive.lua (it is in trunk/examples). Bug is not fixed yet.

Ah, ok. I'll have a look at that one. In the meantime i figured out, that the bug is half-fixed in the svn-version though. As before i run 10 (or more) selects with user1 and afterwards only the FIRST connections attempt with user2 fails. All further attempts succeed and the behaviour is all well. Any ideas on that? I'll post the output of my rw-splitting.lua which is the svn-version with additional debug output around the call of lb.idle_ro():

==== Last working select for user1 ====
  [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
  using pooled connection from: 1
[read_query] 
  current backend   = 0
  client default db = db1
  client username   = u1
  query             = select @@version_comment limit 1
  picking backend
  picked: 2
  sending to backend : 127.0.0.1:3306
    is_slave         : true
    server default db: db1
    server username  : u1
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 
  current backend   = 0
  client default db = db1
  client username   = u1
  query             = select * from t
  picking backend
  picked: 2
  sending to backend : 127.0.0.1:3306
    is_slave         : true
    server default db: db1
    server username  : u1
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 
  current backend   = 0
  client default db = db1
  client username   = u1
  (QUIT) current backend   = 0

==== First (failing) request for user2 ====

  [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
  using pooled connection from: 1
[read_query] 
  current backend   = 0
  client default db = db2
  client username   = u2
  query             = select @@version_comment limit 1
  picking backend
  picked: 2
    server default db: db1
    client default db: db2
    syncronizing
  sending to backend : 127.0.0.1:3306
    is_slave         : true
    server default db: db1
    server username  : u1
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 
  current backend   = 2
  client default db = db2
  client username   = u2
  query             = select * from t
  picking backend
  picked: 0
    server default db: db1
    client default db: db2
    syncronizing
  sending to backend : 127.0.0.1:3306
    is_slave         : true
    server default db: db1
    server username  : u1
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 
  current backend   = 2
  client default db = db2
  client username   = u2
  (QUIT) current backend   = 2

==== Second (working) request for user2 ====

  [1].connected_clients = 0
  [1].pool.cur_idle     = 0
  [1].pool.max_idle     = 8
  [1].pool.min_idle     = 4
  [1].type = 1
  [1].state = 1
  [1] idle-conns below min-idle
[read_query] 
  current backend   = 0
  client default db = db2
  client username   = u2
  query             = select @@version_comment limit 1
  picking backend
  picked: 0
  sending to backend : 127.0.0.1:3306
    is_slave         : false
    server default db: db2
    server username  : u2
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 
  current backend   = 0
  client default db = db2
  client username   = u2
  query             = select * from t
  picking backend
  picked: 0
  sending to backend : 127.0.0.1:3306
    is_slave         : false
    server default db: db2
    server username  : u2
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 
  current backend   = 0
  client default db = db2
  client username   = u2
  (QUIT) current backend   = 0

Sorry for the longish post.

Hello,

Any news on this bug? I've been trying to fix it many ways, most of them mentioned here - nothing worked.

i think that nobody have the solution at this time
i wait and see (11 months)

The reason is that mysql-proxy assumes that you have at least 5 active client connections. After the sixth it begins to split connections correctly

Dear leonardo,

it is not the problem, that load balancing only kicks in after the first 5 connections have been established, but that mysql-proxy doesn't account for change-user commands when reusing connections from its pool. Thus, if you have more different users than connections in your pool, mysql-proxy will reuse a connection with a wrong users thus failing on permission.

regards, lars

Any updates on this one ?

We'd like to setup mysqlproxy with rw-splitting soon :)

Thanks.

I also can confirm this bug. I'm using mysql-proxy 0.8.0. (today is 2010-03-16) I reworked the rw script and everything looked good until I started getting permission denied errors.

Mysql-proxy is mixing users and databases, sending the wrong user to a database.  The username for db1 is being used to connect to db2. Is this being worked on please?

Dear developers,
  Is there any update on this issue? As of writing, I am having problem with mysql-proxy 0.8.0 on redhat 5.0. A packet dumps show clearly that mysql-proxy did not change the user while access the second database. Is there any way to force change user?

Has anyone found a solution to this problem? I was thinking to use Mysql Proxy but we have many users, we can't let them connect using the same user.

In case there's no solution yet, does anyone have an alternative method for RW Splitting and load balancing?