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:
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.0.4 OS:Windows (XP Pro SP2)
Assigned to: Reggie Burnett CPU Architecture:Any

[31 Aug 2009 14:38] The Assimilator
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.
[31 Aug 2009 14:39] The Assimilator
Error generated when removing database name from CommandText

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

[1 Sep 2009 6:19] The Assimilator
Updated OS.
[9 Sep 2009 7:14] Tonci Grgin
Verified just as described.

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

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

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?
[9 Sep 2009 12:54] The Assimilator
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.
[10 Sep 2009 6:21] Tonci Grgin
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.
[15 Jul 2010 19:22] Reggie Burnett
This was fixed with the patch on the related bug mentioned.  The database name is no longer included in the SQL.