4 ms·

PHP+MySQL still hasn’t figured this stuff out? I remember fighting mojibake in FogBugz 6 for Linux/Mac, fifteen years ago!

dn3500 5y ago
It's figured out now. The problem is that when the database was created in 2007 someone specified the wrong character set.
kalleboo 5y ago
In this case his problem likely stems from when the install was set up in 2007, 14 years ago. Back then having these charset mismatches in LAMP were super common These days the defaults tend to be slightly more sane. PHP is still messed up with the various mb_ string functions though.
marcosdumay 5y ago
Anything on Windows has problems with it, anything in Java has some difficulty, the Oracle DB has just adjusted and many people still don't know how to get the modern setting. The corporate world still didn't do the UTF-8 migration. But I don't know about MySQL. What I do know is that PHP by itself doesn't have this problem anymore.
marcan_42 5y ago
Only recently did MySQL start defaulting to proper Unicode support, and it's still not the default everywhere in the stack nor on all OSes. I did a mysqldump/restore last year with default options and it ate all emoji in the database. This is because MySQL's "utf-8" isn't real UTF-8, it's "utf8mb3" which does not support non-BMP characters. You have to explicitly ask for utf8mb4 to get sane behavior. In my case the database was configured correctly, but mysqldump still defaulted to utf8mb3. I found out that day all my database backups had been subtly corrupted forever. This is one reason I use PostgreSQL everywhere I can get away with it. It has infinitely saner tooling. But WordPress insists on being married to MySQL...
- bombcar 5y ago
  The easiest test for utf8mb4 is try to round-trip a emoji smiley. UTF real handles it, the fake version doesn’t.
- KronisLV 5y ago
  > You have to explicitly ask for utf8mb4 to get sane behavior. Here's more information about the difference between utf8 and utfmb4 in MySQL, in case anyone is wondering: https://stackoverflow.com/a/30074553 https://stackoverflow.com/a/30074553 I'd say that in most modern circumstances you probably should just go with utfmb4, unless you have a good reason for doing otherwise (apart from only ever needing to use the BMP, or having some odd storage restrictions). Can anyone come up with an actual good reason to use utf8mb3?
zerocrates 5y ago
This specific problem used to be extremely common. You set up the database, you know you want Unicode, so you set it to use utf8 (we'll ignore for now MySQL's legacy BMP issue), your tables, your columns are using it: great. You run your site for a while and everything is fine: characters all display fine, though you do occasionally notice some searches with non-ascii characters don't work quite as you expect, and one day you use an admin tool and you see things are messed up... What you didn't realize was that you set up all that stuff but not the connection charset, which used to default to latin1. So MySQL sees your UTF-8 data coming in, thinks "this is latin1" because that's what the connection charset is set to, sees your tables want utf8, and happily "converts" your UTF-8 text to UTF-8. You don't notice it because it does the same thing in reverse when sending results back to you, so you think everything's fine. Things got wise to setting the connection charset, avoiding this problem, and eventually the defaults also changed. So it's been pretty uncommon for new databases for a while now. But, you still see people in this poster's situation, discovering their database is stored in this mis-encoded form when they move to new servers or versions of PHP that changed the default connection charset. It's fairly easy to fix the data so it's stored correctly and you don't need the incorrect latin1 parameter to hide the problem, but just hope you didn't run a while without noticing the charset change so you now have a mix of good and bad encodings in the same tables.

dn3500 5y ago

It's figured out now. The problem is that when the database was created in 2007 someone specified the wrong character set.

kalleboo 5y ago

In this case his problem likely stems from when the install was set up in 2007, 14 years ago. Back then having these charset mismatches in LAMP were super common These days the defaults tend to be slightly more sane. PHP is still messed up with the various mb_ string functions though.

marcosdumay 5y ago

Anything on Windows has problems with it, anything in Java has some difficulty, the Oracle DB has just adjusted and many people still don't know how to get the modern setting. The corporate world still didn't do the UTF-8 migration. But I don't know about MySQL. What I do know is that PHP by itself doesn't have this problem anymore.

marcan_42 5y ago

Only recently did MySQL start defaulting to proper Unicode support, and it's still not the default everywhere in the stack nor on all OSes. I did a mysqldump/restore last year with default options and it ate all emoji in the database. This is because MySQL's "utf-8" isn't real UTF-8, it's "utf8mb3" which does not support non-BMP characters. You have to explicitly ask for utf8mb4 to get sane behavior. In my case the database was configured correctly, but mysqldump still defaulted to utf8mb3. I found out that day all my database backups had been subtly corrupted forever. This is one reason I use PostgreSQL everywhere I can get away with it. It has infinitely saner tooling. But WordPress insists on being married to MySQL...

bombcar 5y ago
The easiest test for utf8mb4 is try to round-trip a emoji smiley. UTF real handles it, the fake version doesn’t.
KronisLV 5y ago
> You have to explicitly ask for utf8mb4 to get sane behavior. Here's more information about the difference between utf8 and utfmb4 in MySQL, in case anyone is wondering: https://stackoverflow.com/a/30074553 https://stackoverflow.com/a/30074553 I'd say that in most modern circumstances you probably should just go with utfmb4, unless you have a good reason for doing otherwise (apart from only ever needing to use the BMP, or having some odd storage restrictions). Can anyone come up with an actual good reason to use utf8mb3?

zerocrates 5y ago

This specific problem used to be extremely common. You set up the database, you know you want Unicode, so you set it to use utf8 (we'll ignore for now MySQL's legacy BMP issue), your tables, your columns are using it: great. You run your site for a while and everything is fine: characters all display fine, though you do occasionally notice some searches with non-ascii characters don't work quite as you expect, and one day you use an admin tool and you see things are messed up... What you didn't realize was that you set up all that stuff but not the connection charset, which used to default to latin1. So MySQL sees your UTF-8 data coming in, thinks "this is latin1" because that's what the connection charset is set to, sees your tables want utf8, and happily "converts" your UTF-8 text to UTF-8. You don't notice it because it does the same thing in reverse when sending results back to you, so you think everything's fine. Things got wise to setting the connection charset, avoiding this problem, and eventually the defaults also changed. So it's been pretty uncommon for new databases for a while now. But, you still see people in this poster's situation, discovering their database is stored in this mis-encoded form when they move to new servers or versions of PHP that changed the default connection charset. It's fairly easy to fix the data so it's stored correctly and you don't need the incorrect latin1 parameter to hide the problem, but just hope you didn't run a while without noticing the charset change so you now have a mix of good and bad encodings in the same tables.