[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2930
  • Last Modified:

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

0
cyberstalker
Asked:
cyberstalker
  • 4
  • 3
1 Solution
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now