Fixing our true Unicodeness
We recently moved zen.org to a different server, and in the process my dump and reload of our MySQL database worked—mostly. However any posts with UTF-8 Unicode characters didn’t get displayed correctly.
After spending too much time trying to figure out how to make mysql and mysqldump help me, I realized I should look around for others who’ve had the same problem.
Voila, Jonkepon in Japan gave the fix for exactly the problem we had. The fix has to do with the collation of the entries in the database, not the actual dumping and importing of the content itself.
Since the newer Wordpress already does their first step with SET TABLE, I just had to go in via phpMyAdmin. For each of post_content in wp_posts and comment_content in wp_comments, I changed the collation of each to binary (noting the type of LONGTEXT or TEXT) and saved it. Then I edited them again and set each to utf8_unicode_ci, and saved them.
Bingo! All is happy and good again. The other tables are all still latin1_swedish_ci (?!), but I’ll leave them alone until we bump into somewhere else that it’s a problem.
2 Comments »
RSS feed for comments on this post.
Leave a comment
Line and paragraph breaks automatic, e-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>
MySQL was originally made by a Swedish company, now owned by Sun. Maybe that is why the other tables are
latin1_swedish_ci.Comment by Sven — March 13, 2008 @ 16:02 GMT
Hi Brendan,
I was visiting your website zen.org and wondered if we could sponsor your penpals page at http://www.zen.org/~brendan/kids-pen.html
Our website has been running for over 10 years and we specialise in postal and email penpals worldwide. The main difference with our site is all profiles are manually reviewed before approval to the web. It is takes alot of time, but the result is a clean database that is family friendly and child safe.
I hope you can take the time to have a look at our website at http://www.penfriends.com.au/webdate. After reading your warning to kids about using penpal sites - i think you will be impressed with the privacy and security our site incorporates for kids.
I notice you have several sites listed on your links page, I hope you will consider adding ours too. I don’t mind paying a sponsorship fee either. Just let me know your paypal account and I will happily make a donation to support zen.org.
Thanks for taking the time to read this.
Sincerely,
Stuart Bourn
Mng Global Penfriends.
Comment by stuart — March 25, 2008 @ 18:11 GMT