Thursday April 10
 
18:55
 
Arrrgh! Databases Screwed Up

Lately I’ve been moving my websites over to my new webhost, Cirtex. I’m doing the process myself rather than getting Cirtex to do it because, basically, I only trust myself to do it. I want to make sure every last thing is tested and working. I have a lot of history tied up in my websites..

Today, when testing one of my sites, I discovered that some of my databases got screwed up on the move over. The cause? Character set and collation options. My previous webhost uses a version of MySQL which is pre-”character set and collation options”, Cirtex uses a later version.

I knew that, and I thought I’d caught every possible snag related to collation options in this post but I hadn’t. Despite using phpMyAdmin for both the export and the import, today I found some text fields had been chopped. It seems that the export from the old MySQL server contained characters such as the accented ‘e’ in cafe and other control characters. When imported into the new server, phpMyAdmin didn’t like those characters and chopped the text without warning. The solution seems to be to open the exported file in Editpad Lite, convert to UTF8, and then upload that file.

It is very annoying - and scary, because I only found it by accident. I was testing something else at the time when I noticed a truncated value. I was already very wary that after I finish the move and cancel the old webhosting package, I’d suddenly find something wrong - not I’m VERY VERY wary that will happen. I’ll need to go through extensive testing.

Luckily there has been no damage to The Korean Blog List, but some of my other sites will need their entire databases redoing - and retesting, of course. Thankfully, in the end there will me more benefits than just having moved webhosts - I’ll have tidied up my sites, have verified copies that I know I can restore, and I’ll be on servers that handle character sets correctly - so I shouldn’t have this problem again. If I stayed with my current webhost, I might suddenly find a new server thrust upon me and not have the leisure to get things right.