MySQL Bugs: #71044: 'commands out of sync' after calling a stored procedure from C-Connector

Bug #71044	'commands out of sync' after calling a stored procedure from C-Connector
Submitted:	1 Dec 2013 9:34	Modified:	1 Dec 2013 14:33
Reporter:	erick ringot	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S2 (Serious)
Version:	6.0.2	OS:	Windows (Win 7 - 64 bits)
Assigned to:		CPU Architecture:	Any
Tags:	API, C, out of sync, PROCEDURE, SELECT

Description:
When calling a stored procedure (working fine) from a C code using the C-Connector library next requests provoke 'Commands out of sync' errors. Such errors do not happen when using the Workbench or the command line.

MySql version 5.5.20 (community Server) running under Win64 (x86)

How to repeat:
MySQL server side - dummy stored procedure is:

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `TESTPROC`()
BEGIN
select 1,2,3,4,5,6,7,8;
END

C language client side (error handling code removed):

MYSQL_ROW row;
MYSQL_RES *res=NULL;
char *mycmd="CALL TESTPROC()";
mysql_query(mysql,mycmd);
res = mysql_store_result(mysql);
while (row = mysql_fetch_row(res))
  {
  ... proceed with row ...
  }
mysql_free_result(res);
// all is ok at this point when called once
// but all subsequent "SELECT" fail
// with an 'Commands out of sync' error
// despite the same sequence /query/store_result/fetch_row/free_result/
// is used each time.

Suggested fix:
Notes:
1) In the above C-code, defining mycmd by 
     char *mycmd="select 1,2,3,4,5,6,7,8";
clears the error.

2) It seems that only procedures containing 'SELECT' instructions provoke subsequent errors ; errors happen only when the procedure is called from the C-connector ; the first call works fine only subsequent 'SELECT' fail.

Not a bug ...

A procedure can return more than one result set, and it will always return one extra empty result set that carries some meta information on the procedure call itself, especially error information.

You only fetched the results of the SELECT executed within the procedure though.

What you actually need to do is to iterate over all returned result sets using mysql_more_results() / mysql_next_result() until all result sets returned by the procedure are consumed. 

Only then can you send new commands to the server without receiving an "out of sync" error.

See also: 

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

Just adding a call to mysql_next_result() as suggested by Hartmut fixes the problem. See the slightly modified code below :

MYSQL_ROW row;
MYSQL_RES *res=NULL;
char *mycmd="CALL TESTPROC()";
mysql_query(mysql,mycmd);
res = mysql_store_result(mysql);
while (row = mysql_fetch_row(res))
  {
  ... proceed with row ...
  }
mysql_free_result(res);
mysql_next_result(mysql); // <- added instruction
// Now subsequent SELECT work

This 'bug' (apology, it was not) report can be closed now. Thanks to Hartmut.

Thanks Hartmut.