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:
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
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"))
	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", 1234567).MySqlDbType = MySqlDbType.Int24;

It can be worked around by writing:

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


    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().

[26 Jun 2019 8:48] MySQL Verification Team
Hello Bradley,

Thank you for the report and test case.

[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

Thank you for the bug report.