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

Call it again using a JDBC URL with the following parameters:

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.

[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(EXTRA = 'auto_increment','YES','NO') IS_AUTOINCREMENT,

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.
[17 Nov 2022 17:04] Filipe Silva
Thank you all for the detailed information.

As a temporary workaround you can set `useInformationSchema=false` in the connection string and it should now work.