MySQL Bugs: #39223: proxy with query analysis enabled can invalidate found

Bug #39223	proxy with query analysis enabled can invalidate found_rows
Submitted:	3 Sep 2008 21:21	Modified:	12 Jan 2009 17:21
Reporter:	Darren Oldag
Status:	Closed
Category:	Monitoring: Agent	Severity:	S1 (Critical)
Version:	2.0.0.7111	OS:	Any
Assigned to:	MC Brown	Target Version:

Description:
A query that is issued through the proxy, and that has an auto-explain performed on that
query, does not have a proper response for SELECT FOUND_ROWS() as the next query.

This also extends to other connection-state oriented queries, such as show
warnings/errors.

Note:  originally made a S1 because this shows up as the proxy causing different/invalid
results than what a user expects, thus making proxy non-transparent.

How to repeat:
log into a proxy/mysql via the mysql command line client.

> select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 | 
+----------+
1 row in set (1.00 sec)

> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            0 | 
+--------------+
1 row in set (0.00 sec)

the same queries executed directly to mysql are:

> select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 | 
+----------+
1 row in set (1.00 sec)

> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            1 | 
+--------------+
1 row in set (0.00 sec)

notice, the found rows through the proxy is zero, and the direct mysql is 1.

Suggested fix:
don't ruin connection state for the user connection.

several possible

1) use a different, specialized connection for the explain query

2) prefetch the found_rows BEFORE calling explain, and return the results for that if the
user requests it.

2b) like 2, but only prefetch if the SQL_CALC_FOUND_ROWS is present.  otherwise,
FOUND_ROWS() is just the same as the last result set size anyway, which we should already
have.  but the proxy would still need to trap the request and return the mocked response.

moving to QATest, to try with the SQL_CALC_FOUND_ROWS modifier.

verified fixed using SQL_CALC_FOUND_ROWS  on 2.0.0.7111

An entry was added to the 2.0 changelog:

A query that was issued through the proxy, and that had an auto-explain performed on that
query, did not give the correct response to a subsequent query of SELECT FOUND_ROWS().