Re: MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION' -- again

[Gleb Paharenko]

Hello.

> MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) 
>and (utf8_general_ci,IMPLICIT) for operation 'UNION' -- again

Check the character set of fields in you tables with
'SHOW CREATE TABLE' statement. I recommend you to test if
the problem remains in 4.1.14 version, because the similar
bug was fixed in 4.1.11. See:

  http://bugs.mysql.com/bug.php?id=6519


Jeff Kolber <jeff.kolber@stripped> wrote:
>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


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com