MySqlParamater Value unboxing enum - cast exception #1384

BOBJohnson · 2023-11-03T18:34:41Z

Software versions
MySqlConnector version: 2.2.7
Server type (MySQL, MariaDB, Aurora, etc.) and version: MySQL 5.7 & 8.0 AWS RDS
.NET version: 4.8

Describe the bug
Not sure if this is a bug, or a misunderstanding on my part. Recently migrated from Oracle's connector to your connector. Other than swapping namespaces, drop in was seamless.

Found an issue during testing from unboxing an enum value that is being passed into a MySqlParameter. In Oracle's connector, no unboxing / cast exception.

The column in question has a MySql type of smallint - so a short/int16 in .Net
The enum has always just been the default underlying type in .Net (int32).
The amount of values the enum has nowhere the limit of int16 - so not worried about an overflow when casting.

Exception
System.InvalidCastException: Specified cast is not valid.
at MySqlConnector.MySqlParameter.AppendSqlString(ByteBufferWriter writer, StatementPreparerOptions options) in //src/MySqlConnector/MySqlParameter.cs:line 463
at MySqlConnector.Core.StatementPreparer.ParameterSqlParser.DoAppendParameter(Int32 parameterIndex, Int32 textIndex, Int32 textLength) in //src/MySqlConnector/Core/StatementPreparer.cs:line 88
at MySqlConnector.Core.StatementPreparer.ParameterSqlParser.OnNamedParameter(Int32 index, Int32 length) in //src/MySqlConnector/Core/StatementPreparer.cs:line 77
at MySqlConnector.Core.SqlParser.Parse(String sql) in //src/MySqlConnector/Core/SqlParser.cs:line 197
at MySqlConnector.Core.StatementPreparer.ParseAndBindParameters(ByteBufferWriter writer) in //src/MySqlConnector/Core/StatementPreparer.cs:line 39
at MySqlConnector.Core.SingleCommandPayloadCreator.WriteCommand(IMySqlCommand command, ByteBufferWriter writer, Boolean appendSemicolon) in //src/MySqlConnector/Core/SingleCommandPayloadCreator.cs:line 260
at MySqlConnector.Core.SingleCommandPayloadCreator.WriteQueryCommand(CommandListPosition& commandListPosition, IDictionary`2 cachedProcedures, ByteBufferWriter writer, Boolean appendSemicolon) in //src/MySqlConnector/Core/SingleCommandPayloadCreator.cs:line 47
at MySqlConnector.Core.CommandExecutor.d__0.MoveNext() in //src/MySqlConnector/Core/CommandExecutor.cs:line 45
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at MySqlConnector.MySqlCommand.d__84.MoveNext() in //src/MySqlConnector/MySqlCommand.cs:line 344
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at MySqlConnector.MySqlCommand.ExecuteReader() in //src/MySqlConnector/MySqlCommand.cs:line 112
at TestMySqlCastException.Program.Main(String[] args) in Program.cs:line 34

Code sample

using MySqlConnector;

using System;

namespace TestMySqlCastException
{
    internal class Program
    {
        enum MyEnum
        {
            Value1 = -2,
            Value2 = -1,
            Value3 = 0,
            Value4 = 1,
            Value5 = 2,
            Value6 = 3,
        }

        static void Main(string[] args)
        {
            MyEnum myEnum = MyEnum.Value3;

            try
            {
                // This one works.
                MySqlParameter param1 = new MySqlParameter("@MyEnumColumn1", MySqlDbType.Int16) { Value = (short)myEnum };
                // This one throws a cast exception
                MySqlParameter param2 = new MySqlParameter("@MyEnumColumn2", MySqlDbType.Int16) { Value = myEnum };
                MySqlConnection conn = new MySqlConnection("<my connection string>");
                conn.Open();
                MySqlCommand cmd = new MySqlCommand("SELECT * FROM MyTable WHERE MyEnumColumn1 = @MyEnumColumn1 OR MyEnumColumn1 = @MyEnumColumn2;", conn);
                cmd.Parameters.Add(param1);
                cmd.Parameters.Add(param2);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine(reader["MyColumn"]);
                }
                reader.Close();
                reader.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.ReadLine();
        }
    }
}

Expected behavior
The enum is converted and no exception is thrown.

Additional context
I anonymized the code sample a little.
Note, not only do I not get a cast exception with the Oracle connector, I also don't get a cast exception when using MS SQL and the built in .Net SqlClient using the same core DB structure.

This probably isn't a bug according to how your project is structured. I am assuming you are doing straight casts instead of conversions for performance.

What I am unclear on, and I tried googling this, should the DbType enum match the database column type or the .Net variable type? I always assumed it was supposed to match the database column type.

Using your connector, I can change the DbType enum to be int32 (to match the enum default type), and even though the column in the actual DB is int16, the query works.

I've gone through our codebase and just added casts to any numeric parameter where the .Net type didn't match the DbType - so this is mostly a non-issue. Just confused because again I thought DbType was meant to match column type.

bgrainger · 2023-11-04T19:59:01Z

Thanks for the detailed bug report!

MySqlParameter param1 = new MySqlParameter("@MyEnumColumn1", MySqlDbType.Int16)

Setting MySqlParameter.MySqlDbType (or .DbType) is almost never necessary with MySqlConnector, because the parameter type will be inferred correctly, and because there's no actual need to specify the parameter type on the wire for MySQL Server.

However, because there's no need to do this (for correctness), MySqlConnector shouldn't be more strict than what is required. The code you ported from MySql.Data should "just work".

BOBJohnson · 2023-11-06T16:19:05Z

Thanks for looking into this. Just an FYI, the port from MySql.Data didn't "just work" because the code specifies DbType in all parameterized queries. So our solution is either do the pre-cast I mentioned in my original post, or go through and delete DbType from all our parameterized queries.

bgrainger · 2023-11-06T16:28:41Z

Just an FYI, the port from MySql.Data didn't "just work"

Sorry for the confusion. I was describing what should ideally happen. It didn't, which is why you filed the bug, and why I fixed it.

BOBJohnson · 2023-11-07T16:53:20Z

Ah gotcha. I didn't realize you had done anything. I thought you had closed it as 'not a bug' which I wasn't opposed to either - as I was unsure it was a bug vs an explicit design choice for speed optimization.

I see the 80bec17 you put in. Thanks!

bgrainger self-assigned this Nov 4, 2023

bgrainger added the bug label Nov 4, 2023

bgrainger closed this as completed in 80bec17 Nov 4, 2023

bgrainger added this to the 2.3 milestone Nov 12, 2023

bgrainger mentioned this issue Dec 17, 2023

Fixed packet corruption issue when enum was not 4 bytes #1421

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MySqlParamater Value unboxing enum - cast exception #1384

MySqlParamater Value unboxing enum - cast exception #1384

MySqlParamater Value unboxing enum - cast exception #1384

MySqlParamater Value unboxing enum - cast exception #1384

Comments