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'.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.060 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.