Hi list, I've got a query coming out of sugarCRM that is generating this error:
MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION' I recently converted the entire database to utf8 - made sure all the connections are utf8 etc -- made php use utf8 - set the doctype on the page to utf8 -- when I run the same query in the mysql monitor it runs fine - when apache/php run it it fails to deal with the collation. the data was converted via mysqldump to text file and reimporting changing all tables/database to utf. fwiw: the query looks like this: ( SELECT meetings.id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , meetings.date_start , meetings.parent_id , meetings.parent_type , meetings.time_start , 'meetings' panel_name FROM meetings where ( meetings.parent_id= '63301596-6175-1b89-75df-431283170495' 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(CONCAT(jt0.first_name , ' '), CONCAT(jt0.last_name , ' ')) contact_name, tasks.contact_id , tasks.date_due , tasks.parent_id , tasks.parent_type , tasks.time_due , 'tasks' panel_name FROM tasks LEFT JOIN contacts jt0 ON jt0.id= tasks.contact_id AND jt0.deleted=0 where ( tasks.parent_id= '63301596-6175-1b89-75df-431283170495' 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 , calls.date_start , calls.parent_id , calls.parent_type , calls.time_start , 'calls' panel_name FROM calls where ( calls.parent_id= '63301596-6175-1b89-75df-431283170495' AND calls.parent_type='Opportunities' AND calls.deleted=0 AND (calls.status='Planned')) AND calls.deleted=0 ) and in this case it doesn't return anything - which is correct given the data. we are using: mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i386) Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 I've seen some stuff that versions before 4.1.11 suffered from collation issues - is this likely to my case or can anyone see some other path through this - we have a single production database that we are very reluctant to update at this time. should i just switch back to latin1 ? thanks mysql list - you guys rock, lost in translation Jeff
