If this is your first visit, check our documentation. To post, register or login.
Forums / General Questions / Illegal mix of collations

Illegal mix of collations

Started by dml73 on 31 Dec 2013, 3:12 AM · Views: 2,200

Results 1 to 7 of 7
31 Dec 2013, 3:12 AM
#1
dml73 avatar

dml73

Zen Follower

Join Date:
Oct 2007
Posts:
416
Plugin Contributions:
0

Illegal mix of collations

In my logs folder I found a log today with this error:

[30-Dec-2013 11:31:50 America/New_York] PHP Fatal error: 1267:Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' :: SELECT zone_id
FROM zones
WHERE zone_country_id = 73
AND zone_code = '�SAVOIE'
OR zone_name = '�SAVOIE'
LIMIT 1 in /home/mywebsite/includes/classes/db/mysql/query_factory.php on line 120

I believe this error was created when a customer was trying to place an order. Any idea what is causing this error? or even better, how to fix it?

31 Dec 2013, 1:07 PM
#2
balihr avatar

balihr

Totally Zenned

Join Date:
Oct 2008
Location:
Croatia
Posts:
1,817
Plugin Contributions:
24

Re: Illegal mix of collations

I will make a wild guess and say that you're using Zen Cart v.1.3.9x and have Newsletter Subscribe module installed - this was the most common scenario where I noticed this.
The solution would be to go into your database (using phpMyAdmin) and change your table collation to whatever other tables are using. If all tables are using utf8 and one is using latin1, you know what to do... :smile:

My site - Pro ZC Help
My portfolio | My plugins

31 Dec 2013, 3:54 PM
#3
dml73 avatar

dml73

Zen Follower

Join Date:
Oct 2007
Posts:
416
Plugin Contributions:
0

Re: Illegal mix of collations

No I recently upgraded from 1.3.8a to 1.5.1,a nd I do not have the Newsletter Subscribe module installed. So I guess I still need to go into the database and change the tables; but which one do I change??

31 Dec 2013, 4:27 PM
#4
balihr avatar

balihr

Totally Zenned

Join Date:
Oct 2008
Location:
Croatia
Posts:
1,817
Plugin Contributions:
24

Re: Illegal mix of collations

Well, it's still pretty much the same thing - you obviously have tables with different collations... Go into the database and change the latin1 tables to utf8 (that's standard in 1.5.1) and you should be fine. Of course, make sure your site uses utf8 as well. This is quite important if you have international customers...

My site - Pro ZC Help
My portfolio | My plugins

31 Dec 2013, 5:05 PM
#5
dml73 avatar

dml73

Zen Follower

Join Date:
Oct 2007
Posts:
416
Plugin Contributions:
0

Re: Illegal mix of collations

Ok, thanks, I checked some of the tables in phpMyAdmin, most of the tables has "latin1_swedish_ci" and a few of them has "utf8_general_ci".
The zone_id and zone_code tables the above error is referring to has "latin1_swedish_ci".
So two questions remain:

  1. Should I change all tables to "latin1_swedish_ci" or to "utf8_general_ci"?

  2. How can I check if my site is using utf8 or not?

31 Dec 2013, 5:23 PM
#6
lat9 avatar

lat9

Administrator

Join Date:
Sep 2009
Location:
Stuart, FL
Posts:
14,140
Plugin Contributions:
56

Re: Illegal mix of collations

Besides your database's collation, there are a couple of other settings that control whether or not your site is using UTF-8:

  1. DB_CHARSET in /includes/configure.php and /YOUR_ADMIN/includes/configure.php.
  2. The value used in the setlocale function call in /includes/languages/english.php and /YOUR_ADMIN/includes/languages/english.php.
  3. CHARSET in /includes/languages/YOUR_TEMPLATE/english.php and /YOUR_ADMIN/includes/languages/english.php.
31 Dec 2013, 10:54 PM
#7
dml73 avatar

dml73

Zen Follower

Join Date:
Oct 2007
Posts:
416
Plugin Contributions:
0

Re: Illegal mix of collations

lat9:

Besides your database's collation, there are a couple of other settings that control whether or not your site is using UTF-8:

  1. DB_CHARSET in /includes/configure.php and /YOUR_ADMIN/includes/configure.php.
  1. The value used in the setlocale function call in /includes/languages/english.php and /YOUR_ADMIN/includes/languages/english.php.
  1. CHARSET in /includes/languages/YOUR_TEMPLATE/english.php and /YOUR_ADMIN/includes/languages/english.php.

Thanks, I was able to find a guide for how to convert from latin to utf8 here:
https://www.numinix.com/blog/2011/08/01/changing-the-charset-in-zen-cart-1-3-9-to-utf-8/