I’ve spent much of this evening looking into the cause of this error:
Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation ‘UNION’
whilst hacking on SugarCRM v4, running with PHP 4.4.1 and MySQL-5.0.17, all running on Gentoo Linux.
The solution I found for tonight was to ensure that the PHP connection to MySQL, and the database being used, are using the same collation.
If you’d like to know more about this problem, please read on. (Blog syndication site readers will find the rest of this article on my blog page).
5 Comments
January 26th, 2006 at 4:25 pm
I have to support 2 differents SugarCRM installation. I just upgraded SugarCRM from 4.0 to 4.0.1. Went ok on the box with PHP4 but my own Gentoo box with 5.0.5 just didn’t make it. Seems the new release does not support this version of PHP (Compatibility issues the Release Notes says).
Result, my SugarCRM has a semi installed 4.0.1 patch. There’s 2 steps in the upgrade process and the second one give me this error
Fatal error: Only variables can be passed by reference in/var/www/localhost/htdocs/301/cache/upload/upgrades/temp/WBFhgT/scripts/south_hemi_dst_fix.php on line 115
Did you got this problem too?
Emile
February 26th, 2006 at 6:59 pm
Yup, I had the same problem - your fix also seems to work for me. It did seem apparent fairly quickly where the problem was, but the fact that tge default Gentoo install inserts;
character-sets-dir=utf8
default-character-set=utf8
Sort of obscures the problem ..
Given these statements don’t do anything, it might be as well if the default skeleton didn’t include them. I must admit I assumed these were working given they were default settings …
Anyway, thanks for the fix - if you get a server based fix I’d love to hear ..
May 4th, 2006 at 6:44 pm
Thanks for this detailed explaination and the various fixes that did and did NOT work for you. I think a similar fix is on the SCRM forums, but it is not explained anywhere near as clearly as this. Also the other fix hardcodes the collation instead of seeing what the server wants and then giving it that collation.
Very elegant fix.
June 18th, 2006 at 2:59 am
Thank you for this extensive explanation.
I do not use Gentoo, but came across similar problems in an application I was writing myself.
Unfortunately your solution did not work for me as it turned out I also needed to change the ‘character_set_results’ variable which was not changed now.
“SET NAMES” changed both the collation and the results variables, so inspired by your solution, I have written the following fix which I believe is slightly more comprehensive:
$dh = mysql_connect(…);
mysql_select_db(…, $dh);
$db_charset = mysql_query(”SHOW VARIABLES LIKE ‘character_set_database’”);
$charset_row = mysql_fetch_assoc($db_charset);
mysql_query(”SET NAMES ‘” . $charset_row['Value'] . “‘”);
Let’s hope this may help other people avoid the headache I got from it today !
Greeting,
Juliette
June 18th, 2006 at 6:49 pm
I’ve now put a page up explaining how my fix works. Hope this helps !
http://www.adviesenzo.nl/examples/php_mysql_charset_fix/
Greetings,
Juliette
Add Your Comments To This Article Using The Form Below
Your comments may not appear until they have been approved by a moderator.