Bug #98620 Using DatabaseMetaData.getColumns() gives collation mix error
Submitted: 16 Feb 2020 11:54 Modified: 17 Jun 2020 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 2020 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 2020 11:47] MySQL Verification Team
Hello Claude Brisson,

Thank you for the report and feedback.

regards,
Umesh
[29 Oct 2021 5:36] Ida Infront
I have run into the same problem and did some digging.

Looks like the problem is not related to the conditions on table_schema or table_name that is in the where-clause.
Rather the problem seems to be with a few IF's that are selected, these:

IF(IS_NULLABLE = 'yes',1,0) NULLABLE,
IF(EXTRA = 'auto_increment','YES','NO') IS_AUTOINCREMENT,
IF(EXTRA in ('VIRTUAL', 'PERSISTENT', 'VIRTUAL GENERATED', 'STORED GENERATED') ,'YES','NO') IS_GENERATEDCOLUMN

I guess this is somehow related with INFORMATION_SCHEMA.COLUMNS being a view instead of a table? Since the same thing worked in earlier versions of mysql.