Incorrect number of arguments for PROCEDURE

[Mimetis]

I don't why, but it seems there is somewhere an internal cache preventing to execute a modified stored procedure.

Here is my setup, hopefully self explanatory:

private static void BugExecutingProcedureMySql()
{
    var databaseName = "test";

    // creating database
    using (var connection = GetMyConnection())
    {
        connection.Open();
        CreateMySqlDatabase(databaseName, connection);
        connection.Close();
    }

    // creating 1st version of table and stored proc (then call it)
    using (var connection = GetMyConnection(databaseName))
    {
        connection.Open();

        CreateMySqlTable(databaseName, connection);
        CreateMySqlProcedure(databaseName,  connection);
        // call 1st version of my stored procedure
        InsertMySqlRecord(connection);

        connection.Close();
    }

    // creating 2nd version of table and stored proc (then call it)
    using (var connection = GetMyConnection(databaseName))
    {
        connection.Open();
        // Adding one column to table
        AlterMySqlTable(databaseName, connection);
        // Creating 2nd version of my stored procedure
        CreateMySqlProcedure2(databaseName, connection);
        // FAIL: Trying to call this new stored procedure
        InsertMySqlRecord2(connection);

        connection.Close();
    }

}

// Creating my database
private static void CreateMySqlDatabase(string databaseName, MySqlConnection mySqlConnection)
{
    string sqlDB = $"DROP DATABASE IF EXISTS `{databaseName}`; CREATE DATABASE `{databaseName}`;";
    var cmd = new MySqlCommand(sqlDB, mySqlConnection);
    cmd.ExecuteNonQuery();

}

// Creating my table
private static void CreateMySqlTable(string databaseName, MySqlConnection mySqlConnection)
{

    string sql = $"DROP TABLE IF EXISTS `F`; " +
        $" CREATE TABLE `F` (" +
        $" `ID` char(36) NOT NULL " +
        $",`F1` int NULL" +
        $",`F2` longtext NOT NULL" +
        $",`F3` longtext NULL" +
        $", PRIMARY KEY(`ID`))";

    var cmd = new MySqlCommand(sql, mySqlConnection);
    cmd.ExecuteNonQuery();
}

// Adding new column to my table
private static void AlterMySqlTable(string databaseName, MySqlConnection mySqlConnection)
{
    var cmd = new MySqlCommand($"ALTER TABLE `F` ADD `F4` longtext NULL;", mySqlConnection);
    cmd.ExecuteNonQuery();
}

// Creating 1st version of the stored procedure
private static void CreateMySqlProcedure(string databaseName, MySqlConnection mySqlConnection)
{
    var procedure = new StringBuilder();
    procedure.AppendLine($"DROP PROCEDURE IF EXISTS `insert`;");
    procedure.AppendLine($"CREATE PROCEDURE `insert` (");
    procedure.AppendLine($" in_ID char(36)");
    procedure.AppendLine($",in_F1 int");
    procedure.AppendLine($",in_F2 longtext");
    procedure.AppendLine($",in_F3 longtext)");
    procedure.AppendLine($"BEGIN");
    procedure.AppendLine($"  INSERT INTO `F` (");
    procedure.AppendLine($"  `ID`, `F1`, `F2`, `F3`) ");
    procedure.AppendLine($"VALUES (");
    procedure.AppendLine($"  in_ID, in_F1, in_F2, in_F3);");
    procedure.AppendLine($"END;");

    var cmd = new MySqlCommand(procedure.ToString(), mySqlConnection);

    cmd.ExecuteNonQuery();
}

// Creating 2nd version of the stored procedure
private static void CreateMySqlProcedure2(string databaseName, MySqlConnection mySqlConnection)
{
    var procedure = new StringBuilder();
    procedure.AppendLine($"DROP PROCEDURE IF EXISTS `insert`;");
    procedure.AppendLine($"CREATE PROCEDURE `insert` (");
    procedure.AppendLine($" in_ID char(36)");
    procedure.AppendLine($",in_F1 int");
    procedure.AppendLine($",in_F2 longtext");
    procedure.AppendLine($",in_F3 longtext");
    procedure.AppendLine($",in_F4 longtext)");
    procedure.AppendLine($"BEGIN");
    procedure.AppendLine($"  INSERT INTO `F` (");
    procedure.AppendLine($"  `ID`, `F1`, `F2`, `F3`, `F4`) ");
    procedure.AppendLine($"VALUES (");
    procedure.AppendLine($"  in_ID, in_F1, in_F2, in_F3, in_F4);");
    procedure.AppendLine($"END;");

    var cmd = new MySqlCommand(procedure.ToString(), mySqlConnection);

    cmd.ExecuteNonQuery();
}

// Executing 1st version of the stored procedure
private static void InsertMySqlRecord(MySqlConnection mySqlConnection)
{
    var cmd = new MySqlCommand
    {
        CommandText = "`insert`",
        CommandType = CommandType.StoredProcedure,
        Connection = mySqlConnection
    };

    cmd.Parameters.AddWithValue("in_ID", Guid.NewGuid().ToString());
    cmd.Parameters.AddWithValue("in_F1", 1);
    cmd.Parameters.AddWithValue("in_F2", "Hello");
    cmd.Parameters.AddWithValue("in_F3", "world");

    cmd.ExecuteNonQuery();
}

// Executing 2nd version of the stored procedure
private static void InsertMySqlRecord2(MySqlConnection mySqlConnection)
{
    var cmd = new MySqlCommand
    {
        CommandText = "`insert`",
        CommandType = CommandType.StoredProcedure,
        Connection = mySqlConnection
    };

    cmd.Parameters.AddWithValue("in_ID", Guid.NewGuid().ToString());
    cmd.Parameters.AddWithValue("in_F1", 1);
    cmd.Parameters.AddWithValue("in_F2", "Hello");
    cmd.Parameters.AddWithValue("in_F3", "world");
    cmd.Parameters.AddWithValue("in_F4", "again !");

    cmd.ExecuteNonQuery();
}

Once my table has been altered (and the stored proc) the call to the new version of the stored procedure raised an Incorrect number of arguments for PROCEDURE

Here is the stack (cleaned for clarity):

Incorrect number of arguments for PROCEDURE test.insert; expected 5, got 4

at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlDataReader.cs:line 130
at MySql.Data.MySqlClient.MySqlDataReader.<CreateAsync>d__95.MoveNext() in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlDataReader.cs:line 391
at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext() in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 62
at MySql.Data.MySqlClient.MySqlCommand.<ExecuteNonQueryAsync>d__69.MoveNext() in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlCommand.cs:line 218
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySql.Data.MySqlClient/MySqlCommand.cs:line 68

[Mimetis]

Seems than clearing the pool is resolving the issue MySqlConnection.ClearPool(connection);
It's a workaround, but a little bit too much in my opinion :)

// creating 1st version of table and stored proc (then call it)
using (var connection = GetMyConnection(databaseName))
{
    connection.Open();

    CreateMySqlTable(databaseName, connection);
    CreateMySqlProcedure(databaseName, connection);
    // call 1st version of my stored procedure
    InsertMySqlRecord(connection);

    connection.Close();
}

// creating 2nd version of table and stored proc (then call it)
using (var connection = GetMyConnection(databaseName))
{
    //Workaround
    MySqlConnection.ClearPool(connection);

    connection.Open();
    // Adding one column to table
    AlterMySqlTable(databaseName, connection);
    // Creating 2nd version of my stored procedure
    CreateMySqlProcedure2(databaseName, connection);
    // Trying to call this new stored procedure
    InsertMySqlRecord2(connection);

    connection.Close();
}

[bgrainger]

Similar to #730.

I don't why, but it seems there is somewhere an internal cache preventing to execute a modified stored procedure.

The cache exists for performance.

Seems than clearing the pool is resolving the issue

This seems like a reasonable workaround to me. In my experience, it's very unusual for the parameters of a stored procedures to change in the database at runtime; it's almost always associated with a code deployment to use those new parameters (which implicitly clears the cache).

That said, if MySQL Server returns a unique error code for this situation, we might be able to detect it and retry the operation automatically.

[bgrainger]

I think this can be handled in the client, and doesn't need to be addressed by MySqlConnector.

try
{
    storedProcedureCommand.ExecuteNonQuery();
}
catch (MySqlException ex) when (ex.ErrorCode is MySqlErrorCode.StoredProcedureNumberOfArguments)
{
    MySqlConnection.ClearPool(storedProcedureCommand.Connection);
    // retry stored procedure
}

(Although it's still unclear to me how the code would be written to handle both the old and the new number of parameters correctly.)