| Bug #61798 | using mysql-proxy connection pool error | ||
|---|---|---|---|
| Submitted: | 8 Jul 2011 8:42 | Modified: | 17 Apr 2018 23:35 |
| Reporter: | Thomas Zhang | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Proxy | Severity: | S2 (Serious) |
| Version: | 0.8.1, 0.9.0 | OS: | FreeBSD (7.3-STABLE (64bit)) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | proxy database | ||
[16 Jul 2011 13:23]
Valeriy Kravchuk
Please, check if the same problem happens with a newer version of proxy, 0.9.0.
[18 Jul 2011 8:30]
Thomas Zhang
>> Please, check if the same problem happens with a newer version of proxy, 0.9.0. how to download MySQL Proxy 0.9.0 ?
[18 Jul 2011 11:23]
Valeriy Kravchuk
You can get it using bzr: bzr branch lp:mysql-proxy See https://launchpad.net/mysql-proxy/trunk
[27 Jul 2011 8:44]
Thomas Zhang
I installed the MYSQL-PROXY 0.9.0 , But still have the same problem Are there other ways to test the database switching problem ?
[27 Jul 2011 9:05]
Thomas Zhang
mysql-proxy 0.9.0
chassis: mysql-proxy 0.9.0
glib2: 2.26.1
libevent: 1.4.13-stable
LUA: Lua 5.1.4
package.path: /usr/local/mysql-proxy090/lib/mysql-proxy/lua/?.lua
package.cpath: /usr/local/mysql-proxy090/lib/mysql-proxy/lua/?.so
-- modules
admin: 0.9.0
proxy: 0.9.0
-------------------------------------
[read_query] 192.168.9.225:56743
current backend = 0
client default db =
client username = report
sending to backend : 192.168.9.176:3306
is_slave : false
server default db: report_zc
server username : report
in_trans : false
in_calc_found : false
COM_QUERY : false
[read_query] 192.168.9.225:56743
current backend = 0
client default db = report_zc
client username = report
query = SELECT count(*) as count FROM report_einfo WHERE i_etype IN(1,2,3,4,6)
server default db: arab_space_cluster_1
client default db: report_zc
syncronizing
sending to backend : 172.21.15.73:3306
is_slave : true
server default db: arab_space_cluster_1
server username : tm198102
in_trans : false
in_calc_found : false
COM_QUERY : true
---------------------------------------------------------
query = SELECT count(*) as count FROM report_einfo WHERE i_etype IN(1,2,3,4,6)
server default db: arab_space_cluster_1
client default db: report_zc
syncronizing <--- ( switch DB fail ?! )
site info: MySQL Query Error
Script: /index.php
DB Server: Default
SQL: SELECT count(*) as count FROM report_einfo WHERE i_etype IN(1,2,3,4,6)
Error: Table 'arab_space_cluster_1.report_einfo' doesn't exist
Errno.: 1146
[20 Mar 2012 21:50]
Sveta Smirnova
Thank you for the feedback. Please ignore previous comment: I had error in my test setup. I can not repeat described behavior. Do you have all databases on each of 3 servers?
[21 Apr 2012 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[24 Jan 2014 23:41]
Mikko Lampikoski
I ended up with the same problem in the case of a multi-database server. It clearly attempt to do SELECT from wrong database. I have similar configuration as Thomas have. Error: Table 'wrongdatabasename.store' doesn't exist Error No: 1146 SELECT * FROM store mysql-proxy version: 0.8.1, Debian GNU/Linux 7.
[9 Dec 2014 19:10]
Sveta Smirnova
Thank you for the feedback. Do you have all databases on all servers? Simply if you use 1 database on first server, second database on the second one and third on third you cannot use bundled scripts such as rw-splitting.lua, because they are not supposed to handle such complicated setup. You should modify bundled scripts to suite your needs.
[9 Dec 2014 19:32]
Mikko Lampikoski
All databases on one server (master+slave). One mysql-proxy and one mysql-master and one mysql-slave. Just want to split reads to master+slave and writes only to master.
[15 Dec 2014 20:29]
Sveta Smirnova
Thank you for the feedback.
I still cannot repeat described behavior with following test:
1. Start master-slave setup.
2. Create following tables and records on master:
create database test1;
create table test1.t1(f1 int);
create database test2;
create table test2.t2(f1 int);
create database test3;
create table test3.t3(f1 int);
insert into test1.t1 values(1);
insert into test2.t2 values(2);
insert into test3.t3 values(3);
3. Start proxy:
mysql-proxy --proxy-backend-addresses=127.0.0.1:13000 --proxy-read-only-backend-addresses=127.0.0.1:13001 --log-level=debug
4. Run following PHP scripts in three terminal windows:
while true; do php -r '$m=mysqli_connect("127.0.0.1", "root", "", "", 4040); $m->select_db("test1"); sleep(1); $m->query("select * from t1");if (0 != $m->errno) var_dump($m->error, $m->errno);'; done
while true; do php -r '$m=mysqli_connect("127.0.0.1", "root", "", "", 4040); $m->select_db("test3"); sleep(1); $m->query("select * from t3");if (0 != $m->errno) var_dump($m->error, $m->errno);'; done
while true; do php -r '$m=mysqli_connect("127.0.0.1", "root", "", "", 4040); $m->select_db("test2"); sleep(1); $m->query("select * from t2");if (0 != $m->errno) var_dump($m->error, $m->errno);'; done
Please check how these scripts differ from way you connect to MySQL Proxy and select databases.
[16 Jan 2015 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[20 Jan 2015 11:54]
Bruno Morais
Hi, I have the same problem. I have Master + Slave setup with replication. Master has mysql-proxy-0.8.5 installed. my /etc/mysql-proxy.cnf: [mysql-proxy] daemon = true pid-file = /var/run/mysql-proxy.pid log-file = /var/log/mysql-proxy.log log-level = debug max-open-files = 2048 plugins = admin,proxy user = mysql-proxy #local min_idle_connections = 4 #local max_idle_connections = 8 # #Proxy Configuration proxy-address = 192.168.10.30:3306 proxy-backend-addresses = db01.local:3305 proxy-read-only-backend-addresses = db02.local:3305 proxy-lua-script = /usr/lib64/mysql-proxy/lua/proxy/rw-splitting.lua #proxy-skip-profiling = true # # Admin Configuration admin-address = 0.0.0.0:4041 admin-lua-script = /usr/lib64/mysql-proxy/lua/admin.lua admin-username = admin admin-password = xxxxxx I have several databases with different users. When 1 connection is made everything goes ok. When a second connection is made is like is is using previous connection to database as the results are wrong. (Database structure are the same only data differs). If I change database name just to check if I receive error, it works ok. I receive error saying database does not exists. But with database name correct I receive data from another database like a previous connection database. So I believe the behaviour is the same as the other posts. Is there a solution already? Regards, Bruno Morais
Description: I have many sites using MYSQL-PROXY by rw-splitting.lua Each site uses a different database For example: Site A -> Database jy_91_com_global Site B -> Database report_zc Site C -> Database ts_81813 When Site A connect to the database `jy_91_com_global` using MYSQL-PROXY is normal. But at the same time site B to connect the database `report_zc`,`ts_81813` using MYSQL-PROXY has an error like this: site A is OK site B error Script: /index.php SQL: SELECT count(*) as count FROM report_einfo WHERE i_etype IN(1,2,3,4,6) Error: Table 'jy_91_com_global.report_einfo' doesn't exist Errno.: 1146 MYSQL-PROXY debug log: [connect_server] 192.168.9.225:53092 [1].connected_clients = 0 [1].pool.cur_idle = 2 [1].pool.max_idle = 20 [1].pool.min_idle = 1 [1].type = 1 [1].state = 1 [2].connected_clients = 0 [2].pool.cur_idle = 2 [2].pool.max_idle = 20 [2].pool.min_idle = 1 [2].type = 2 [2].state = 1 [1] taking master as default using pooled connection from: 1 [read_query] 192.168.9.225:53092 current backend = 0 client default db = client username = report query = SET NAMES 'utf8' server default db: report_zc client default db: syncronizing sending to backend : 192.168.9.176:3306 is_slave : false server default db: report_zc server username : report in_trans : false in_calc_found : false COM_QUERY : true [read_query] 192.168.9.225:53092 current backend = 0 client default db = client username = report query = SET sql_mode='' sending to backend : 192.168.9.176:3306 is_slave : false server default db: server username : report in_trans : false in_calc_found : false COM_QUERY : true [read_query] 192.168.9.225:53092 current backend = 0 client default db = client username = report sending to backend : 192.168.9.176:3306 is_slave : false server default db: report_zc server username : report in_trans : false in_calc_found : false COM_QUERY : false [read_query] 192.168.9.225:53092 current backend = 0 client default db = report_zc client username = report query = SELECT count(*) as count FROM report_einfo WHERE i_etype IN(1,2,3,4,6) server default db: jy_91_com_global client default db: report_zc syncronizing sending to backend : 172.21.15.73:3306 is_slave : true server default db: jy_91_com_global server username : tc91 in_trans : false in_calc_found : false COM_QUERY : true [read_query] 192.168.9.225:53092 current backend = 0 client default db = report_zc client username = report (QUIT) current backend = 0 [disconnect_client] 192.168.9.225:53092 site C error Script: /index.php SQL: SELECT count(*) as count FROM report_einfo WHERE i_etype IN(1,2,3,4,6) Error: Table 'jy_91_com_global.report_einfo' doesn't exist Errno.: 1146 [connect_server] 192.168.9.225:51062 [1].connected_clients = 0 [1].pool.cur_idle = 2 [1].pool.max_idle = 10 [1].pool.min_idle = 1 [1].type = 1 [1].state = 1 [2].connected_clients = 0 [2].pool.cur_idle = 2 [2].pool.max_idle = 10 [2].pool.min_idle = 1 [2].type = 2 [2].state = 1 [1] taking master as default using pooled connection from: 1 [read_query] 192.168.9.225:51062 current backend = 0 client default db = client username = user_ts_81813 query = SET NAMES 'utf8' sending to backend : 192.168.9.176:3306 is_slave : false server default db: ts_81813 server username : user_ts_81813 in_trans : false in_calc_found : false COM_QUERY : true [read_query] 192.168.9.225:51062 current backend = 0 client default db = client username = user_ts_81813 query = SET sql_mode='' sending to backend : 192.168.9.176:3306 is_slave : false server default db: server username : user_ts_81813 in_trans : false in_calc_found : false COM_QUERY : true [read_query] 192.168.9.225:51062 current backend = 0 client default db = client username = user_ts_81813 sending to backend : 192.168.9.176:3306 is_slave : false server default db: ts_81813 server username : user_ts_81813 in_trans : false in_calc_found : false COM_QUERY : false [read_query] 192.168.9.225:51062 current backend = 0 client default db = ts_81813 client username = user_ts_81813 query = SELECT count(*) as count FROM report_einfo WHERE i_etype IN(1,2,3,4,6) server default db: jy_91_com_global client default db: ts_81813 syncronizing sending to backend : 172.21.15.73:3306 is_slave : true server default db: jy_91_com_global server username : tc91 in_trans : false in_calc_found : false COM_QUERY : true [read_query] 192.168.9.225:51062 current backend = 0 client default db = ts_81813 client username = user_ts_81813 (QUIT) current backend = 0 [disconnect_client] 192.168.9.225:51062 How to repeat: enviorment: FreeBSD 7.3-STABLE PHP 5.2.13 MYSQL 5.1.30-log (old passwords=OFF) /usr/local/src/mysql-proxy-0.8.1-freebsd7-x86-64bit/bin/mysql-proxy -V mysql-proxy 0.8.1 chassis: mysql-proxy 0.8.1 glib2: 2.16.6 libevent: 1.4.13-stable LUA: Lua 5.1.4 package.path: /usr/local/src/mysql-proxy-0.8.1-freebsd7-x86-64bit/lib/mysql-proxy/lua/?.lua; package.cpath: /usr/local/src/mysql-proxy-0.8.1-freebsd7-x86-64bit/lib/mysql-proxy/lua/?.so; -- modules admin: 0.8.1 proxy: 0.8.1 run command: /usr/local/src/mysql-proxy-0.8.1-freebsd7-x86-64bit/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/mysql-proxy.cnf /usr/local/mysql-proxy/mysql-proxy.cnf [mysql-proxy] daemon=true keepalive=true proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua admin-address=192.168.9.225:4041 admin-lua-script=/usr/local/mysql-proxy/lua/admin.lua proxy-backend-addresses=192.168.9.176:3306 proxy-read-only-backend-addresses=192.168.19.30:3306 proxy-read-only-backend-addresses=172.21.15.73:3306 log-file=/var/log/mysql-proxy.log log-level=debug max-open-files=1024 ------------------------------------------------ the rw-splitting.lua I changed two places: 1. -- connection pool if not proxy.global.config.rwsplit then proxy.global.config.rwsplit = { min_idle_connections = 1, max_idle_connections = 10, is_debug = true } end 2. -- 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, { resultset_is_needed = true }) end ------------------------------------------- please help me solve the problem,thanks very much!