Thread: Illegal mix of collations

Database error
Query:
SELECT `Name` FROM `Groups` WHERE UPPER(`Name`)='λαλαλαλα' LIMIT 1

Mysql error:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Found error in file /inc/groups.inc.php
at line 174. Called db_res function
with erroneous argument #0

this appears to be a bug in dolphin - or a database setup problem - so moved to the Dolphin 6.0.1 bug fixing forum

I assume... but need to check - that we should make sure the database collation is set to UTF-8 (and none other) on install.

Will try this tomorrow on CLEAN test site.

Smoge

Perhaps this is the major problem that people are having re the DATABASE CONNECTION ERRORS. There are a lot of posts on expertZzzzzzz with people having so many database connection errors on different server configs. And I did also read a post about the collation thing there that 'Boonex' mentioned. When I go there next I will try and dig it up.

Hope this collation deal can fix the problems Smoge.

This was posted on expertz as a reply to database connection errors etc.

You should change the character set and collation of your database for "utf8".

I did a test install of d6null just now and got default utf-8 unicode in bold below.

I am using Mysql 5, and it has a heap of UTF-8 collation options as well as all the others. Users should make sure things match up via your Phpmyadmin control panel:

MySQL charset: UTF-8 Unicode (utf
MySQL connection collation: utf8_unicode_ci

Actually - I think I have the fix for this bug and also for Dolphin (not Ray or Orca) multi-byte language support - and we will be doing this in CLEAN.

Not the CLEAN release for today - but for a future one in several days.

In a nutshell - it is to force all database tables and fields to UTF8 on install, and also all Dolphin code to UTF8 - and use UTF8 site wide - thus all collations will be UTF8 and thus no mismatches.

It also results in UTF8 encoding for all pages (Dolphin only) in the browser.

I have done something similar in 5.6.5 for a ModMySite customer - using some methods, I converted his non-UTF based database (with existing data) to all UTF8 - in addition to all his Dolphin 5.6.5 code.

If you need your existing site converted to UTF8 send me a PM.

The changes to CLEAN will take care of this problem at install time.

Smoge

They have apparently attended to this in 602.

I have just checked and the table collation is uft8_general_ci not utf8_unicode_ci - will this also create some problems?

Like Boonex mentions on their blog site, better to do a new install if your site can handle it, for testers no worries.

actually, you can export any db collation as binary (from mysql command line) - then edit resulting .sql file with desired collation - and reimport. It works - and is how you should do it for old database collation changes. Not sure what BoonEx is recommending.

Smoge