Illegal mix of collations after system update

By ca_grover on 26 Apr 2006 at 02:48 UTC

I'm running a version of Drupal 4.6, and have recently updated the underlying system. Now I'm seeing an "Illegal mix of collations" error when I try to login to my sites on this server.

The sub systems that were updated include Apache (minor revision change), PHP (from 4 to 5), and MySQL (to 4.1.14-r1). This is on a Gentoo server (if that helps).

I've searched the forums, and see that the only thing close to a recommended fix is to run the upgrade script. But, the upgrade script warns not to run it more than once (and it was already run once when the current version was installed).

So, is the upgrade script smart enough to not mess things up? Or do I bite the bullet and upgrade to the next version of Drupal. (current install is one or two versions of 4.6 old) I'm backing up the site/db now, in preparation for an upgrade if needed.

As an alternative, isn't there some way to set the collation for a database or table after the fact?

Thanks for any tips.

Comments

MySQL 4.1 problem

Standart commented 26 April 2006 at 03:01

I had many problems with mySQL 4.1, collations and character sets, too. You don't have to upgrade to 4.7 now. But if you are planning on doing so in the future, better get you database in shape now!

Your tables should all have the character set UTF8 and as the error reads, have the same collation (utf8_general_ci or utf8_unicode_ci). Maybe you will have to add a line of code into your database.mysql in 4.6. All detailed information about that are definitely to be found on this site.

I'm not understanding this.

ca_grover commented 26 April 2006 at 03:15

I'm not understanding this. I've seen reference to the database.mysql file in some other posts. But my understanding is that this file is only used when the database is created. Is this implying that I have to dump a full database and recreate it just to change the collation?

I do appreciate the reply, but I'm missing a piece of info in here somewhere that would make things click... Thanks.

Sorry, wrong file

Standart commented 26 April 2006 at 04:37

Your're right. I meant includes/database.mysql.inc . This is, where the connection is made. If you converted your DB to UTF8 you may add SET NAMES 'UTF8'. Read more: http://drupal.org/node/40515

Drupal 4.7 deals with char sets and collations by default depending on the database.

So, I tried the update.

ca_grover commented 26 April 2006 at 03:31

I downloaded drupal 4.6.6, dropped in the files, modified the settings.php with the database URL, and the base site, then tried to run the update script. Oops, can't log in (due to this error), so had to modify the $access_check flag as indicated on the resulting message page.

No updates available. (which means my database is up to date). clicked the update button anyways (without specifying a date), and it seemed to run just fine - just not sure if it did anything. But it doesn't matter because I STILL get these error messages when I try to login. So I can't update my website, yet it runs fine. go figure. almost worth just going back to plain HTML files. (nah, that's just frustration speaking...)

If it helps, here's the output I get when I try to login:

user error: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like'
query: SELECT * FROM access WHERE status = 1 AND type = 'user' AND LOWER('myuser') LIKE LOWER(mask) in /home/www/grover/includes/database.mysql.inc on line 66.
user error: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like'
query: SELECT * FROM access WHERE status = 0 AND type = 'user' AND LOWER('myuser') LIKE LOWER(mask) in /home/www/grover/includes/database.mysql.inc on line 66.
warning: session_regenerate_id() [function.session-regenerate-id]: Cannot send session cookie - headers already sent by (output started at /home/www/grover/includes/common.inc:384) in /home/www/grover/modules/user.module on line 817.
warning: Cannot modify header information - headers already sent by (output started at /home/www/grover/includes/common.inc:384) in /home/www/grover/includes/common.inc on line 192.

I can see the database.mysql.inc file, and find where the query is being executed, but I have no clue where the query is being created. (another post suggested removing the "LOWER" function from the query...)

Any Tips? I really don't want to wipe my database... and start over (with a backup file of course).

SOLVED

ca_grover commented 26 April 2006 at 04:18

Took some digging but found an answer.

First, I made the assumption that the problem was a MySQL problem, not a Drupal problem. After some more digging, I found the method to change the collation for the database/tables. I logged into MySQL as my root user, then ran the following commands:

alter database mydatabase default CHARACTER SET utf8 COLLATE utf8_general_ci;
use mydatabase;
ALTER TABLE access CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Now I can log in.

But, to be safe, I'm going to have to create a script to convert the character set/collation on all the tables. Or do it manually.

This is a good thing - as it turns out my backup routine for my db part was failing.

Thanks for the suggestions and moral support... :)

Here's text you can copy to

ericatkins commented 8 October 2007 at 03:36

Here's text you can copy to run as an SQL command in PHPmyAdmin if you get stuck. Of course, my table structure is different than most everyone so you'll have to remove some lines and replace them with the tables that are in your database. Hope this saves someone a lot of time.

ALTER TABLE akismet_moderator CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE akismet_spam_marks CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE authmap CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE blocks CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE blocks_roles CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE book CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE boxes CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE cache CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE cache_filter CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE cache_menu CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE cache_page CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE client CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE client_system CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE comments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE contact CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE files CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE file_revisions CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE filters CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE filter_formats CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE flood CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE forum CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE gsitemap CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE locales_meta CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE locales_source CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE locales_target CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE menu CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE node CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE nodewords CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE node_access CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE node_comment_statistics CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE node_counter CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE node_revisions CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE node_type CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE onlinestatus CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE page_title CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE permission CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE poll CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE poll_choices CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE poll_votes CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE profile_fields CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE profile_values CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE review CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE role CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE search_dataset CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE search_index CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE search_total CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sequences CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sessions CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE spam_custom CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE spam_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE spam_reported CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE spam_tokens CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE spam_tracker CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE system CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE term_data CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE term_hierarchy CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE term_node CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE term_relation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE term_synonym CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE tinymce_role CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE tinymce_settings CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE trackback_node CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE trackback_received CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE trackback_sent CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE troll_blacklist CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE troll_ip_ban CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE troll_ip_track CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE troll_whitelist CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE url_alias CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE users_roles CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE variable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE vocabulary CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE vocabulary_node_types CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE watchdog CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

---
Eric Atkins | Hushed Casket | Sojourn Huntsville

Thanks for the code snippets!

Acert93 commented 19 November 2007 at 18:14

Eric, I have been wrestling with the issue of MySQL 4.0->4.1 and the charset issue and there is a fairly lengthy thread on this issue actively being discussed. Your information above is potentually quite helpful.

Garbled text often seems to accompany 4.0->4.1 site moves because of conflicts between latin1 and utf8. Maybe you can elaborate some on this point: Since the above commands are being ran on the 4.1 server, this is essentially just changing the table charset "label", so any potential corruption (?) caused by exporting a 4.0 (latin1) database into 4.1 (utf8) won't be resolved? Or am I misunderstanding the issue?

As you can see in the other thread, I was able to remove random garbled text inserted into my websites by doing a dump from 4.0:

mysqldump -udrupal -ppassword --add-drop-table --default-character-set=latin1 drupal > droplatin.sql

And then populated on the 4.1 server:

mysql -udrupal -ppassword --default-character-set=utf8 drupal < droplatin.sql

I am still feeling out the correct way to migrate from MySQL 4.0 to 4.1. A couple of us are exploring this so we can contribute some clear documentation to Drupal. The code snippets above should be quite helpful, so thanks!!

After upgrading from 4.7.11

geerlingguy commented 18 March 2010 at 15:51

After upgrading from 4.7.11 to 5.22, the above queries (adapted to only include tables for my own site) fixed the problems... thanks!

__________________
Personal site: www.jeffgeerling.com

Not For Me

lamb0176 at umn dot edu commented 1 May 2007 at 05:18

I tried that (Drupal 5.1) with no avail. I even used phpMyAdmin to manually set collations to general. Now I just get the error with general instead of unicode.
"user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)..."

Working in 5.2

rrbaker commented 4 August 2007 at 17:02

This solution worked for me in v5.2. I was working in a local copy in MySQL5, ran a simple mysqldump from the command line to be compatible with the hosting provider (MySQL4 only):

mysqldump -u user -p --compatible=mysql4 dbname > dbname.sql

From there I imported that into the database at the host and ran into those same errors listed above. Went into PHPmyAdmin and ran these commands:

alter database default CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE access CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Worked just fine after that. Thanks for posting this problem and solution!

I needed to alter "users" as well for this to work.

sam moore

he/him

Sandy Hook, CT

commented 17 August 2007 at 01:02

Drupal 5.2, upgrading from 4.7 (last of my old installations :-)
Here's what I had to run:
ALTER TABLE users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

After this, i could log on as #1 user.

Thanks everyone for the advice.

============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics

Thanks!

nonenone commented 24 November 2007 at 08:26

Thanks!

Even though i did not ask

mrrijo commented 19 March 2008 at 15:38

Even though i did not ask for support, the solution here helped me for a move.
Thanks to everyone whoever has updated here.

-------------------------------------
Sabarimala Information |Team Ayyanthole

Illegal mix of collations after system update

Comments

MySQL 4.1 problem

I'm not understanding this.

Sorry, wrong file

So, I tried the update.

SOLVED

Here's text you can copy to

Thanks for the code snippets!

After upgrading from 4.7.11

Not For Me

Working in 5.2

I needed to alter "users" as well for this to work.

Thanks!

Even though i did not ask

New forum topics

News items

Our community

Documentation

Drupal code base

Governance of community