Convert Database Charset (cdc)

Quote from: weaknsick at Sep 23, 2010, 12:31 PM

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!

Sorry, I don’t know if it will work or not. I haven’t had time to try it. Let me know.

Yes, changing the syntax in the generated .SQL should work if you know how it needs to change. I don’t know what the necessary changes are. Let me know if you find out.

Sorry I can’t be more help at this time.

Hi Guys!

I got a stupid question. I installed modx in my local using xampp and created a database set to utf8.
After the installation on my local computer. I exported the tables over to my remote server.

I logged-in at the modx manager and see the database connection reports. I saw my database connection
charsets and collations are still in LATIN1. My question is why does modx still reported that my
connections charsets and collation are still in LATIN1 even though I manage to export a UTF8 database?

How will I manage to make the connection charsets and collation be in UTF8?

Thank you.

Conversion is complicated, to say the least. There are a bunch of server variables that may not match up. If you go to the "home" page in PhpMyAdmin and click on "Variables" you can see what all the settings are.

The problem is that you need to change the variables, but you also need to change the character set of the data itself. The only reliable way to do that is with an alter table command to convert each text field to a blob, then another alter table command to convert the character set of the table, then convert the field back to the correct type. If the field is an index, you have to drop the index before you can do the conversion, then recreate the index when you’re done. The CDC script did all that automatically, but it no longer works, either because of changes in the MODx DB or changes in MySQL (or both).

I’ve spent a fair amount of time trying to get it to work but, TBH, I’m not having much luck. Sorry.

I find that I have to use CPanel to create the database, then use phpMyAdmin to change the collation to what I want (usually utf8_general_ci), then I can import/install or whatever.

If you’re using a localhost dev environment, you can edit your own my.cnf or my.ini to make utf8 the MySQL default.

Quote from: sottwell at Oct 08, 2010, 12:45 AM

I find that I have to use CPanel to create the database, then use phpMyAdmin to change the collation to what I want (usually utf8_general_ci), then I can import/install or whatever.

If you’re using a localhost dev environment, you can edit your own my.cnf or my.ini to make utf8 the MySQL default.

I don’t think that changes the charset of the data properly (though I could be wrong). If it were that easy, there wouldn’t be all the complex instructions for conversion out there (most involve manually dropping all text-based indexes, converting text fields to blobs, converting them to text again, and recreating all the dropped indexes). I’m trying to do all that in code.

I am making some progress with this. Part of the problem was that some of the fields in the MODx DB are MySQL key words (key, constraint, and release). I’ve dealt with that, but in the latest MySQL, "SHOW FULL COLUMNS FROM " . $table; and "SHOW INDEX FROM ".$table; don’t return what they used to so identifying compound keys is trickier. Also the SQL for dropping a primary key seems to have changed.

I think I’m getting closer, but it’s hard to tell until I can generate a batch of SQL statements that will run throwing an error.

Ah, I was talking about setting up the database initially. Actually converting existing data from one charset to another is indeed another matter altogether.

Quote from: sottwell at Oct 09, 2010, 12:27 AM

Ah, I was talking about setting up the database initially. Actually converting existing data from one charset to another is indeed another matter altogether.

No kidding. I thought I had it earlier tonight, but it’s still not handling the compound primary keys properly. It also chokes if a compound key has the same name as one of its elements (no MODx tables do that, but a couple of my own custom tables do).

OK, I’ve got it working on Revolution now. It should still work with Evolution as well. Let me know.