Bug #49553 Execution of a stored procedure fails after first attemp
Submitted: 9 Dec 2009 10:33 Modified: 11 Jan 2010 11:46
Reporter: Mathieu Massebœuf Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S2 (Serious)
Version:5.2.10 r4686 OS:Mac OS X (10.6.2)
Assigned to: Sergei Tkachenko
Tags: command out of sync, stored procedure

[9 Dec 2009 10:33] Mathieu Massebœuf
Description:
Calling a stored procedure works just after opening a new database connexion.
Subsequent calls to the same stored procedure (or another) fails with the following error : "Error code: 2013 - Commands out of sync, you can't run this command now"

Restarting the connexion to the server (using the red stop button) and then calling the exact same procedure gives the expected result.

Another related bug : call two stored procedure consecutively by typing the two CALLs in the sql statements window - only the first result will be displayed, the second result tab will be empty.
The output tab will still have the proper response (the two calls with the proper row count)

How to repeat:
1 - Connect to the server (Open connection to start Querying)
2 - Execute any stored procedure by calling it from the SQL Statements window - get expected results
3 - Execute the same procedure again - get the error message

4 - Click the red stop button
5 - Execute the same sql statement again - get expected results
[9 Dec 2009 13:05] Miguel Solorzano
I couldn't repeat on Linux Ubuntu 9.10.
[9 Dec 2009 14:18] Sergei Tkachenko
Failed to repeat with windows version as well.
Could you bring example of the routine causing described problem? DBMS version would be also helpful.
[10 Dec 2009 16:02] Mathieu Massebœuf
I tried again with a simple procedure doing a single select :

CREATE DEFINER=`xx`@`172.x.x.%` PROCEDURE `p__Test`(
)
BODY:
BEGIN	  
    SELECT * FROM Agenda LIMIT 1;    
END

The error message I get is :
Error Code: 2014
Commands out of sync; you can't run this command now

The server version I connect to is MySQL 5.0.51a-24+lenny2 (Debian) - running on Debian Lenny up-to-date (which means it's not the MySQL official binary version)

Particular settings which could change MySQL's default behaviour (except memory / cache and other stuffs) :
connect_timeout = 15
wait_timeout = 300
net_buffer_length = 12k

Connection settings are saved in MySQL workbench - they are 'regular', no option selected, and using a TCP connexion (over a VPN - but that part is transparent)

Feel free to ask for any other informations.

Thanks, Mathieu
[11 Dec 2009 12:18] Susanne Ebrecht
I am not able to repeat this on Debian with MySQL Server 5.1 and Workbench 5.2.10.

How did you install Workbench?

Do you have replication or so running?

Are you sure all is fine on TCP/IP? I meant that network not just is striking or so.
[11 Dec 2009 15:43] Mathieu Massebœuf
In case some of you didn't noticed, I'm using MacOSX version of Workbench - it may be a platform specific bug.
Workbench was installed using the regular OSX way (copy everything to /Applications)
Replication is not working on this server.

In cas this could be server-related, I did the test again using a local server on my laptop - I get the exact same error.
Calling the procedure many times from the command line using mysql client works as expected.
This eliminates the network issue (although I was connected to localhost, not via the socket)

As a side-note (in case it can give a hint), I have a similar issue in phpmyadmin - from what understanding it's due to the order used to process the procedure call (phpmyadmin is doing some more than just calling it - not sure what) :
CALL p__Test;
SQL Query: 
SELECT DATABASE( ) AS  'db';
MySQL answered:
#2014 - Commands out of sync; you can't run this command now
[17 Dec 2009 5:17] Bill Karwin
I can reproduce this error.  It appears to be specific to MySQL Workbench on Mac OS X.

I ran Workbench 5.2.10 on Windows XP, connecting to MySQL 5.5.0 on localhost (Windows XP).  Everything worked fine.

I defined a simple procedure:

  CREATE PROCEDURE FooProc()
  BEGIN
    SELECT CURRENT_DATE;
  END

And I executed it:

  CALL FooProc();

The first CALL works fine.  I get the current date back in the result.  I can call the procedure multiple times, and I can execute other SQL statements too.

I ran Workbench 5.2.10 on Mac OS X 10.5.8, connecting to MySQL 5.1.41 on localhost (the Mac) and then connecting remotely to MySQL 5.5.0 on the Windows XP host.

The first time I CALL FooProc() it returns the current date as expected.  Any subsequent SQL statement, either a CALL or a SELECT, gets the error:

  Error code 2014
  Commands out of sync; you can't run this command now

If I kill the connection and issue a query, it spawns a new connection and executes the query successfully.

My understanding is that this error is typical when a SQL statement that has a result set hasn't been closed properly.  MySQL can have only one open statement at a time, so if I try to run a query while another query is still open, this error is typical.

So I would guess that some of the Mac OS X specific code of MySQL Workbench 5.2 isn't implementing the same SQL statement workflow that the Windows specific code does.  Perhaps in the Windows code, it closes a previously open statement before it tries to execute a new statement?  In any case, the OS X code and the Windows code should do the same thing.
[18 Dec 2009 14:55] Sergei Tkachenko
The bug confirmed to be OS X specific. Fixed.
[21 Dec 2009 10:38] Mathieu Massebœuf
I confirm this bug has been fixed in the latest release (5.2.11 Beta 2)
[7 Jan 2010 13:12] Johannes Taxacher
fix confirmed, the problem with executing  multiple call statements and only get proper output in first result panel is now also fixed. fix will be included in 5.2.12
[11 Jan 2010 11:46] Tony Bedford
An entry has been added to the 5.2.12 changelog:

In the SQL Editor, if a Stored Procedure was executed and then immediately executed again, the following error was generated:

Error code 2014
  Commands out of sync; you can't run this command now

This problem only affected MySQL Workbench running on the Mac OS X platform.