user warning: Illegal mix of collations error, help please!

This looks like it can be closed at this point.

This post went on with no solution.

It is usually a database problem caused by a database set up as general_ci instead of unicode. Fantastico is known to cause this among other automated setup programs for Drupal.

Since these posts get indexed in search engines - a solution posted is important or it is garbage in garbage out.

Simply convert the database and the tables.

Use a simple php program below

// your connection
mysql_connect("localhost","username","password");
mysql_select_db("databasename");

// convert code
$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res))
{
    foreach ($row as $key => $table)
    {
        mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
        echo $key . " =&gt; " . $table . " CONVERTED<br />";
    }
}

Where username, password and databasename are all your current values

Save it as something like "convert.php" (no quote marks of course) and push it up to the root of your server

Navigate to the server from your browser and run the script - and it will return "CONVERTED" for all tables it converts.

Delete the file when you are done

The mixed mode error should go away.

It fixes errors like this below - the trigger module seems to bring the warning about mixed mode collations

user warning: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' query: SELECT aa.aid, a.type FROM trigger_assignments aa LEFT JOIN actions a ON aa.aid = a.aid WHERE aa.hook = 'nodeapi' AND aa.op = 'presave' ORDER BY weight in /home/saltatv/public_html/MYSITE.com/profesionales/modules/trigger/trigger.module on line 146.

bobburns, Thank you.

after i run the script it returns:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/shafeez/public_html/convert.php on line 8

encountering same problem as @kapis

bobburns' fix worked for me too. Thank you!

see the correct username and password in your settings.php file

I'm using Drupal 7, installed with Fantastico, and I can confirm the bug and that the fix works. I'm pretty new to this and embarrassingly had to fiddle with it for a while before I figured it out - so I thought I would summarize for anyone else in my position.

If you're getting the error described at the beginning of the thread, it's because of the character encodings of some of your database tables. You can see this if you go to phpmyadmin (probably in your cpanel if you used a one-click installer like Fantastico), then click the relevant database, and look at the field "Collation". You want each table to read "utf8_unicode_ci". To fix it, do the following:

(1) Go to your settings.php file (sites --> default --> settings.php). (Thanks helloanshul) Open it and scroll down past the notes to where you see the Database, Username, and Password. If you used Fantastico, the password was randomly generated.
(2) Create the file as described in Comment #2 (Thanks bobburns) using the Database, Username, and Password from (1)
(3) Put the file in your root directory (usually the folder called public_html). I used Filezilla, but your cpanel probably works too.
(4) Open your browser and navigate to the file. If your site is www.example.com, then go to www.example.com/convert.php

If it worked, you'll see pages worth of executed code for each time that a table's character encoding was converted. You can delete the convert.php file afterward.

Hope this helps.

These would be one of many reasons found on the forums to not use Fantasico for a production install and to use drupals own installer.

Thus sounds like fantastico is the culprit and where the bug needs to be fixed. Installing drupal natively and upgrading natively I don't see this happening. If it's a fantastico thing, drupal.org can't fix it.

woop_light, bobburns I thank you! I thank you greatly!

This worked for me too! Thank you bobburns!

For a while I thought I had forgotten my MySQL user password and then realized that because I'd installed Drupal with HostGator's Fantastico, the password was automatically generated. You can find your password in Drupal's settings.php file.

Problem solved like magic. Can't thank you enough. Great!!

My installation was done by Fantastico DeLuxe - Drupal 7.8.

Problem solved like magic. Can't thank you enough. Great!!

My installation was done by Fantastico DeLuxe - Drupal 7.8.

I wanted to thank #2 bobburns for his fix and #8 woop_light for spelling out how to implement it. It worked for me as well. I had Drupal 7.9 installed, though not through Fantastico.

Followed all the steps, verified correct username, password, and database name provided in settings.php folder. Still get the following error message. Please help.

Parse error: syntax error, unexpected T_FOREACH in /home/esjanoff/public_html/convert.php on line 10

a parse error is usually some kind of typo. goto drupalbin.com pastebin your file (with personal info XXX'd out and link it here or compare your file with the code in comment #2 and make sure you didn't accidentally omit a single quote , double quote and so on.

After hours of looking for the solution to this problem, thank you this worked.

Those that are getting
"Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/shafeez/public_html/convert.php on line 8"

If you are using the cpanel code editor, you may want to try editing the code in a simple text editor and then cutting and pasting it into the code editor. Not sure what was going on but for some reason it wasn't working for me and then I did that and suddenly it worked.

Oh and this will be the last time I ever install drupal with Fantastico. Now I'm starting to wonder if a few of the sudden problems I saw with the last d7 update may have been caused by this too.

it worked, thanks.

Got the Cross on my back!

Genius! It worked. Thanks!

I spent hours on this problem knowing almost certainly it was a database issue but even using phpmyadmin didn't get the job done.

The bobburns script worked.
woop_lite makes is simple
THANK YOU - THANK YOU - THANK YOU

Thank You!

Thanks bobburns - script fixed issue probably caused by fantastico

This works if you have installed via fantastico.

Thanks, above script worked for me! Still (since I am just starting site-setup) I rethink if I should rebuild manually from scratch without using drupal autoinstaller... maybe there will be some other issues coming...!?

Hey dude, I was able to fix this problem tediously by copy/pasting all of the rows that I needed to edit and created a huge query for the database, here are the tables I used, yours might be somewhat similiar. http://pastebin.com/6ULhxYZL

Comment #8 combined with #2 make the solution a breeze. Thanks.

Thank you to everyone, this thread has resolved my problem.
#2 bobburns and #8 woop_light have inspired me to become a Drupal diehard.
This CMS is so robust that it is extremely overwhelming at times. But when I view Drupal.org and my dashboard page it inspires me to create my sites in such a fashion.
Having full control of all the views and activities on my site is beyond comprehension, but not so now that I have Drupal.
Thanks again.

I know this is an older post, but THANK YOU BOBBURNS AND WOOP_LIGHT. FIXED IT!!! :D

I'm so glad for a solution from #2 and #8.A problem that disturbed me for more than a month was finally solved.
The reason why I took so long to consult drupal community was because I thought it was an issue to do with server or local coding.I Just followed #2 and #8 & the problem was solved.

A similar error but apparently caused by ctools is solved by a code change
https://www.drupal.org/node/1806608#comment-8138471
https://www.drupal.org/node/1806608#comment-9251603
PDOException: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=': SELECT s.uid, c.updated FROM {ctools_object_cache} c INNER JOIN {sessions} s ON c.sid = s.sid WHERE s.sid <> :session_id AND c.obj = :obj AND c.name = :name ORDER BY c.updated ASC; Array ( [:session_id] => cyGgQj_7dXvBWdlMWx3QdICZDS2mHC0o8FupGegqQjw [:obj] => view [:name] => test ) in ctools_object_cache_test() (line 145 of /home/reimagi8/public_html/new/sites/all/modules/ctools/includes/object-cache.inc).
The website encountered an unexpected error. Please try again later.