Solved

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

Posted on 2009-07-11
7
2,512 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
ID: 24829813
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
ID: 24829831
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
ID: 24829875
>> 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 10

Author Comment

by:cyberstalker
ID: 24829928
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
ID: 24830008
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
ID: 31602392
Even though my procedure returns only one resultset, a call to mysql_use_next() fixes the problem..
0
 
LVL 53

Expert Comment

by:Infinity08
ID: 24830064
>> 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
php image upload 3 36
How many transactions can mysql handle? 3 18
updating the date data 12 19
MySqlDump not dumping triggers 1 12
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Grant
The goal of this video is to provide viewers with basic examples to understand and use nested-loops in the C programming language.
The goal of this video is to provide viewers with basic examples to understand opening and reading files in the C programming language.

943 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

10 Experts available now in Live!

Get 1:1 Help Now