Bug #101302 Stored Procedure BOOL parameter can only be mapped to MySqlDbType.Byte

• Submitted: 23 Oct 2020 23:03
• Modified: 30 Nov 2020 19:04
• Reporter: Bradley Grainger (OCA)
• Status: Closed
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 8.0.22
• OS: Windows (10)
• CPU Architecture: Any

[23 Oct 2020 23:03] Bradley Grainger

Description:
In Connector/NET 8.0.22, a stored procedure parameter of type BOOL can only be assigned from a MySqlParameter of type MySqlDbType.Byte. In previous versions of Connector/NET, at least the following MySqlDbType values were allowed: Bit, Int16, Int32, Int64, Float, Double, Decimal.

In 8.0.22, using those throws the following exception:
Unhandled Exception: System.FormatException: Value 'success' is not of the correct type.
   at MySql.Data.MySqlClient.StoredProcedure.GetAndFixParameter(String spName, MySqlSchemaRow param, Boolean realAsFloat, MySqlParameter returnParameter)
   at MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName)
   at MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()

This is a regression that will break code that was working under 8.0.21, e.g., see https://stackoverflow.com/q/64491910/23633. This may be a regression introduced by the fix for Bug #99793.

How to repeat:
Execute the following C# code:

using (var connection = new MySqlConnection("..."))
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = @"
            DROP PROCEDURE IF EXISTS Test;
            CREATE PROCEDURE Test (success BOOL)
            BEGIN
                SELECT success;
            END
        ";
        command.ExecuteNonQuery();
    }

    using (var command = connection.CreateCommand())
    {
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "Test";
        // in 8.0.22, only MySqlDbType.Byte works; in 8.0.21, could use Int32, Bit, Int64, etc.
        command.Parameters.Add(new MySqlParameter("success", MySqlDbType.Int32) { Value = true });
        Console.WriteLine(command.ExecuteScalar()); // expect "True"
    }
}

[24 Oct 2020 6:10] MySQL Verification Team

Hello Bradley,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[10 Nov 2020 12:16] MySQL Verification Team

Bug #101424 marked as duplicate of this one.

[30 Nov 2020 19:04] Christine Cole

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.23 release, and here's the proposed changelog entry from the documentation team:

Incomplete validation limited the expected range of values that a stored
procedure with a parameter of type Boolean could assign using the
MySqlParameter.MySqlDbType property.

Thank you for the bug report.