MySQL Bugs: #45380: Parsing bug when there is space between parameter type and parameter name

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

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

Hi Pavel and thanks for your report. I think this is a duplicate of Bug#41034. Can you please confirm my suspicion.

Btw, Bug#41034 is fixed in 5.2.6.

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());
}

I believe this is a duplicate of bug #41034 which was fixed in 5.2.6

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());
}

I am sorry, ParsingBugTest3() passes on 5.2.6. 
But, again, ParsingBugTest() fails on ALL versions.

changed versions in which the bug appears.

ParsingBugTest3() passes on 6.0.3. ****
I am sorry it's not possible to edit comments here :(

Pavel

I plugged ParsingBugTest into 5.2.6 and 6.0.3 here and it works fine.

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.

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

Pavel 

I just tried ParsingBugTest on mysql 6.0.11 with c/net 5.2.6.  Pass

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.

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

Bug doesn't appear in later versions of MySQL

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".

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?

Any news?

Guys, you need to open this bug, because it still exists in later versions of MySQL, as I wrote above.

The bug doesn't appear in MySql Server 5.1.38 & Connector/Net 6.1.1

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();
		}
	}
}

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.