Bug #102303 Preparing command with incorrect parameter type puts connection in bad state
Submitted: 20 Jan 2:31 Modified: 20 Jan 7:16
Reporter: Bradley Grainger (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:8.0.23 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:Other (x64)

[20 Jan 2:31] Bradley Grainger
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.
[20 Jan 7:16] MySQL Verification Team
Hello Bradley,

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

regards,
Umesh