How to prematurely finish mysql_use_result() / mysql_fetch_row()?

user_3239842 0
Viewed 737

I am in the process of writing my first C client for MySQL 5.5 and have stumbled across the following page in the documentation. Nearly at the end, it states (bold emphasis mine, italic emphasis not mine):

An advantage of mysql_use_result() is [...]. Disadvantages are that [...]. Furthermore, you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for.

The last sentence is not clear to me.

1) What happens if I don't follow that line?

2) I think that there actually must be a way to prematurely end fetching rows if I decide that I have enough information (otherwise, this whole thing wouldn't make much sense in my eyes).

I understand that something bad could happen if I just stop fetching rows and then try to execute the next statement, but isn't there a function like mysql_finish_fetch() or something like that?

And what happens if I call mysql_free_result()? This should free the result even if I haven't fetched all rows yet, so it should be safe to call it in mid-retrieval and continue with whatever I'd like to do. Am I wrong here?

libmysql c mysql
1 Answers

This sounds like an internal threading issue that MySQL exposes to the client. Chalk it up to the various MySQL gotchas. The short of it is that MySQL apparently has a finite number of "searchers" internally, and using mysql_use_result() apparently dedicates one of them to your API request. Further, MySQL apparently has no exposed API call to cancel such a request. The only option is to see the fetch through until the end.

The slightly longer version: internally, MySQL's cursors apparently have a single code path -- I imagine for performance in the common cases. That code path exits only when the cursor finds no more results. When you use the more common mysql_store_result(), MySQL has done this already before returning the result to the application. When you use mysql_use_result(), however, MySQL requires that you do "the dirty work" of iterating the rest of the result set so as to clear the cursor. Fun.

From the documentation:

mysql_use_result() initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result() does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row(). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result(). The client allocates memory only for the current row and a communication buffer that may grow up to max_allowed_packet bytes.

On the other hand, you should not use mysql_use_result() for locking reads if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This ties up the server and prevent other threads from updating any tables from which the data is being fetched.

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!

So, to actually answer your questions:

1) What happens if I don't follow that line?

The C API will return the error message: Commands out of sync; you can't run this command now

2) I think that there actually must be a way to prematurely end fetching rows if I decide that I have enough information (otherwise, this whole thing wouldn't make much sense in my eyes).

One would think, but no. You must iterate the result set completely.

user_87301310
Related