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 19:21	Modified:	12 Jan 2009 16:21
Reporter:	Darren Oldag	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S1 (Critical)
Version:	2.0.0.7111	OS:	Any
Assigned to:	MC Brown	CPU Architecture:	Any

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().