MySQL Error # 2014 - Commands are not synchronized; you cannot run this command now

Question

I am using MySQL and I am defining a stored procedure as follows:

delimiter ;; Create procedure sp_test() select * from name_table; end 

When I try to execute this procedure, I get this error:

 #2014 - Commands out of sync; you can't run this command now 

What does this mean and what am I doing wrong?

Answer 1

From Guide

C.5.2.14. Commands out of sync
If you get Commands out of sync; you can't run this command now Commands out of sync; you can't run this command now in your client code, you are calling the client function in the wrong order.

This can happen, for example, if you use mysql_use_result() and try a new query before you call mysql_free_result() . This can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() between them.

This post (taken from here )

I solved this problem. I use MySQL-Fron instead of MySQL Query browser. And everything is working fine.

makes me think that this is not a server or database problem, but a problem in the tool you use.

Answer 2

I managed to reproduce this error with MySQL and phpmyadmin:

 #2014 - Commands out of sync; you can't run this command now 

In this version of MySQL:

 el@apollo:~$ mysql --version mysql Ver 14.14 Distrib 5.5.34, for debian-linux-gnu (x86_64) using readline 6.2 

Use the following SQL to run the phpmyadmin query window:

 use my_database; DELIMITER $$ CREATE PROCEDURE foo() BEGIN select 'derp' as 'msg'; END $$ CALL foo()$$ <----Error happens here, with or without delimiters. 

I could not get the error through the MySQL terminal, so I believe this is a phpmyadmin error.

It works fine on the terminal:

 mysql> delimiter $$ mysql> use my_database$$ create procedure foo() begin select 'derp' as 'msg'; end $$ call foo() $$ Database changed Query OK, 0 rows affected (0.00 sec) +------+ | msg | +------+ | derp | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) 

I think the error is due to changing the intermediate delimiter request in phpmyadmin.

Workaround: Slow down, cowboy, and run your SQL statements one at a time using phpmyadmin. phpmyadmin is a "single bean", it can only do one job.

Answer 3

You forgot to use the "Begin" keyword, and at compile time MySQL got confused, this should work:

 DELIMITER ;; Create procedure sp_test() BEGIN select * from name_table; END;; DELIMITER ; 

Answer 4

Suppose that when you created a stored procedure, you saved it in a database called mydatabase to call the procedure. Go to the local database and:

 CALL mydatabase.sp_test(); 

Where sp_test() is the name of your procedure.

Answer 5

I also ran into this problem with the C API.

I found a solution with the last example above that talks about delimiters.

 use my_database; DELIMITER $$ CREATE PROCEDURE foo() BEGIN select 'derp' as 'msg'; END $$ CALL foo()$$ 

My code executes the stored procedure, then checks for a return. I am using mysql_free_result () correctly.

Since I did not add the procedure to the procedure "in" the procedure, this error occurred.

The last example above is in the same case.

I removed select and, as that is normal.

Alex

Answer 6

I just got the same error from phpMYadmin when calling the user function I'm working on.

mysql console reports, however:

 ERROR 1054 (42S22): Unknown column 'latitude' in 'field list' 

... which is absolutely correct, it was written with an error in the list of fields, so the operator referred to the undefined variable.

I would have to conclude that

 #2014 - Commands out of sync; you can't run this command now 

of phpMYadmin is a rather nonspecific error than in many cases, if not most, it simply hides the real problem, and you should not spend too much time trying to understand it.

Answer 7

You have this problem, obviously, because both statements are executed simultaneously. The only workaround I found was to close the connection after sp and execute another statement on the new one. Read about it here .

Answer 8

This happened to me because the function inside the procedure gave a value back that was not assigned to the variable.

Decision:

 select function .... INTO @XX; 

Answer 9

The possible reason is that the mysql client in your code is not thread safe, I encountered the same error when I call mysqldb in python, I have one mysql interface used in 2 threads, an error occurs In this situation, you need to create more mysql interfaces along with threads.

Answer 10

I fixed this problem on phpMyAdmin 4.8.6

Problem: https://github.com/phpmyadmin/phpmyadmin/issues/14614

Retrieval Request: https://github.com/phpmyadmin/phpmyadmin/pull/15234

Patch: https://github.com/phpmyadmin/phpmyadmin/pull/15234/files#diff-de1b7e9dd5969db226563678c658ea67

The patch consists of, and if mysqli_more_results, then call mysqli_next_result