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

Solution 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.

Solution 2:

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

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

enter image description here 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.

Solution 3:

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!

Solution 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.

Solution 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