| Bug #56789 | using pooled connection err | ||
|---|---|---|---|
| Submitted: | 15 Sep 2010 9:31 | Modified: | 17 Sep 2010 11:32 |
| Reporter: | Zhang Guangpei | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Proxy: Core | Severity: | S3 (Non-critical) |
| Version: | 0.8.0 | OS: | Linux (redhat5.4(32bit)) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | pooled connection proxy | ||
[16 Sep 2010 19:02]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior. Additionally version 0.8.0 is a bit outdated. Please upgrade to current version 0.8.1, try with it and inform us if problem still exists.
[17 Sep 2010 2:23]
Zhang Guangpei
lua script
Attachment: tutorial-keepalive.lua (text/plain), 6.97 KiB.
[17 Sep 2010 2:29]
Zhang Guangpei
I install it in 0.8.1 today,but the same thing happened.
enviorment:
----------------
mysql-proxy -V
mysql-proxy 0.8.1
chassis: mysql-proxy 0.8.1
glib2: 2.24.0
libevent: 1.4.14b-stable
LUA: Lua 5.1.4
package.path: /usr/local/lib/mysql-proxy/lua/?.lua
package.cpath: /usr/local/lib/mysql-proxy/lua/?.so
-- modules
admin: 0.8.1
proxy: 0.8.1
How to repeat it
--------------------
Lua script: as I uploaded
Run Command at 192.168.0.1:mysql-proxy --daemon --admin-address=:4401 --proxy-address=:4040 --log-level=debug --proxy-backend-addresses=192.168.0.2:3306 --proxy-read-only-backend-addresses=192.168.0.3:3306 --proxy-read-only-backend-addresses=192.168.0.4:3306 --admin-username=root --admin-password=abc789 --admin-lua-script=/usr/local/lib/mysql-proxy/lua/admin.lua --proxy-lua-script=/home/zhanggp/mysql-proxy-0.8.1/examples/tutorial-keepalive.lua
I test like this :
at the client:
[rhel542]:/home/zhanggp#mysql -uroot -pabc789 -P4040 -h192.168.0.1 -D test -e "use test"
[rhel542]:/home/zhanggp#mysql -uroot -pabc789 -P4040 -h192.168.0.1 -D test -e "use test"
[rhel542]:/home/zhanggp#mysql -uroot -pabc789 -P4040 -h192.168.0.1 -D test -e "use test"
[rhel542]:/home/zhanggp#mysql -uroot -pabc789 -P4040 -h192.168.0.1 -D test -e "use test"
[rhel542]:/home/zhanggp#
[rhel542]:/home/zhanggp#mysql -uroot -pabc789 -P4040 -h192.168.0.1 -D test -e "use test"
ERROR 1047 (08S01): Unknown command
then the debug info at the proxy :
[connect_server]
[1].connected_clients = 0
[1].idling_connections = 0
[1].type = 1
[1].state = 0
[1] open new connection
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[disconnect_client]
2010-09-17 10:15:29: (debug) abs wait-for-event::done usec= 0
2010-09-17 10:15:29: (debug) abs lua-exec::done usec= 0
[connect_server]
[1].connected_clients = 0
[1].idling_connections = 1
[1].type = 1
[1].state = 1
[2].connected_clients = 0
[2].idling_connections = 0
[2].type = 2
[2].state = 0
[2] open new connection
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[disconnect_client]
2010-09-17 10:15:30: (debug) abs wait-for-event::done usec= 0
2010-09-17 10:15:30: (debug) abs lua-exec::done usec= 0
[connect_server]
[1].connected_clients = 0
[1].idling_connections = 1
[1].type = 1
[1].state = 1
[2].connected_clients = 0
[2].idling_connections = 1
[2].type = 2
[2].state = 1
[3].connected_clients = 0
[3].idling_connections = 0
[3].type = 2
[3].state = 0
[3] open new connection
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[disconnect_client]
2010-09-17 10:15:31: (debug) abs wait-for-event::done usec= 0
2010-09-17 10:15:31: (debug) abs lua-exec::done usec= 0
[connect_server]
[1].connected_clients = 0
[1].idling_connections = 1
[1].type = 1
[1].state = 1
[2].connected_clients = 0
[2].idling_connections = 1
[2].type = 2
[2].state = 1
[3].connected_clients = 0
[3].idling_connections = 1
[3].type = 2
[3].state = 1
opening new connection on: 1
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[read_query]
authed backend = 0
used db = test
[disconnect_client]
2010-09-17 10:15:39: (debug) abs wait-for-event::done usec= 0
2010-09-17 10:15:39: (debug) abs lua-exec::done usec= 0
[connect_server]
[1].connected_clients = 0
[1].idling_connections = 2
[1].type = 1
[1].state = 1
[2].connected_clients = 0
[2].idling_connections = 1
[2].type = 2
[2].state = 1
[3].connected_clients = 0
[3].idling_connections = 1
[3].type = 2
[3].state = 1
using pooled connection from: 1
2010-09-17 10:15:51: (debug) [network-mysqld.c:937]: error on a connection (fd: -1 event: 0). closing client connection.
[disconnect_client]
please help me solve the problem,thanks very much!
[17 Sep 2010 9:46]
Zhang Guangpei
I have resolved the problem like this:
modify the /etc/my.cnf
as:
old_passwords=0
and then enter mysql:SET PASSWORD FOR 'root'@'192.168.0.1' = PASSWORD('abc789');
everything goes ok.
=========
but now I have another question more about the connection pool.
when the clients sends many connections such as 1000 at the same time,but I just have 100
connections in the pool.
if there are no idle connection in the pool,Can I use mysql-proxy to make the connection
wait,not open new connection
[17 Sep 2010 11:32]
Sveta Smirnova
Thank you for the feedback. If old-passwords option was cause of the problem this is duplicate of bug #31574
Description: when i use the mysql-proxy connection pool ,the client close at once like this :"ERROR 1047 (08S01): Unknown command" and the proxy like this: "using pooled connection from: 1 2010-09-15 17:22:15: (debug) [network-mysqld.c:817]: error on a connection (fd: -1 event: 0). closing client connection. [disconnect_client] " How to repeat: run command: mysql-proxy --daemon --admin-address=:4401 --proxy-address=:4040 --log-level=debug --proxy-backend-addresses=192.168.0.2:3306 --proxy-read-only-backend-addresses=192.168.0.3:3306 --proxy-read-only-backend-addresses=192.168.0.4:3306 --proxy-lua-script=/home/zhanggp/mysql-proxy-0.8.0/examples/tutorial-keepalive.lua the tutorial-keepalive.lua I changed two places: 1. local min_idle_connections = 1 local max_idle_connections = 4 2. -- try to open at least min_idle_connections if least_idle_conns_ndx == 0 or ( cur_idle < min_idle_connections and cur_idle < least_idle_conns ) then least_idle_conns_ndx = i least_idle_conns = s.pool.users[""].cur_idle_connections end and then i test like this: [rhel543]:/home/zhanggp#mysql -uroot -pabc789 -P4040 -h192.168.0.1 -D test -e " use test" [rhel543]:/home/zhanggp#mysql -uroot -pabc789 -P4040 -h192.168.0.1 -D test -e " use test" [rhel543]:/home/zhanggp#mysql -uroot -pabc789 -P4040 -h192.168.0.1 -D test -e " use test" [rhel543]:/home/zhanggp#mysql -uroot -pabc789 -P4040 -h192.168.0.1 -D test -e " use test" [rhel543]:/home/zhanggp#mysql -uroot -pabc789 -P4040 -h192.168.0.1 -D test -e " use test" ERROR 1047 (08S01): Unknown command at the same time, mysql-proxy like this: [connect_server] nil [1].connected_clients = 0 [1].idling_connections = 0 [1].type = 1 [1].state = 0 [1] open new connection [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [disconnect_client] 2010-09-15 17:22:05: (debug) abs wait-for-event::done usec= 0 2010-09-15 17:22:05: (debug) abs lua-exec::done usec= 0 [connect_server] nil [1].connected_clients = 0 [1].idling_connections = 1 [1].type = 1 [1].state = 1 nil [2].connected_clients = 0 [2].idling_connections = 0 [2].type = 2 [2].state = 0 [2] open new connection [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [disconnect_client] 2010-09-15 17:22:10: (debug) abs wait-for-event::done usec= 0 2010-09-15 17:22:10: (debug) abs lua-exec::done usec= 0 [connect_server] nil [1].connected_clients = 0 [1].idling_connections = 1 [1].type = 1 [1].state = 1 nil [2].connected_clients = 0 [2].idling_connections = 1 [2].type = 2 [2].state = 1 nil [3].connected_clients = 0 [3].idling_connections = 0 [3].type = 2 [3].state = 0 [3] open new connection [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [disconnect_client] 2010-09-15 17:22:11: (debug) abs wait-for-event::done usec= 0 2010-09-15 17:22:11: (debug) abs lua-exec::done usec= 0 [connect_server] nil [1].connected_clients = 0 [1].idling_connections = 1 [1].type = 1 [1].state = 1 nil [2].connected_clients = 0 [2].idling_connections = 1 [2].type = 2 [2].state = 1 nil [3].connected_clients = 0 [3].idling_connections = 1 [3].type = 2 [3].state = 1 opening new connection on: 1 [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [read_query] authed backend = 0 used db = test [disconnect_client] 2010-09-15 17:22:12: (debug) abs wait-for-event::done usec= 0 2010-09-15 17:22:12: (debug) abs lua-exec::done usec= 0 [connect_server] nil [1].connected_clients = 0 [1].idling_connections = 2 [1].type = 1 [1].state = 1 nil [2].connected_clients = 0 [2].idling_connections = 1 [2].type = 2 [2].state = 1 nil [3].connected_clients = 0 [3].idling_connections = 1 [3].type = 2 [3].state = 1 using pooled connection from: 1 2010-09-15 17:22:15: (debug) [network-mysqld.c:817]: error on a connection (fd: -1 event: 0). closing client connection. [disconnect_client] Suggested fix: with the other lua script ,when i use the connection pool,the same thing happened.