{"id":349,"date":"2009-03-16T21:14:36","date_gmt":"2009-03-16T18:14:36","guid":{"rendered":"http:\/\/www.void.gr\/kargig\/blog\/?p=349"},"modified":"2009-05-03T13:13:10","modified_gmt":"2009-05-03T10:13:10","slug":"convert-greek-characters-from-latin1-mysql-database-fields-to-pure-utf8","status":"publish","type":"post","link":"https:\/\/www.void.gr\/kargig\/blog\/2009\/03\/16\/convert-greek-characters-from-latin1-mysql-database-fields-to-pure-utf8\/","title":{"rendered":"Convert greek characters from latin1 mysql database fields to pure utf8"},"content":{"rendered":"

The Problem<\/strong>
\nTo sum it up, the case is this: many many many web applications were programmed so that they used latin1 collation for their fields inside mysql databases. But most users now use utf8 from within their browsers. What happens is that utf8 characters are getting stored inside latin1 fields, which in return produces chaos! A huge web application that used that kind of madness was WordPress<\/a>. Luckily (or not) WordPress now uses utf8 everywhere. I’ve known many many many people that got so frustrated when they tried to move from their old WordPress installation to a newer one because all their greek posts couldn’t be exported “easily”, I won’t say “properly” because there are always solutions to problems like this, but all the solutions were not straightforward at all, that they finally dumped the idea of moving the posts and started a new blog. <\/p>\n

This is a HUGE problem for many greek (and not only) users and I hope I now have an elegant(?) solution to it.
\n
\nThe solution that I provide does not require any use of the mysqldump utility at all. Most solutions to the problem I’ve seen so far were more or less using the mysqldump utility like this:
\n$ mysqldump --default-character-set=latin1 --opt -u user -p dbname> latin1-dbname.sql<\/code>
\nSince many people have their blogs on shared hosting or have very limited shell access, the previous solution is a no-go for them because it requires that they contact their hosting support, explain to them what they want and wait for their reply. If they are lucky they might get the .sql file…else…they are back to where they started.<\/p>\n

My solution:<\/strong>
\nFirst of all it is based purely on this post: http:\/\/combatwombat.7doves.com\/2008\/10\/26\/mysql-latin1-to-utf8-issues<\/a> .While that post does not mention greek characters at all, it gave me an idea of how it should be done.
\nIn order to solve the problem using my solution you need a Linux or a MacosX host. This is because the solution is based on a bash script that needs the sed utility for character conversion. Both bash and sed are of course not included on a default windows installation. So if you are a windows only user you can either install those tools through cygwin<\/a> and try if it works (never tested), ask a friend of yours that uses linux or macosX to help you, boot a linux live cd, or install linux \ud83d\ude00<\/p>\n

What every hosting solution definitely has is access to mysql databases through phpmyadmin<\/a>. Even if your hosting provider or control panel does not provide it for you, you can always install it manually. One of the easiest things to do on phpmyadmin is export a database. Just open phpmyadmin, select your database, click on export on the right, select some or all of the database tables you want, select “Save as File” and click on zipped. Click on “Go” and after a few seconds you will have your .sql.zip file sent to you. If you find that hard to do please ask a friend. Please don’t blame me for blowing up your mysql database if you don’t know how to handle these simple directions..
\nLet’s say that the db name was sample-db, then you should have gotten a file named: sample-db.sql.zip
\nUnzip it:
\n$ unzip sample-db.sql.zip<\/code>
\nand then edit it with a text editor,vim for example:
\n$ vim sample-db.sql<\/code>
\nIf you are suffering from the problem mentioned before you will probably see things like:
\n\"greek-utf8-inside-latin1\"<\/a>
\nTo start the conversion you need to download<\/strong> the following script: greek-convert-latin1-to-utf8.sh<\/strong><\/a> or greek-convert-latin1-to-utf8.sh.gz<\/strong><\/a> (you need to extract the .gz)
\nThe make it an executable: $ chmod +x greek-convert-latin1-to-utf8.sh<\/code>
\nAnd then execute the script with the database as an input:
\n$ .\/greek-convert-latin1-to-utf8.sh sample-db.sql
\nI'll work on sample-db.sql ...
\nsample-db.sql...done<\/code>
\nThen you will have a new file named sample-db.sql.clean as output on the same dir you ran the script.
\nOpen it and you should now see every post in pure utf8 greek like this:
\n\"greek-utf8-inside-latin1-converted\"<\/a>
\nAs you can see 99% percent of the characters were correctly converted to proper greek utf8 ones. I don’t currently have the time to investigate why a few characters don’t get properly converted, but I’ll soon find a solution for that too \ud83d\ude42<\/p>\n

What’s now left to do is to import the sample-db.sql.clean file to your new hosting…you can do that of course through phpmyadmin…<\/p>\n

The conversion table that I used is here:greek-replacement-latin1-to-utf8.ods<\/a><\/p>\n

This might be a late solution, since the problem is quite old, but I am sure that there are many people still having headaches over issues like this. Enjoy \ud83d\ude42<\/p>\n

Downloads:
\ngreek-convert-latin1-to-utf8.sh<\/strong><\/a>
\ngreek-convert-latin1-to-utf8.sh.gz<\/strong><\/a> <\/p>\n","protected":false},"excerpt":{"rendered":"

The Problem To sum it up, the case is this: many many many web applications were programmed so that they used latin1 collation for their fields inside mysql databases. But most users now use utf8 from within their browsers. What happens is that utf8 characters are getting stored inside latin1 fields, which in return produces […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ep_exclude_from_search":false,"footnotes":""},"categories":[11,5,3],"tags":[25,24,600,22,19,26,23,16],"class_list":["post-349","post","type-post","status-publish","format-standard","hentry","category-greek","category-internet","category-linux","tag-bash","tag-convert","tag-greek","tag-latin1","tag-mysql","tag-sed","tag-utf8","tag-wordpress"],"aioseo_notices":[],"views":40873,"_links":{"self":[{"href":"https:\/\/www.void.gr\/kargig\/blog\/wp-json\/wp\/v2\/posts\/349","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.void.gr\/kargig\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.void.gr\/kargig\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.void.gr\/kargig\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.void.gr\/kargig\/blog\/wp-json\/wp\/v2\/comments?post=349"}],"version-history":[{"count":24,"href":"https:\/\/www.void.gr\/kargig\/blog\/wp-json\/wp\/v2\/posts\/349\/revisions"}],"predecessor-version":[{"id":489,"href":"https:\/\/www.void.gr\/kargig\/blog\/wp-json\/wp\/v2\/posts\/349\/revisions\/489"}],"wp:attachment":[{"href":"https:\/\/www.void.gr\/kargig\/blog\/wp-json\/wp\/v2\/media?parent=349"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.void.gr\/kargig\/blog\/wp-json\/wp\/v2\/categories?post=349"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.void.gr\/kargig\/blog\/wp-json\/wp\/v2\/tags?post=349"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}