MySQL Bugs: #54001: Allow early call of mysql_free_result after mysql_use

Bug #54001	Allow early call of mysql_free_result after mysql_use_result
Submitted:	26 May 2010 15:55
Reporter:	DJ Miller	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S4 (Feature request)
Version:	5.0	OS:	Linux (CentOS 5.3)
Assigned to:		CPU Architecture:	Any

Description:
According to the documentation up through 5.5, after calling mysql_use_result, all rows must be read via mysql_fetch_row until the end of the recordset is reached before mysql_free_result is called.

http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html

I have noticed, if you do not do this, a call to mysql_free_result can hang.  (I've found another report in the forum at the following URL.)

http://forums.mysql.com/read.php?168,361807,361807#msg-361807

However, there may be programmatic reasons for opening a recordset and not wanting to read to the end.  Certainly opening more rows than needed is one, but if the program reads rows and detects an unexpected or error condition and needs to abort (but not necessarily shut down), it would be more efficient to drop the results rather than be forced to loop through all selected rows after the program has determined it will not use them.

How to repeat:
Open a large query with mysql_use_result
Read a small number (less than 10) of rows with mysql_fetch_row
Call mysql_free_result
Repeat; system may hang (I can get 5.0.77 to hang consistently; 5.0.45 not so much)

Suggested fix:
When a mysql_free_result method is called on a recordset opened with mysql_use_result, release/reset resources appropriately on the server, such that reading to the end is not necessary on the client.

Interestingly enough, for me on Mac OS X using the 5.5.11 connector to a 5.1.x server, it works fine. I can't follow the server code well enough to understand what's happening, but the comments in the code suggest it still shouldn't work, but it does for me. 

The documentation says:

"When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned, otherwise, the unfetched rows are returned as part of the result set for your next query. The C API gives the error Commands out of sync; you can't run this command now if you forget to do this!"

Both parts are untrue in my case. The correct rows are returned for the second query, and no out-of-sync error happens anywhere.

I agree this is quite an important thing to add.

Ok, finally stepped through the code well enough to know what was going on. If you have not called mysql_fetch_row until it returns NULL, when you call mysql_free_result, it "flushes" the result set by simply reading all data available on the connection until there isn't any left. At that point it's finished reading all of what the server has written to the client and then can be in-sync again, apparently doing essentially the same thing as calling mysql_fetch_row() until NULL, just more efficiently.

Given that server has already written a chunk of data to the client, it'll need to read until all of that is gone anyway, so I don't see how mysql_free_result() can be made to work properly and return instantly. But I'm hardly familiar with MySQL's internals. Anyway, found my answer.  Hopefully this is of use to anyone else wondering the same thing.