By ajwwong on

I have been getting some bizarre and crazy errors, related to collation inconsistencies:

For example,

Fatal error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' query: SELECT i.type, i.sid, SUM(i.score/t.count) AS score FROM search_index i INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid INNER JOIN search_total t ON i.word = t.word WHERE n.status = 1 AND (i.word = 'support') GROUP BY i.type, i.sid ORDER BY score DESC LIMIT 0, 15 in /home/ithou/public_html/includes/database.mysql.inc on line 66

Also some messages of collation conflict with the Latin character set.

This was after an unfortunate implementation of the update.php file.

so, I have three questions:

1) what is the agreed-upon collation that is to be used for drupal?
2) how do I change all of the database tables and objects into one collation format? (I have been changing them all by hand for the past 30 minutes, but at this rate, it will take way too long.)
3) I read somewhere that upgrading to 4.7 might possibly solve the collation problems. is this true?

I have been using 4.6.5 on mysql 4.1.13

thank you,
Albert

Comments

cog.rusty’s picture

cog.rusty commented

I think Drupal's default collation is utf8_general_ci because (a) a collation is not specified anywhere in Drupal's CREATE TABLE statements and (b) utf8_general_ci is the default collation for the utf8 character set in MySQL. Someone correct me if I'm wrong.

The utf8_unicode_ci collation is supposed to be hot but not quite ready yet.

About changes... if your database is English-only, then you don't have much to worry about, because your characters will be in the same places in the character set and their representation will have the same length. The statement is something like:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ajwwong’s picture

ajwwong commented

I appreciate you taking the time to reply. Helps me a bunch both in terms of the valuable information and "moral support"! (It's tough goin' sometimes here in Drupal-land!) :-)

All the best wishes to you and I'll definitely try the Alter table command -- (on a backup database first! at least *this* time :-( )

Take care,

Albert
www.ithou.org

cog.rusty’s picture

cog.rusty commented

Thanks Albert. About your third question, utf8 in 4.7, I have noticed a couple of things. First, a line

/*!40100 DEFAULT CHARACTER SET utf8 */ ;

is always added after the closing parenthesis of every CREATE TABLE statement. I always edit the mysql table definitions in the contributed modules to do the same before I install them.

Also, the update.php now contains logic to make sure that the tables are utf8, and this can also be used by the modules.