MySQL Bugs: #98620: Using DatabaseMetaData.getColumns() gives collation mix error

Bug #98620	Using DatabaseMetaData.getColumns() gives collation mix error
Submitted:	16 Feb 2020 11:54	Modified:	10 Jul 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

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

Hello Claude Brisson,

Thank you for the report and feedback.

regards,
Umesh

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.

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.

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."

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."