Bug #98620 Using DatabaseMetaData.getColumns() gives collation mix error

Submitted: 16 Feb 2020 11:54 Modified: 10 Jul 2025 1:46
Reporter: Claude Brisson Email Updates:
Status: Closed 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 Ab Development Team
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.
[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.
[10 Jul 2025 1:46] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 9.4.0 changelog: 

"Executing DatabaseMetadata.getColumns() using the information_schema and setting connectionCollation to anything other than utf8mb3_general_ci would cause a java.sql.SQLException for Illegal mix of collations. It was because the collations differ between the connection and the information_schema. This patch fixes the metadata query executed in getColumns() so that the collations match."
[16 Jul 2025 18:36] Daniel So
Posted by developer:
 
Updated the changelog entry to the following:

"Executing DatabaseMetadata.getColumns() with useInformationSchema=true and setting connectionCollation to anything other than utf8mb3_general_ci would cause a java.sql.SQLException for Illegal mix of collations.
It was because the collations differ between the connection and the information_schema. This patch fixes the metadata query executed in getColumns() so that the collations match."

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.