Bug #45380 Parsing bug when there is space between parameter type and parameter name
Submitted: 8 Jun 2009 20:24 Modified: 8 Apr 2010 14:21
Reporter: Pavel Bazanov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:5.2.5, 5.2.6, 6.0.3 OS:Any
Assigned to: CPU Architecture:Any

[8 Jun 2009 20:24] Pavel Bazanov
Description:
Hello,

I found a small bug in parsing stored procedure parameters, when there are space between parameter data type and size (see How to repeat section). 
The problem is in ISSchemaProvider.ParseProcedureBody() method. As a result, only first parameter gets resolved.

PS. Can you please finally comment my other bug report: http://bugs.mysql.com/bug.php?id=45098 ?

How to repeat:
[Test]
public void ParsingBugTest()
{
	MySqlCommand command = OpenConnectionAndCreateCommand();
	command.CommandText = "DROP FUNCTION IF EXISTS `TestFunction`";
	command.ExecuteNonQuery();
	command.CommandText =
		@"CREATE FUNCTION `TestFunction`(A INTEGER (11), B INTEGER (11), C VARCHAR (20)) 
			RETURNS int(11)
		  RETURN 1";
	command.ExecuteNonQuery();
	command.CommandType = CommandType.StoredProcedure;
	command.CommandText = "TestFunction";
	command.Parameters.AddWithValue("?A", 1);
	command.Parameters.AddWithValue("?B", 2);
	command.Parameters.AddWithValue("?C", "test");
	command.ExecuteNonQuery();
}

The test fails with the following error:
MySql.Data.MySqlClient.MySqlException: Incorrect number of arguments for FUNCTION mydb.TestFunction; expected 3, got 1
[9 Jun 2009 15:58] Tonci Grgin
Hi Pavel and thanks for your report. I think this is a duplicate of Bug#41034. Can you please confirm my suspicion.
[9 Jun 2009 15:59] Tonci Grgin
Btw, Bug#41034 is fixed in 5.2.6.
[10 Jun 2009 15:49] Pavel Bazanov
Yes, bug 41034 looks very similar to this one, but it's hard to be sure, because the test in my first post fails on both 5.2.5 and 6.0.3. Also I made 2 more tests:

The following runs ok on both 5.2.5 and 6.0.3:

[Test]
public void ParsingBugTest2()
{
	MySqlCommand command = OpenConnectionAndCreateCommand();
	command.CommandText = "DROP PROCEDURE IF EXISTS `TestProcedure`";
	command.ExecuteNonQuery();
	command.CommandText =
		@"CREATE PROCEDURE TestProcedure(myparam char (10)) SELECT 100";
	command.ExecuteNonQuery();
	command.CommandType = CommandType.StoredProcedure;
	command.CommandText = "TestProcedure";
	command.Parameters.AddWithValue("?myparam", "dfgd");
	Assert.AreEqual(100, command.ExecuteScalar());
}

The following test fails with "System.ArgumentException: Parameter '3' not found in the collection." exception on BOTH 5.2.5 and 6.0.3:

[Test]
public void ParsingBugTest3()
{
	MySqlCommand command = OpenConnectionAndCreateCommand();
	command.CommandText = "DROP PROCEDURE IF EXISTS `TestProcedure`";
	command.ExecuteNonQuery();
	command.CommandText =
		@"CREATE PROCEDURE TestProcedure(myparam double (11,3)) SELECT 100";
	command.ExecuteNonQuery();
	command.CommandType = CommandType.StoredProcedure;
	command.CommandText = "TestProcedure";
	command.Parameters.AddWithValue("?myparam", 10.5);
	Assert.AreEqual(100, command.ExecuteScalar());
}
[18 Jun 2009 17:04] Reggie Burnett
I believe this is a duplicate of bug #41034 which was fixed in 5.2.6
[19 Jun 2009 10:48] Pavel Bazanov
I am not sure if it is a duplicate or not, but I am sure that the following test fails on 5.2.5, 5.2.6 and 6.0.3:

[Test]
public void ParsingBugTest()
{
	MySqlCommand command = OpenConnectionAndCreateCommand();
	command.CommandText = "DROP FUNCTION IF EXISTS `TestFunction`";
	command.ExecuteNonQuery();
	command.CommandText =
		@"CREATE FUNCTION `TestFunction`(A INTEGER (11), B INTEGER (11), C VARCHAR (20)) 
			RETURNS int(11)
			RETURN 1";
	command.ExecuteNonQuery();
	command.CommandType = CommandType.StoredProcedure;
	command.CommandText = "TestFunction";
	command.Parameters.AddWithValue("?A", 1);
	command.Parameters.AddWithValue("?B", 2);
	command.Parameters.AddWithValue("?C", "test");
	command.ExecuteNonQuery();
}

And the next one passes on 5.2.6 and fails on 5.2.5 and 6.0.3:

[Test]
public void ParsingBugTest3()
{
	MySqlCommand command = OpenConnectionAndCreateCommand();
	command.CommandText = "DROP PROCEDURE IF EXISTS `TestProcedure`";
	command.ExecuteNonQuery();
	command.CommandText =
		@"CREATE PROCEDURE TestProcedure(myparam double (11,3)) SELECT 100";
	command.ExecuteNonQuery();
	command.CommandType = CommandType.StoredProcedure;
	command.CommandText = "TestProcedure";
	command.Parameters.AddWithValue("?myparam", 10.5);
	Assert.AreEqual(100, command.ExecuteScalar());
}
[19 Jun 2009 10:51] Pavel Bazanov
I am sorry, ParsingBugTest3() passes on 5.2.6. 
But, again, ParsingBugTest() fails on ALL versions.
[19 Jun 2009 10:52] Pavel Bazanov
changed versions in which the bug appears.
[19 Jun 2009 10:55] Pavel Bazanov
ParsingBugTest3() passes on 6.0.3. ****
I am sorry it's not possible to edit comments here :(
[19 Jun 2009 14:09] Reggie Burnett
Pavel

I plugged ParsingBugTest into 5.2.6 and 6.0.3 here and it works fine.
[19 Jun 2009 15:03] Pavel Bazanov
Reggie, what version of MySQL server are you using? I am using 6.0.2.

PS. What happenned to 6.x series? It is removed from downloads.
[19 Jun 2009 15:27] Reggie Burnett
6.x series of what?  The connector/net 6.0 can still be found at http://dev.mysql.com/downloads/connector/net/6.0.html

I was using mysql 5.1.35 x86
[19 Jun 2009 15:29] Reggie Burnett
Pavel 

I just tried ParsingBugTest on mysql 6.0.11 with c/net 5.2.6.  Pass
[19 Jun 2009 16:24] Pavel Bazanov
6.x series of MySql server.

I am downloading 5.1.35 now and will test on virtual machine. Maybe the problem is in how server describes stored procedures?

If you want I can prepare a simple test project.
[19 Jun 2009 17:46] Pavel Bazanov
Yes, the problem is in MySql server. For example, the test passes on MySql server 6.0.8 with c/Net 2.5.6 and fails on MySql server 6.0.2 with c/Net 2.5.6
[19 Jun 2009 19:47] Reggie Burnett
Bug doesn't appear in later versions of MySQL
[6 Jul 2009 15:48] Pavel Bazanov
The bug still exists in MySql server 5.1.36.
When using Connector.NET 6.0.4 it throws OutOfMemoryException.
When using Connector.NET 5.2.6 it throws MySqlException with message "Incorrect number of arguments for FUNCTION test.TestFunction; expected 3, got 1".
[6 Jul 2009 16:28] Pavel Bazanov
PS. Why the bug is fixed in 6.x branch and is not fixed in 5.1.x branch? I thought you should fix bugs in all branches?
[14 Jul 2009 9:49] Pavel Bazanov
Any news?
[25 Jul 2009 16:00] Pavel Bazanov
Guys, you need to open this bug, because it still exists in later versions of MySQL, as I wrote above.
[4 Sep 2009 22:47] Pavel Bazanov
The bug doesn't appear in MySql Server 5.1.38 & Connector/Net 6.1.1
[2 Apr 2010 21:06] Pavel Bazanov
Hello,
The bug reincarnated in MySql 5.1.38 and MySqlConnector 6.3.1:

[Test]
public void Bug45380ShouldNotAppearAnymore()
{
	using (MySqlConnection conn = DB.ConnectToDb())
	{
		const string DropFunctionSql = "DROP FUNCTION IF EXISTS `TestFunction`";
		try
		{
			var command = new MySqlCommand(DropFunctionSql, conn);
			command.ExecuteNonQuery();
			command.CommandText =
				@"CREATE FUNCTION `TestFunction`(A INTEGER (11), B INTEGER (11), C VARCHAR (20)) 
					RETURNS int(11)
					RETURN 1";
			command.ExecuteNonQuery();
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = "TestFunction";
			command.Parameters.AddWithValue("?A", 1);
			command.Parameters.AddWithValue("?B", 2);
			command.Parameters.AddWithValue("?C", "test");
			// ArgumentNullException: Key cannot be null. Parameter name: key
			command.ExecuteNonQuery();
		}
		finally
		{
			new MySqlCommand(DropFunctionSql, conn).ExecuteNonQuery();
		}
	}
}
[8 Apr 2010 14:21] Pavel Bazanov
As noted by Tonci in bug52562, I forgot to add return parameter (sorry).
But I think we need to discuss the exception being thrown. It doesn't seem meaningful in such situation. I think MySqlException saying something like "Return parameter must be specified." should be thrown instead.