Advertisement:

Author Topic: Illegal mix of collations ... Problem  (Read 6164 times)

Offline hackrishna

  • Semi-Newbie
  • *
  • Posts: 37
  • Gender: Male
    • Epic Tech Forums
Illegal mix of collations ... Problem
« on: April 01, 2012, 05:07:21 AM »
Using SMF 2.0.2. Error Message i'm getting is

Database Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'like'
File: /home/epicmaga/public_html/tech/Sources/ManageBans.php
Line: 1710


All the tables are of UTF8_unicode, which table i need to change the collation at phpmyAdmin for fixing this Error...

Thank you in advance  :)


« Last Edit: April 01, 2012, 11:59:11 AM by hackrishna »

Offline hackrishna

  • Semi-Newbie
  • *
  • Posts: 37
  • Gender: Male
    • Epic Tech Forums
Re: Illegal mix of collations ... Problem
« Reply #1 on: April 01, 2012, 11:58:50 AM »
Anybody :(

Offline Oldiesmann

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 24,817
  • Gender: Male
  • Ask me about the function DB :)
    • oldiesmann on Facebook
    • Oldiesmann on GitHub
    • http://www.linkedin.com/in/michaeleshom on LinkedIn
    • @oldiesmann on Twitter
    • Archie Comics Fan Forum
Re: Illegal mix of collations ... Problem
« Reply #2 on: April 02, 2012, 12:12:52 AM »
You need to change the collation of the email_address column in the members table to utf8_unicode_ci since that's what all your other tables are.
Michael Eshom
Webmaster / SMF Lead Developer
oldiesmann@simplemachines.org

Offline ziycon

  • On Hiatus
  • SMF Hero
  • *
  • Posts: 2,692
  • Gender: Male
Re: Illegal mix of collations ... Problem
« Reply #3 on: April 02, 2012, 05:47:30 AM »
Using SMF 2.0.2. Error Message i'm getting is

Database Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'like'
File: /home/epicmaga/public_html/tech/Sources/ManageBans.php
Line: 1710


All the tables are of UTF8_unicode, which table i need to change the collation at phpmyAdmin for fixing this Error...

Thank you in advance  :)
This isn't an issue with the table collation its regarding a mismatch between two fields, for the 'LIKE' statement to work both fields being used need to be of the same collation. check the collation for what Oldiesmann said, if it doesn't solve your issue let us know.

Offline hackrishna

  • Semi-Newbie
  • *
  • Posts: 37
  • Gender: Male
    • Epic Tech Forums
Re: Illegal mix of collations ... Problem
« Reply #4 on: April 02, 2012, 09:23:21 AM »
You need to change the collation of the email_address column in the members table to utf8_unicode_ci since that's what all your other tables are.

How to change it ?? The column of email_address in members table.... I coudn't select it all together and change the collation :(

Offline hackrishna

  • Semi-Newbie
  • *
  • Posts: 37
  • Gender: Male
    • Epic Tech Forums
Re: Illegal mix of collations ... Problem
« Reply #5 on: April 02, 2012, 09:24:31 AM »
This isn't an issue with the table collation its regarding a mismatch between two fields, for the 'LIKE' statement to work both fields being used need to be of the same collation. check the collation for what Oldiesmann said, if it doesn't solve your issue let us know.

 :o It didn't work  :'(

Offline ziycon

  • On Hiatus
  • SMF Hero
  • *
  • Posts: 2,692
  • Gender: Male
Re: Illegal mix of collations ... Problem
« Reply #6 on: April 02, 2012, 10:22:19 AM »
You need to check all columns in your smf tables to make sure if there is a collation set against them that it is utf8_unicode_ci and not utf8_general_ci.

Offline hackrishna

  • Semi-Newbie
  • *
  • Posts: 37
  • Gender: Male
    • Epic Tech Forums
Re: Illegal mix of collations ... Problem
« Reply #7 on: April 02, 2012, 12:11:32 PM »
You need to check all columns in your smf tables to make sure if there is a collation set against them that it is utf8_unicode_ci and not utf8_general_ci.

All the tables are UTF8_unicode_ci
Except these three..


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.


Offline ziycon

  • On Hiatus
  • SMF Hero
  • *
  • Posts: 2,692
  • Gender: Male
Re: Illegal mix of collations ... Problem
« Reply #8 on: April 03, 2012, 03:32:38 AM »
You need to check all columns in your smf tables to make sure if there is a collation set against them that it is utf8_unicode_ci and not utf8_general_ci.

All the tables are UTF8_unicode_ci
Except these three..


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.
Does this mean the issue is fixed or still occurring!? If still occurring, if you change the columns back to UTF8_unicode_ci from UTF8_general_ci does the issue still persist?

Offline hackrishna

  • Semi-Newbie
  • *
  • Posts: 37
  • Gender: Male
    • Epic Tech Forums
Re: Illegal mix of collations ... Problem
« Reply #9 on: April 03, 2012, 04:32:47 AM »
Nooo .. it occurred two times before
1. http://www.simplemachines.org/community/index.php?topic=468663.0
2. http://www.simplemachines.org/community/index.php?topic=471696.0

they were solved... Now its the third time  :-[

All the tables in the database are of collations UTF8_unicode_ci, except three tables in "log_reported" .. Everything is perfect and working and Fine.
When i ban spam members' hostname, i get the above error

Database Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'like'
File: /home/epicmaga/public_html/tech/Sources/ManageBans.php
Line: 1710


I want to know how to fix that .. Help me please  :'( As Oldiesmann said
You need to change the collation of the email_address column in the members table to utf8_unicode_ci since that's what all your other tables are.

I'm not able select the column "email_address" and change it's collations to "utf8_unicode_ci" .... Its a column and not a row to select all together (correct me if m wrong)...  Can you Explain me that how to change them  :'(

Offline ziycon

  • On Hiatus
  • SMF Hero
  • *
  • Posts: 2,692
  • Gender: Male
Re: Illegal mix of collations ... Problem
« Reply #10 on: April 03, 2012, 05:00:05 AM »
Can you also check the collation set on columns for the ban_items and ban_groups tables and change if needed and let me know how you get on?

Offline hackrishna

  • Semi-Newbie
  • *
  • Posts: 37
  • Gender: Male
    • Epic Tech Forums
Re: Illegal mix of collations ... Problem
« Reply #11 on: April 03, 2012, 06:06:59 AM »
Can you also check the collation set on columns for the ban_items and ban_groups tables and change if needed and let me know how you get on?

They both are utf8_unicode_ci only

Offline ziycon

  • On Hiatus
  • SMF Hero
  • *
  • Posts: 2,692
  • Gender: Male
Re: Illegal mix of collations ... Problem
« Reply #12 on: April 03, 2012, 06:17:39 AM »
If you would like me to look at the database drop me a PM, have a look at this topic first http://www.simplemachines.org/community/index.php?topic=87130.0