InvalidCastException when using StoredProcedure with MySQL 5.0

[SolacomYongYao]

Software versions
MySqlConnector version: 2.2.6 or above
Server type: MySQL 5.0 (an old MySQL version, connection.Session.ServerVersion.Version < ServerVersions.RemovesMySqlProcTable)

Describe the bug
Got InvalidCastException in C# when use MySQLCommand (StoredProcedure) to ExecuteReader.
Unable to cast object of type 'System.String' to type 'System.Byte[]'.

The stored procedure has no input/output parameters, simply a select statement
BEGIN
select distinct(name) from tablename WHERE code LIKE 'ABC%';
END

The exception is when CachedProcedure.FillAsync force converting the String to byte[]
line 32: var returnsSqlBytes = (byte[]) reader.GetValue(1);

Exception

   at System.Runtime.CompilerServices.CastHelpers.ChkCastAny(Void* toTypeHnd, Object obj)
   at MySqlConnector.Core.CachedProcedure.<FillAsync>d__0.MoveNext() in /_/src/MySqlConnector/Core/CachedProcedure.cs:line 31
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at MySqlConnector.MySqlConnection.<GetCachedProcedure>d__88.MoveNext() in /_/src/MySqlConnector/MySqlConnection.cs:line 818
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext() in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 33
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at MySqlConnector.MySqlCommand.<ExecuteReaderAsync>d__84.MoveNext() in /_/src/MySqlConnector/MySqlCommand.cs:line 357
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at MySqlConnector.MySqlCommand.ExecuteReader() in /_/src/MySqlConnector/MySqlCommand.cs:line 112

Code sample

/* A concise code sample to reproduce the bug */
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
    connection.Open();
    using (MySqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "spname";
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.ExecuteNonQuery();
    }
}

Expected behavior
Should be able to use stored procedure with MySQL 5.0

[bgrainger]

As per #1126 (comment), there is no available Docker image for mysql:5.0, so I'm unable to reproduce this bug and test a fix.

Since MySQL 5.0 has been EOL for 12+ years, I'd strongly recommend upgrading to a newer server version.

Alternatively, if you'd like to open a PR to add the support you need (with code that is entirely your original contribution and able to be licensed to this project), I'd be happy to review it.

(It seems plausible that the fix is to test if (reader.GetValue(1) is string returnsSql) then fall back to decoding the byte[] returned by newer MySQL versions. However, without an available test version of MySQL 5.0, this is just speculation on my part.)

[SolacomYongYao]

Hi Bradley, I'd like to open a PR to add the support please.

[SolacomYongYao]

How can I create a pull request or branch? I don't think I have the permission.

[bgrainger]

• https://docs.github.com/en/pull-requests/collaborating-with-pull-requests/working-with-forks/about-forks
• https://docs.github.com/en/pull-requests/collaborating-with-pull-requests/working-with-forks/fork-a-repo
• https://docs.github.com/en/pull-requests/collaborating-with-pull-requests/proposing-changes-to-your-work-with-pull-requests/creating-a-pull-request-from-a-fork

[bgrainger]

Fixed by #1432.

[bgrainger]

Fixed in 2.3.4.