MySQL Bugs: #113378: Java enums in prepared statement for a VARCHAR throws exception

Bug #113378	Java enums in prepared statement for a VARCHAR throws exception
Submitted:	8 Dec 2023 12:31	Modified:	29 Mar 2024 17:12
Reporter:	Cyril DE CATHEU	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	connector-j>=8.0.29	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A formatted version of this description is available on github: 
https://github.com/cyrilou242/mysql-jdbc-regression 

It seems there was a breaking behaviour change in version 8.0.29 when using prepared 
statement. The behaviour when passing an Enum instance to a VARCHAR column has changed.

For such table:
CREATE TABLE demo_table (col1 VARCHAR(255) NOT NULL)

And such prepared statement:
INSERT INTO demo_table (col1) VALUES (?)

When doing
preparedStatement.setObject(1, MyEnum.VAL1, Types.VARCHAR);

In 8.0.28: works fine, MyEnum.VAL1 is converted to a String.  
In 8.0.29: fails with WrongArgumentException - Conversion from MyEnum to VARCHAR is not supported  

The issue happens for SELECT statements too.

The issue is still present in the most recent version 8.2.0.

Is this a bug or an expected behaviour change?

How to repeat:
See github project for an easy way to reproduce on multiple MySql versions and multiple connection versions: https://github.com/cyrilou242/mysql-jdbc-regression

It boils down to: 

Connection c = ... // connection to MySql>=8.0.28
// create a simple table with a VARCHAR
c.createStatement().execute("CREATE TABLE demo_table (col1 VARCHAR(255) NOT NULL)");

// insert an enum object using a prepared statement
PreparedStatement preparedStatement = c.prepareStatement(
              "INSERT INTO demo_table (col1) VALUES (?)");

// BUG: in 8.28.0: the line below works. In >=8.0.29, it throws
preparedStatement.setObject(1, MyEnum.VAL1, Types.VARCHAR);

Suggested fix:
It is most likely related to this line in the changelog of 8.0.2: https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-29.html
> The code for prepared statements has been refactored to make the code simpler and the logic for binding more consistent between ServerPreparedStatement and ClientPreparedStatement. (WL #14750)

But I have not looked at the implementation yet.

Hi Cyril,

Thank you for your interest in MySQL Connector/J and for taking the time to report this issue.

Previous to version 8.0.29, Connector/J was making some loose assumptions with regard to type mapping of unsupported types. It was blindly converting to String without taking into consideration the type mapping defined in the JDBC specification, which was wrong.

From 8.0.29 and above, Connector/J does its best to be more JDBC compliant and this is why this type conversion is not supported anymore. Your best option is to be specific about the data you are submitting to the database and converting it to String by yourself. This way you don't rely on a non-standard behavior that can change at any moment, as it did.

I hope this clarifies your question.

Hey Filipe, 

Makes sense. Thanks for the explanation.