3

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?

Binarus
  • 4,005
  • 3
  • 25
  • 41
  • 1
    It's well documented: `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!` – Ctx Nov 14 '17 at 14:00
  • You usually should query exactly the information you need. Querying more and then start looking for the "important parts" in the results is the wrong approach to begin with! If you really need to filter in such a complex way that it isn't doable with plain sql, use stored procedures – Ctx Nov 14 '17 at 14:01
  • @Ctx I agree with you regarding your second comment. Nevertheless, some scripting languages with MySQL bindings (e.g. Perl) have a "statement / fetching finishing function", and I have wondered how this works. I have used this in certain situations where it came in very handy. – Binarus Nov 14 '17 at 14:12
  • I think those will have to fetch the remaining rows in the background, too – Ctx Nov 14 '17 at 14:13

1 Answers1

3

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.

hunteke
  • 3,648
  • 1
  • 7
  • 17
  • Of course MySQL cleans up, indeed to reconnect could be a workaround if a huge amount of data was still pending... But one should really only ask for data which is actually needed, otherwise the query or the database design is broken, – Ctx Nov 14 '17 at 14:17
  • @Ctx my poor attempt at humor, given the above non-obvious implementation. But perhaps you're correct. Let me edit that last out. – hunteke Nov 14 '17 at 14:20
  • It begins to make sense. Then `mysql_free_result()` just frees the client side resources, but does not communicate with the server to tell the server to "abort the query"? – Binarus Nov 14 '17 at 14:24
  • @Binarus "Does not say 'abort the query": correct. One might say "We're in `C` land, where **nothing** is provided. We have to do it all. Including cleanup." – hunteke Nov 14 '17 at 14:26
  • Is this "you must execute mysql_fetch_row() until a NULL value is returned" applicable for each connection? If another connection did this mistake will my connection be affected? – Andrei Sura Mar 18 '21 at 00:24