By bslade on

When using CivicSpace 0.8.2 (Drupal 4.6.3), when I manually added a user, I got the error:

user error: Illegal mix of collations 
(latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)  
for operation '='
query: SELECT * FROM profile_fields 
WHERE LOWER(category) = LOWER('account') 
ORDER BY weight 
in /<homedir>/public_html/includes/database.mysql.inc on line 66.

But the account did get created successfully. Is anybody else seeing this?

Looking at the profile_fields table, the category field does have a collation type of latin1_swedish_ci, but there's no account field in that table. I'm confused.

My MySQL server does have a default MySQL charset of UTF-8 Unicode (utf8) and a "MySQL connection collation" of utf8_general_ci (as reported by phpMyAdmin)

Thanks in advance
Ben in DC

Comments

SavoryMedia’s picture

SavoryMedia commented

I'm having a very similar error when trying to save anything to do with a user. Anyone have any ideas?

MJ - Armageddon Design - http://armageddondesign.net/

(also in DC)

bslade’s picture

bslade commented

On my MySQL server/database, it looks like all the tables created by Drupal have a collation (sorting?) sequence of latin1_swedish_ci (probably the default for my MySQL server), but all the tables created by civicrm have a collation sequence of utf8_unicode_ci.

Searching through the databases/*.mysql table creation code, I don't see any command overriding the default server collation.

If I disable CiviCRM, the problem goes away!

There is a note at ObjectLedge.com about the CiviCRM collation sequence being changed this past May (2005).

Also see http://civicspacelabs.org/home/node/15173

Ben in DC

bslade’s picture

bslade commented

Hmmmm, looking at the output of the "SHOW COLLATION" command in my MySQL server (hosted by webhostfreaks.com), I think I see the problem. Out of a long list of collation options, "latin1_swedish_ci" is the first one that uses the latin1 charset and is flagged with Default=Yes. So when a table is created with the default charset (latin1) it gets the first defaultable latin1 collation sequence, aka latin1_swedish_ci. Anyway, I don't know why phpMyAdmin is saying "utf8_general_ci".

They are two different character sets here: latin1 for Drupal/Civicspace tables and unicode for CiviCRM tables. Can collations be valid across different character sets. Seems like the answer should be no, but then again unicode is generally a superset of latin1 so maybe it's possible.

It looks like the CiviCRM people know about this and are working on a solution.

Ben in DC

alexis’s picture

alexis commented

Hi, I had a similar problem when downgraded one Fedora Core 4 server from PHP5 to PHP4. I was running MySQL 4.1.11-2 and used yum to upgrade to MySQL 4.1.16-1.

This fixed the problem for me.

I'm running latest Drupal from CVS.

Regards.

Alexis Bellido - Ventanazul web solutions

alexmc’s picture

alexmc commented

I too am using Fedora Core 4.

If I try to upgrade MySQL on a live server and it goes wrong can you tell me how to undo the yum upgrade?

(Thanks for your time!)

alexis’s picture

alexis commented

You should backup all your databases first and have available rpm's of your current Mysql version, so you can yum -remove and then reinstall if needed.

Be very careful, however, and read more about it in Linux or MySQL forums if needed.

Regards!

Alexis Bellido - Ventanazul web solutions

SavoryMedia’s picture

SavoryMedia commented

What about those of us who are using shared hosting and don't have the ability to do any of this?

I'm assuming that with all the talk of changing MySQL versions and such that the Drupal development team have no intention of fixing the database problem on their end...?

For the record, I've tried all versions of 4.7 (including beta 5) and am still having this error.

=========================================
MJ - SavoryMedia - http://savorymedia.com/

beppause’s picture

beppause commented

I resolve the problem commenting the rows:

/* On MySQL 4.1 and later, force UTF-8 */
if (version_compare(mysql_get_server_info(), '4.1.0', '>=')) {
mysql_query('SET NAMES "utf8"', $connection);
}

of the function db_connect in database.mysql.inc

beppause@gmail.com

qube’s picture

qube commented

I had problems with charsets and collations in MySQL, beacouse I'm using czech language and database version 4.1.

I corrected the problem in three steps:
1) setting database charset and collation to utf8 charset and utf8_czech_ci

ALTER DATABASE drupal_database CHARACTER SET utf8;
ALTER DATABASE drupal_database COLLATE utf8_czech_ci;

2) explicitly setting charsets and collations in all tables:

ALTER TABLE drupal_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_czech_ci;

3) executing following two queries immediatelly after database connect in database.mysql.inc

mysql_query('SET CHARACTER SET utf8', $connection); 
mysql_query('SET collation_connection = utf8_czech_ci', $connection);

'SET CHARACTER SET utf8' sets character_set_client and character_set_results to utf8 and character_set_connection is set to character_set_database, so it will be utf8 too. Then I set explicitly collation_connection to utf8_czech_ci. I could use 'SET NAMES', but it sets collation_connection to default collation for utf8 (utf8_general_ci). Beacouse I set collation_connection myself, I dont need SET NAMES.
More on this in MySQL manual in sections SET syntax and character set support

By this solution, I hope that database don't need to convert character sets and there is never bad mix of character sets or collations. You can check your settings by executing queries SHOW VARIABLES LIKE '%character%' and SHOW VARIABLES LIKE '%collation%'

cwalkabout’s picture

cwalkabout commented

Thanks a lot for this, qube!

I transferred data from MySQL 5.0.24a to 4.1.11 (naively assuming it would just work) and got the collation errors on login. Steps 1 and 2 worked for me (replacing utf8_czech_ci with utf_general_ci). As I used utf_general_ci, I didn't need step 3.

coplan’s picture

coplan commented

Is there a way to alter/convert all tables at the same time? Or do I have to convert them one at a time?

-- Coplan
~/ http://www.antisoc.net - Socialize about your antisocial hobbies
~/ http://luminosity.antisoc.net - Coplan's Luminosity

cwalkabout’s picture

cwalkabout commented

I don't know of one command you can use. I created a set of SQL statements in an editor and then ran them all at once (after testing one command first, of course :).

qube’s picture

qube commented

I think, there is no SQL command to alter all tables at once. But you can try following tips:

1) If you have dump with table structure and without charset and collating information, you can first create or alter db to use desired charset and collating settings and then import dump. As there are no charset and collating information in dump, defaults will be taken from database settings.

2) You can write PHP script to automate it. Query database for all table names (SHOW TABLES) and then iterate through them and issue ALTER command for each.

3) You can write MySQL stored procedure.

loloyd’s picture

loloyd commented

i was trying to interface my own tables within a drupal node. these tables are custom built and unknown to drupal. i was able to lick my own problem by setting "CHARACTER SET utf8 COLLATE utf8_general_ci" for EACH COLUMN of my custom table and for setting its default character set to the same. the funny thing is that it will not work if any of the following conditions are hanging:

1. the default character set for the table has been set to utf8 but the columns are left to latin1. understandably. ok, i submit.
2. the default character set for the table has been left to latin1 but all my columns are set to utf8. crazy? go figure.