Bug #20070 do not require mysql_next_result() to be called for update/insert/etc.
Submitted: 25 May 2006 18:10 Modified: 26 Jul 2006 22:49
Reporter: Timothy Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.22 OS:Linux (linux, freebsd, any)
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[25 May 2006 18:10] Timothy Smith
Description:
Currently, when one calls mysql_query() with multiple statements (e.g., two UPDATE statements), it is required to call mysql_next_result() even though no result set is available.  Otherwise, a subsequent mysql_query() will fail with "Packets out of order (Found: 2, expected 1)".

It would be nice if one could call mysql_query() without needed to call mysql_next_result(), when one knows that no result sets can be returned.

How to repeat:

See attached test program.  Here is a run of the program:

$ ./mult_update
iter 0: buf: 'update mult_update set f2 = 'test f2 0' where f1 = '0'; update mult_update set f3 = 'test f3 0' where f1 = 0'
        - A statement affected 0 row(s)
iter 1: buf: 'update mult_update set f2 = 'test f2 1' where f1 = '1'; update mult_update set f3 = 'test f3 1' where f1 = 1'
Packets out of order (Found: 2, expected 1)
Program terminated at line 57: Lost connection to MySQL server during query

Suggested fix:

The workaround for now is to just call mysql_next_result(), which is not too odious.
[25 May 2006 18:17] Timothy Smith
Test program

Attachment: mult_update.c (application/octet-stream, text), 2.39 KiB.

[25 May 2006 18:34] Timothy Smith
See also bug #20072; fixing it may also fix this one.
[22 Jul 2006 16:07] Timothy Smith
Magnus,

After reviewing this, I think you're right and it just needs to be documented properly that mysql_next_result() *must* be called any time there are multiple statements, regardless of the content of those statements.

In particular, the following passage is misleading:

http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

"By default, mysql_query() and mysql_real_query() return only the first query status and the subsequent queries status can be processed using mysql_more_results() and mysql_next_result()."

And, I would want a single sentence on the following pages stating that if more results exist, then mysql_next_result() *must* be called:

http://dev.mysql.com/doc/refman/5.0/en/mysql-next-result.html
http://dev.mysql.com/doc/refman/5.0/en/mysql-more-results.html

Regards,

Timothy
[26 Jul 2006 22:49] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.