It didn't work and it took 3 weeks for me to find a problem on the live site.
So now I have the same problem many have run into during testing where some characters such as é now show up as Ã©. The problem is I've been live for 3 weeks so I can't go back.
I thought about finding every character in latin1 that is different from utf8 (I'm guessing there are less than 150), then search the database for all affected columns. Then do a query for each column like this with all of the replacements (not just the three below):
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'Ã¡','á'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'Ã¤','ä'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'Ã©','é'); ....
What I did was created a copy of the live site to a test site.
In the database for the test site, I converted the column custom_fields in the table xf_user_profile from mediumblob to mediumtext, since I found out that that contents of the blob won't be converted with the next steps because they are already stored as binary.
Then I did a database dump on the test db forcing the character set to latin1 like this:
mysqldump -u username -p --opt --quote-names --skip-set-charset --default-character-set=latin1 database > fix_char.sql
mysql -u username -p --default-character-set=utf8 database < fix_char.sql
I need to do a lot more looking around and testing on the test site, but it seems to have worked. Is there anything I'm missing? Has anyone done this before with success? Anything I should worry about or look into before trying the fix on the live site?
Is there a reason that xf_user_profile is stored as a binary that I might be messing up with this process?
Thanks in advance!