Illegal mix of collations ... Problem

[hackrishna]

I m getting this Illegal mix of Collations Error again. First it was when sending PMs to other, that was solved by Oldiesman. Now i face this new one. I have changed all collations to UTF-8 in phpmyAdmin but still

Database Error

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'ifnull'
File: /home/epicmaga/public_html/tech/Sources/ModerationCenter.php
Line: 633

What's the SQL Query to Fix this ?? Or anything else ?
I'm using SMF 2.0.2, Attached Screenshot and ModerationCenter.php

Thank you

[Ashley S]

This is a custom theme made by Dzinerstudio.
You will need to ask for support there ( http://www.dzinerstudio.com/ )

Sorry but we can't help with this.

[hackrishna]

This is a custom theme made by Dzinerstudio.
You will need to ask for support there ( http://www.dzinerstudio.com/ )

Sorry but we can't help with this.

It's not a theme problem. The same appears in core and deafault theme too  It's collations error, how can the theme be dragged into it ??

Correct me if m wrong

EDIT: Oldiesmann previously had helped me for the same kind of Problem http://www.simplemachines.org/community/index.php?topic=468663.msg3276153#msg3276153

[Ashley S]

Have you tried replacing the ModerationCenter.php with a fresh one from the SMF 2.0 folder?
That may stop the problem.

[hackrishna]

Have you tried replacing the ModerationCenter.php with a fresh one from the SMF 2.0 folder?
That may stop the problem.

Yup i tried, Nothing special happened 

[Illori]

you need to make sure all your tables in the database are using the same collation as the error message says one is not using the same collation as the rest, you would need to convert that table.

[hackrishna]

you need to make sure all your tables in the database are using the same collation as the error message says one is not using the same collation as the rest, you would need to convert that table.

I converted all tables first. Then tried converting each tables one by one, i can see that all are of the same collation now i.e. UTF-8 ..... But 

[Illori]

read the error message again, they are not all the same uft-8

[hackrishna]

read the error message again, they are not all the same uft-8

hehehee ... No bro it isn't working  You know which table to alter for this ?? The specific table for that is what ??

[Illori]

the error message you posted does not include the table names, so no we cant tell you what they are.

[hackrishna]

the error message you posted does not include the table names, so no we cant tell you what they are.

ohh ok 

[Kays]

Hi, looking at the query in question. Check either the members table or log_reported. It's most probably the latter.

[hackrishna]

Hi, looking at the query in question. Check either the members table or log_reported. It's most probably the latter.

ok ... I will try and see

[Aleksi "Lex" Kilpinen]

If you have phpmyadmin, you should be able to see the collation for all the tables there. Generally, all the SMF tables should be using the same collation, so you might want go through them all.

[hackrishna]

If you have phpmyadmin, you should be able to see the collation for all the tables there. Generally, all the SMF tables should be using the same collation, so you might want go through them all.

I  can see that all are of the same collation now i.e. UTF-8 .....

Yup it is using same collation. BTW ... which collation it should be actually ?? Here all are UTF8_unicode nw

[Aleksi "Lex" Kilpinen]

No, the error says that some of your table are UTF8_unicode and some are UTF8_general.
Normally UTF8_general is good enough for any need with SMF.

EDITED to add:
But I would actually check which one is used more, and then convert all the rest to be the same. ( This would make it a tiny bit safer to be messing around with them )
And I would always backup before doing ANYTHING with the DB.

[hackrishna]

No, the error says that some of your table are UTF8_unicode and some are UTF8_general.
Normally UTF8_general is good enough for any need with SMF.

Ok, in my phpmyAdmin, the global collation set is : utf8_general
For my WordPress DB there is mixture of utf8_unicode and utf8_general
For my SMF, all the collations are utf8_unicode

And yeah i always take a backup

[Oldiesmann]

Specifically, the error is stating that the two columns being compared by ifnull are using two different collations.

Taking a look at your ModerationCenter.php, line 633 is the end of this query:

Code Select Expand

// By George, that means we in a position to get the reports, golly good.
$request = $smcFunc['db_query']('', '
SELECT lr.id_report, lr.id_msg, lr.id_topic, lr.id_board, lr.id_member, lr.subject, lr.body,
lr.time_started, lr.time_updated, lr.num_reports, lr.closed, lr.ignore_all,
IFNULL(mem.real_name, lr.membername) AS author_name, IFNULL(mem.id_member, 0) AS id_author
FROM {db_prefix}log_reported AS lr
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = lr.id_member)
WHERE lr.closed = {int:view_closed}
AND ' . ($user_info['mod_cache']['bq'] == '1=1' || $user_info['mod_cache']['bq'] == '0=1' ? $user_info['mod_cache']['bq'] : 'lr.' . $user_info['mod_cache']['bq']) . '
ORDER BY lr.time_updated DESC
LIMIT ' . $context['start'] . ', 10',
array(
'view_closed' => $context['view_closed'],
)
);

So in this case, the two columns that are causing problems are mem.real_name and lr.membername.

From the error given, I'm assuming that the lr.membername column is utf8_unicode_ci while the mem.real_name column is utf8_general_ci.

Because mem.real_name is used a lot more frequently than lr.membername, and you're not getting errors elsewhere, the best thing to do in this case is to change the collation of lr.membername:

Login to phpMyAdmin
Select the SMF database
Click on "log_reported" in the left column
Check all three text columns and click "change" at the bottom of the list
Set the collation to utf8_general_ci for all three
Save the changes

Changing the collation of all three ensures that there won't be any other issues with those columns.

[hackrishna]

Thanks a lot for that wonderful explanation

Login to phpMyAdmin
Select the SMF database
Click on "log_reported" in the left column
Check all three text columns and click "change" at the bottom of the list
Set the collation to utf8_general_ci for all three
Save the changes

Changing the collation of all three ensures that there won't be any other issues with those columns.

Okk .. so now i selected "log_reported" and these are what i see there. Should i select all of them?
I didn't get that "Check all three text columns and click "change" at the bottom of the list"

EDIT: is it membername, subject and body ?

[hackrishna]

Heyyy .. it worked! Thanks a lot super thank you Oldiesmann 

Thanks to Ashley S, Illori, Kays, Aleksi "Lex" Kilpinen and Oldiesmann for helping me

Topic Marked "Solved"