Description:
Problem: mysql-proxy failed to use pooled connection to backend
I have a host with mysql-proxy-0.8.3, three hosts with mysql-5.5.30-1.ius.el6 x86_64, a master and two slaves.
I use rw-splitting.lua script with default configs
min_idle_connections = 4,
max_idle_connections = 8,
15 requests to proxy run well, 16th and next ones fail with:
ERROR 1047 (08S01): Unknown command
There is a log for 15th and 16th queries
[connect_server] 172.16.1.4:58591
[1].connected_clients = 0
[1].pool.cur_idle = 5
[1].pool.max_idle = 8
[1].pool.min_idle = 4
[1].type = 1
[1].state = 1
[2].connected_clients = 0
[2].pool.cur_idle = 5
[2].pool.max_idle = 8
[2].pool.min_idle = 4
[2].type = 2
[2].state = 1
[3].connected_clients = 0
[3].pool.cur_idle = 0
[3].pool.max_idle = 8
[3].pool.min_idle = 4
[3].type = 2
[3].state = 1
== End init all backends, proxy.connection.backend_ndx = 3
[3] idle-conns below min-idle
[read_query] 172.16.1.4:58591
current backend = 0
client default db =
client username = vm1
query = select @@version_comment limit 1
sending to backend : 172.16.3.105:3306
is_slave : true
server default db:
server username : vm1
in_trans : false
in_calc_found : false
COM_QUERY : true
[read_query] 172.16.1.4:58591
current backend = 0
client default db =
client username = vm1
query = select id from rozetka.tmp where id = FLOOR(7 + (RAND() * 5))
sending to backend : 172.16.3.105:3306
is_slave : true
server default db:
server username : vm1
in_trans : false
in_calc_found : false
COM_QUERY : true
[read_query] 172.16.1.4:58591
current backend = 0
client default db =
client username = vm1
(QUIT) current backend = 0
[disconnect_client] 172.16.1.4:58591
[connect_server] 172.16.1.4:58592
[1].connected_clients = 0
[1].pool.cur_idle = 5
[1].pool.max_idle = 8
[1].pool.min_idle = 4
[1].type = 1
[1].state = 1
[2].connected_clients = 0
[2].pool.cur_idle = 5
[2].pool.max_idle = 8
[2].pool.min_idle = 4
[2].type = 2
[2].state = 1
[3].connected_clients = 0
[3].pool.cur_idle = 5
[3].pool.max_idle = 8
[3].pool.min_idle = 4
[3].type = 2
[3].state = 1
== End init all backends, proxy.connection.backend_ndx = 0
[1] taking master as default
using pooled connection from: 1
== Returning proxy.PROXY_IGNORE_RESULT = 4
I have added two prints (starts with ==) at line 111 and 132 for additional info.
--
Same operations work good on mysql-server-5.1.67-1.el6_3
How to repeat:
1. Install mysql55-server-5.5.30-1, mysql-proxy-0.8.3
2. Prepare test environment:
172.16.3.103 mysql-proxy host
172.16.3.104 mysql master host
172.16.3.105 mysql slave host
172.16.3.106 mysql slave host
3. Proxy configuration:
/usr/local/mysql-proxy-0.8.1/bin/mysql-proxy --daemon --log-file=/var/log/mysql/proxy.log --log-level=debug --user=nginx --admin-username=root --admin-lua-script=/root/mysql-proxy-0.8.3/lib/admin.lua --admin-password=pass --proxy-lua-script=/root/mysql-proxy-0.8.3/lib/rw-splitting.lua.my --proxy-address=172.16.3.103:3306 --proxy-backend-addresses=172.16.3.104:3306 --proxy-read-only-backend-addresses=172.16.3.105:3306 --proxy-read-only-backend-addresses=172.16.3.106:3306 --pid-file=/var/run/mysql-proxy.pid
4. Run serveral queries like:
mysql -uvm1 -ppass -e "insert into test.tmp set value = RAND();" --port 3306 --host 172.16.3.103
mysql -uvm1 -ppass -e "select value from test.tmp where id = RAND()" --port 3306 --host 172.16.3.103
No matter read or write queries.
Description: Problem: mysql-proxy failed to use pooled connection to backend I have a host with mysql-proxy-0.8.3, three hosts with mysql-5.5.30-1.ius.el6 x86_64, a master and two slaves. I use rw-splitting.lua script with default configs min_idle_connections = 4, max_idle_connections = 8, 15 requests to proxy run well, 16th and next ones fail with: ERROR 1047 (08S01): Unknown command There is a log for 15th and 16th queries [connect_server] 172.16.1.4:58591 [1].connected_clients = 0 [1].pool.cur_idle = 5 [1].pool.max_idle = 8 [1].pool.min_idle = 4 [1].type = 1 [1].state = 1 [2].connected_clients = 0 [2].pool.cur_idle = 5 [2].pool.max_idle = 8 [2].pool.min_idle = 4 [2].type = 2 [2].state = 1 [3].connected_clients = 0 [3].pool.cur_idle = 0 [3].pool.max_idle = 8 [3].pool.min_idle = 4 [3].type = 2 [3].state = 1 == End init all backends, proxy.connection.backend_ndx = 3 [3] idle-conns below min-idle [read_query] 172.16.1.4:58591 current backend = 0 client default db = client username = vm1 query = select @@version_comment limit 1 sending to backend : 172.16.3.105:3306 is_slave : true server default db: server username : vm1 in_trans : false in_calc_found : false COM_QUERY : true [read_query] 172.16.1.4:58591 current backend = 0 client default db = client username = vm1 query = select id from rozetka.tmp where id = FLOOR(7 + (RAND() * 5)) sending to backend : 172.16.3.105:3306 is_slave : true server default db: server username : vm1 in_trans : false in_calc_found : false COM_QUERY : true [read_query] 172.16.1.4:58591 current backend = 0 client default db = client username = vm1 (QUIT) current backend = 0 [disconnect_client] 172.16.1.4:58591 [connect_server] 172.16.1.4:58592 [1].connected_clients = 0 [1].pool.cur_idle = 5 [1].pool.max_idle = 8 [1].pool.min_idle = 4 [1].type = 1 [1].state = 1 [2].connected_clients = 0 [2].pool.cur_idle = 5 [2].pool.max_idle = 8 [2].pool.min_idle = 4 [2].type = 2 [2].state = 1 [3].connected_clients = 0 [3].pool.cur_idle = 5 [3].pool.max_idle = 8 [3].pool.min_idle = 4 [3].type = 2 [3].state = 1 == End init all backends, proxy.connection.backend_ndx = 0 [1] taking master as default using pooled connection from: 1 == Returning proxy.PROXY_IGNORE_RESULT = 4 I have added two prints (starts with ==) at line 111 and 132 for additional info. -- Same operations work good on mysql-server-5.1.67-1.el6_3 How to repeat: 1. Install mysql55-server-5.5.30-1, mysql-proxy-0.8.3 2. Prepare test environment: 172.16.3.103 mysql-proxy host 172.16.3.104 mysql master host 172.16.3.105 mysql slave host 172.16.3.106 mysql slave host 3. Proxy configuration: /usr/local/mysql-proxy-0.8.1/bin/mysql-proxy --daemon --log-file=/var/log/mysql/proxy.log --log-level=debug --user=nginx --admin-username=root --admin-lua-script=/root/mysql-proxy-0.8.3/lib/admin.lua --admin-password=pass --proxy-lua-script=/root/mysql-proxy-0.8.3/lib/rw-splitting.lua.my --proxy-address=172.16.3.103:3306 --proxy-backend-addresses=172.16.3.104:3306 --proxy-read-only-backend-addresses=172.16.3.105:3306 --proxy-read-only-backend-addresses=172.16.3.106:3306 --pid-file=/var/run/mysql-proxy.pid 4. Run serveral queries like: mysql -uvm1 -ppass -e "insert into test.tmp set value = RAND();" --port 3306 --host 172.16.3.103 mysql -uvm1 -ppass -e "select value from test.tmp where id = RAND()" --port 3306 --host 172.16.3.103 No matter read or write queries.