Bug #52562 Sometimes we need to reload cached function parameters
Submitted: 2 Apr 2010 20:36 Modified: 5 May 2010 11:06
Reporter: Pavel Bazanov Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S4 (Feature request)
Version:6.3.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: "Incorrect number of arguments for FUNCTION", "Key cannot be null. Parameter name: key"

[2 Apr 2010 20:36] Pavel Bazanov
Description:
Hi guys,
I decided to add tests into my project for all the bugs which I sent you for the last years and during this I have found 2-3 new bugs :) so here we go.
Actually, this bug report shows 2 bugs or, if you wish, 1 bug + 1 feature request.

Look at the how to repeat section for details.

First, there is a bug in the first part of the test. The code for first part of the test is taken from my Bug45380 and I will update that bug in a moment.

In this bug report let's discuss the second exception: 
MySqlException: Incorrect number of arguments for FUNCTION test.TestFunction; expected 0, got 3.

Obviously, we get this exception because of MySqlConnector caches function parameters. It's normal. But I suggest that maybe we try to reload function parameter cache when we see that number or type of arguments is incorrect.
What do you think? If you agree with me, then I think implementing it should not be very difficult.

PS. This time I really checked if this case is already reported :)

How to repeat:
public void CachingSpParametersTest()
{
	using (MySqlCommand command = OpenConnectionAndCreateCommand())
	{
		try
		{
			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");
			// ArgumentNullException: Key cannot be null. Parameter name: key
			command.ExecuteNonQuery();
		}
		catch (Exception ex)
		{
			Debug.WriteLine(ex.Message);
		}

		try
		{
			command.CommandType = CommandType.Text;
			command.CommandText = "DROP FUNCTION IF EXISTS `TestFunction`";
			command.ExecuteNonQuery();
			command.CommandText =
				@"CREATE FUNCTION `TestFunction`() 
			RETURNS varchar(20)
			RETURN ''";
			command.ExecuteNonQuery();
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = "TestFunction";
			MySqlParameter returnParam = new MySqlParameter();
			returnParam.ParameterName = "?RetVal_";
			returnParam.Direction = ParameterDirection.ReturnValue;
			command.Parameters.Add(returnParam);
			// MySqlException: 
			// Incorrect number of arguments for FUNCTION test.TestFunction; expected 0, got 3
			command.ExecuteNonQuery();
		}
		catch (Exception ex)
		{
			Debug.WriteLine(ex.Message);
		}
	}
}
[6 Apr 2010 7:36] Tonci Grgin
Hey Pavel and thanks for your new report.

At a first glance I do agree with you but I have to test it properly. So please include your connection string with all the specific options used.
[6 Apr 2010 10:01] Pavel Bazanov
Hi Tonci, 
The connection string I use is:
"server={0};database={1};User Id={2};password={3};Character Set=cp1251"
[8 Apr 2010 10:44] Tonci Grgin
Pavel, regarding first part of code, we can only debate if the error returned is meaningful... It should be thrown cause you have nowhere to return the value from function.

As per second part of test, it just works for me...
  Environment: 5.1.31-log MySQL Community Server (GPL) on OpenSolaris x64 host. VS2008Pro running on W2K8R2 x64. c/NET 6.3.1, running against trunk code.

    MySqlConnection conn = new MySqlConnection();
    conn.ConnectionString = "DataSource=opensol;Database=test;UserID=**;Password=**;PORT=**;Character Set=cp1251";
    conn.Open();

    MySqlCommand cmdQry = new MySqlCommand();
    cmdQry.Connection = conn;
    cmdQry.CommandTimeout = 0;
    cmdQry.CommandText = "DROP FUNCTION IF EXISTS `TestFunction`";
    cmdQry.ExecuteNonQuery();

    cmdQry.CommandText =
      @"CREATE FUNCTION `TestFunction`(A INTEGER (11), B INTEGER (11), C VARCHAR (20)) 
      RETURNS int(11)
      RETURN 1";
    cmdQry.ExecuteNonQuery();

    try {
          cmdQry.CommandType = CommandType.StoredProcedure;
          cmdQry.CommandText = "TestFunction";
          cmdQry.Parameters.AddWithValue("?A", 1);
          cmdQry.Parameters.AddWithValue("?B", 2);
          cmdQry.Parameters.AddWithValue("?C", "test");

          MySqlParameter returnParam = new MySqlParameter();
          returnParam.ParameterName = "?RetVal_";
          returnParam.Direction = ParameterDirection.ReturnValue;
          cmdQry.Parameters.Add(returnParam);
          cmdQry.ExecuteNonQuery();
          Console.WriteLine("Returned: " + returnParam.Value.ToString());
        }
        catch (MySql.Data.MySqlClient.MySqlException ex)
        {
           MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

produces:
...
WindowsFormsApplication1.vshost.exe Information: 0 : Retrieving procedure metadata for `test`.`TestFunction` from server.
Returned: 1
The thread 0x1f4 has exited with code 0 (0x0).
The thread 0x8a8 has exited with code 0 (0x0).
The program '[2424] WindowsFormsApplication1.vshost.exe: Managed' has exited with code 0 (0x0).

Did I miss anything?
[8 Apr 2010 14:24] Pavel Bazanov
>Pavel, regarding first part of code, we can only debate if the error returned is meaningful... It should be thrown cause you have nowhere to return the value from function.

I am sorry for being so unattentive. I agree that we should discuss the exception being thrown. Here is what I wrote in the comment to bug45380:

"It doesn't seem meaningful in such situation. I think MySqlException saying something like "Return parameter must be specified." should be thrown instead."

Do we need to open a new report (Feature request) for this issue?
[8 Apr 2010 14:41] Pavel Bazanov
>Did I miss anything?

I will explain better. Here is how to reproduce the bug: 

1) First we create a function with, say, 3 arguments
2) then we execute it
3) then we drop it
4) then we create a function with the same name, but with other number of arguments/return type.
5) then we execute it and get an exception.

Here is slightly modified test case:

public void CachingSpParametersTest()
{
	using (var conn = new MySqlConnection())
	{
		try
		{
			conn.ConnectionString = string.Format(
				"server={0};database={1};User Id={2};password={3};Character Set=cp1251",
				txtHost.Text,
				txtDatabase.Text,
				txtUsername.Text,
				txtPassword.Text);

			conn.Open();
			var command = new MySqlCommand("DROP FUNCTION IF EXISTS `TestFunction`", 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");
			command.Parameters.Add("?RetVal", MySqlDbType.Int32).Direction = ParameterDirection.ReturnValue;
			command.ExecuteNonQuery();

			command.CommandType = CommandType.Text;
			command.CommandText = "DROP FUNCTION IF EXISTS `TestFunction`";
			command.ExecuteNonQuery();
			command.CommandText =
				@"CREATE FUNCTION `TestFunction`() 
			RETURNS varchar(20)
			RETURN ''";
			command.ExecuteNonQuery();
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = "TestFunction";
			var returnParam = new MySqlParameter();
			returnParam.ParameterName = "?RetVal_";
			returnParam.Direction = ParameterDirection.ReturnValue;
			command.Parameters.Add(returnParam);
			// MySqlException: 
			// Incorrect number of arguments for FUNCTION test.TestFunction; expected 0, got 3
			command.ExecuteNonQuery();
		}
		catch (MySqlException ex)
		{
			MessageBox.Show(
				string.Format("Error {0} has occurred: {1}", ex.Number, ex.Message), 
				"Error", 
				MessageBoxButtons.OK, 
				MessageBoxIcon.Error);
		}
	}
}
[8 Apr 2010 15:04] Tonci Grgin
Ok Pavel, will see to this tomorrow.
[14 Apr 2010 10:54] Pavel Bazanov
Hi Tonchi,
Any news?
[22 Apr 2010 15:30] Bugs System
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/106370

808 Reggie Burnett	2010-04-22
      - improved our procedure caching so that if you drop and recreate a proc with a different number of parameters
        it will find the new proc as long as your are actually using the right number of parameters (bug #52562)
[22 Apr 2010 15:41] Reggie Burnett
Fixed in 6.0.6, 6.1.4, 6.2.4, and 6.3.2
[22 Apr 2010 16:39] Tony Bedford
An entry has been added to the 6.0.6, 6.1.4, 6.2.4, and 6.3.2 changelogs:

Procedure cacheing had a problem whereby if you created a procedure, dropped it, and recreated it with a different number of parameters an exception was generated.

MySQL Connector/NET has been changed so that if the procedure is recreated with a different number of parameters, it will still be recognized.
[23 Apr 2010 15:55] Pavel Bazanov
The bug still appears in the latest revision from repository.
Your test passes, but mine fails:

[Test]
public void Bug52562ShouldNotAppearAnymore()
{
	using (var conn = DB.ConnectToDb())
	{
		const string DropFunctionSql = "DROP FUNCTION IF EXISTS `TestFunction`";
		var command = new MySqlCommand("", conn);
		try
		{
			command.CommandText = DropFunctionSql;
			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.Parameters.Add("?RetVal", MySqlDbType.Int32).Direction = ParameterDirection.ReturnValue;
			command.ExecuteNonQuery();

			command.CommandType = CommandType.Text;
			command.CommandText = "DROP FUNCTION IF EXISTS `TestFunction`";
			command.ExecuteNonQuery();
			command.CommandText =
				@"CREATE FUNCTION `TestFunction`() 
				RETURNS varchar(20)
				RETURN ''";
			command.ExecuteNonQuery();
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = "TestFunction";
			var returnParam = new MySqlParameter();
			returnParam.ParameterName = "?RetVal_";
			returnParam.Direction = ParameterDirection.ReturnValue;
			command.Parameters.Add(returnParam);
			// MySqlException: 
			// Incorrect number of arguments for FUNCTION test.TestFunction; expected 0, got 3
			command.ExecuteNonQuery();
		}
		finally
		{
			command.CommandType = CommandType.Text;
			command.CommandText = DropFunctionSql;
			command.ExecuteNonQuery();
		}
	}
}
[1 May 2010 22:25] Pavel Bazanov
Please fix it properly.
[5 May 2010 10:37] Tonci Grgin
Pavel, first of all, I think command.Parameters.Clear() is introduced for a reason, so I have it in proper place. Without it, I get the exception as reported but I do not think it is a bug...

Test case:
     MySqlConnection conn = new MySqlConnection();
     conn.ConnectionString = "DataSource=**;Database=test;UserID=**;Password=**;PORT=3306";
     conn.Open();

     MySqlCommand cmdQry = new MySqlCommand();
     cmdQry.Connection = conn;
     cmdQry.CommandTimeout = 0;
     cmdQry.CommandText = "DROP FUNCTION IF EXISTS `TestFunction`";
     cmdQry.ExecuteNonQuery();

     cmdQry.CommandText =
@"CREATE FUNCTION `TestFunction`(A INTEGER (11), B INTEGER (11), C VARCHAR(20)) 
RETURNS int(11)
RETURN 1";
     cmdQry.ExecuteNonQuery();
     try {
         cmdQry.CommandType = CommandType.StoredProcedure;
         cmdQry.CommandText = "TestFunction";
         cmdQry.Parameters.AddWithValue("?A", 1);
         cmdQry.Parameters.AddWithValue("?B", 2);
         cmdQry.Parameters.AddWithValue("?C", "test");

         MySqlParameter returnParam = new MySqlParameter();
         returnParam.ParameterName = "?RetVal_";
         returnParam.Direction = ParameterDirection.ReturnValue;
         cmdQry.Parameters.Add(returnParam);
         cmdQry.ExecuteNonQuery();
         Console.WriteLine("Returned1: " + returnParam.Value.ToString());

         cmdQry.CommandType = CommandType.Text;
         cmdQry.CommandText = "DROP FUNCTION IF EXISTS `TestFunction`";
         cmdQry.ExecuteNonQuery();

         cmdQry.CommandText =
         @"CREATE FUNCTION `TestFunction`() 
	    RETURNS VARCHAR(20)
	    RETURN 'nothing'";
         cmdQry.ExecuteNonQuery();

         cmdQry.CommandType = CommandType.StoredProcedure;
         cmdQry.Parameters.Clear();
         cmdQry.CommandText = "TestFunction";
         MySqlParameter retParam2 = new MySqlParameter();
         retParam2.ParameterName = "?RetVal";
         retParam2.Direction = ParameterDirection.ReturnValue;
         cmdQry.Parameters.Add(retParam2);
         cmdQry.ExecuteNonQuery();
         Console.WriteLine("Returned2: " + retParam2.Value.ToString());

     }
     catch (MySql.Data.MySqlClient.MySqlException ex)
     {
         MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
                    "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }

produces:
Returned1: 1
Returned2: nothing

against latest revision. If you think this is still a bug and that using command.Parameters.Clear() should not be mandatory, please say so and we'll consult Reggie.
[5 May 2010 10:41] Tonci Grgin
General query log proves c/NET does nothing weird/wrong:
100505 12:39:15   93 Connect	root@QCW2K8.dummy.porta.siemens.net on test
		   93 Query	SHOW VARIABLES
		   93 Query	SHOW COLLATION
		   93 Query	SET NAMES utf8
		   93 Query	SET character_set_results=NULL
		   93 Init DB	test
		   93 Query	DROP FUNCTION IF EXISTS `TestFunction`
		   93 Query	CREATE FUNCTION `TestFunction`(A INTEGER (11), B INTEGER (11), C VARCHAR (20)) 
			RETURNS int(11)
			RETURN 1
		   93 Query	SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'test' AND ROUTINE_NAME LIKE 'TestFunction'
		   93 Query	SHOW CREATE FUNCTION `test`.`TestFunction`
		   93 Query	SHOW CHARSET
		   93 Query	SET @_cnet_param_RetVal_=`test`.`TestFunction`(1, 2, 'test')
		   93 Query	SELECT @_cnet_param_RetVal_
		   93 Query	DROP FUNCTION IF EXISTS `TestFunction`
		   93 Query	CREATE FUNCTION `TestFunction`() 
			            RETURNS VARCHAR(20)
			            RETURN 'nothing'
		   93 Query	SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'test' AND ROUTINE_NAME LIKE 'TestFunction'
		   93 Query	SHOW CREATE FUNCTION `test`.`TestFunction`
		   93 Query	SET @_cnet_param_RetVal=`test`.`TestFunction`()
		   93 Query	SELECT @_cnet_param_RetVal
100505 12:39:26   93 Quit
[5 May 2010 11:06] Pavel Bazanov
Tonci, please forgive me!
In my daily work I use high-level DbManager, so I don't have to care about parameters collection, that is why I forgot to clear it...
I am sorry..
[5 May 2010 11:08] Tonci Grgin
Pavel, no problem. Anyways, there was a legit problem here Reggie fixed.

Thanks for your interest in MySQL.