MySQL Bugs: #40068: Extra white space in the parameter list of a stored procedure causes failure.

Bug #40068	Extra white space in the parameter list of a stored procedure causes failure.
Submitted:	15 Oct 2008 21:35	Modified:	16 Nov 2008 18:30
Reporter:	Mike Harman	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.3	OS:	Windows (Vista(all flavors))
Assigned to:		CPU Architecture:	Any
Tags:	.net Connector, stored procedure

Description:
When creating or editing a stored procedure, an extra space between the parameter (in or out) and the parameter type or an extra space after the parameter type before the comma, causes the stored procedure to register the incorrect number of parameters being passed. For example the following will run correctly:

CREATE DEFINER=`root`@`localhost` PROCEDURE `spU_Addresses`(IN AddressId INT, IN Address1 VARCHAR(50), IN Address2 VARCHAR(50), IN City VARCHAR(35), IN StateCode CHAR(2), IN PostalCode CHAR(7), IN EncodedFips CHAR(5), OUT ErrorCode INT)

However the following will result in an exception stating, "expected 8 parameters, got 2.":

CREATE DEFINER=`root`@`localhost` PROCEDURE `spU_Addresses`(IN AddressId  INT , IN Address1  VARCHAR(50) , IN Address2  VARCHAR(50) , IN City  VARCHAR(35) , IN StateCode  CHAR(2) , IN PostalCode  CHAR(7) , IN EncodedFips  CHAR(5) , OUT ErrorCode  INT )

How to repeat:
Create a stored procedure with extra spaces between parameters and parameter types and parameter separaters. Execute the stored procedure from a funcation using connector 5.2.3

Suggested fix:
This is how we solved the problem for our web service:

while (paramList.IndexOf("  ", StringComparison.OrdinalIgnoreCase) >= 0)
                paramList = paramList.Replace("  ", " ");

Hi Mike and thanks for your report.

It looked rather intriguing so I checked against MySQL server 5.0.68 and with latest sources of c/NET 5.2 branch... No luck repeating.

Here's my test case, if you think I missed something please attach self-sufficient test case like this one which proves your point. Does my test fail for you?

    MySqlConnection conn = new MySqlConnection();
     conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=***;PORT=xxx;Allow Zero Datetime=True";
     conn.Open();
     MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS Bug40068", conn);
     cmdCreateTable.CommandTimeout = 0;
     cmdCreateTable.ExecuteNonQuery();
     cmdCreateTable.CommandText = "CREATE TABLE Bug40068 (id BIGINT(10) UNSIGNED AUTO_INCREMENT, PRIMARY KEY (id)) ";
     cmdCreateTable.ExecuteNonQuery();
     cmdCreateTable.CommandText = ("drop procedure if exists spTestBug40068");
     cmdCreateTable.ExecuteNonQuery();
     
     MySqlCommand cmd = new MySqlCommand("spTestBug40068", conn);
     cmdCreateTable.CommandText = ("CREATE PROCEDURE spTestBug40068 ( IN  id  BIGINT  UNSIGNED , IN  tst CHAR(50)) " +
                      "BEGIN INSERT INTO Bug40068 VALUES (id); END");
     cmdCreateTable.ExecuteNonQuery();
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Parameters.AddWithValue("?id", (ulong.MaxValue - 1000));
     cmd.Parameters[0].DbType = DbType.UInt64;
     cmd.Parameters[0].Direction = ParameterDirection.Input;

     cmd.Parameters.AddWithValue("?tst", "Test");
     cmd.Parameters[0].DbType = DbType.String;
     cmd.Parameters[0].Direction = ParameterDirection.Input;
            
     cmd.ExecuteNonQuery();
     Console.WriteLine("Done Test");

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".