How to modify the collation of an existing MYSQL database?

4,205 Posts

dev_cw Reply #1, 18 years, 11 months ago

I unknowingly made a major mistake, my database defaulted to one collation but it needs to be in another. I am having sorting problems with special chars (ö comes before a) and it has been called to my attention that it is most likely a collation error. Sure enough I tested this on another db with uft8 collation and it worked fine.

Now I need to change the collation (and charset) of an existing db from ’latin1_swedish_ci’ to ’utf8_unicode_ci’. I have no idea how to accomplish this. I think I will need to somehow parse the data to the new charset.

I found these tips but I do not have root access to this server so this will not help:
http://gentoo-wiki.com/TIP_Convert_latin1_to_UTF-8_in_MySQL
http://drupal.org/node/105151

I guess that I could use a text editor and do a series find/replace to get all the chars.

Anyone have any tips on how to get this done? Is it really necessary or is there some work around that I could try first?

[font=Verdana]Shane Sponagle | [wiki] Snippet Call Anatomy | MODx Developer Blog | [nettuts] Working With a Content Management Framework: MODx

Something is happening here, but you don't know what it is.
Do you, Mr. Jones? - [bob dylan]

☆ A M B ☆
24,524 Posts

sottwell Reply #2, 18 years, 11 months ago

You might be able to run an "ALTER DATABASE ... " query from your database management application. Usually this is phpMyAdmin.

ALTER DATABASE COLLATE collation_name

This depends on whether or not you have ALTER privileges on your database.
http://dev.mysql.com/doc/refman/4.1/en/alter-database.html

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

4,205 Posts

dev_cw Reply #3, 18 years, 11 months ago

That is a start, I was able to alter the database and the tables but it does not change the individual fields within the table.

[font=Verdana]Shane Sponagle | [wiki] Snippet Call Anatomy | MODx Developer Blog | [nettuts] Working With a Content Management Framework: MODx

Something is happening here, but you don't know what it is.
Do you, Mr. Jones? - [bob dylan]

4,205 Posts

dev_cw Reply #4, 18 years, 11 months ago

I found this script that was made for WordPress, I wonder if it will work, with a few modifications, on a modx db?

What it does is transform the fields into blob type and then converts them back, this way mysql will transform the special chars to the correct coding. I read about this method in several places. Otherwise you get stuff like Ã¼ in your content.

The body of the script looks straight forward but it uses WPs db connect to connect to the db:

require_once("wp-config.php");
global $wpdb;

Then uses this to do all the connections:

$wpdb->get_results( $sql_tables );

How do I get the global $wpdb; to reflect ModX’s db connect?

The complete script is attached in case anyone wants to have a look.

----------
Edit: OK, I see that wpdb is the WP database class, so this may ne a bit trickier than I firs thought. Back to the drawing board.

Another thing that I noticed is that once you change the collation/charset of the db you also should edit the ModX config.inc.php $database_connection_charset = ’’; if it is set.

[font=Verdana]Shane Sponagle | [wiki] Snippet Call Anatomy | MODx Developer Blog | [nettuts] Working With a Content Management Framework: MODx

Something is happening here, but you don't know what it is.
Do you, Mr. Jones? - [bob dylan]

4,205 Posts

dev_cw Reply #5, 18 years, 11 months ago

OK, I now have the DB with new collation and charset and have changed all the encoding to utf8... now after all this Ditto still displays the order wrong Ö comes before B!!! I give up, this is too much. If I query the DB directly the order is correct but if I order this in Ditto it is wrong.

I am not saying it is a ditto problem but it is a problem none the less that shows up in Ditto...I am sure ai am somehow responsible for this problem.

Edit: I also notice that if I edit a field through modX it ends up in one encoding but if I edit it directly with phpMyAdmin it ends up in another. By looking into the DB I can see why the order is messed up, the ö is recorded as Ã which IS before B...but I want it to be Õ which would come between N and P.

I am very frustrated with this and have lost several days of work since I can’t move ahead until I resolve this. There is definately a problem between my DB and my MODX.

Edit 2: After thinking about this and looking at the db in latin1 (both encode Ö as Ã) it seems that Ditto is sorting before doing any char decoding since the chars are presenting correctly but are sorting pre decoding, does this make any sense?

[font=Verdana]Shane Sponagle | [wiki] Snippet Call Anatomy | MODx Developer Blog | [nettuts] Working With a Content Management Framework: MODx

Something is happening here, but you don't know what it is.
Do you, Mr. Jones? - [bob dylan]

24,544 Posts

BobRay Reply #6, 18 years, 11 months ago

It seems to me that there is a setting in MODx that determines the character encoding that MODx uses. I thought it was in config.inc.php, but I couldn’t find it there. I don’t think the default is UTF-8.

There’s a message to me somewhere in the forums about this but I couldn’t find that either. I’ll look again and re-post here if I find it.

I found this:

It should be setting the default charset (not collation) in $database_connection_charset (i.e. utf8 or latin1, not utf8_general_ci or latin1_swedish_ci) on upgrade from 0.9.5. If you installed an 0.9.6 RC release, then this likely got set improperly to a blank value which is now causing you problems; in 0.9.6 final, it should now be setting that variable properly if it does not yet exist in your config file. Without this variable being set properly, you are at the mercy of the database server and PHP mysql client configurations with regards to character sets and collations.

You can rerun the upgrade in advanced mode to edit the database settings manually to correct this, or you can just edit the config.inc.php file.

Bob

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

4,205 Posts

dev_cw Reply #7, 18 years, 11 months ago

Thanks for the reply BobRay,

I had found that post as well earlier but did not understand it. Now looking again it seems that a value would be needed to force a charset.

In my config the setting is blank, $database_connection_charset = ’’; I put utf8 as the setting and things got worst so I went back to leaving it blank. I will try it again, give it more time (flush the cache) and see what happens.

EDIT: adding a value to the $database_connection_charset helped the issue regarding editing the DB directly and editing from within ModX. Thanks for pointing that out to me, I had seen it but did not think it really made a difference...I was wrong.

However the sort order issue is still a problem. Sorts perfectly within the DB and is messed up in Ditto. Hummmm, what could this be?

[font=Verdana]Shane Sponagle | [wiki] Snippet Call Anatomy | MODx Developer Blog | [nettuts] Working With a Content Management Framework: MODx

Something is happening here, but you don't know what it is.
Do you, Mr. Jones? - [bob dylan]

225 Posts

nightsignals Reply #8, 18 years, 11 months ago

Quote from: dev_cw at Nov 02, 2007, 10:18 AM

Edit: I also notice that if I edit a field through modX it ends up in one encoding but if I edit it directly with phpMyAdmin it ends up in another. By looking into the DB I can see why the order is messed up, the ö is recorded as Ã which IS before B...but I want it to be Õ which would come between N and P.

What you see in phpMyAdmin is not necessarily reliable, which can be very confusing when handling Umlauts and charsets in mysql databases, because the way phpMyAdmin connects to the database is dependent on server configuration and not necessarily the same as with MODx (or other applications). For me it usually worked better to work with mysql dumps to track problems like that instead of phpMyAdmin.

René

24,544 Posts

BobRay Reply #9, 18 years, 11 months ago

You could try the suggested "update" install of MODx. In Advanced mode, it lets you set the charset.

If that doesn’t work, I’d be inclined to try nightsignals’ idea of using PhpMyAdmin to "export" the database to a file. That creates a text file and you can look in it to see what the char encodings really are for the db and each table. It won’t affect the db itself.

If you’re daring (or desperate) enough, you can try editing the charsets in that file, emptying the db, and "importing" the file. Be sure to back everything up well before trying this.

Bob

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

4,205 Posts

dev_cw Reply #10, 18 years, 11 months ago

Quote from: BobRay at Nov 02, 2007, 03:37 PM

You could try the suggested "update" install of MODx. In Advanced mode, it lets you set the charset.

If that doesn’t work, I’d be inclined to try nightsignals’ idea of using PhpMyAdmin to "export" the database to a file. That creates a text file and you can look in it to see what the char encodings really are for the db and each table. It won’t affect the db itself.

If you’re daring (or desperate) enough, you can try editing the charsets in that file, emptying the db, and "importing" the file. Be sure to back everything up well before trying this.

Thanks for the help folks, very much appreciated

I have exported the database and I found out about a very cool terminal (iconv) command that modifies the charset of a file. This worked great, and with Bob’s suggestion I got the quickedit/modx to input the correct charset. I even made sure that the server headers are OK , which they were. Also by looking at the db file the chars are uncoded correctly (ö shows as ö not as Ã¶®) so that is not the issue.

What is NOT wanting to work and I do not understand why, is that the Ditto sort order which is putting ’ö’ before ’a’. To better illustrate the issue, attached is a screen shot of the ditto sort (sorry I cant post the site yet) and one of the direct query sort.

My ditto call is (the facLastName is a text TV, but the problem also exists with the document vars as well):

[!Ditto? &parents=`12` &display=`all` &sortBy=`facLastName` &sortDir=`ASC` &tpl=`dtFaculty`!]

I wish I understood German so that I could lookk in the German forum, this must have happened to others before, I doubt that I am the first with this problem.

Anyway, the search for an answer continues. I will consider the install suggestion, a bit scarry to do a new install on a almost finished site...I am a bit chicken about this.

BTW here is the command:
iconv -f iso-8859-15 -t utf8 file-to-be-altered.sql > altered-file.sql;

[font=Verdana]Shane Sponagle | [wiki] Snippet Call Anatomy | MODx Developer Blog | [nettuts] Working With a Content Management Framework: MODx

Something is happening here, but you don't know what it is.
Do you, Mr. Jones? - [bob dylan]