MySQL Bugs: #53688: Data of resultset some times is error when many client access simultaneity

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

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.

Thank you for the report.

Please provided full lua script including reconnect part.

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."

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?

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".