MySQL Bugs: #92912: Prepared commands execute with error on MySQL Server 8.0.13

Bug #92912	Prepared commands execute with error on MySQL Server 8.0.13
Submitted:	24 Oct 2018 0:47	Modified:	25 Jun 2019 17:02
Reporter:	Guest User	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	8.0.13	OS:	Windows (10)
Assigned to:		CPU Architecture:	Any

Description:
We've run into a critical issue that happened right after upgrading our MySQL Server from 8.0.12 to 8.0.13. Note that this issue affects both Connector.NET 8.0.12 and 8.0.13, so this could possibly be a bug in MySQL Server instead, but it makes sense to start here.

The issue is that calling a MySqlCommand's Prepare method causes the command to fail when it's executed, with this exception: MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect arguments to mysqld_stmt_execute

This same code worked fine in MySQL Server 8.0.12! So the syntax is right.

How to repeat:
1. Install MySQL Server 8.0.13. Set the root password to "root".
2. Download the attached program package and run Create Schema.sql, and Create Table.sql, in that order against the server.
3. Compile and run the program. You should see the error.

Suggested fix:
Check what MySQL Server 8.0.13 broke.

Files needed to reproduce the bug

Attachment: Program Package.zip (application/x-zip-compressed, text), 5.01 KiB.

Hello John,

Thank you for the report and test case.
Observed that test case fails against MySQL Server 8.0.13.

regards,
Umesh

I suspect this is a client-side Connector/NET bug because I can't repro with an alternate ADO.NET library, MySqlConnector https://mysql-net.github.io/MySqlConnector/ , even with MySQL Server 8.0.13.

I haven't done a packet capture to try to figure out what the problem is yet.

This is definitely a client-side bug.

Here's the breakdown of the COM_STMT_EXECUTE packet https://dev.mysql.com/doc/internals/en/com-stmt-execute.html Connector/NET sends:

17 // COM_STMT_EXECUTE
03 00 00 00 // statement ID
00 // flags
01 00 00 00 // iteration count
00 // null bitmap
01 // new params bound flag
FD 00 // Protocol::MYSQL_TYPE_VAR_STRING
EF 02 // *** INVALID VALUE ***
05 70 68 6F 6E 65 // "phone"
0C 31 31 31 2D 31 31 31 2D 31 31 31 31 // "111-111-1111"

The bug is sending 0x2FE as the column type for MySqlDbType.LongText. This is the literal value of the MySqlDbType.LongText enum value https://github.com/mysql/mysql-connector-net/blob/edafe8d93140949ab1f4a4ee00f9871c4fb04e8a... but is not an acceptable value from the table of values in the protocol definition: https://dev.mysql.com/doc/internals/en/com-query-response.html#packet-Protocol::ColumnType. (Technically, 0xFE is a valid column type, but the high byte 0x02 is not a valid flags value; it should be either 0x80 (unsigned) or 0x00 (signed).)

Connector/NET should be sending Protocol::MYSQL_TYPE_LONG_BLOB (0xFB).

Fortunately there is an easy workaround in your code: don't specify the MySqlDbType for the parameter, but let it be inferred.

// Don't set: .MySqlDbType = MySqlDbType.LongText;
getCustomerUserIDByMetaValueCmd.Parameters.AddWithValue("@MetaValue", "111-111-1111");

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 6.10.9 (8.0.15) release, and here's the changelog entry:

An exception was generated when the MySqlDbType enumeration was given an
explicit value and then passed as a parameter to the MySqlCommand.Prepare
method.

Thank you for the bug report.

Still having trouble related to this, opened a new bug: https://bugs.mysql.com/bug.php?id=95984