Would really appreciate any help with an error I'm having. It happens when I run a MySQL view through Metabase (v0.39.2)
Incorrect string value: '\xC5\x81\xC3\xB3d\xC5...' for column '$str' at row 1`
The question used to work fine, but it suddenly stopped working. I can still run the view through mySQL workbench without errors.
I did some digging and was able to find the issue related to just two entries. Both contained polish characters. Specifically "ó".
I spent some time on google and most of the usual responses for this error focus on ensuring the charset is utf8mb4. But I've checked the char set for that field in the properties of the mySQL view and it's showing as utf8mb4.
So when did it "suddenly stopped working"? There must have been a change somewhere - MySQL was updates, perhaps Metabase was, or perhaps the database structure was changed?
Charset and collation are difficult, when it's mixed. Keeping everything as UTF8(mb4) is the best way.
Post "Diagnostic Info" from Admin > Troubleshooting.
Try running the following via Metabase's Native query:
SHOW SESSION VARIABLES WHERE Variable_name LIKE 'version' OR Variable_name LIKE 'character_set%' OR Variable_name LIKE 'collation%';
SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'version' OR Variable_name LIKE 'character_set%' OR Variable_name LIKE 'collation%';
Thanks for the quick response Flamber! The thing that changed was when the data changed, we had data that included polish characters. Specifically "Łódź" Before this there was no issue, and when I removed that data the query ran again.
Thanks for the tips on diagnostic data. I'll take a look now.
@DailySamba So you basically have everything as the default (latin1 Swedish), which is why you're having problems. Follow the article I linked and change all your database structure and data to UTF8mb4
I saw some improvement when I ran the session variable query. Now Character set server and collation server have both changed to utf8mb4. There was no change to the results of the global query.
But in any case it didn't fix the problem. I'll try the article you linked to.
@DailySamba You'll want to change character_set_system to utf8mb4
And it's important to remember to update all your data if it was stored in a different format.
Unless everything is running UTF8, then you're just going to fight encoding problems everywhere.
And remember that you have only posted you variables, that doesn't tell if your database, table or columns are using a different encoding.
Hi @flamber I just followed your advice. I checked through my tables, functions and views. I had a rogue function which was returning text as latin1 still rather than utf8mb4.
Interestingly I'd already tried changing that before we updated the database collations and it didn't fix the problem.
So it really needed absolutely everything to be aligned with utf8mb4.
@DailySamba Thank you. Trust me, you're not the first, and not the last person who have been fighting with encoding. But your life should be so much easier going forward, now that everything is UTF8 and you don't need to go through multiple conversions.
If you have ever tried to support multiple legacy apps, which each had their own encoding, but storing information to the same database, then you'll know how much work it takes.
