Bug #95984 Prepared commands execute with error on MySQL Server 8.0.16
Submitted: 25 Jun 16:59 Modified: 26 Jun 8:53
Reporter: John Belge Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:8.0.16 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:Any

[25 Jun 16:59] John Belge
Description:
This is a follow up to Bug #92912 (https://bugs.mysql.com/bug.php?id=92912)

Unfortunately I am still having trouble after upgrading to the latest versions of Connector and Server.

How to repeat:
1. Install MySQL Server 8.0.16. 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:
Please expand on the fixes made in Bug #92912.
[25 Jun 17:01] John Belge
Files needed to reproduce the bug

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

[25 Jun 17:32] John Belge
The issue appears to be with MySqlDbType.JSON. When setting a parameter to that type, that causes the exception. If you comment that out in the sample, it works fine.

JSON worked fine in 8.0.14 - what changed?
[25 Jun 22:06] Bradley Grainger
"Incorrect arguments to mysqld_stmt_execute" is a server-side error. It's being generated from this code, which disallows MYSQL_TYPE_JSON as a valid parameter type for prepared statements: https://github.com/mysql/mysql-server/blob/124c7ab1d6f914637521fd4463a993aa73403513/sql/sq...

The workaround is to avoid the JSON column type when adding parameters, e.g.,

    cmd.Parameters.AddWithValue("@Five", "[]").MySqlDbType = MySqlDbType.VarChar;

This is also a bug in MySqlConnector (because the client's column type isn't permitted): https://github.com/mysql-net/MySqlConnector/issues/659
I plan to fix it in the next version.

> JSON worked fine in 8.0.14 - what changed?

I get the same error with MySQL Server 8.0.14 and Connector/NET 8.0.14. It looks like the server-side change (https://github.com/mysql/mysql-server/commit/69e990f35449bbc493ae9df2b2ed83ac62ed1720) predates 8.0.14, so I would expect this to fail in that version.
[26 Jun 2:05] John Belge
Thanks for the investigation, Bradley!

So I guess the fix is to remove MySqlDbType.JSON from Connector, then. Seems bad to have it exist when it will lead to code that errors...
[26 Jun 8:53] Umesh Shastry
Hello John Belge,

Thank you for the report and test case.

regards,
Umesh
[26 Jun 20:35] Bradley Grainger
> So I guess the fix is to remove MySqlDbType.JSON from Connector, then.

A possible fix would be to handle that value specially when preparing the command. For example, MySqlDbType.UInt24 is actually transmitted as Int32: https://github.com/mysql/mysql-connector-net/blob/f2a0f56edf4a12b739d19fbcc2b1099929eb464c...

MySqlDbType.JSON could similarly be automatically translated to MySqlDbType.VarChar with no impact on users' code.