Illegal mix of collations

I've never heard of this error.

Which database are you using?

Also, did you specify utf8-unicode-ci? I don't believe that is the default character set.

This is a Mysql database. Yes I set the encoding that way, I think it was original set to utf8-swedish? ( I really don't recall the original with specificity).

I use Greek and Hebrew on my site and needed to ensure that it would display correctly which is why i set it to utf8-unicode-ci.

MySQL database 4.1.22
PHP 5.2.5
Drupal 6.4

Looks like you don't have the same collation on the 2 tables. I tested setting just the collation of url_alias to utf8_unicode_ci and it caused this error.

Could you check the two tables and see if maybe this is the case?

I changed my collation on the url_alias to utf8_general_ci and reattempted bulk generating some of the blog paths and got the white screen of death.
The logs are not showing anything really useful. Browsing the site still works (After a few minutes in which something was surely being reset).
I have yet to determine what happened at the white screen.

@tcblack - if you have to change the collations on your site then that's not something that can really be supported in the pathauto issue queue so I'm marking this "fixed".

I have this problem too. I haven't edited any collations or encoding on my site. I don't even know how to edit that.

"user warning: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' query: SELECT n.nid, n.vid, n.type, n.title, n.uid, n.created, n.language, alias.src, alias.dst FROM node n LEFT JOIN url_alias alias ON CONCAT('node/', CAST(n.nid AS CHAR)) = alias.src WHERE alias.src IS NULL AND n.type IN ('photos','blog','event','forum','listing','page') LIMIT 0, 100 in" ..."drupal/modules/pathauto/pathauto_node.inc on line 101."

Now I've changed the database, all tables and all fields to "utf8_unicode_ci" but the error is still there...

Ok. Let's set it back to active. Unfortunately the situation remains that I don't know how to fix it :(

And for completeness, marked #308383: Illegal mix of collations as a duplicate.

So, is "utf8_unicode_ci" the wrong encoding? If it is, I should figure out how to make it "utf8_general_ci" right?
It was "utf8_unicode_ci" automatically upon the Drupal installation. I installed Drupal with Fantastico.

I have fixed the problem on my site. I don't know why the error happened in the first place though....

This is what I did:
- I copied a script from http://bogdan.org.ua/2008/02/08/convert-mysql-database-from-one-encoding... and made a php file within my drupal folder.
- I used my browser to run that php file.
- This gave me SQL code that I put in the "SQL" box in phpmyadmin.
- It would change a bunch of tables and fields then give me an error... for the ones that I got error on, I just changed them manually then continued using the SQL code where it left off. Repeated this a bunch of times...

Now every table and field in my Drupal installaation is in "utf8_general_ci" and I no longer get the error I originally did.

It would be nice if the root cause of this error was fixed in case it happens to other people... so if anybody needs more info about my Drupal installation or hosting setup or anything, just let me know.

@taite11 - I'm glad to hear you were able to fix it.

This seems like a configuration issue with mysql rather than a bug in Pathauto.

So, I'm marking this support request and fixed.

Automatically closed -- issue fixed for two weeks with no activity.

It seems that the CAST function defaults to utf8_general_ci. I don't know how to configure mysql to use my collation for the CAST function. But using COLLATE in the ON part of the JOIN removes the error.

From pathauto_node.inc:
$query = "SELECT nid, type, title, uid, created, src, dst, vid FROM {node} LEFT JOIN {url_alias} ON CONCAT('node/', CAST( nid AS CHAR)) COLLATE utf8_general_ci = src COLLATE utf8_general_ci WHERE src IS NULL ". $type_where;

The important part is ' ON CONCAT('node/', CAST( nid AS CHAR)) COLLATE utf8_general_ci = src COLLATE utf8_general_ci

Hope this is of some help.

Sorry, changing back to version 6.x-1.1. Didn't realize I changed the version of the whole topic.

I've acquired this problem using since I started using pathauto with Views. (D6.8; Views 6.x-2.0-rc4)

"user warning: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' query: SELECT s.uid, v.updated FROM views_object_cache v INNER JOIN sessions s ON v.sid = s.sid WHERE s.sid != 'fa2de7a0702eee2721253f9b8d58f4f0' and v.name = 'list_content_sorts' and v.obj = 'view' ORDER BY v.updated ASC in xxxxx/sites/all/modules/views/views_ui.module on line 235."

From reading above, it seems like pathauto is generating the problem.
I'll now try and understand comment #11 and see if I can fix my tables. And maybe leave pathauto off until it is clearer.
Suggestions for an easy fix welcome -- I'm new to MySQL.

@johanh - The issue is covered at http://dev.mysql.com/doc/refman/5.1/en/charset-convert.html. The collation of the CAST function is determined by a system variable which can be overridden in a couple ways per the MySQL documentation.

Thanks for providing that link and insight, DwightAspinwall!

johanh's code works, also for me, in the same situation but different coding, how about making a patch to pathauto_taxonomy.inc?

Sure, what should the patch look like?

AFAIK, this issue comes from a lack of configuration of MySQL which means there is nothing Pathauto can do.

Not sure this is so - my MySQL database is defaulted to use UTF-8 Unicode (utf8) , collation utf8_unicode_ci -- but a number of tables have been added by modules that create the tables as utf8_general_ci. It seems to happen when a module has been enabled: book, search, contact, views, and wywiwyg have all created tables as utf8_general_ci... This doesn't seem like proper behavior.

I'm using Drupal 6.9.

This thread also seems relevant:
http://drupal.org/node/349654

This article implies that some modules create tables with the wrong collation:
http://www.filination.com/tech/2007/04/27/drupal-change-db-and-table-col...

In my case, the collation_connection system variable mentioned in http://dev.mysql.com/doc/refman/5.1/en/charset-convert.html is set to utf8_unicode_ci. But I have everything in my database set to utf8_general_ci (because it is faster and because I don't have any language with ligatures and expansions).

I would expect problems if the CAST gets its collation from the collation_connection variable. Despite that, the bulk generation of path aliases works fine. No collation errors. My guess is that only inconsistent collations between tables or fields will cause errors.

Always check the default collation of any new database that you have created, in the "Operations" tab in phpmyadmin. Drupal itself doesn't specify a collation, and I have never found a module, core or contributed, which creates its tables in a collation different than that. Also watch out when you load it to a development database, which may have a different default collation, and you install new modules there.

Some fixes for the tables (for those looking):

alter table table default collate your_collation;

Or, dump and restore the data if your data dumps reset the tables to the correct collation.

It still seems to me like there is nothing for us to do in Pathauto itself.

Automatically closed -- issue fixed for 2 weeks with no activity.

(My first post to drupal.org!!!) I had the problem today... My website requieres support of chinese and spanish.

My Warning said the problem was on the SQL statement, since in pathauto_node.inc the search is for the nodes without a path by looking for 'node/'.

I replaced the CONCAT part like this (line 100):

CONCAT('node/', CAST(n.nid AS CHAR ) COLLATE utf8_unicode_ci
CONCAT('node/', CAST(n.nid AS CHAR )

After this I tried the module again. This is not a complete fix since it wont work for every collation... it could be an interesting patch, maybe for the next versions pathauto could try to avoid the CAST function for the sake of collation issues?

@dwightaspinwall Thanks for the link was very useful.

That doesn't work across all databases so we can't do that. I suggest changing the database settings and table definitions themselves rather than changing the code.

johanh's fix was able to work for me. Thanks.

I have all my databse tables set to unicode. Is this a good idea? or will general work better? My site language is english.

About those two Unicode collations:

utf8_general_ci is the default utf8 collation.
- It is a little faster.
- Less worries about conflicts, because if you or your modules never mention a collation, everything utf8 will default to this one.

utf8_unicode_ci supports correct sorting of special double letters and ligatures. You only need it if you have such letters *and* you care about their sorting order.

In any case, johanh's suggestion in #14, with the explicit COLLATEs, should work in any emergency.

Thanks for the uft overview.

I changed all my tables over to general_ci. My database became unicode_ci, only because I installed using fantastico. It gets annoying to get the illegal mix error, since all my new modules use general_ci.

In case you get more of those errors, also keep in mind that a collation may be specified
- for the whole database, as its default
- for each table
- for each column in a table.

phpmyadmin lets you handle those from the "Operations" menu (if I remember correctly).

Okay, I'm running Drupal 6.14 and PathAUto 6.x-2.x-dev (2009-Sep-14) and I got the error.
Basically, it requires you to go into the PATHAUTO module inside .inc files and ADD a small blurb of code after the SQL statement.

The code you'll be adding is

COLLATE utf8_general_ci just before the "= src" part of each query.

Some examples of the fix are below. Good luck. I don't know how to make a patch, sorry.

Here is the fix.

In /modules/pathauto/pathauto_node.inc

Around line 100

Find this:

Change to this:

(notice the insertion of COLLATE utf8_general_ci )

In /modules/pathauto/pathauto_taxonomy.inc

Around line 83

Find this:

Change to this:

(notice the insertion of COLLATE utf8_general_ci )

In /modules/pathauto/pathauto_user.inc

Around line 94, 140, 116
same thing...

Thanks to everyone above who contributed to this issue.

It appears that it is a Fantastico install that is a culprit in this illegal mix. For those less tech savvy (me), here is a cPanel/phpMyAdmin solution.

Launch cPanel -> phpMyAdmin and choose your Drupal install database (on the left).

You'll then get a long list of all the Drupal tables on the left. Look at your encoding error for the table or tables that are encoded wrong (for instance: views_object_cache) and select it.

In the center of the page you will get more sql data. Make sure the tab "Structure" is selected on the top. You will see a list of fields and how they are encoded. You will need to select only one row that has a "general" encoding in the Collation column. Then, select the pencil button at the bottom of the table which will take you to an edit screen where you select the "utf8_unicode_ci" from the pop-up. Select save and repeat for any other fields.

Important: Once all the fields are changed, select the Operations tab and change the Collation on this edit screen to utf8_unicode_ci."

If you have a lot of fields, this takes some time but it works. I have only seen issues with the image fields on this post so if that is true, then there are only about 20 fields across all the image tables. ~Chad

@Chad

Thanks. This worked like a charm.

@DrupalMagic - you made this active again. Is there something you think Pathauto should do to fix it?

*NOT* a Pathauto problem. This same thing could go wrong many many other places in Drupal if you don't use the same collations in your database tables.