MySQL Bugs: #53786: Visual Studio addin calls stored procedure with scalar when variable is expected

Bug #53786	Visual Studio addin calls stored procedure with scalar when variable is expected
Submitted:	19 May 2010 8:12	Modified:	22 Jul 2010 17:15
Reporter:	Dave Adams	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.2.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	dataset, out parameter, stored procedure, Visual Studio

Description:
I was digging through some server logs to troubleshoot why I still can't work with stored procedures that use OUT parameters and discovered that the Data Source Configuration Wizard is calling stored procedures with scalar (constant) values for OUT and INOUT parameters.  I assume the stored procedure is called to determine the schema of any returned result set.

MySQL Connector/Net   6.2.3
MySQL Server 5.1.45-community-log
Windows XP Pro SP3
Microsoft Visual Studio 2008 Version 9.0.30729.1 SP

How to repeat:
Here is the DDL for a simple test database:

DROP DATABASE IF EXISTS `foo`;
CREATE DATABASE `foo`;
DELIMITER $$
CREATE PROCEDURE `foo`.`bar`(OUT pMyParam INT)
BEGIN
	SET pMyParam = 1;
END$$
DELIMITER ;

To reproduce, launch Visual Studio, create a C# Windows Forms application, and add a new MySQL data source.  Expand the Stored Procedures node in the Data Source Configuration Wizard, then expand the node for the `bar` stored procedure.  You get the following error:

An error occured while retrieving the column information
OUT or INOUT argument 1 for routine foo.bar is not a variable or NEW pseudo-variable in BEFORE trigger

Here is what the corresponding server log shows is happening under the hood (annotation added):

41 Query	SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE != 'VIEW' AND TABLE_SCHEMA LIKE 'foo'
41 Query	SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA LIKE 'foo'
41 Query	SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'foo' AND ROUTINE_TYPE LIKE 'PROCEDURE'
41 Init DB	foo
41 Query	SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'foo' AND ROUTINE_NAME LIKE 'bar'
41 Query	SHOW CREATE PROCEDURE `foo`.`bar`
41 Query	SET SQL_SELECT_LIMIT=0
41 Query	call `foo`.`bar` (0)       /**********  Here is the problem.  *********/
41 Query	SET SQL_SELECT_LIMIT=-1

Note that the Data Source Configuration Wizard calls foo.bar with a scalar argument of 0, but the parameter is an OUT parameter.

Suggested fix:
To correct this, the Visual Studio addin should pass a user variable instead:
SET @a = 0;
CALL `foo`.`bar`(@a);

As an aside, it seems dangerous that the stored procedure should be invoked at all.  There are bound to be side effects with this practice.  What if the stored procedure performs record deletions?  Then simply attempting to create a data source that uses this stored procedure in Visual Studio will delete records unexpectedly.

Hi Dave and thanks for your report.

I do agree with you but till 5.5 is GA we rely on "tricks" to gather SP metadata so I do not think there is anything that can be done here. Let me think a bit about this.

Tried on soon to be released 6.3-beta. Works for me, under "root" user, 
under non-privileged user I get a box telling me to grant privileges

Here is excerpt from the log

		    2 Query	SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'foo' AND ROUTINE_NAME LIKE 'bar' AND ROUTINE_TYPE LIKE 'PROCEDURE'
		    2 Query	SHOW CREATE PROCEDURE `foo`.`bar`
		    2 Query	SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'foo' AND ROUTINE_NAME LIKE 'bar'
		    2 Query	SHOW CREATE PROCEDURE `foo`.`bar`
		    2 Query	SET @_cnet_param_pMyParam=NULL
		    2 Query	SET SQL_SELECT_LIMIT=0
		    2 Query	call `foo`.`bar` (@_cnet_param_pMyParam)
		    2 Query	SELECT @_cnet_param_pMyParam
		    2 Query	SET SQL_SELECT_LIMIT=DEFAULT

So I close that bug. Feel free to reopen if problem is not fixed for you in the next release.

And, I agree that calling stored procedure without being asked if "fishy". As Tonci said, it is a trick required to get the result set metadata (fields, types etc). There is unfortunately no way currently to know this info for stored procedures, without actually calling it.