Bug #108756 Can't insert negative number using prepared statement with MySqlDbType.Int24
Submitted: 12 Oct 2022 12:48 Modified: 7 Jun 2023 14:30
Reporter: Bradley Grainger (OCA) Email Updates:
Status: Closed Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.31 OS:Windows (10)
Assigned to: CPU Architecture:Any

[12 Oct 2022 12:48] Bradley Grainger
Setting MySqlParameter.MySqlDbType to MySqlDbType.Int24 throws an error similar to "Out of range value for column 'data' at row 1" when a prepared command is executed and the parameter is a negative number

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

MySql.Data.MySqlClient.MySqlException (0x80004005): Out of range value 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.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()

How to repeat:
Execute the following C#:

using var connection = new MySqlConnection("...");

using (var command = new MySqlCommand(@"drop table if exists prepared_statement_test;
	create table prepared_statement_test(data MEDIUMINT);", connection))

using (var command = new MySqlCommand(@"insert into prepared_statement_test(data) values(@data);", connection))
	command.Parameters.AddWithValue("@data", -1).MySqlDbType = MySqlDbType.Int24;

It can be worked around by writing:

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


    command.Parameters.AddWithValue("@data", -1);

Suggested fix:
This appears to be an incomplete fix for bug #95986.

Most likely, the UNSIGNED_MASK should not be getting added here: https://github.com/mysql/mysql-connector-net/blob/56ea6cf19d7b65e874efc2b26b579560e8126b76...
[12 Oct 2022 12:58] MySQL Verification Team
Hello Bradley,

Thank you for the report and test case.

[7 Jun 2023 14:30] Christine Cole
Posted by developer:
Fixed as of the upcoming MySQL Connector/NET 8.1.0 release, and here's the proposed changelog entry from the documentation team:

MySqlParameter.MySqlDbType, if set to MySqlDbType.Int24, emitted an error
similar to Out of range value for column 'data' at row 1 when a prepared
statement was executed and the parameter was a negative number.

Thank you for the bug report.