| 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: | |
| 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
[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.
