Bug #98620 Using DatabaseMetaData.getColumns() gives collation mix error
Submitted: 16 Feb 11:54 Modified: 17 Jun 11:47
Reporter: Claude Brisson Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.19, 8.0.20 OS:Ubuntu (19.10)
Assigned to: CPU Architecture:x86

[16 Feb 11:54] Claude Brisson
Description:
Mysql daemon version: 8.0.19-0ubuntu0.19.10.3 for Linux on x86_64 ((Ubuntu)).

The server is configured with: collation-server = utf8_unicode_ci (but I'm not sure it's relevant).

The DatabaseMetadata.getColumns() method, when supplied with a table name, builds a query where the table name is hardcoded as a string value. It's prone to generate an illegal mix of collations, since the hardcoded string will use the client collation, not the information schema collation.

Also, I found that the collation used in information_schema can vary if the mysql database has been upgraded from 5.x or not.

How to repeat:
Have a JDBC program calling databaseMetadata.getColumns() for a specific table.

Call it once using a JDBC URL with the following parameters:
useUnicode=truecharacterEncoding=utf-8connectionCollation=utf8_unicode_ci

Call it again using a JDBC URL with the following parameters:
useUnicode=truecharacterEncoding=utf-8connectionCollation=utf8_general_ci

One of the two preceding calls will fail with the error:

java.sql.SQLException: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (utf8_unicode_ci,COERCIBLE) for operation '=' 

Suggested fix:
The suggested fix would be to either:

- use prepared parameters
- impose a specific collation for the comparison
[17 Jun 11:47] MySQL Verification Team
Hello Claude Brisson,

Thank you for the report and feedback.

regards,
Umesh