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 [EMAIL PROTECTED] 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
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
