Mysql-proxy 读写分离 多连接时，ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306

[jabincn]

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:3308 \
--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 \
--daemon

测试发现多连接会出现ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306错误。
在mysql-shell里出现这个错误时，use dataname一下即不会报错。但是如果用工具的话，就无法正常使用了。

[root@localhost ]# mysql -h xxx.xxx.xxx.xxx -u xxx -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.1.24-rc-log

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 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)

mysql>

在这里找到关于这个错误的有关解释，可是没有找到有解决方法方法，特请教，谢谢。
http://jan.kneschke.de/projects/ ... -more-r-w-splitting

Improvements
keeping default-db in sync

One of the basic problems with rw-splitting is that each connection has a state, e.g. the default_db. If you switch to another backend you have to make sure that before we issue a SQL query, that also set the new default-db, if they are not in sync.

[read_query]
current backend = 0
client default db = mysql
client username = root
query = select * from user
server default db: repl
client default db: mysql
syncronizing

The client-side did a USE mysql against the master and wanted to SELECT from the a slave afterwards. As the connection the slave was still using repl from the previous query we have apply the DB-change now. For achieve this we insert a USE mysql before sending the SELECT to the slave.

In case the DB want to switch to doesn't exist on the slave, you will get an error like:

ERROR 1000 (00S00): can't change DB 'norepl' to on slave ':3307'

for the SELECT statement.
Stateful SQL Statements

Not all statements in MySQL are stateless and allow easy R/W splitting. Some of them need special support to make sure that still work:

* SELECT SQL_CALC_FOUND_ROWS ... will lead to a SELECT FOUND_ROWS() which has to be executed on the same connection
* INSERTing into table with auto_increment fields might lead to a SELECT LAST_INSERT_ID() which has to be execute on the same master-connection.
* SHOW WARNINGS is similar

The solution is simple: don't give away the connection in that cases and don't send the SELECT FOUND_ROWS() to a slave. For sure there are some more statements which might not harmonize with r/w splitting. For example prepared statements. But that's another story.

[懒得去死]

I can find your footprints anywhere.

[jabincn]

[Quote=引用 4 楼 yueliangdao0608 的回复:]
I can find your footprints anywhere.

[/Quote]

哈哈。当然。这个圈子太小。

[jabincn]

lua脚本的代码部份：

if inj.id ~= 1 then
-- ignore the result of the USE <default_db>
-- the DB might not exist on the backend, what do do ?
--
if inj.id == 2 then
-- the injected INIT_DB failed as the slave doesn't have this DB
-- or doesn't have permissions to read from it
if res.query_status == proxy.MYSQLD_PACKET_ERR then
proxy.queries:reset()

proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = "can't change DB ".. proxy.connection.client.default_db ..
" to on slave " .. proxy.backends[proxy.connection.backend_ndx].address
}

return proxy.PROXY_SEND_RESULT
end
end
return proxy.PROXY_IGNORE_RESULT
end

[chenxin2835]

顶一下

[耗子]

顶一下

[jabincn]

谢谢chenxin2835 and fuowen20

我在ITPUB 和Chinaunix也发了请教贴子，自己也折腾了第二天了。还没有找到解决方法。imysql.cn的叶老大讲可能是lua脚本的bug。

http://www.itpub.net/thread-993433-1-1.html

http://bbs.chinaunix.net/thread-1104131-1-1.html