Bug #95986 "Incorrect integer value" using prepared statement with MySqlDbType.Int24
Submitted: 25 Jun 2019 22:59 Modified: 29 Aug 2022 14:52
Reporter: Bradley Grainger (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.16 OS:Windows (10)
Assigned to: CPU Architecture:Other (x64)

[25 Jun 2019 22:59] Bradley Grainger 
Description:
Setting MySqlParameter.MySqlDbType to MySqlDbType.Int24 throws an error similar to "Incorrect integer value: '?' for column 'data' at row 1" when a prepared command is executed.

When "command.ExecuteNonQuery()" is executed in the attached sample, the following exception is thrown:

MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect integer value: '?12' for column 'data' at row 1
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()

How to repeat:
Execute the following C#:

using (var connection = new MySqlConnection("...;IgnorePrepare=false"))
{
	connection.Open();
	using (var command = new MySqlCommand(@"drop table if exists prepared_statement_test;
		create table prepared_statement_test(data MEDIUMINT);", connection))
	{
		command.ExecuteNonQuery();
	}
	
	using (var command = new MySqlCommand(@"insert into prepared_statement_test(data) values(@data);", connection))
	{
		command.Parameters.AddWithValue("@data", 1234567).MySqlDbType = MySqlDbType.Int24;
		command.Prepare();
		command.ExecuteNonQuery();
	}
}

It can be worked around by writing:

    command.Parameters.AddWithValue("@data", 1234567).MySqlDbType = MySqlDbType.Int32;

or:

    command.Parameters.AddWithValue("@data", 1234567);;
[25 Jun 2019 23:05] Bradley Grainger 
Suggested fix:

MySqlDbType.Int24 should be treated similarly to MySqlDbType.UInt24 in MySqlParameter.GetPSType().

https://github.com/mysql/mysql-connector-net/blob/f2a0f56edf4a12b739d19fbcc2b1099929eb464c...
[26 Jun 2019 8:48] MySQL Verification Team 
Hello Bradley,

Thank you for the report and test case.

regards,
Umesh
[29 Aug 2022 14:52] Christine Cole 
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.31 release, and here's the proposed changelog entry from the documentation team:

An error was emitted after passing in MySqlParameter.MySqlDbType set with
type MySqlDbType.Int24 to command.ExecuteNonQuery() using a prepared
statement.

Thank you for the bug report.