MySQL Collation Error: Leads Error

[Saro]

I recently upgraded to 4.2.1d and now have the following error under the first Lead box (where it has customer details). Normally, this is the area where I would see the history of my calls and the associated notes. It appears in the Opportunity section as well. Is this the Details section?

- I've rebuilt my relationships without error but this did not correct the problem.
- I've confirmed the the relationship php file (under cache/module/relationship) is not read only.
- I saw on another thread the suggestion to run the SQL upgrade against your database, it's located at the Source Forge download page. However, I cannot find this SQL upgrade file nor do I know if that is a fix.

Here is the Lead error:

Error retrieving Lead list: Query Failed SELECT meetings.id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.date_start , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, meetings.time_start , meetings.assigned_user_id , 'meetings' panel_name FROM meetings LEFT JOIN users jt1 ON jt1.id= meetings.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( meetings.parent_id= 'b9dd2ceb-d802-9cec-fb38-438b40cd8dc7' AND meetings.parent_type='Leads' AND meetings.deleted=0 AND (meetings.status='Planned')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name , tasks.status , CONCAT(IFNULL(jt0.first_name,''),' ',IFNULL(jt0.last_name,'')) contact_name , tasks.contact_id , jt0.assigned_user_id contact_name_owner , 'Contacts' contact_name_mod, tasks.date_due as date_start , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, tasks.time_due as time_start , tasks.assigned_user_id , 'tasks' panel_name FROM tasks LEFT JOIN contacts jt0 ON jt0.id= tasks.contact_id AND jt0.deleted=0 AND jt0.deleted=0 LEFT JOIN users jt1 ON jt1.id= tasks.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( tasks.parent_id= 'b9dd2ceb-d802-9cec-fb38-438b40cd8dc7' AND tasks.parent_type='Leads' AND tasks.deleted=0 AND (tasks.status='Not Started' OR tasks.status='In Progress' OR tasks.status='Pending Input')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.date_start , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, calls.time_start , calls.assigned_user_id , 'calls' panel_name FROM calls LEFT JOIN users jt1 ON jt1.id= calls.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( calls.parent_id= 'b9dd2ceb-d802-9cec-fb38-438b40cd8dc7' AND calls.parent_type='Leads' AND calls.deleted=0 AND (calls.status='Planned')) AND calls.deleted=0 ) ORDER BY date_start desc LIMIT 0,10::MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_bin,IMPLICIT) for operation 'UNION'

Here is the Opportunity error:

Error retrieving Opportunity list: Query Failed SELECT meetings.id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.date_start , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, meetings.time_start , meetings.assigned_user_id , 'meetings' panel_name FROM meetings LEFT JOIN users jt1 ON jt1.id= meetings.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( meetings.parent_id= '4e3ccda5-f6b9-7a5f-3536-436123566cd3' AND meetings.parent_type='Opportunities' AND meetings.deleted=0 AND (meetings.status='Planned')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name , tasks.status , CONCAT(IFNULL(jt0.first_name,''),' ',IFNULL(jt0.last_name,'')) contact_name , tasks.contact_id , jt0.assigned_user_id contact_name_owner , 'Contacts' contact_name_mod, tasks.date_due as date_start , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, tasks.time_due as time_start , tasks.assigned_user_id , 'tasks' panel_name FROM tasks LEFT JOIN contacts jt0 ON jt0.id= tasks.contact_id AND jt0.deleted=0 AND jt0.deleted=0 LEFT JOIN users jt1 ON jt1.id= tasks.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( tasks.parent_id= '4e3ccda5-f6b9-7a5f-3536-436123566cd3' AND tasks.parent_type='Opportunities' AND tasks.deleted=0 AND (tasks.status='Not Started' OR tasks.status='In Progress' OR tasks.status='Pending Input')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.date_start , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, calls.time_start , calls.assigned_user_id , 'calls' panel_name FROM calls LEFT JOIN users jt1 ON jt1.id= calls.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( calls.parent_id= '4e3ccda5-f6b9-7a5f-3536-436123566cd3' AND calls.parent_type='Opportunities' AND calls.deleted=0 AND (calls.status='Planned')) AND calls.deleted=0 ) ORDER BY date_start desc LIMIT 0,10::MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_bin,IMPLICIT) for operation 'UNION'

Thanks to anyone who can help!

- Saro

[andydreisch]

Saro, this Wiki article about collation errors may be of some use.

Andy

[Saro]

Thanks for the quick reply Andy! I was able to change the sugar database via that route but for some reason the SQL's mysql database will not change from a collation of latin1_swedish_ci. Everytime I go into PhpMyAdmin and make the change it reverts back to latin1_swedish_ci. I even tried changing it in the db.opt file. No luck. Any ideas?

- Saro

[Saro]

Update:

I followed the recomendation given here:
http://www.sugarcrm.com/forums/showt...ysql+collation

Which is to add the following to the my.ini file. My only database is sugar.
[client]
default-character-set=utf8
[mysqld]
default-character-set=utf8
init-connect=SET NAMES utf8

Errors are now gone! Sales team is happy!

However, latin1_swedish_ci still exists within the mysql database columns_priv table. That doesn't seem right. Comments?

- Saro

[sugarchris]

I hate this question - I really do. There are many fixes that work for one person which will not work for another - unfortunately there is no blanket fix that will address all sitautions in all cases.

The points that need to be laid out before any real discussion can commence:
1. MySQL 4.1.2+ has code to handle UTF-8 strings
2. The above is only true when it is compiled/installed with the charset files it needs to en/decode from/to UTF-8 to another character set.
3. MySQL will have a default character set (usually latin1_swedish) which will have a default collation (usually latin1_swedish_ci)
4. A MySQL client can declare certain environmental settings when connected to tell the server to use a different charset and/or collation
5. In SugarCRM or any PHP application where this is an issue, Apache/IIS is the MySQL client; more specifically, every spun-off thread is a different MySQL client. To make things even MORE complicated, PHP can pool database connection resources across those threads in certain configurations, especially big rollouts like ISPs and web hosts.
6. SugarCRM codes for the lowest-common denominator to enable the broadest range of installation types - hence we still support many versions of PHP 4 and MySQL 4.1.x.

Given the above, the theory around how we handle MBCS and in particular UTF-8 in Sugar is to code as defensively as possible. We assume:
1. Old-school default setups of PHP and MySQL
2. The instance is in a hosted environment - minimal control over php.ini, my.conf, httpd.conf, etc. files on the web/db servers.

The single most common cause of collation errors in SugarCRM is that recycled or common pool database connections fail to state character set and collation on connect. This results in a rogue MySQL client connection that is NOT processing UTF-8 and thus using a non-UTF-8 collation like latin1_swedish_ci. We can arrive at this situation in many different ways:
1. multiple versions of SugarCRM on the same host (i.e., 4.2.x and 4.5)
2. SugarCRM 4.5.x and another PHP application that specifies a different charset and collation on connect (i.e., Sugar 4.5 & VBulletin)
3. Someone directly manipulating the DB with a web tool like PHPMyAdmin connecting with a non-UTF-8 charset.

There are several more situations that can get progressively more technical (just imagine the nightmare in a multi-application environment spanned over several redundant systems - a typical large-scale webhost), but this will give you a taste of the underlying problem.

To answer your last question, the tables that have a default collation in the cols_priv table, you can probably safely ignore that - MySQL uses those internally and probably has a really good reason to leave them that way. As long as all the tables in your SugarCRM database are using utf8_general_ci or some variation thereof (and consistently!), you should be fine.