Bug #92574 When converting from VARCHAR to Java Boolean, 'N' is not supported
Submitted: 26 Sep 2018 8:46 Modified: 3 Dec 2018 21:04
Reporter: Xinfeng Zhang Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0, 8.0.12 OS:Any
Assigned to: CPU Architecture:Any

[26 Sep 2018 8:46] Xinfeng Zhang
Description:
In MySQL connector/J 5.1 and previous versions. If you store 'N' 'n' '0' or 'false' in a VARCHAR, mysql connector can convert it into False in Java.
But in MySQL connector/J 8, it can't and will throw exception.
Example:

Caused by: org.springframework.dao.DataIntegrityViolationException: Error attempting to get column '   XXXXXXXXXXXXXX    ' from result set.  Cause: java.sql.SQLDataException: Cannot determine value type from string 'N'
; SQL []; Cannot determine value type from string 'N'; nested exception is java.sql.SQLDataException: Cannot determine value type from string 'N'

.....

Caused by: java.sql.SQLDataException: Cannot determine value type from string 'N'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:114)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLExce...

How to repeat:
Create a table with a VARCHAR(1) column. Set it's value to 'N'. With connector/J 5.1, it can parse it into a False. But with connector/J 8.0, the program will throw an exception.

Suggested fix:
Compatible with Connector/J 5.1.  Regard 'n'/'N'/'0'/null/'false' as a Java False.
[26 Sep 2018 9:40] Umesh Shastry
Hello Xinfeng,

Thank you for the report.

regards,
Umesh
[3 Dec 2018 21:04] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.0.14 changelog:

"The method ResultSet.getBoolean() now returns a false when the the designated coulmn is of data type CHAR or VARCHAR and contains an ā€œNā€ or ā€œnā€. This makes Connector/J 8.0 behaves like Connector/5.1 when it comes to converting strings to booleans."