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

可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

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?

回答1:

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.

回答2:

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()$$

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 their cowboy and run your SQL statements one at a time when using phpmyadmin. phpmyadmin is "single task bob", he can only do one job.

回答3:

Suppose that when you created the stored procedure you stored it in database named mydatabase to CALL the procedure. Go to your localhost DB and: CALL mydatabase.sp_test();

Where sp_test() is the name of your procedure.

回答4:

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 ;

回答5:

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

回答6:

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.

回答7:

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.

回答8:

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;