Bug #58876 Error: 2014 Commands out of sync; you can't run this command now
Submitted: 10 Dec 2010 20:04 Modified: 1 May 2019 0:04
Reporter: Jonathan Miller Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / C Documentation Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[10 Dec 2010 20:04] Jonathan Miller
Description:
Docs 
======
Error: 2014  (CR_COMMANDS_OUT_OF_SYNC)
Message: Commands out of sync; you can't run this command now 
======

Why? What could be the causes? What should I look at?

Documenting like this provide no use. I was getting this error and looked to the docs for help.

Yet, all I found was the above. Not of any use as I already new err: 2014 was "Commands out of sync; you can't run this command now" and what I was needing to know is "Why? What could be the causes? What should I look at?"

I admit that some errors speak for themselves, yet others really do nothing more but create questions. And our docs should help to answer and guide.

How to repeat:
http://dev.mysql.com/doc/refman/5.0/en/error-messages-client.html#error_cr_shared_memory_c...

Suggested fix:
Add and error help section that gives some types or at least explains the cases that the error can be returned which would give a programmer a place to start without having to go read MySQL code like I did.
[13 Dec 2010 14:21] Paul DuBois
"Add and error help section that gives some types or at least explains the cases that the
error can be returned which would give a programmer a place to start without having to go
read MySQL code like I did."

Since you read the code, what did you discover?
[13 Dec 2010 14:33] Jonathan Miller
Hi,

What I found is that I needed to call mysql_stmt_fetch(stmt); until it returned 100 (i.e. MYSQL_NO_DATA).

Even-though there was only one row of data returned, it was not in the proper state because it had not gotten to MYSQL_NO_DATA.

So I added extra while() loop after the initial call to get to MYSQL_NO_DATA. 

All worked well after.
[13 Dec 2010 14:38] Jonathan Miller
Before:

void MCAPI_Atomics::GetDbSize()
{
  rc = 0;
  //Get account table count
  rc = mysql_stmt_execute(stmt[SELECT_COUNT_ACCOUNT]);
  check_execute(stmt[SELECT_COUNT_ACCOUNT], rc);
  rc = mysql_stmt_fetch(stmt[SELECT_COUNT_ACCOUNT]);
  if(debug){printf("Account tables has %li rows \n",g_IdRange);}
} 

After:

void MCAPI_Atomics::GetDbSize()
{
  rc = 0;
  //Get account table count
  rc = mysql_stmt_execute(stmt[SELECT_COUNT_ACCOUNT]);
  check_execute(stmt[SELECT_COUNT_ACCOUNT], rc);
  rc = mysql_stmt_fetch(stmt[SELECT_COUNT_ACCOUNT]);
  while(rc != MYSQL_NO_DATA && rc != 1){
    rc = mysql_stmt_fetch(stmt[SELECT_COUNT_ACCOUNT]);
  }
  if (rc == 1){
    check_execute(stmt[SELECT_COUNT_ACCOUNT], rc);
  }
  if(debug){printf("Account tables has %li rows \n",g_IdRange);}
}
[30 Apr 2019 23:57] Paul DuBois
Posted by developer:
 
re:
"
Error: 2014  (CR_COMMANDS_OUT_OF_SYNC)
Message: Commands out of sync; you can't run this command now
======

Why? What could be the causes? What should I look at?

Documenting like this provide no use.
"

That "documentation" is actually pulled straight from the source tree (errmsg-utf8.txt), so for that message, if you'd like to see it improved, it'd be better to file a server bug.

That said, I did modify https://dev.mysql.com/doc/refman/8.0/en/mysql-stmt-fetch.html to point out explicitly that mysql_stmt_fetch() is called within a loop until the result set is exhausted (with example code). I also pointed out in the Errors section that if you do NOT read the entire result set, the *following* C API call is likely to produce a sync error.

Also, at https://dev.mysql.com/doc/refman/8.0/en/client-error-reference.html#error_cr_commands_out_..., I gave the mysql_stmt_fetch() issue as an example of what could cause the problem. If you have other examples you'd like mentioned, let me know. Otherwise, trying to put together some exhaustive list would be a research project.