MySQL Bugs: #45952: DeriveParameters command throws a null reference exception

Bug #45952	DeriveParameters command throws a null reference exception
Submitted:	5 Jul 2009 17:30	Modified:	8 Jul 2009 13:52
Reporter:	Eu Mesmo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.0.4	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	DeriveParameters, null exception

Description:
I have downloaded 5.2.6 and 6.0.4 Connector/Net sources and noticed an strange behavior.

The 'DeriveParameters' command uses a DataSet with two DataTables ('parameters' and 'procTable'). These DataTables are created by 'GetProcData' command (GetProcedure -> AddNew -> GetProcData).
The 5.2.6 version of 'GetProcData' creates the  'parameters' DataTable by a isp.GetProcedureParameters() which uses the procedure SQL Create script to build a list of parameters in case of the MySql Server version is minor to 6.

The problem with 6.0.4 version of 'GetProcData' occurs because the 'parameters' DataTable is only created with a certain circumstances. These circumstances are if MySql Server version is at least 6.0.6 (in my case I'm using 5.0.67-community) or if the UseProcedureBodies parameter is true. 
My web application host forces me to set UseProcedureBodies as false, so, in my scenario, the 'parameters' DataTable is never created and the DataSet  returns without errors. Back to 'DeriveParameters' command, as the DataSet has only one valid DataTable, its throws a null reference exception because the 'parameters' DataTable (null) is used in a ‘for each’ loop.

Am I missing something or I just can't use the Connector/Net 6.0.4 version with my 5.0.67-community MySql Server?

How to repeat:
Simply use the DeriveParameters command with a connection string that sets the "user procedure bodies" as false in a db connection with a 5.0.67 MySql Server.

Suggested fix:
Implement the same method used at 5.2.6 Connector/Net version when the server version is minor to 6.0.6 or when is not possible to use procedure bodies.

Something like:
DataTable dt = new DataTable("Procedure Parameters");
dt.Columns.Add("SPECIFIC_CATALOG", typeof(string));
dt.Columns.Add("SPECIFIC_SCHEMA", typeof(string));
dt.Columns.Add("SPECIFIC_NAME", typeof(string));
dt.Columns.Add("ORDINAL_POSITION", typeof(Int32));
dt.Columns.Add("PARAMETER_MODE", typeof(string));
dt.Columns.Add("PARAMETER_NAME", typeof(string));
dt.Columns.Add("DATA_TYPE", typeof(string));
dt.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(Int32));
dt.Columns.Add("CHARACTER_OCTET_LENGTH", typeof(Int32));
dt.Columns.Add("NUMERIC_PRECISION", typeof(byte));
dt.Columns.Add("NUMERIC_SCALE", typeof(Int32));
dt.Columns.Add("CHARACTER_SET_NAME", typeof(string));
dt.Columns.Add("COLLATION_NAME", typeof(string));
dt.Columns.Add("DTD_IDENTIFIER", typeof(string));
dt.Columns.Add("ROUTINE_TYPE", typeof(string));
GetParametersFromShowCreate(dt, restrictions, routines);

return dt;

Hi Eu and thanks for your report.

Can you please confirm a suspicion of mine. Install c/NET 5.2.7 and re-test, I suspect it will fail.

Also, please check https://docsrva.mysql.com/idocs/changelog.cgi?m=showchangelog&fromversion=6.0.6&toversion=... :

Incompatible Change: A change has been made to the way that the server handles prepared statements. This affects prepared statements processed at the SQL level (using the PREPARE statement) and those processed using the binary client-server protocol (using the mysql_stmt_prepare() C API function). ...

Where do I can download 5.2.7 version of C/Net?

I don't have access to https://docsrva.mysql.com/idocs

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/78160

fixed in 6.0.5

An entry was added to the 6.0.5 changelog:

In MySQL Connector/NET 6.0.4 using GetProcData generated an error because the parameters data table was only created if MySQL Server was at least version 6.0.6, or if the UseProcedureBodies connection string option was set to true.

Also the DeriveParameters command generated a null reference exception. This was because the parameters data table, which was null, was used in a for each loop.