| Bug #20123 | #1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swed | ||
|---|---|---|---|
| Submitted: | 29 May 2006 11:04 | Modified: | 30 Jun 2006 16:39 |
| Reporter: | ejaz sakhi | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Query Browser | Severity: | S3 (Non-critical) |
| Version: | 1.0 | OS: | Windows (windows 2000) |
| Assigned to: | CPU Architecture: | Any | |
[30 May 2006 16:40]
Valeriy Kravchuk
Thank you for a problem report. Please, specify the exact versions of Query Browser and MySQL server used.
[23 Jun 2006 14:56]
Sergiy Kuzmenko
I have the same problem. Here are some details:
MySQL server: 4.1.11-standard (Linux)
Query Browser: 1.1.20 (Win XP Pro)
Table definition (from "show create table"):
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(40) default NULL,
`music` set('Classical','Jazz','Rock','World','Other') default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Query:
select * from test where find_in_set('Classical', music) = 0;
This query executes ok if run directly on the server (locale is set to en_US.UTF-8) from mysql client program.
However, when entered from the QB remotely it gives me "illegal mix of collations" error.
If I check table details in the QB I see that it implicitely sets collation for this table to latin1_swedish_ci.
[30 Jun 2006 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[4 Aug 2007 15:04]
Brice Burgess
I also am experiencing the same problem. It doesn't happen in the MySQL shell, only in the MySQL Tools' Query Browser. [!-- QUERY --!] SELECT field_id, IF (field_id=10,FROM_UNIXTIME(value,'%Y'),value), subscriber_id FROM `pommo_subscriber_data` WHERE subscriber_id IN(6,2,1,11,8,10,3,4,9,7,5); [!-- RESULT --!] Query Browser: Error executing query. 1267 - Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'if' [!-- WORKAROUNDS --!] 1) If I change the character set/collation of the pommo_subscriber_data table to utf8 via; "ALTER TABLE pommo_subscriber_data CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;" it will *work in the query broswer* 2) The query executes *fine* in MySQL shell regardless of the character set/collation of pommo_subscriber_data -- leading me to believe this issue is related to the libraries the Query Browser is linked against?? Pertinent Info: MySQL Query Browser: 1.2.12 MySQL Server: mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (i686) using readline 5.2 libmysqlclient: 5.0.45 System: Linux Slade 2.6.22-iCEBURG #11 Fri Aug 3 21:19:33 EDT 2007 i686 AMD Athlon(tm) 64 Processor 3000+ AuthenticAMD GNU/Linux Let me know if there's anything else I can provide. Thanks! ~ Brice
Description: Salam guys i have a query and receiving the error like this #1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' query is here SELECT c.claim_id, c.claim_identifier, c.revision, c.status, case when c.date_sent != '0000-00-00 00:00:00' then DATE_FORMAT(c.date_sent,'%m/%d/%Y') else DATE_FORMAT(c.timestamp,'%m/%d/%Y') end as timestamp, sum(cl.amount) as amount, concat_ws(', ',p.last_name,p.first_name) as patient_name, p.record_number, p.identifier as patient_identifier, co.name as facility_name, pa.name as payer_name, date_format(min(cl.date_of_treatment),'%m/%d/%Y') date_of_treatment FROM fblatest_revision lr left join fbclaim c using (claim_identifier,revision) left join fbclaimline cl using(claim_id) left join fbperson as p on p.claim_id = c.claim_id and p.type='FBPatient' and p.`index` = 0 left join fbcompany as co on p.claim_id = co.claim_id and co.type='FBTreatingFacility' and co.`index` = 0 left join fbcompany as pa on p.claim_id = pa.claim_id and pa.type='FBPayer' and pa.`index` = 0 GROUP BY c.claim_id setup for database is as follows CREATE TABLE fbclaim ( claim_id int(11) NOT NULL DEFAULT '0' COMMENT '', claim_identifier varchar(255) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci, revision int(11) NOT NULL DEFAULT '0' COMMENT '', status enum('new','pending','sent','archive') NOT NULL DEFAULT 'new' COMMENT '' COLLATE latin1_swedish_ci, `timestamp` timestamp NULL DEFAULT 'CURRENT_TIMESTAMP' COMMENT '', date_sent datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '', format varchar(255) NOT NULL DEFAULT '' COMMENT '' COLLATE latin1_swedish_ci, PRIMARY KEY (claim_id) ) TYPE=MyISAM; CREATE TABLE `fbclaimline` ( `claimline_id` int(11) NOT NULL default '0', `claim_id` int(11) NOT NULL default '0', `procedure` varchar(10) NOT NULL default '', `modifier` varchar(4) NOT NULL default '', `amount` float(11,2) NOT NULL default '0.00', `units` float(5,2) NOT NULL default '0.00', `comment` varchar(80) NOT NULL default '', `comment_type` varchar(10) NOT NULL default '', `date_of_treatment` datetime NOT NULL default '0000-00-00 00:00:00', `amount_paid` float(11,2) NOT NULL default '0.00', PRIMARY KEY (`claimline_id`), KEY `claim_id` (`claim_id`) ) TYPE=MyISAM; CREATE TABLE `fbcompany` ( `company_id` int(11) NOT NULL default '0', `claim_id` int(11) NOT NULL default '0', `index` tinyint(4) NOT NULL DEFAULT '0' COMMENT '', `identifier` varchar(25) NOT NULL default '', `identifier_type` varchar(10) NOT NULL default '', `type` varchar(50) NOT NULL default '', `name` varchar(100) NOT NULL default '', `phone_number` varchar(45) NOT NULL default '', PRIMARY KEY (`company_id`) ) TYPE=MyISAM COMMENT='Base Company record most of the data is in linked tables'; CREATE TABLE `fbperson` ( `person_id` int(11) NOT NULL default '0', `claim_id` int(11) NOT NULL default '0', `index` tinyint(4) NOT NULL DEFAULT '0' COMMENT '', `type` varchar(50) NOT NULL default '', `identifier` varchar(100) NOT NULL default '', `identifier_type` varchar(10) NOT NULL default '', `record_number` varchar(255) NOT NULL default '', `salutation` varchar(20) NOT NULL default '', `last_name` varchar(100) NOT NULL default '', `first_name` varchar(100) NOT NULL default '', `middle_name` varchar(50) NOT NULL default '', `gender` enum('M','F','O') default NULL, `date_of_birth` date NOT NULL default '0000-00-00', `phone_number` varchar(45) NOT NULL default '', `comment` varchar(255) NOT NULL default '', PRIMARY KEY (`person_id`) ) TYPE=MyISAM COMMENT='A person in the system'; CREATE TABLE `fblatest_revision` ( `claim_identifier` varchar(255) NOT NULL default '', `revision` int(11) NOT NULL default '0', PRIMARY KEY (`claim_identifier`) ) TYPE=MyISAM; Help Out to execute the above mentioned select query successfully... How to repeat: execute the selecte query the same error message will appear again and again.