MySQL Bugs: #95984: Prepared commands execute with error on MySQL Server 8.0.16

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:	Windows (10)
Assigned to:		CPU Architecture:	Any

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.

Files needed to reproduce the bug

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

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?

"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.

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

Hello John Belge,

Thank you for the report and test case.

regards,
Umesh

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

Can we get a fix in before the next release please?

Aaaand the next release is out without a fix, now we wait months for the next one.😭

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.

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.

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.