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 Manual

C.5.2.14. 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 (taken from here)

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.