Description
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.