Commands out of sync; You can't run this command now

When running queries from my program, the second query *always* fails with the message: Commands out of sync; You can't run this command now. I clean up the result sets, and even check if there are more, unexpected resultsets. Nothing helps.
void    db_request_group(MYSQL *conn, struct nn_group *group)
{
        MYSQL_RES       *db_result;
        MYSQL_ROW       db_row;
        char            *query;
 
        query   =       malloc(strlen(group->group_name) + 27);
        sprintf(query, "CALL nn_request_group('%s')", group->group_name);
 
        if (mysql_real_query(conn, query, strlen(query)))
        {
                printf("Error during query execution!\nQuery: %s\nError: %s\n", query, mysql_error(conn));
                exit(1);
        }
 
        db_result               =       mysql_use_result(conn);
        db_row                  =       mysql_fetch_row(db_result);
        group->database_id      =       atoi(db_row[0]);
        group->old_mark         =       atoi(db_row[1]) > 0 ? atoi(db_row[1]) : group->old_mark;
 
        mysql_free_result(db_result);
        free(query);
 
        // mysql adds extra, empty resultsets after each sproc
        // preventing further queries, so we clear this first
        while (db_result = mysql_use_result(conn))
                mysql_free_result(db_result);
}

Open in new window

LVL 10
cyberstalkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Infinity08Commented:
The problem is most likely that your select query returns more than one row, and you're fetching only one. You have to call mysql_fetch_row in a loop until it returns NULL, and only then do the mysql_free_result.

Also, get rid of the while loop at the end of the function - it's not needed.

More info here :

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

So, assuming you only care about the first row :
void    db_request_group(MYSQL *conn, struct nn_group *group)
{
        MYSQL_RES       *db_result;
        MYSQL_ROW       db_row;
        char            *query;
 
        query   =       malloc(strlen(group->group_name) + 27);
        sprintf(query, "CALL nn_request_group('%s')", group->group_name);
 
        if (mysql_real_query(conn, query, strlen(query)))
        {
                printf("Error during query execution!\nQuery: %s\nError: %s\n", query, mysql_error(conn));
                exit(1);
        }
 
        db_result               =       mysql_use_result(conn);
        db_row                  =       mysql_fetch_row(db_result);
        group->database_id      =       atoi(db_row[0]);
        group->old_mark         =       atoi(db_row[1]) > 0 ? atoi(db_row[1]) : group->old_mark;
 
        while (mysql_fetch_row(db_result) != NULL);   // fetch all rows
 
        mysql_free_result(db_result);
        free(query);
}

Open in new window

0
cyberstalkerAuthor Commented:
This does not work. First of all, the procedure I am calling only ever returns one row, so this should not be necessary. I have tried your suggestion though, without any effect.
0
Infinity08Commented:
>> First of all, the procedure I am calling only ever returns one row, so this should not be necessary.

Better safe than sorry eh, I'd say ... In any case, if you read the reference page I linked to, it 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!"

I'd say that's pretty clear ;)

Anyway, if you say, it doesn't resolve your issue, then I don't see anything wrong with this (modified) function that would cause this. I'd suggest to make sure that the code you tested is actually the one I posted (make sure it's recompiled and everything), and then check the rest of the code for similar problems. Maybe the error occurs due to some other code.

Maybe you are trying to run multiple queries in parallel ? Because that's not allowed either.


If nothing helps, then there is a small chance that this might be due to a bug in your specific MySQL API.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

cyberstalkerAuthor Commented:
The problem is not in parallel queries. To make sure of it, I created a testcase which presents the problem. I have posted it here so you can take a look at it.

It compiles, runs, and gives the following output:

Database connection established!
Error during second query execution!
Query: CALL nn_request_group('alt.comp.software.financial.quickbooks')
Error: Commands out of sync; you can't run this command now
#include <mysql.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
 
int main()
{
	MYSQL		*conn;
	MYSQL_RES	*db_result;
	MYSQL_ROW	db_row;
	char		query[]	=	"CALL nn_request_group('alt.comp.software.financial.quickbooks')";
 
	conn	=	mysql_init(NULL);
 
	if (!mysql_real_connect(conn, "localhost", "nn_sync", "148hJy", "usenet", 0, NULL, CLIENT_MULTI_RESULTS))
	{
		printf("Database connection failed!\nError:%s\n", mysql_error(conn));
		exit(1);
	}
 
	printf("%s\n", "Database connection established!");
 
	if (mysql_real_query(conn, query, strlen(query)))
	{
		printf("Error during first query execution!\nQuery: %s\nError: %s\n", query, mysql_error(conn));
		exit(1);
	}
 
	db_result		=	mysql_use_result(conn);
 
	while (mysql_fetch_row(db_result) != NULL);
	mysql_free_result(db_result);
 
	if (mysql_real_query(conn, query, strlen(query)))
	{
		printf("Error during second query execution!\nQuery: %s\nError: %s\n", query, mysql_error(conn));
		exit(1);
	}
 
	db_result		=	mysql_use_result(conn);
 
	while (mysql_fetch_row(db_result) != NULL);
	mysql_free_result(db_result);
	mysql_close(conn);
}

Open in new window

0
Infinity08Commented:
Ah, I missed earlier that you were calling a stored procedure rather than a simple select. Is it possible that your stored procedure performs multiple queries ? If so, you need to call mysql_next_result until all results for all queries have been retrieved.

Try something like this to see if it works better (off the top of my head, untested) :
#include <mysql.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
 
int main()
{
        MYSQL           *conn;
        MYSQL_RES       *db_result;
        MYSQL_ROW       db_row;
        char            query[] =       "CALL nn_request_group('alt.comp.software.financial.quickbooks')";
 
        conn    =       mysql_init(NULL);
 
        if (!mysql_real_connect(conn, "localhost", "nn_sync", "148hJy", "usenet", 0, NULL, CLIENT_MULTI_RESULTS))
        {
                printf("Database connection failed!\nError:%s\n", mysql_error(conn));
                exit(1);
        }
 
        printf("%s\n", "Database connection established!");
 
        if (mysql_real_query(conn, query, strlen(query)))
        {
                printf("Error during first query execution!\nQuery: %s\nError: %s\n", query, mysql_error(conn));
                exit(1);
        }
 
        db_result               =       mysql_use_result(conn);
        while (mysql_fetch_row(db_result) != NULL);
        mysql_free_result(db_result);
 
        while (mysql_next_result(conn) == 0) {
          db_result = mysql_use_result(conn);
          while (mysql_fetch_row(db_result) != NULL);
          mysql_free_result(db_result);
        }
 
        if (mysql_real_query(conn, query, strlen(query)))
        {
                printf("Error during second query execution!\nQuery: %s\nError: %s\n", query, mysql_error(conn));
                exit(1);
        }
 
        db_result               =       mysql_use_result(conn);
 
        while (mysql_fetch_row(db_result) != NULL);
        mysql_free_result(db_result);
        mysql_close(conn);
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyberstalkerAuthor Commented:
Even though my procedure returns only one resultset, a call to mysql_use_next() fixes the problem..
0
Infinity08Commented:
>> Even though my procedure returns only one resultset

Afaik, it's not related to the amount of result sets the procedure returns, but to the amount of queries it does.

I'd have to look that up, but even if there is only one query in the procedure, I think calling mysql_next_result until it returns an error is still necessary to make it work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.