14356
« Last post by mouser on February 13, 2011, 05:41 AM »
Well basically here is what seems to have happened:
On the old server, the old mysql forum database was using character set latin1_swedish_ci.
And the forum software was configured to serve up pages using html charset=ISO-8859-1.
This combination worked fine in the past and everything was right with the world.
Ok so now we get to the server move, where we backed up the old forum database and imported it into a new database on the new server.
Now when you backup and restore (export and import) mysql databases (using mysqldump) it appears there are some tricky pitfalls to watch out for regarding character sets.
Now our intention was to keep the exact same database character set. But when we imported the database on the new server, *something* changed in the character set. But we weren't exactly sure how or why.. The mysql tables were still latin1_swedish_ci but the data seemed to be being served in UTF8 characters.
At that time we had some threads on the forum about character set issues after the move. That was when I made my first mistake, which was rather than to figure out the exact cause of the problem, I found a solution without understanding the problem completely. The solution was to switch the forum software into UTF8 mode -- which basically serves up pages labeled as having character set UTF-8 in the html headers.
This essentially solved the character set problems on the forum, and we ran that way for a month.
Except.. There were some very small lingering issues.
The lingering issues were a rare and non-fatal database warning about "Illegal mix of collations", and the fact that at least one non-english signature (dc member fenixproductions signature) stopped working (turned to ????).
Eventually what we did this weekend to solve the remaining problems is re-drop the database and re-import it but this time force all tables and columns to UTF-8. The source of the remaining issues was that the data was in UTF-8 format and the tables were still marked as latin1_swedish_ci. So all is good now and every player in the chain is agreed that the data is pure UTF-8.
Now although it may seem like a straightforward solution in hindsight, what really through me for a loop and caused so much consternation and struggle with all this is that after we decided last week that we needed to get to the bottom of the remaining problems, we couldnt figure out WHY the data was being served up from mysql in UTF8 format, when it had always been latin1_swedish_ci. And we were really nervous about trying to convert everything to UTF8. smf forums has a script to convert data to UTF8 and we actually tried running as our first attempt at a fix, on our backup forum database, and it failed badly enough and subtley enough to make me really paranoid about converting to UTF8.
It was this fear that some post data might not convert properly to UTF8 using the scripts that made me resist the idea of trying to switch over to UTF8, and more focused on getting the forum database back to its original character set.
In the end though, what *appears* to have happened is that when the database is exported/dumped, it was automatically, without any clues, being CONVERTED by mysql to UTF8 in the export dump. So that when we imported it back in, even with explicit latin1_swedish_ci tables, it ended up storing the newly converted to UTF8 data in those tables, without our even realizing it. After satisfying myself that all of the forum data actually was converted to UTF8 smoothly, we decided to embrace UTF8 and so that is what we are using now and going forward.
And with yet another international character set experience under my belt, I can safely say: There is no god.
These character set issues and interactions are truly a nightmare of epic proportions and we can only hope that if there is life on other planets they have figured out how to use a single and SIMPLE (1 byte) universal character set, and that they invade us soon and impose their language upon us. I welcome our new 1-byte-character-set overlords.
Recent Posts