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