MySQL Bugs: #47021: "Failed to update CommandText" error dialog when removing database name

Bug #47021	"Failed to update CommandText" error dialog when removing database name
Submitted:	31 Aug 2009 14:38	Modified:	15 Jul 2010 19:22
Reporter:	The Assimilator	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.0.4	OS:	Windows (XP Pro SP2)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
This bug is related to http://bugs.mysql.com/bug.php?id=33870 in that attempting to remove the database/catalog name from the CommandText does not work.

*This error only seems to occur when using stored procedures with at least 1 input parameter.*

Please see attached screenshot of error message. This error only appears after a TableAdapter has been successfully configured and dropped onto a DataSet, and one then goes and changes the CommandText property.

-----------------------------------------------

I dug through the source and from what I can see the issue is due to the MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) method. That method initialises a StoredProcedure or PreparedStatement object, then calls the new object's Resolve() method.

However, if we're dealing with a stored procedure that has its CommandText changed in the designer (to remove the database name), the MySqlCommand passed to the new object will have no parameters defined (why, I do not know). Resolve() calls MySql.Data.MySqlClient.MySqlParameterCollection.GetParameterFlexible(), which fails because there are no parameters, and hence the exception is thrown.

How to repeat:
1. Create and compile a stored procedure that has at least 1 input parameter and performs a SELECT.
2. In Visual Studio, create a new DataSet.
3. Use the TableAdapter wizard to create a new DataTable. Specify the stored procedure you created in step (1).
4. Once the DataTable and TableAdapter are created, select the TableAdapter and edit its CommandText to remove the database name.
5. Tab out of the CommandText field and observe the error that appears.

Suggested fix:
Ensure that the MySqlCommand passed to the StoredProcedure constructor is properly initialized. In the StoredProcedure.Resolve() method, check that parameters.Rows.Count == command.Parameters.Count and if this is not the case, throw an exception.

Error generated when removing database name from CommandText

Attachment: sproc-param-error.PNG (image/png, text), 13.25 KiB.

Updated OS.

Verified just as described.

Environment:  
  Remote MySQL server 5.1.31 on OpenSolarisx64
  c/NET 6.0.4 on Win2K8x64 with VS2008Pro

SP:
DELIMITER $$
DROP PROCEDURE IF EXISTS `ptestfwsel` $$
CREATE DEFINER=`root`@`%` PROCEDURE `ptestfwsel`(IN param1 INT)
BEGIN SELECT CURRENT_DATE AS cd, RAND(param1) AS rnd, param1 AS parval; end $$
DELIMITER ;

Now, I am not so convinced this is truly a bug as you are warned of "... and might not reflect data retrieved by your command." which is, in fact, true...

Reggie, Wlad?

Just to make things clearer: in both cases it's the *same* stored procedure on the same server/database. If you use the procedure as-is, everything is fine. If you change the procedure name to remove the database name - without recompiling the procedure or making any other changes to the TableAdapter - this error occurs.

Hence I have to disagree and say that it's definitely a bug. "Parameter 'name' was not found in the collection." is an ArgumentException explicitly raised by the GetParameterFlexible() method when it can't find the parameter it's told to - which, from my understanding, should never happen if the identical stored procedure is used.

There is a possibility that the problem causing this is different than the one described in Bug#47013 so please check that report against the patch too.

This was fixed with the patch on the related bug mentioned.  The database name is no longer included in the SQL.