Convert DB to UTF-8

Quote from: wordcooper at Feb 20, 2008, 07:15 AM

I don’t know if this is what you are talking about, but I had to do the same thing. I backed up the database in Modx and then opened it in Programmer’s Notepad (or TextWrangler), did a search and replace on all the charsets and changed them to utf8. Then dumped the whole thing back into phpMyAdmin. It worked pretty well.

It sounds like you are already doing some cutting and pasting, so why would your snippet be better? Would it be faster?

Not faster but I think safer. With your method, as I understand it, there may be characters in your database that are stored in Latin1 that would take a different form in UTF-8, but you’re telling the database during the import that they’re in UTF-8 format (which they’re not). So the import is corrupted. For regular ASCII characters, this won’t matter, so you can often get away with it. If there are problem characters there, though (say in an encrypted user or manager password or a blog comment, movie or song title, etc.) it will bite you. I tried your method on a MODx install once and wasn’t able to log in because my password was no longer recognized.

One solution is to use the iconv program on the text file to convert its charset before importing. My snippet, if it ever gets finished, would be an alternative.

See this article, though, for a worst-case scenario of what can happen with your method: http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html

Bob

Wow. My database was still only a shell/test, so I didn’t have anything important in the actual content. I guess what I was doing was just changing all the collation charset from latin1 to utf8.

Are you going to map out conversion characters, too?

Looks interesting.

Quote from: wordcooper at Feb 20, 2008, 11:57 AM

Are you going to map out conversion characters, too?

I’m not sure what you mean by that, but the snippet changes the field-type of all the text fields to "blob" which converts them to binary representations that are not tied to a character set. It does this before making the charset changes, then converts them back to their previous values (e.g. varchar, etc) afterwards. So the character conversions are, in theory, safe and automatic.

I hope this makes sense. It just barely makes sense to me.

BTW, please don’t confuse me with an expert on character sets and collation (Jason is the only one I know of around here). I’ve done a lot of reading on it but much of it is still a mystery to me.

Bob

Still some bugs due to indexes not being in the same order as the table fields. I think I’m getting closer. Stay tuned.

Bob

Maybe not. Disaster ensues -- see update in post at the top of this thread.

Bob