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 16:38	Modified:	9 Sep 14:54
Reporter:	The Assimilator
Status:	Verified
Category:	Connector/Net	Severity:	S3 (Non-critical)
Version:	6.0.4	OS:	Microsoft Windows (XP Pro SP2)
Assigned to:	Reggie Burnett	Target Version:
Triage:	D3 (Medium)

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.