12.10.05
Migrating data from MySQL 4.0 to 4.1
Well, this took me some time. I needed to migrate all data generated by our Slovak Game Development portal based od TikiWiki 1.9.1 and MySQL 4.0 to another server with TikiWiki 1.9.2 and MySQL 4.1. Sound easy, eh? I’ll explain why it wasn’t.
First and least important, my Internet connection is quite slow and I had to move 4.5MB text/SQL data, or 1.8MB compressed – some 200 tables.
MySQL 4.1 introduced one nasty limit – well, maybe it isn’t serious real world problem, but TikiWiki (or Tiki CMS/Groupware if you want) database schema up to version 1.9.1 was unoptimised – primary keys consisted of more than 333B limit. So, I’ve spend quite a few hours trying to modify them to fit in the limit (before the release of 1.9.2, of course).
Anyway, when I’ve finally managed to import database structure to destination server, the other problem popped up and it seemed unsolvable this time. Migrating data didn’t seem to be much of a problem – export data with phpMyAdmin, save them on local disk and then import them to using phpMyAdmin. After some minor problems with table tiki_searchindex, which outputted duplicate entry errors that resulted in typically flegmatic solution (skipping this table entirely), it totally messed up all characters with diacritical marks – Tiki outputted question marks instead of them.
Strange thing was that phpMyAdmin showed everything the way it should be – so I’ve played around with resaving exported data under different charsets, and uploaded and uploaded data all over. Good sign was that content I’ve modified from TikiWiki was properly saved and displayed. When I’ve looked into phpMyAdmin, saved data seemed messed up – local characters appeared as when you view UTF-8 characters in some 1Byte charset – for example, there was an “ý” instead of “ý”.
It didn’t make any sense, since all my tables were using UTF-8 encoding. And when I’ve tried to view original database dump in ANSI instead of UTF-8, copy and paste the INSERTs into phpMyAdmin and execute – it messed things even worse (I got ‘??’ for every local char instead of just plain old ‘?’).
OK, enough of boring description. How have I solved it?
- Export from original database in plain text or compressed, doesn’t matter.
- Import ONLY uncompressed – and even when you’ve saved it in UTF-8, import it as Latin1. It seems that phpMyAdmin ignores this setting if you upload [g|b]zipped file.
That’s all, all characters are now displayed correctly. Easy, wasn’t it? Contact me in case you have similar problems.
