Bug #95984 Prepared commands execute with error on MySQL Server 8.0.16
Submitted: 25 Jun 2019 16:59 Modified: 21 Feb 2020 16:39
Reporter: Guest User Email Updates:
Status: Closed 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 2019 16:59] Guest User
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 2019 17:01] Guest User
Files needed to reproduce the bug

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

[25 Jun 2019 17:32] Guest User
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 2019 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 2019 2:05] Guest User
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 2019 8:53] MySQL Verification Team
Hello John Belge,

Thank you for the report and test case.

regards,
Umesh
[26 Jun 2019 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.
[28 Sep 2019 17:55] Guest User
Can we get a fix in before the next release please?
[14 Oct 2019 22:22] Guest User
Aaaand the next release is out without a fix, now we wait months for the next one.😭
[21 Jan 2020 2:53] Guest User
Aaaand the next release is out without a fix, now we wait months for the next one.😭

Disappointed there is still no word on a fix.
[20 Feb 2020 19:49] Gustavo Cuatepotzo
Posted by developer:
 
The fix to this bug will be released in 8.0.20, MySqlDbType.JSON type still can be used as parameter, but internally will be handled as a varchar.
[21 Feb 2020 16:39] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.20 release, and here's the proposed changelog entry from the documentation team:

The MySqlDbType.JSON type when used as a parameter in prepared statements
produced code errors. Now, Connector/NET interprets MySqlDbType.JSON as
MySqlDbType.VarChar without any change required by existing applications
that specify a JSON column.

Thank you for the bug report.