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
