Bug #36962 mysql-proxy ERROR 1105 (07000): can't change DB to on slave rw-splitting.lua
Submitted: 26 May 2008 12:58 Modified: 3 Jul 2008 12:03
Reporter: jabincn jabincn Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Proxy Severity:S3 (Non-critical)
Version:0.6 0.7 OS:Linux
Assigned to: Assigned Account CPU Architecture:Any
Tags: Contribution

[26 May 2008 12:58] jabincn jabincn 
Description:
[root@localhost mysql-proxy]# lua -v
Lua 5.1.3 Copyright (C) 1994-2008 Lua.org, PUC-Rio
[root@localhost mysql-proxy]# mysql-proxy -V
mysql-proxy 0.7.0
glib2: 2.12.3
libevent: 1.4.1-beta

mysql> select version();
+---------------+
| version() |
+---------------+
| 5.1.24-rc-log |
+---------------+
1 row in set (0.00 sec)

[root@localhost mysql-proxy]#
LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua" \
/usr/local/mysql-proxy/sbin/mysql-proxy \
--admin-address=127.0.0.1:3305 \
--proxy-address=0.0.0.0:3306 \
--proxy-backend-addresses=192.168.0.8:3307 \
--proxy-read-only-backend-addresses=192.168.0.9:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua \
--pid-file=/tmp/mysql-proxy.pid \
--log-level=debug \
--log-use-syslog \
--log-file=/tmp/mysql-proxy.log \
--plugins=admin \
--plugins=proxy \
--plugin-dir=/usr/local/mysql-proxy/lib/mysql-proxy/ \
--daemon

mysql -h xxx.xxx.xxx.xxx -u root -p
......
mysql> show databases;
ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
mysql> select count(*) from cjhjd.t_plan;
ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
mysql> use cjhjd;
Database changed
mysql> select count(*) from cjhjd.t_plan;
+----------+
| count(*) |
+----------+
| 1440 |
+----------+
1 row in set (1.10 sec)

why? please help,thank you!

I see the link below on the wrong, but did not find a solution.

http://forums.mysql.com/read.php?146,174740,176066#msg-176066

http://jan.kneschke.de/projects/mysql/mysql-proxy-more-r-w-splitting

How to repeat:
[root@localhost mysql-proxy]# lua -v
Lua 5.1.3 Copyright (C) 1994-2008 Lua.org, PUC-Rio
[root@localhost mysql-proxy]# mysql-proxy -V
mysql-proxy 0.7.0
glib2: 2.12.3
libevent: 1.4.1-beta

mysql> select version();
+---------------+
| version() |
+---------------+
| 5.1.24-rc-log |
+---------------+
1 row in set (0.00 sec)

[root@localhost mysql-proxy]#
LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua" \
/usr/local/mysql-proxy/sbin/mysql-proxy \
--admin-address=127.0.0.1:3305 \
--proxy-address=0.0.0.0:3306 \
--proxy-backend-addresses=192.168.0.8:3307 \
--proxy-read-only-backend-addresses=192.168.0.9:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua \
--pid-file=/tmp/mysql-proxy.pid \
--log-level=debug \
--log-use-syslog \
--log-file=/tmp/mysql-proxy.log \
--plugins=admin \
--plugins=proxy \
--plugin-dir=/usr/local/mysql-proxy/lib/mysql-proxy/ \
--daemon

mysql -h xxx.xxx.xxx.xxx -u root -p
......
mysql> show databases;
ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
mysql> select count(*) from cjhjd.t_plan;
ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
mysql> use cjhjd;
Database changed
mysql> select count(*) from cjhjd.t_plan;
+----------+
| count(*) |
+----------+
| 1440 |
+----------+
1 row in set (1.10 sec)

why? please help,thank you!

I see the link below on the wrong, but did not find a solution.

http://forums.mysql.com/read.php?146,174740,176066#msg-176066

http://jan.kneschke.de/projects/mysql/mysql-proxy-more-r-w-splitting
[2 Jun 2008 11:12] Sveta Smirnova 
Thank you for the report.

Please provide output of show variables like 'port';
[3 Jun 2008 0:31] jabincn jabincn 
thank you for you replay.
this is debug log:
    server default db: cjhjd
    client default db:
    syncronizing
    server default db: cjhjd
    client default db:
    syncronizing
    server default db: cjhjd
    client default db:
    syncronizing
    server default db:
    client default db: cjhjd
    syncronizing
[3 Jun 2008 0:37] jabincn jabincn 
mysql-proxy run:
LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua" \
/usr/local/mysql-proxy/sbin/mysql-proxy \
--proxy-backend-addresses=192.168.0.8:3306  \
--proxy-read-only-backend-addresses=192.168.0.9:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua \
--pid-file=/tmp/mysql-proxy.pid \
--log-level=debug \
--log-use-syslog \
--log-file=/tmp/mysql-proxy.log \
--plugins=admin \
--plugins=proxy \
--plugin-dir=/usr/local/mysql-proxy/lib/mysql-proxy/ \
--daemon 

[root@localhost ~]# mysql -h 192.168.0.8 -P 4040 -u devdbuser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.1.24-rc-log Source distribution

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

mysql> show variables like 'port';
ERROR 1105 (07000): can't change DB  to on slave 192.168.0.8:3306
mysql> use mysql;
Database changed
mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  | 
+---------------+-------+
1 row in set (0.00 sec)

mysql>
[18 Jun 2008 8:43] Christian Meisinger 
Adding 'c.default_db ~= ""' for default DB check seems to solve the problem:

  -- if client and server db don't match, adjust the server-side
  --
  -- skip it if we send a INIT_DB anyway
  if cmd.type ~= proxy.COM_INIT_DB and c.default_db and c.default_db ~= "" and c.default_db ~= s.default_db then
    print("    server default db: '" .. s.default_db .. "'")
    print("    client default db: '" .. c.default_db .. "'")
    print("    syncronizing")
    proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db)
  end
[24 Jun 2008 8:14] jabincn jabincn 
it's ok.
thank you very much!
[3 Jul 2008 12:03] Sveta Smirnova 
Thank you for the feedback.

Verified as described with version 0.6.1.

To repeat:

1. Start Proxy as ./src/mysql-proxy --proxy-backend-addresses=127.0.0.1:3351 --proxy-read-only-backend-addresses=127.0.0.1:3306 --proxy-lua-script=./lib/rw-splitting.lua 
2. Connect to MySQL server via Proxy.
3. Issue USE db_name command.
4. Disconnect
5. Connect again
6. Issue SHOW DATABASES query.

From my terminal:

apple@apple ~/bzr/mysql-6.0
$mysql -h127.0.0.1 -P4040
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2305
Server version: 5.1.28-debug Source distribution

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

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> \q
Bye

apple@apple ~/bzr/mysql-6.0
$mysql -h127.0.0.1 -P4040
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2306
Server version: 5.1.28-debug

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

mysql> show databases;
ERROR 1105 (07000): can't change DB  to on slave 127.0.0.1:3351
mysql>
[11 Dec 2008 13:36] Sveta Smirnova 
Bug #41392 was marked as duplicate of this one.
[9 Jan 2009 19:14] boyko yordanov 
Seems that proxy selects a connection from the pool which is authed for another username.. check backend logs for "access denied" entries.. 

Proxy tries to sync the client/server databases but the selected connection username has no rights on the database it has to 'USE'.