Bug #53688 Data of resultset some times is error when many client access simultaneity
Submitted: 17 May 2010 4:16 Modified: 22 Jul 2010 9:38
Reporter: wang zone Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Proxy Severity:S1 (Critical)
Version:0.6.1 OS:Linux
Assigned to: CPU Architecture:Any

[17 May 2010 4:16] wang zone
Description:
Test precondition:

1.There are 2 backends in my system, A and B, works in loadbalance mode. 
2.20 clients connect to proxy simultaneity, sending query 'select XX' unremittingly, XX is a rand key, some key is neither in backend A nor in backend B.
3. I get the rows number in resultset by lua, the simple script as following:
  
function getResultNum(inj)
    local res = assert(inj.resultset)

    if res.query_status ~= proxy.MYSQLD_PACKET_OK then
        return false, 0, 0
    end
    local fields = inj.resultset.fields 
    
    if not fields then
    return false, 0, 0
    end
    
    local field_count = #fields
   
    if not inj.resultset.rows then
       return false, 0, 0
    end
    
    local row_count =0
    for row in inj.resultset.rows do
            row_count = row_count + 1
    end

    return true, field_count, row_count
   
end

4. In proxy lua, if I can't get the data in backend A, I will send the same sql to backend B by returning a proxy.PROXY_SEND_QUERY to mysql-proxy to wait the bakend B result.  some rand key value neither in backend A nor in backend B.
 
Test result:

  I get some error result from the function getResultNum sometimes, row_count is 0.  I connect the backend directly and execute the same sql the result set is not empty.

How to repeat:
It is easy to repeat when many clients unremittingly.  repeating it, Preconditon 4 is very important. 

Suggested fix:
I print every resultset and the field_count and row_count  in lua, the print result as following:

inj.id = 1
inj.query = ^CSELECT * from cft_digit_crt_29.t_user_attr_7 where Fuid = 110003729 and Fattr = 0
| | field[1] = { type = 3, name = Fuid }
| | field[2] = { type = 2, name = Fattr }
| | field[3] = { type = 253, name = Fvalue }
| | field[4] = { type = 253, name = Fattach }
| | field[5] = { type = 253, name = Fmemo }
| | field[6] = { type = 2, name = Fstate }
| | field[7] = { type = 12, name = Fcreate_time }
| | field[8] = { type = 12, name = Fmodify_time }
| | field[9] = { type = 3, name = Fstandby1 }
| | field[10] = { type = 3, name = Fstandby2 }
| | field[11] = { type = 253, name = Fstandby3 }
| | field[12] = { type = 253, name = Fstandby4 }
field_count12row_count0
print_inj  start ...
query-time: 0.77ms
response-time: 0.78ms
inj.id = 5
inj.query = ^Cselect fuid,fstate from cft_digit_crt_11.t_user_attr_0 where Fuid = 110125011 and Fattr = 0
| | field[1] = { type = 3, name = fuid }
| | field[2] = { type = 2, name = fstate }
entry [read_query] get a new packet: ^Cselect fuid,fstate from cft_digit_crt_42.t_user_attr_0 where Fuid = 110028042 and Fattr = 0
[read_query]  packet to mainDB: ^Cselect fuid,fstate from cft_digit_crt_42.t_user_attr_0 where Fuid = 110028042 and Fattr = 0
print_inj  start ...
query-time: 0.582ms
response-time: 0.593ms
inj.id = 2
inj.query = ^Cselect fuid,fstate from cft_digit_crt_29.t_user_attr_7 where Fuid = 110003729 and Fattr = 0
| | field[1] = { type = 3, name = fuid }
| | field[2] = { type = 2, name = fstate }
field_count2row_count0

You can see that , there is another sql executed between I can't get the data in backend A and getting the result in backend B.
[27 May 2010 9:16] Sveta Smirnova
Thank you for the report.

Please provided full lua script including reconnect part.
[4 Jun 2010 2:15] wang zone
I have fixed this bug. Some times, the following lua script will failed.
       proxy.connection.backend_ndx = 2
I check it in the source code of proxy0.6.1, I get it. 
In function 
static int proxy_connection_set(lua_State *L) {
...
(NULL != (send_sock = proxy_connection_pool_swap(con, backend_ndx))) {
			con->server = send_sock;
		} else {
			st->backend_ndx = backend_ndx;
		}
 When too many client accessing the proxy simultaneity, proxy_connection_pool_swap some times return NULL. The statement , st->backend_ndx = backend_ndx;  isn't perfect result, especially in the condition 4

"
4. In proxy lua, if I can't get the data in backend A, I will send the same sql to
backend B by returning a proxy.PROXY_SEND_QUERY to mysql-proxy to wait the bakend B
result.  some rand key value neither in backend A nor in backend B."
[22 Jun 2010 9:38] Sveta Smirnova
Thank you for the feedback.

When you say "I have fixed this bug." does this mean you fixed lua script which you wrote and use?
[22 Jul 2010 23: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".