Download & Extend
Issues

Illegal mix of collations error

Posted by sabermouth on February 1, 2009 at 3:55am
Project:Drupal core
Version:7.9
Component:database system
Category:support request
Priority:critical
Assigned:Unassigned
Status:closed (fixed)

Issue Summary

this occurs when i log in and go to my account at http://tango-wavewireradio.cczn.org/
Home
admin

* View
* Edit
* OpenID identities
* Track
* Contact

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 = 'user' AND aa.op = 'view' ORDER BY weight in /home/cczn/public_html/tango-wavewireradio/modules/trigger/trigger.module on line 146.

Login or register to post comments

Comments

#1

Posted by ainigma32 on February 1, 2009 at 1:36pm
Title:help with error» Illegal mix of collations error
Component:other» database system
Status:active» postponed (maintainer needs more info)

This means that one (or more) of your database tables are using collation utf8_unicode_ci while the rest use utf8_general_ci.
So you should change the collation of the offending tables and you should be good to go.

See here http://mostrey.be/drupal_character_encoding_mysql for more info on fixing this.

Please post back if that works for you.

- Arie

#2

Posted by sabermouth on February 2, 2009 at 7:57am

i do not know how to do this im kinda illiterate to this can you may be help me?

#3

Posted by ainigma32 on February 2, 2009 at 10:09am

Ok I'm going to assume you are using PHPMyAdmin to manage your db and that the problem is the collation of an entire table and not just a single column.

  • Go into PHPMyAdmin and export your database (i.e. create a backup) If you don't succeed in doing that don't execute the next steps.
  • Now click the structure tab so your tables are listed
  • Look at the values under the header Collation. Most/all should be the same (I think utf8_general_ci)
  • Either trigger_assignments or actions will have the utf8_unicode_ci collation
  • Click on the structure icon next to the name of the table you need to change
  • Now click the operations tab
  • Change the collation at the bottom of the screen
  • Cross your fingers and check if the problem was solved
  • If it was not, import the export you created as the first step so you are back to where you started

Please note that you are doing this at your own risk. If at all possible I would suggest asking someone you know and trust and who is more skilled to assist you

Please post back if that works for you.

- Arie

#4

Posted by ainigma32 on February 18, 2009 at 11:07am
Status:postponed (maintainer needs more info)» fixed

Looks like sabermouth won't be posting any feedback so I'm setting this to fixed.

Feel free to reopen if you think that is wrong.

- Arie

#5

Posted by System Message on March 4, 2009 at 11:10am
Status:fixed» closed (fixed)

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

#6

Posted by owenmcnamara on September 3, 2009 at 4:42am

I have had exactly the same problems with my Drupal installation (my first).

The error message was: "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 = 'cron' AND aa.op = 'run' ORDER BY weight in /home/knowledg/public_html/test-drupal/modules/trigger/trigger.module on line 146."

I tried what you suggested and changed the 'actions' table collation to utf8_general_ci and it fixed the problem!
Thanks for your suggestion.

Along the way I found a short interesting post on the difference between utf8_general_ci and utf8_unicode_ci in the MySQL Forums
http://forums.mysql.com/read.php?103,187048,188748#msg-188748

One thing puzzles me is that when I used PHPAdmin to check all the tables in my Drupal database I found there was a mix of both utf8_general_ci and utf8_unicode_ci collations. I don't know if they should all be the same collation or not? The default collation on my Host MySQL is utf8_unicode_ci, not utf8_general_ci.

Is the problem caused because some of the Drupal SQL code specifies the collation sequence to be used when the table is created while some code doesn't? Hence, some tables are defined with the Host MySQL default, while others with the collation value in the Drupal code?

I used Fantastico to set up Drupal. Is that the problem?

Should all Drupal tables be the same collation?

And thanks again, you have solved my problem.

Owen

#7

Posted by owenmcnamara on September 3, 2009 at 6:51am

And just a few more things on this topic.

Not long after I wrote the above reply of course what happened was more collation errors on different tables in my Drupal installation.

So rather than uncover them one my one I changed all the table collations to the same type as the Host MySQL type, in fact utt8_unicode_ci.

I don't know much at all about collations, but for me this opens up a question:

Should all Drupal modules use the same collation or should they not use any and leave that to the database default?

Should there be a way to set them all to the same or check that as part of the installation process rather than manually changing it with phpMyAdmin after installation.

Owen

#8

Posted by davidw on July 21, 2011 at 10:07pm

Thank you Arie, (#3 above) for the easy instructions on how to modify the table collation using phpmyadmin! I have struggled with the phpmyadmin interface regarding the mixed collation issues that I have had recently, and it was not easy to figure out at all. Your explanation is wonderful!
Now I understand it.
Thank You.

#9

Posted by DrupalQ on November 3, 2011 at 2:43pm
Version:6.9» 7.9

An alternative solution worked for me:
See: http://www.drupal.org/node/251938.