Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='

hi, i try to use filter and there is error with

Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation ‘=’

i selecting view type table and if i select with where statement directly to database, there is no problem. table use utf8mb4_unicode_ci

Hi @hafriz
Please post “Diagnostic Info” from Admin > Troubleshooting.
Metabase requires the collation utf8mb4_unicode_ci - and I don’t understand how you’re getting that error.

hi @flamber

{
“browser-info”: {
“language”: “en-GB”,
“platform”: “Linux armv8l”,
“userAgent”: “Mozilla/5.0 (Linux; Android 8.1.0; Redmi 5 Plus) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Mobile Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.7+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.7”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.7+10”,
“os.name”: “Linux”,
“os.version”: “3.10.0-1127.el7.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“h2”,
“mysql”,
“googleanalytics”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-11-16”,
“tag”: “v0.37.2”,
“branch”: “release-x.37.x”,
“hash”: “25e5f70”
},
“settings”: {
“report-timezone”: “Asia/Kuala_Lumpur”
}
}
}

@hafriz I’m not sure which version of MySQL you’re using, but latest release is 0.37.6 and you should migrate away from H2 as the application database:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

Hi @flamber, i’m using percona mysql 8. You mean that the reason might because i’m using h2 application database and your advise to change it to mysql right?

Thanks

@hafriz Okay, try adding this to the connection string in Admin > Databases > (your MySQL):
useUnicode=true&characterEncoding=UTF-8 or detectCustomCollations=true
Could it be that a column or the database itself is not using utf8mb4_unicode_ci ?
It sounds like it’s an issue with MySQL: https://bugs.mysql.com/bug.php?id=101346

You should never use H2 if you are using Metabase in production, but changing that will not fix the problem you are currently having.

1 Like

hi @flamber

Thanks a lot, it seems the column not in the same colation with other joined column. thanks, after changing by adding COLLATE utf8mb4_unicode_ci it seems working now.

also thanks for the suggestion to change to mysql for application. i try doing it now

thanks a lot

by the way, is there any guide that i can follow to copy metabase.db to external folder? so then after that i can point the docker to new metabase.db path before migrate it to mysql

@hafriz I would recommend doing migration with JAR file instead of with Docker, since it’s simpler if you’re not advanced Docker user, and since the migration is a one-off process.