So i have a dashboard for testing and one for production, they both recieve data from the same software and are in the same language, but they show diferent characters, like this;
In testing enviroment: ação
In production: a��o
any idea of whats the problem? My file encoding is "UTF-8"
Sorry, forgot to do that
Diagnostic Info from my testing enviroment:
{
"browser-info": {
"language": "pt-BR",
"platform": "Win32",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "Java(TM) SE Runtime Environment",
"java.runtime.version": "1.8.0_161-b12",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_161",
"java.vm.name": "Java HotSpot(TM) 64-Bit Server VM",
"java.vm.version": "25.161-b12",
"os.name": "Linux",
"os.version": "4.15.0-154-generic",
"user.language": "en",
"user.timezone": "America/Sao_Paulo"
},
"metabase-info": {
"databases": [
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "5.7.35-0ubuntu0.18.04.1"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.5.1"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.36.2",
"date": "2020-07-31",
"branch": "release-0.36.x",
"hash": "13f0225"
},
"settings": {
"report-timezone": null
}
}
}
And here is the Diagnostic Info from the production enviroment:
{
"browser-info": {
"language": "pt-BR",
"platform": "Win32",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "Java(TM) SE Runtime Environment",
"java.runtime.version": "1.8.0_161-b12",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_161",
"java.vm.name": "Java HotSpot(TM) 64-Bit Server VM",
"java.vm.version": "25.161-b12",
"os.name": "Linux",
"os.version": "4.15.0-154-generic",
"user.language": "en",
"user.timezone": "America/Sao_Paulo"
},
"metabase-info": {
"databases": [
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "5.7.35-0ubuntu0.18.04.2"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.6.2"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.37.0.2",
"date": "2020-10-26",
"branch": "release-x.37.x",
"hash": "ba7be09"
},
"settings": {
"report-timezone": null
}
}
}
@TomDrews My first guess is that your Metabase application database (MySQL 5.7.35) is different collation or charset for either the MySQL server, database or tables.
https://www.metabase.com/docs/latest/operations-guide/configuring-application-database.html#mysql-or-mariadb
It is very important to use utf8mb4 for charset and utf8mb4_unicode_ci for collation.
You should really consider upgrading. Latest release is 0.40.5 and 0.41.0 is coming soon.
https://github.com/metabase/metabase/releases/latest
And upgrade Java:
https://www.metabase.com/docs/latest/operations-guide/java-versions.html
Yes, the charset on the server is latin1, and we tried to solve it using the adicional JDBC connection string option, using this:
@TomDrews I think there's a misunderstanding. Where exactly are you seeing this problem? Is it the results or somewhere else?
Metabase only makes utf8 requests to your data sources.
Yes, the JDBC additional connection string works - for the variables that you are allowed to change.
If you are storing utf8 data in a database setup with latin charset, then you're setting yourself up for a world of hurt. You should convert your database to utf8mb4. There are plenty of resources on the internet that explain that, since that has nothing to do with Metabase.
We are using a database that is in latin1, but using the JDBC string, characterEncoding=UTF-8, in another software(Bonita), all the data comes in utf8mb4 and we have no problems, but using this same JDBC string on the metabase field Additional JDBC connection string options, it doesnt work and the characters in all our tables appear in latin1, without ' ´ ', '~' or 'ç'.
Is there a way to just set the character_set_results as utf8?
@TomDrews I have never seen this problem before, but as I already said, Metabase already uses UTF-8 for your data sources:
https://github.com/metabase/metabase/blob/master/src/metabase/driver/mysql.clj#L305-L314
Try upgrading Java and Metabase. If that doesn't work, then I don't know. But you should really store your data in UTF-8 format.
Trying to fight with MySQL's charset and collation is a losing battle.
There's a great in-depth article about converting your data: https://fromdual.com/mariadb-and-mysql-character-set-conversion