MYSQl Proxy 多连接的错误can't change DB to on slave

jabincn

继续查找解决方法......

jabincn

找到这个文章里写的内容：

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.

jinguanding

这么说就是MySQL-Proxy缺乏根据SQL语句或者是连接默认的DBName改变当前默认的数据库，
要是做到这点。。就不存在BUG了

jabincn

原帖由 jinguanding 于 2008-5-24 13:26 发表
这么说就是MySQL-Proxy缺乏根据SQL语句或者是连接默认的DBName改变当前默认的数据库，
要是做到这点。。就不存在BUG了

哥们来了，我还没有找到解决问题的方法。

嗯，MySQL-Proxy缺乏根据SQL语句或者是连接默认的DBName改变当前默认的数据库。

jabincn

proxy输出日志中:
  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


发现当client default db: 没有值 的时候就会出现这个问题。

yueliangdao0608

You have to explicitly point the exact database.
I have already introduced in my article.

yueliangdao0608

Since you got the correct answer.
Congratulations.

jabincn

杨兄，知道问题所在，可是还没找到解决方法。

杨兄的文章中我还没有找到解决方法，请指教。

爱佟儿

学习。

jabincn

原帖由 爱佟儿 于 2008-5-24 13:56 发表
学习。

哥们是李同志？