MySQL Forums :: MySQL Proxy :: mysql-proxy losses connections with applications having multiple users

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