MySQL Forums
Forum List  »  Router & Proxy

Re: mysql-proxy losses connections with applications having multiple users
Posted by: Merlin Aldous Espinoza
Date: September 04, 2007 11:37PM

1) the proxy version and revision (possibly update to the latest one)

mysql-proxy 0.6.0b

*we got this from the svn trunk

2) the server version

RW backend: 5.0.40-enterprise
RO backend: 5.0.37-max-community

*the RO backend is a complete replication of our RW backend

3) the command line statement used to start the proxy

*we run it from a script:

LUA_PATH="/opt/mysql-proxy-0.6.0b/trunk/lib/?.lua" /opt/mysql-proxy-0.6.0b/trunk/src/mysql-proxy \
--proxy-lua-script=/opt/mysql-proxy-0.6.0b/trunk/lib/rw-splitting-org.lua \
--proxy-address=192.168.50.25:3306 \
--proxy-backend-addresses=192.168.50.10:3306 \
--proxy-read-only-backend-addresses=192.168.50.21:3306 \
--daemon

*we renamed the lua script from rw-splitting.lua to rw-splitting-org.lua for reference,
so the lua script we used is the default script we got from the svn trunk
*we used the default port so as to minimize code changes in our applications

4) for each user, their relative privileges

app_dtr % SELECT, INSERT, UPDATE, DELETE
app_inbox % SELECT, INSERT, UPDATE, DELETE

*update: we tried connecting from the commandline with different users and found out it took
only two different users to cause an error, so we listed only two
*both users have access to all databases and tables, and have passwords

5) The structure of databases and tables that were involved. Ideally, one database only,
with just a few tables, but if necessary, add as many as it takes.

database: callbox_pipeline

+----------------------------+
| Tables_in_callbox_pipeline |
+----------------------------+
| agents |
| annualsales |
| billings |
| biztypes |
| bookmarks |
| businessgroups |
| campaigndetails |
| campaigns |
| clientaccounts |
| clientapptsched |
| clientbillings |
| clients |
| clientservice |
| contracttypes |
| countries |
| crdtcustomers |
| crdtfrms |
| crdtqstnanslogs |
| crdtqstns |
| departments |
| dids |
| dncs |
| docs |
| empsizes |
| empstates |
| events |
| eventstates |
| eventtypes |
| followups |
| grps |
| history_type |
| holidays |
| hrpositions |
| incentives |
| incomplete |
| interfacestyles |
| lean_events |
| leave_details |
| leaves |
| lookup_events |
| naicscodes |
| nonclients |
| payments |
| pending_absents |
| pipe_history |
| pmdetails |
| pmproviders |
| pms |
| positions |
| profiles |
| qatableinbound |
| qatableoutbound |
| rawtargets |
| reasons |
| reports |
| roles |
| roles_v2 |
| saleitems |
| salerecords |
| saletypes |
| siccodes |
| sql_monitors |
| tabs |
| targetcontacts |
| targetdetails |
| targettypes |
| tblLogTypes |
| tblPhoneQuality |
| tblSMSqueue |
| tblTimeLogs |
| tblTimeNotes |
| tc_misc |
| tc_misc_details |
| termcodes |
| termgroups |
| termremaps |
| testme |
| timezones |
| trigger_control |
| trim_events |
| usertypes |
| usstates |
+----------------------------+


How we set up the test:

-open 2 ssh sessions, one on the proxy server and another on the RW backend server.
-on the proxy server, start mysql-proxy using the details mentioned above.
-send commands to the proxy from the RW backend server. (this will be our client)

How we wrote the output:

1. task to do (query) with explanation
command: {command we executed from the client}
A. output in client
B. output in proxy debug


Tests
1. try to connect to the proxy with different users and show the tables in the database.
we will use app_dtr first.

command: mysql -u app_dtr -p -h 192.168.50.25 -b callbox_pipeline -e 'show tables;'

A.
Enter password:
+----------------------------+
| Tables_in_callbox_pipeline |
+----------------------------+
| agents |
| annualsales |
| billings |
| biztypes |
| bookmarks |
| businessgroups |
| campaigndetails |
| campaigns |
| clientaccounts |
| clientapptsched |
| clientbillings |
| clients |
| clientservice |
| contracttypes |
| countries |
| crdtcustomers |
| crdtfrms |
| crdtqstnanslogs |
| crdtqstns |
| departments |
| dids |
| dncs |
| docs |
| empsizes |
| empstates |
| events |
| eventstates |
| eventtypes |
| followups |
| grps |
| history_type |
| holidays |
| hrpositions |
| incentives |
| incomplete |
| interfacestyles |
| lean_events |
| leave_details |
| leaves |
| lookup_events |
| naicscodes |
| nonclients |
| payments |
| pending_absents |
| pipe_history |
| pmdetails |
| pmproviders |
| pms |
| positions |
| profiles |
| qatableinbound |
| qatableoutbound |
| rawtargets |
| reasons |
| reports |
| roles |
| roles_v2 |
| saleitems |
| salerecords |
| saletypes |
| siccodes |
| sql_monitors |
| tabs |
| targetcontacts |
| targetdetails |
| targettypes |
| tblLogTypes |
| tblPhoneQuality |
| tblSMSqueue |
| tblTimeLogs |
| tblTimeNotes |
| tc_misc |
| tc_misc_details |
| termcodes |
| termgroups |
| termremaps |
| testme |
| timezones |
| trigger_control |
| trim_events |
| usertypes |
| usstates |
+----------------------------+

B.
[connect_server]
[1].connected_clients = 0
[1].idling_connections = 0
[1].type = 1
[1].state = 0
[1] open new connection
[read_query]
current backend = 0
client default db = callbox_pipeline
client username = app_dtr
query = select @@version_comment limit 1
sending to backend : 192.168.50.10:3306
is_slave : false
server default db: callbox_pipeline
server username : app_dtr
in_trans : false
in_calc_found : false
COM_QUERY : true
[read_query]
current backend = 0
client default db = callbox_pipeline
client username = app_dtr
query = show tables
sending to backend : 192.168.50.10:3306
is_slave : false
server default db: callbox_pipeline
server username : app_dtr
in_trans : false
in_calc_found : false
COM_QUERY : true
[read_query]
current backend = 0
client default db = callbox_pipeline
client username = app_dtr
[disconnect_client]


2. Connect again but this time change the user to app_inbox
command: mysql -u app_inbox -p -h 192.168.50.25 -b callbox_pipeline -e 'show tables;'

A.
Enter password:
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

B.
[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]
current backend = 0
client default db = callbox_pipeline
client username = app_inbox
query = select @@version_comment limit 1
sending to backend : 192.168.50.21:3306
is_slave : true
server default db: callbox_pipeline
server username : app_inbox
in_trans : false
in_calc_found : false
COM_QUERY : true
[read_query]
current backend = 0
client default db = callbox_pipeline
client username = app_inbox
query = show tables
network-conn-pool.c.75: no pool-entry for app_inbox found
(read_query) ...pt/mysql-proxy-0.6.0b/trunk/lib/rw-splitting-org.lua:276: attempt to index local 's' (a nil value)
network-mysqld-proxy.c.2815: I have no server backend, closing connection
network-mysqld.c.1161: plugin_call(CON_STATE_READ_QUERY) failed
[disconnect_client]


3. Try using app_dtr again as the user.
command: mysql -u app_dtr -p -h 192.168.50.25 -b callbox_pipeline -e 'show tables;'

A.
Enter password:
ERROR 2006 (HY000) at line 1: MySQL server has gone away

B.
[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
opening new connection on: 1
[read_query]
current backend = 0
client default db = callbox_pipeline
client username = app_dtr
query = select @@version_comment limit 1
network-conn-pool.c.75: no pool-entry for app_dtr found
(read_query) ...pt/mysql-proxy-0.6.0b/trunk/lib/rw-splitting-org.lua:276: attempt to index local 's' (a nil value)
network-mysqld-proxy.c.2815: I have no server backend, closing connection
network-mysqld.c.1161: plugin_call(CON_STATE_READ_QUERY) failed
[disconnect_client]

4. Just for the sake of it, try using app_inbox as user again?
command: mysql -u app_inbox -p -h 192.168.50.25 -b callbox_pipeline -e 'show tables;'

A.
Enter password:
+----------------------------+
| Tables_in_callbox_pipeline |
+----------------------------+
| agents |
| annualsales |
| billings |
| biztypes |
| bookmarks |
| businessgroups |
| campaigndetails |
| campaigns |
| clientaccounts |
| clientapptsched |
| clientbillings |
| clients |
| clientservice |
| contracttypes |
| countries |
| crdtcustomers |
| crdtfrms |
| crdtqstnanslogs |
| crdtqstns |
| departments |
| dids |
| dncs |
| docs |
| empsizes |
| empstates |
| events |
| eventstates |
| eventtypes |
| followups |
| grps |
| history_type |
| holidays |
| hrpositions |
| incentives |
| incomplete |
| interfacestyles |
| lean_events |
| leave_details |
| leaves |
| lookup_events |
| naicscodes |
| nonclients |
| payments |
| pending_absents |
| pipe_history |
| pmdetails |
| pmproviders |
| pms |
| positions |
| profiles |
| qatableinbound |
| qatableoutbound |
| rawtargets |
| reasons |
| reports |
| roles |
| roles_v2 |
| saleitems |
| salerecords |
| saletypes |
| siccodes |
| sql_monitors |
| tabs |
| targetcontacts |
| targetdetails |
| targettypes |
| tblLogTypes |
| tblPhoneQuality |
| tblSMSqueue |
| tblTimeLogs |
| tblTimeNotes |
| tc_misc |
| tc_misc_details |
| termcodes |
| termgroups |
| termremaps |
| testme |
| timezones |
| trigger_control |
| trim_events |
| usertypes |
| usstates |
+----------------------------+

B.
[connect_server]
[1].connected_clients = 2
[1].idling_connections = 1
[1].type = 1
[1].state = 1
[2].connected_clients = 0
[2].idling_connections = 1
[2].type = 2
[2].state = 1
opening new connection on: 1
[read_query]
current backend = 0
client default db = callbox_pipeline
client username = app_inbox
query = select @@version_comment limit 1
sending to backend : 192.168.50.21:3306
is_slave : true
server default db: callbox_pipeline
server username : app_inbox
in_trans : false
in_calc_found : false
COM_QUERY : true
[read_query]
current backend = 0
client default db = callbox_pipeline
client username = app_inbox
query = show tables
sending to backend : 192.168.50.10:3306
is_slave : false
server default db: callbox_pipeline
server username : app_inbox
in_trans : false
in_calc_found : false
COM_QUERY : true
[read_query]
current backend = 0
client default db = callbox_pipeline
client username = app_inbox
[disconnect_client]

5. We restart mysql-proxy, then we try to connect with only one user, like app_inbox.
command: mysql -u app_inbox -p -h 192.168.50.25 -b callbox_pipeline -e 'show tables;'

for clarity, we'll show only the differences in the output of the debug screen.

A. {always the same as the list_of_tables above}

B1.
[connect_server]
[1].connected_clients = 0
[1].idling_connections = 0
[1].type = 1
[1].state = 0
[1] open new connection

B2.
[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

B3.
[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
opening new connection on: 1

B4.
[connect_server]
[1].connected_clients = 1
[1].idling_connections = 1
[1].type = 1
[1].state = 1
[2].connected_clients = 0
[2].idling_connections = 1
[2].type = 2
[2].state = 1
opening new connection on: 1

B5.
[connect_server]
[1].connected_clients = 2
[1].idling_connections = 1
[1].type = 1
[1].state = 1
[2].connected_clients = 0
[2].idling_connections = 1
[2].type = 2
[2].state = 1
opening new connection on: 1


*P.S. if i change the user at this point, error 2006 happens but the
connect server part looks like this
[connect_server]
[1].connected_clients = 3
[1].idling_connections = 1
[1].type = 1
[1].state = 1
[2].connected_clients = 0
[2].idling_connections = 1
[2].type = 2
[2].state = 1
opening new connection on: 1

{lines removed}

query = select @@version_comment limit 1
network-conn-pool.c.75: no pool-entry for app_dtr found
(read_query) ...pt/mysql-proxy-0.6.0b/trunk/lib/rw-splitting-org.lua:276: attempt to index local 's' (a nil value)
network-mysqld-proxy.c.2815: I have no server backend, closing connection
network-mysqld.c.1161: plugin_call(CON_STATE_READ_QUERY) failed

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: mysql-proxy losses connections with applications having multiple users
5150
September 04, 2007 11:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.