Last Week, i was struggling to revive old blog which is still using old wordpress engine due too machine failure. And migrate it using nginx and php-fpm instead of apache2 web server.

Migration step, will be explain later in separate post. This post will focus in one major problem i face while migrating the blog, which is encoding issue. for information this blog using arabic as primary language and all post published in arabic. I notice this issue, when see all menus and posts show bizzare character instead of arabic.

It wasn’t arabic and even not a regular text. i read a lot of post in wordpress forum to solve this issue. but no luck, some people suggest to comment out lines in wp-config.php which is used for column encoding, DB_CHARSET and DB_COLLATE, but no luck. the blog still show unreadable character.

Then i realised, the problem is all not about encoding issue while displaying blog content, but all posts and setting was stored in DB using this character instead of normal character.

First thing cross in my mind is how we can restore this weird character into original arabic character ?. can we use convert and cast function in mysql to revive the content in arabic ?

I read this nice mysql documentation , then try to find perfect encoding. First attempt, convert content to latin1 and convert it to utf-8, but the failed. after several attempt, finally find correct combination using this query :

After found this correct cast and convert combination, the hardest part is update all records. what i did was creating small query for each table, each related column to revive all record in correct arabic content.

and this the result :

Jakarta, 1st Januari 2018

A. Ahmad Kusumah