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

Question

I am using MySQL and I am defining a stored procedure like this:

delimiter ;;
Create procedure sp_test()

  select * from name_table;
end

When I try to execute that 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 the reference manual section B.3.2.12 Commands out of sync:

B.3.2.12 Commands out of sync

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

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

This post from the MySQL forums has this to say:

I've solved that problem. I use MySQL-Fron instead MySQL Query browser. And everything works fine.

Makes me think that it's not a server or database problem but a problem in the tool you're using.

Answer 2

In my case, I had the following structure in my stored procedure:

DELIMITER //
    DROP PROCEDURE IF EXISTS processcolumns;
    CREATE PROCEDURE processcolumns ()
    BEGIN
        (...)
    END //
DELIMITER ;

CALL processcolumns ();
DROP PROCEDURE processcolumns;

The problem relies here: DROP PROCEDURE IF EXISTS processcolumns; I removed the semi colon ; and replaced it with the delimiter // like this:

DROP PROCEDURE IF EXISTS processcolumns //

And it's now solved!

Answer 3

I was able to reproduce this error with MySQL and phpmyadmin:

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

On 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

With the following SQL run through 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 couldn't get the error to happen through the MySQL terminal, so I think it's a bug with phpmyadmin.

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 bug has something to do with changing the delimiters mid-query within phpmyadmin.

Workaround: Slow down there, cowboy, and run your SQL statements one at a time when using phpmyadmin. phpmyadmin is "single task bob", he can only do one job.

Answer 4

The possible reason is that 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, the error happens. In this situation, you need to create more mysql interfaces along with threads.

Answer 5

I fixed this issue on phpMyAdmin 4.8.6

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

Pull-Request: https://github.com/phpmyadmin/phpmyadmin/pull/15234

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

The patch consists into and if mysqli_more_results then a call to mysqli_next_result

Answer 6

It seems there are multiple sources for this issue. None of the posted answers worked in my case, I found that in my case I had selects and updates on a table in my stored procedure, the same table had an update trigger which was being triggered and sending the procedure into an infinite loop. Once the bug was found the error went away.

I also reproduced the issue in the case where I had a select which was calling a function which modified the table. It makes sense that these types of recursive calls could create issues. The issues were solved by updating a tmp table and at the end updating the original table from the tmp table.

Answer 7

delimiter ;;
Create procedure sp_test()

  select * from name_table;
end ## no end here, must add ;; after end. should be 
delimiter ;;
Create procedure sp_test()

  select * from name_table;
end ;;

Answer 8

You forgot to use the 'Begin' keyword, and during compilation MySQL is confused, this should work:

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

Answer 9

I also encountered this problem with a C API.

I found the solution with the last example above, which speaks of delimiters.

use my_database;
DELIMITER $$

CREATE PROCEDURE foo()
BEGIN
select 'derp' as 'msg';
END $$

CALL foo()$$  

My code executes a stored procedure then tests the return. I use correctly the mysql_free_result().

Since I have not added a select clause "into" in the procedure, this error occurred.

The last example above is in the same case.

I have deleted the select and since it's ok.

Alex

Answer 10

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

mysql console says however:

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

...which is absolutely correct, it was misspelled in the field list, so a statement was referencing an undefined variable.

I'd have to conclude that

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

from phpMYadmin is a rather non-specific error, than in many cases, if not most, is just obscuring the real problem, and one should not spend too much time trying to make sense out of it.

Answer 11

You have this problem apparently because both statements are executing simultaneously . The only workaround I have found is to close the connection after the sp and execute the other statement on a new one. Read about it here.

Answer 12

This was happening to me because a function within an procedure gave a value back that wasn't allocated to a variable.

The solution was:

select function .... INTO @XX;