Solved

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

Posted on 2009-07-11
7
2,488 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:cyberstalker
  • 4
  • 3
7 Comments
 
LVL 53

Expert Comment

by:Infinity08
Comment Utility
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
 
LVL 10

Author Comment

by:cyberstalker
Comment Utility
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
 
LVL 53

Expert Comment

by:Infinity08
Comment Utility
>> 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 10

Author Comment

by:cyberstalker
Comment Utility
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
 
LVL 53

Accepted Solution

by:
Infinity08 earned 500 total points
Comment Utility
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
 
LVL 10

Author Closing Comment

by:cyberstalker
Comment Utility
Even though my procedure returns only one resultset, a call to mysql_use_next() fixes the problem..
0
 
LVL 53

Expert Comment

by:Infinity08
Comment Utility
>> 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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This tutorial is posted by Aaron Wojnowski, administrator at SDKExpert.net.  To view more iPhone tutorials, visit www.sdkexpert.net. This is a very simple tutorial on finding the user's current location easily. In this tutorial, you will learn ho…
Creating and Managing Databases with phpMyAdmin in cPanel.
The goal of this video is to provide viewers with basic examples to understand how to create, access, and change arrays in the C programming language.
The goal of this video is to provide viewers with basic examples to understand and use switch statements in the C programming language.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now