Convert DB to UTF-8

I’ve taken some code designed to convert a WordPress database to UTF-8 and converted it to a MODx snippet.

It doesn’t modify the DB. Rather, it creates the SQL statements to be cut and pasted into PhpMyAdmin to do the job so it’s harmless to run the snippet itself.

Basically, the SQL converts all the text fields of the DB to blobs (remembering what they were), alters the db charset and all the table charsets, and then converts them back.

[Update - 2/22/08]

I got the index problem sorted out and the snippet now appears to output exactly the SQL statements necessary to do the conversion. The down side is that it now trashes my site content.

The content field of one of my pages has an anomalous character at about character 40 and the conversion truncates it at that point.

The same thing happens when I just go in PhpMyAdmin and convert that field to a BLOB or MEDIUMBLOB and then convert back to MEDIUMTEXT using utf8_general_ci as the collation. Converting the table and/or the db to utf8 before converting back doesn’t seem to have any effect.

It’s got nothing to do with MODx because the data is actually missing in the DB immediately after converting the field.

I’ve uploaded the new file here in case anyone wants to play with it. It’s harmless as long as you don’t paste the SQL statements into PhpMyAdmin and execute them. You can see what it would do and you can look at the source, which has some interesting code for finding text fields and then finding and dropping/restoring their respective problem indexes.

BTW, the code to put the fields back to their proper type is incomplete and should include statements for setting DEFAULT and NULL. It wouldn’t be too difficult to add this but I’m not sure there’s any point now.

It’s lucky that the anomalous character was there because, otherwise, I might not have realized that the snippet was unsafe to use.


Bob

I saw this WP script a while back but did not use it, I went for another approach that worked moderetly. This can be very helpful since collation/charset issues with databases are more common everyday. Let us know once you get up the nerve to test it

Well, I have a report.

It sort of works and sort of doesn’t.

The SQL generates errors on a number of MODx tables (about 12) because some text-based indexes can’t be converted to blobs. Adding a key size to those indexes seems like it should fix this, but I haven’t been able to get that to work. The errors can be avoided by dropping those indexes.

Unfortunately, the query aborts on these errors and the db is left half converted, so I had to run it over and over to find out which indexes it would choke on. (Does anyone know how to get PhpMyAdmin to continue on errors?).

After dropping all the indexes. Everything ran fine, the db character set was converted, and my site and the manager look fine so far. Now I can go back and add the indexes but it’s hardly the simple fix I thought it would be.

I wonder if exporting just the structure before doing the conversion would let me re-create the indexes with an import?

I’ve uploaded a new version of the .zip file after fixing some possible SQL errors.

Any ideas appreciated.

Bob

More news.

I was able to modify the key size for all but one of the tables with problems. It would be possible to create SQL to do this and add it to the snippet but I’m not sure if I’ve picked the appropriate key sizes. They could all be set to the max for the field but I’m not sure if that’s necessary or wise. MySQL experts?

The only one I had trouble with is a key called content_ft_idx in the site_content table. It’s a fulltext index on the pagetitle, description, and content. I had to drop it to make the query work. Does anyone know what it’s there for?

Once I added the key sizes (which could be set during the MODx install, unless there’s a good reason not to) and dropped the content_ft_idx index, everything ran without a hitch. The tables and the db are fully converted, the site looks fine and all the snippets run, and all the manager tasks I tried work as expected.

Here are the tables / fields I had to fix in case anyone wants to try it:

Just set the key size of the index field to anything up to 1 less than the field size (i.e. a varchar(100) field can be set to anything up to 99).

document_groupnames / name
manager_users / username
membergroup_names / name
site_content / pagetitle
site_content / alias
site_content / keyword
site_content / content_ft_idx (drop index)
system_settings / setting_name
user_settings / setting_name
web_user_settings / setting_name
web_users / username
webgroup_names / name

Bob

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

You generally don’t need to specify key size. You really should always drop indexes when performing any structure changes on a table or doing mass imports (you’ll find it takes a lot less time to insert records with the indexes dropped, and very little time to recreate them). mysqldump creates files that do this. If you can create a sql file from this tool, you can get the proper statements for recreating them. (Or just dump the structure from phpMyAdmin)

mysqldump and PhPMyAdmin export, as far as I know, will only make "create table" sections:



I would need one series of ALTER TABLE statements to drop the keys and another set to add them back later. I don’t know of any program that will create them.

I can definitely use the dump file to tell what the keys are and create the drop/add SQL by hand, but then I’ve hard-coded the structure of the DB for a particular version of MODx. I was hoping for something more generic (and safer).

I guess, in theory, I could have the code look for the problem keys in the db and then generate the drop/add statements, but that’s getting into some pretty hairy coding (for me anyway).

Is there any harm in having my code alter the DB so those indexes have a key size and leaving it that way? That seems like a safer approach.

Bob

Quote from: sottwell at Feb 19, 2008, 12:46 AM

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Sorry, I wasn’t clear. I wanted to know if there were any consequences of permanently dropping that particular fulltext index in the sit_content table. Does MODx use it for anything (Ajax-Search?).

Bob

Yes, removing that index would destroy search functionality.

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?