Description:
In Connector/NET 8.0.22, calling MySqlCommand.Prepare for a stored procedure when one of the parameters has an incorrect type would throw a FormatException from MySqlCommand.Prepare. Now, Prepare succeeds, but ExecuteNonQuery/Scalar fails and leaves the underlying connection to the server in a bad state. Specifically, a broken connection is returned to the pool and can break totally different code when it's retrieved from the pool and used.
In the example below, calling MySqlCommand.Prepare on a completely different connection/command throws the following exception:
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.ThrowHelper.ThrowKeyNotFoundException()
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at MySql.Data.MySqlClient.MySqlField.SetFieldEncoding()
at MySql.Data.MySqlClient.NativeDriver.GetColumnData(MySqlField field)
at MySql.Data.MySqlClient.NativeDriver.GetColumnsData(MySqlField[] columns)
at MySql.Data.MySqlClient.Driver.GetColumns(Int32 count)
at MySql.Data.MySqlClient.NativeDriver.PrepareStatement(String sql, MySqlField[]& parameters)
at MySql.Data.MySqlClient.PreparableStatement.Prepare()
at MySql.Data.MySqlClient.MySqlCommand.Prepare(Int32 cursorPageSize)
How to repeat:
Run the following C# code:
var connectionString = "...;IgnorePrepare=false"; // IgnorePrepare=false to compare behaviour with 8.0.22; in 8.0.23 this is the default
using (var connection = new MySqlConnection(connectionString))
{
connection.Open();
// create a stored procedure
using var command = connection.CreateCommand();
command.CommandText = @"DROP PROCEDURE IF EXISTS out_string;
CREATE PROCEDURE out_string(OUT value VARCHAR(100))
BEGIN
SELECT 'test value' INTO value;
END;";
command.ExecuteNonQuery();
// prepare it with the wrong parameter type
command.CommandText = "out_string";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new()
{
ParameterName = "@value",
DbType = DbType.Double,
Direction = ParameterDirection.Output,
});
try
{
command.Prepare();
}
catch (FormatException ex)
{
Console.WriteLine("FormatException thrown (8.0.22)");
Console.WriteLine(ex);
}
try
{
command.ExecuteNonQuery();
}
catch (FormatException ex)
{
Console.WriteLine("FormatException thrown (8.0.22 / 8.0.23)");
Console.WriteLine(ex);
}
}
// appears to be a separate MySqlConnection object; actually reusing same pooled connection behind the scenes
using (var connection = new MySqlConnection(connectionString))
{
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = "SELECT 1;";
command.Prepare(); // throws KeyNotFoundException
Console.WriteLine(command.ExecuteScalar());
}
Suggested fix:
Not sure this is the cause, but reverting https://github.com/mysql/mysql-connector-net/commit/1ac7375ca086af81554e0845ce200a67096dd7... might help.
Description: In Connector/NET 8.0.22, calling MySqlCommand.Prepare for a stored procedure when one of the parameters has an incorrect type would throw a FormatException from MySqlCommand.Prepare. Now, Prepare succeeds, but ExecuteNonQuery/Scalar fails and leaves the underlying connection to the server in a bad state. Specifically, a broken connection is returned to the pool and can break totally different code when it's retrieved from the pool and used. In the example below, calling MySqlCommand.Prepare on a completely different connection/command throws the following exception: System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. at System.ThrowHelper.ThrowKeyNotFoundException() at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at MySql.Data.MySqlClient.MySqlField.SetFieldEncoding() at MySql.Data.MySqlClient.NativeDriver.GetColumnData(MySqlField field) at MySql.Data.MySqlClient.NativeDriver.GetColumnsData(MySqlField[] columns) at MySql.Data.MySqlClient.Driver.GetColumns(Int32 count) at MySql.Data.MySqlClient.NativeDriver.PrepareStatement(String sql, MySqlField[]& parameters) at MySql.Data.MySqlClient.PreparableStatement.Prepare() at MySql.Data.MySqlClient.MySqlCommand.Prepare(Int32 cursorPageSize) How to repeat: Run the following C# code: var connectionString = "...;IgnorePrepare=false"; // IgnorePrepare=false to compare behaviour with 8.0.22; in 8.0.23 this is the default using (var connection = new MySqlConnection(connectionString)) { connection.Open(); // create a stored procedure using var command = connection.CreateCommand(); command.CommandText = @"DROP PROCEDURE IF EXISTS out_string; CREATE PROCEDURE out_string(OUT value VARCHAR(100)) BEGIN SELECT 'test value' INTO value; END;"; command.ExecuteNonQuery(); // prepare it with the wrong parameter type command.CommandText = "out_string"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new() { ParameterName = "@value", DbType = DbType.Double, Direction = ParameterDirection.Output, }); try { command.Prepare(); } catch (FormatException ex) { Console.WriteLine("FormatException thrown (8.0.22)"); Console.WriteLine(ex); } try { command.ExecuteNonQuery(); } catch (FormatException ex) { Console.WriteLine("FormatException thrown (8.0.22 / 8.0.23)"); Console.WriteLine(ex); } } // appears to be a separate MySqlConnection object; actually reusing same pooled connection behind the scenes using (var connection = new MySqlConnection(connectionString)) { connection.Open(); using var command = connection.CreateCommand(); command.CommandText = "SELECT 1;"; command.Prepare(); // throws KeyNotFoundException Console.WriteLine(command.ExecuteScalar()); } Suggested fix: Not sure this is the cause, but reverting https://github.com/mysql/mysql-connector-net/commit/1ac7375ca086af81554e0845ce200a67096dd7... might help.