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 = 'view' ORDER BY weight in /home/coke94/public_html/modules/trigger/trigger.module on line 142.

Guys, I really need some help with this one. My buddy and I cannot figure it out. New to site building so any help would be appreciated, thanks!

Comments

jcasis’s picture

jcasis commented

I'am also receiving the same error, is anyone out there able to give any advice as to what could be a possible fix for this?

I've posted the error I get below.

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 = 'view' ORDER BY weight in /home/joe123/public_html/drupal/modules/trigger/trigger.module on line 146.

Any suggestion is greatly appreciated.

Regards,

Joe

alt2’s picture

alt2 commented

I'm getting exactly the same error message visible in Status Report on a new vanilla install 6.12 at HostGator.

I've created no Actions. The MySql version is 5.1.30.

Here's what I see if I go to Triggers:

user warning: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' query: SELECT h.aid, a.description FROM trigger_assignments h LEFT JOIN actions a on a.aid = h.aid WHERE h.hook = 'nodeapi' AND h.op = 'presave' ORDER BY h.weight in /home/atabor/public_html/modules/trigger/trigger.admin.inc on line 279.
user warning: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' query: SELECT h.aid, a.description FROM trigger_assignments h LEFT JOIN actions a on a.aid = h.aid WHERE h.hook = 'nodeapi' AND h.op = 'insert' ORDER BY h.weight in /home/atabor/public_html/modules/trigger/trigger.admin.inc on line 279.
user warning: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' query: SELECT h.aid, a.description FROM trigger_assignments h LEFT JOIN actions a on a.aid = h.aid WHERE h.hook = 'nodeapi' AND h.op = 'update' ORDER BY h.weight in /home/atabor/public_html/modules/trigger/trigger.admin.inc on line 279.
user warning: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' query: SELECT h.aid, a.description FROM trigger_assignments h LEFT JOIN actions a on a.aid = h.aid WHERE h.hook = 'nodeapi' AND h.op = 'delete' ORDER BY h.weight in /home/atabor/public_html/modules/trigger/trigger.admin.inc on line 279.
user warning: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' query: SELECT h.aid, a.description FROM trigger_assignments h LEFT JOIN actions a on a.aid = h.aid WHERE h.hook = 'nodeapi' AND h.op = 'view' ORDER BY h.weight in /home/atabor/public_html/modules/trigger/trigger.admin.inc on line 279.

alt2’s picture

alt2 commented
Status: Postponed (maintainer needs more info) » Fixed

I figured this out. My Fantistico install of Drupal intialized the MySQL database with collation set to utf8_general_ci but installs the core modules with unicode instead. Additional modules come in mixed. Things then start tweaking. I did a fresh manual install so I could set up the database myself and all was well.

Status: Fixed » Closed (fixed)

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

l4smb’s picture

l4smb commented
Status: Closed (fixed) » Needs work

To change the character set (and collation) for all columns in an existing table, use... ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

alby111’s picture

alby111 commented

Can someone give a better explanation on how to resolve thsi Im new in this

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.

thanks a lot

stGeorge-1’s picture

stGeorge-1 commented

Try here for some suggested solutions to similar problems

http://drupal.org/node/140303

jimkomara’s picture

jimkomara commented

So what is it supposed to be? I just set everything to unicode based on this comment. Is it supposed to be general? I am getting some errors.

gábor hojtsy’s picture

gábor hojtsy
he/him
Primary language Hungarian
Location Hungary
commented
Category: bug » support
sun’s picture

sun
Primary language German
Location Karlsruhe
commented
Title: user warning: Illegal mix of collations error, help please! » user warning: Illegal mix of collations
Version: 6.1 » 7.x-dev
Component: user system » database system
Assigned: coke94 » Unassigned
Status: Needs work » Active
Issue tags: +utf8_unicode_ci, +MySQL 5.1, +utf8_general_ci

Tagging, reclassifying. Starting with MySQL 5.1, utf8_general_ci is a serious problem for Drupal sites in other languages than English due to non-backwards-compatible collation changes in MySQL 5.1.

For now, just trying to tag related issues. To figure out a full and proper problem analysis afterwards.

sun’s picture

sun
Primary language German
Location Karlsruhe
commented
Status: Active » Closed (duplicate)

Thanks for taking the time to report this issue.

However, marking as duplicate of #772678: Database default collation is not respected.
You can follow up on that issue to track its status instead. If any information from this issue is missing in the other issue, please make sure you provide it over there.

Anonymous’s picture

Anonymous (not verified) commented

Here is a simple PHP script to recursively change the charset and collation of tables in a database.


// http://kb.siteground.com/article/How_to_change_the_collation_for_all_tables_in_db_to_UTF8.html

$db = mysql_connect('localhost','database','password');

if(!$db) echo "Cannot connect to the database - incorrect details";

mysql_select_db('database');

$result = mysql_query('show tables');

while($tables = mysql_fetch_array($result)) {

	foreach ($tables as $key => $value) {

	mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
	}
}
echo "The collation of your database has been successfully changed!";

Thanks to l4smb for posting the SQL.

Hope this helps!