| Bug #46213 | StoredProcedure.GetParameters(string) ignores UseProcedureBodies option | ||
|---|---|---|---|
| Submitted: | 15 Jul 2009 20:16 | Modified: | 30 Jul 2009 13:16 |
| Reporter: | Yvan Rodrigues | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S1 (Critical) |
| Version: | 5.2.7 | OS: | Any |
| Assigned to: | Reggie Burnett | CPU Architecture: | Any |
| Tags: | 5.2.7, connection string, GetParameters, ignored, stored procedure, storedprocedure, Use Procedure Bodies, UseProcedureBodies | ||
[16 Jul 2009 8:10]
Tonci Grgin
Hi Yvan and thanks for your report. Truly, I found this change in rev. 1594 but it is not clear to me why is it made. So I call upon Reggie and Wlad to explain and document it. My guess would be it's related to: Version 5.2.7 - fixed procedure parameters collection so that an exception is thrown if we can't get the parameters. Also used this to optimize the procedure cache optimization
[30 Jul 2009 1:59]
Reggie Burnett
This is fixed in 5.2.8, 6.0.5, and 6.1.1+
[30 Jul 2009 13:16]
Tony Bedford
Entries were added to the 5.2.8, 6.0.5 and 6.1.1 changelogs:
The MySQL Connector/NET method StoredProcedure.GetParameters(string) ignored the programmer's setting of the UseProcedureBodies option. This broke any application for which the application's parameter names did not match the parameter names in the Stored Procedure, resulting in an ArgumentException with the message “Parameter 'foo' not found in the collection.” and the following stack trace:
MySql.Data.dll!MySql.Data.MySqlClient.MySqlParameterCollection.GetParameterFlexible(stri
ng parameterName = "pStart", bool throwOnNotFound = true) Line 459C#
MySql.Data.dll!MySql.Data.MySqlClient.StoredProcedure.Resolve() Line 157 + 0x25
bytesC#
MySql.Data.dll!MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(System.Data.CommandBeha
vior behavior = SequentialAccess) Line 405 + 0xb bytesC#
MySql.Data.dll!MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(System.Data.Comma
ndBehavior behavior = SequentialAccess) Line 884 + 0xb bytesC#
System.Data.dll!System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(System
.Data.CommandBehavior behavior) + 0xb bytes
System.Data.dll!System.Data.Common.DbDataAdapter.FillInternal(System.Data.DataSet
dataset = {System.Data.DataSet}, System.Data.DataTable[] datatables = null, int
startRecord = 0, int maxRecords = 0, string srcTable = "Table", System.Data.IDbCommand
command = {MySql.Data.MySqlClient.MySqlCommand}, System.Data.CommandBehavior behavior) +
0x83 bytes
System.Data.dll!System.Data.Common.DbDataAdapter.Fill(System.Data.DataSet dataSet, int
startRecord, int maxRecords, string srcTable, System.Data.IDbCommand command,
System.Data.CommandBehavior behavior) + 0x120 bytes
System.Data.dll!System.Data.Common.DbDataAdapter.Fill(System.Data.DataSet dataSet) +
0x5f bytes
Description: Beginning in 5.2.7, for some reason line 67 of StoredProcedure.cs is commented out in the release version (tags/5.2.7 in svn). This means that StoredProcedure.GetParameters(string) ignores the programmer's setting of UseProcedureBodies and forcibly uses the procedure bodies to determine the names of the parameters. This will break any application for which the application's parameter names do not match the parameter names in the SP, resulting in an ArgumentException with message "Parameter 'foo' not found in the collection." and a stack trace like this: > MySql.Data.dll!MySql.Data.MySqlClient.MySqlParameterCollection.GetParameterFlexible(string parameterName = "pStart", bool throwOnNotFound = true) Line 459 C# MySql.Data.dll!MySql.Data.MySqlClient.StoredProcedure.Resolve() Line 157 + 0x25 bytes C# MySql.Data.dll!MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(System.Data.CommandBehavior behavior = SequentialAccess) Line 405 + 0xb bytes C# MySql.Data.dll!MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(System.Data.CommandBehavior behavior = SequentialAccess) Line 884 + 0xb bytes C# System.Data.dll!System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(System.Data.CommandBehavior behavior) + 0xb bytes System.Data.dll!System.Data.Common.DbDataAdapter.FillInternal(System.Data.DataSet dataset = {System.Data.DataSet}, System.Data.DataTable[] datatables = null, int startRecord = 0, int maxRecords = 0, string srcTable = "Table", System.Data.IDbCommand command = {MySql.Data.MySqlClient.MySqlCommand}, System.Data.CommandBehavior behavior) + 0x83 bytes System.Data.dll!System.Data.Common.DbDataAdapter.Fill(System.Data.DataSet dataSet, int startRecord, int maxRecords, string srcTable, System.Data.IDbCommand command, System.Data.CommandBehavior behavior) + 0x120 bytes System.Data.dll!System.Data.Common.DbDataAdapter.Fill(System.Data.DataSet dataSet) + 0x5f bytes How to repeat: - Call a stored procedure for which the parameter names supplied do not match those in the stored procedure itself. In 5.2.6 with UseProcedureBodies = true : failure In 5.2.6 with UseProcedureBodies = false : success In 5.2.7 with UseProcedureBodies = true : failure In 5.2.7 with UseProcedureBodies = false : failure Suggested fix: --- C:/DOCUME~1/yvan/LOCALS~1/Temp/StoredProcedure.c-revBASE.svn001.tmp.cs Wed Jul 15 16:15:28 2009 +++ C:/Documents and Settings/yvan/My Documents/Visual Studio 2008/Projects/MisMabel Trunk/MySql/Source/StoredProcedure.cs Wed Jul 15 16:15:13 2009 @@ -63,12 +63,16 @@ private DataSet GetParameters(string procName) { - // if we can use mysql.proc, then do so - //if (Connection.Settings.UseProcedureBodies) DataSet ds = Connection.ProcedureCache.GetProcedure(Connection, procName); - // if we got both proc and parameter data then just return - if (ds.Tables.Count == 2) return ds; + // if we can use mysql.proc and UseProcedureBodies == true, then do so + if(ds.Tables.Count == 2) + { + if (Connection.Settings.UseProcedureBodies) return ds; + // we got the parameters, but ignore them. + if(ds.Tables.Contains("Procedure Parameters")) + ds.Tables.Remove("Procedure Parameters"); + } // we were not able to retrieve parameter data so we have to make do by // adding the parameters from the command object to our table