Convert Database Charset (cdc)

[UPDATE] Note: This script should now work with all versions of MODx, though it hasn’t been tested on every version.

I’ve created a generic script that runs outside of MODx to convert a database from one character set/collation to another. By default, it converts Latin1 to UTF-8/utf8_general_ci, but can be used to convert to any charset/collation. It’s called cdc.php (for Convert Database Charset).

Instructions for MODx (and other CMS) conversion are in the readme.txt file.

http://bobsguides.com/convert-db-utf8.html

Note that the script should not be run as a snippet, but rather as a stand-alone script. The script doesn’t change your database, it simply creates an SQL query that you can paste into the SQL window in PhpMyAdmin to do the conversion. If you choose to modify your actual MODx database in place (after backing it up, of course), be sure your MODx site is offline (e.g. by renaming the root index.php temporarily) when you do the actual conversion. You’ll have to edit the manager/includes/config.inc.php file to use the new charset (e.g. ’utf8’).

I’m looking for some bold testers to make sure it works. I recently converted Bob’s Guides from Latin1 to UTF-8 with it and, so far, all seems well. I advise creating a new database for the converted db and switching MODx to use the new db and its character set. That way, you can always switch back to the original db if things go wrong.

I await your feedback.

Hello,
thank You for this script. Previously I’ve used and it worked like charm.

Now I have some problems. I get this respond:

SQL query:
ALTER TABLE modx_context DROP INDEX PRIMARY ;

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’PRIMARY’ at line 1.

I’m running MySql 5.0.91-community-log and PHP 5.2.9

Do You know what can be the problem?

Luke

I think the script has a problem with the new Revolution DB mod_context table, but I haven’t had time to mess with it.

I think the problem is that the key name is "PRIMARY" and that confuses the script.

It might work if you edit the script after pasting so it looks like this:



Let me know.

Hello Bob,

Unfortunately that didn’t help. SQL reply was:

Thanks for checking. Try this:

ALTER TABLE modx_context
drop PRIMARY KEY;

Hello,
that didn’t work either.

I’ve reinstalled Revo and applyed proper db collation. So I don’t need to change collation by this script anymore. But in future it might be useful to have a revo version of this script.

Regards,
Luke

Thanks for checking. If I have time and can figure out what’s wrong, I’ll fix it.

Meanwhile, if anyone else feels like giving it a go -- feel free.

Hi,

I tried using the cdc.php with the phpmyadmin ver 2.10.11 (same with the remote version)
with a modx ver 1.0.4. After running the script and pasting the query.

I got this error:


I examined the content of the database and seen that some of the fields where
converted into a BLOB. Though, the collation is still in latin1_swedish_ci is
this because the convertion did not took place because of the error?

How can I get rid of this error? Sorry I’m still very young at these stuffs.
Hope you can help. Thank you.

Cheers!

Yes, the conversion didn’t finish (or worse, only part of it finished). All the text fields are converted to blobs, then back again in the final step.

There’s a problem with CDC and the Revo DB (and maybe the newer Evo DB). Unfortunately, I’m swamped and won’t have time to look into it for some time.

What version of MODx were you trying it on?

Bob

Yes, the conversion didn’t finish (or worse, only part of it finished). All the text fields are converted to blobs, then back again in the final step.

There’s a problem with CDC and the Revo DB (and maybe the newer Evo DB). Unfortunately, I’m swamped and won’t have time to look into it for some time.

What version of MODx were you trying it on?

Bob

Hi,

I’m using Evo 1.0.4 rev 6981. Do you think the script will work on this version? Does changing the syntax manually will help?

Cheers!