MySQL Bugs: #25983: MySQL Query Browser for Mac OS X doesn't properly handle calling SPs

Bug #25983	MySQL Query Browser for Mac OS X doesn't properly handle calling SPs
Submitted:	31 Jan 2007 15:30	Modified:	15 Dec 2009 10:39
Reporter:	Joshua Butcher	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	r5 to r9	OS:	MacOS (Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	MySQL Query Browser does not properly handle SPs

Description:
When I call a stored procedure in Mac OS X Query Browser, it will return

-1 Error executing SQL command.

When executing any SP whether it was successful or not.

If there is an error, the error message QB does give is not very meaningful.

How to repeat:
DELIMITER //
CREATE PROCEDURE  db_update_and_maintenance()
BEGIN

  CALL update_report_stats(DATE_SUB( CONCAT(DATE(NOW()),' 00:00:00'),INTERVAL 1 DAY), now());

  CALL _database_cleanup();

END //
DELIMITER ;

CALL db_update_and_maintenance();

Suggested fix:
Please make it report meaningful data the way the Windows version of QB does.

Thank you for the report.

I get useful information on self Intel Mac.

Please provide output of SHOW CREATE PROCEDURE update_report_stats;, SHOW CREATE PROCEDURE _database_cleanup and SHOW CREATE TABLE for all tables to which these procedures access.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Here is a simple test case:

Server version: MySQL 5.1.41 
Tool version: MySQL Query Browser 1.2.12
Platform: Mac OS X 10.5.8 on Intel architecture.

DROP TABLE IF EXISTS Foo;
CREATE TABLE Foo (id SERIAL PRIMARY KEY);
INSERT INTO Foo (id) VALUES (1234);

DELIMITER //
DROP PROCEDURE IF EXISTS FooProc//
CREATE PROCEDURE FooProc(doquery SMALLINT)
BEGIN
  IF doquery THEN
    SELECT * FROM Foo;
  END IF;
END//
DELIMITER ;

CALL FooProc(1);

Result: id => 1234, as expected

CALL FooProc(0);

Result: "-1 Error executing SQL Command."

I haven't run a trace, but I've downloaded looking at the source for MySQL GUI Tools 5.0 r14, which includes Query Browser 1.2.14.

I see in file common/library/base-library/source/myx_recordset.c, function  myx_query_execute(), it seems to treat a query that has no result set as an error.  That is, if mysql_store_result() returns NULL, myx_query_execute() sets the error status to MYX_SQL_ERROR.

It should be valid for a stored procedure to return without generating a result set, but I don't see how the code permits this.  Consequently, when you CALL such a stored procedure in MySQL Query Browser, it's bound to generate the error seen.

Bill,

thank you for the feedback. Verified as described.

Although most likely this only be fixed when MySQL Query Browser functionality is part of MySQL workbench.

Thanks for the speedy response.  I have tried MySQL Workbench 5.2.10 beta.  

When I `CALL FooProc(1)` the response is the result of `SELECT * FROM Foo` as expected.

When I `CALL FooProc(0)` the response is no result set as expected.  The status is simply "OK".

So that shows that the new MySQL Workbench supports procedures that have no result set.  Thanks!

As a side note, MySQL Workbench 5.2.10 doesn't seem to know when to close a statement that may have multiple result sets, e.g. a call to a procedure.  When I try to issue the next query, I get an error: "Commands out of sync; you can't run this command now."  But this is a separate issue, covered in Bug #49553.

Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Query Browser into MySQL Workbench. We won't fix this in Query Browser anymore.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/