Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT)
Hi Guy`s! Today i hacked MySql but hove some trouble. i have two tables (lentas and categories_documents) i try join them: SELECT `lentas`.* FROM `lentas` left join categories_documents As cd ON(cd.document_id = lentas.document_id AND cd.document_type = lentas.document_type) WHERE (cd.category_id = 1); mysql show table status like '%categories_documents%'\G *** 1. row *** Name: categories_documents Engine: InnoDB Version: 10 Row_format: Compact Rows: 12391 Avg_row_length: 128 Data_length: 1589248 Max_data_length: 0 Index_length: 1589248 Data_free: 4194304 Auto_increment: 12746 Create_time: 2009-11-27 17:46:36 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: mysql show table status like '%lentas%'\G *** 1. row *** Name: lentas Engine: InnoDB Version: 10 Row_format: Compact Rows: 12611 Avg_row_length: 126 Data_length: 1589248 Max_data_length: 0 Index_length: 507904 Data_free: 4194304 Auto_increment: 11895 Create_time: 2009-11-27 17:55:45 Update_time: NULL Check_time: NULL Collation: utf8_unicode_ci Checksum: NULL Create_options: Comment: But BUM faqen MYSQL sad ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' I fly on Mac OS 10.5 MySql 5.4.3-beta on Linux it work allrigth! Please help. Thanks.
Illegal mix of collations
Hi, I have tried to select data from more tables using union, but it gaves the following error: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'UNION' I have verified the tables (using show create table table_name) and all of them are: engine=InnoDB default charset=utf8 and there are no fields with another charset. The same database works under Windows, but it gives that error under Linux. Please tell me what can I do to make it work. Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
I have upgraded a website from drupal 4.7.4 to drupal 5.1 (I have also moved
website from one server to another), now when I try to login I'm getting
following error.
user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '=' query: user_is_blocked SELECT
name FROM users WHERE status = 0 AND name = LOWER('user1') in
/www/mysite/includes/database.mysql.inc on line 172.
user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '=' query: user_load SELECT * FROM
users u WHERE LOWER(name) = LOWER('user1') AND pass =
'7b063a8b8aa2219449cb35f4e415295f' AND status = 1 in
/www/mysite/includes/database.mysql.inc on line 172.
Sorry, unrecognized username or password. Have you forgotten your password?
I have also posted this in drupal forum http://drupal.org/node/140303.
What I've done is I have exported data from old db via phpMyAdmin and
imported into new database.
Since it related to mysql so i'm posting here.
--
Sharique uddin Ahmed Farooqui
(C++/C# Developer, IT Consultant)
http://www.managefolio.net/news/
A revolution is about to begin.
A world is about to change.
And you and me are the initiator.
Illegal mix of collations ...
I get Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'. in a query like (SELECT ...) UNION (SELECT * INTO OUTFILE ...) to build a CSV file. The database has default charset UTF8 and collation utf8_general_ci and all query tables have default charset UTF8 and collation utf8_general_ci MySQL client says for show variables character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_results utf8 character_set_serverutf8 character_set_system utf8 collation_connectionutf8_general_ci collation_database utf8_general_ci collation_server utf8_general_ci I'm using MySQL 5.0.15. Any idea why I get this Illegal mix error and how to solve it ? TIA, Cor
Illegal mix of collations
Hi all. I have some code which was working brilliantly, but just stopped :( I'm importing stuff in to a temporary table, mangling it considerably, then importing it into the final destination table. The tmp table is created: create temporary table tmp_AAPT_OnlineAnalyser_ChargeTypeSummary ( Statement_Datevarchar(100), Account_Numbervarchar(100), Invoice_Numbervarchar(100), Service_Numbervarchar(100), Service_Type varchar(100), Description_of_Charge varchar(100), Type_of_Chargevarchar(100), Chargevarchar(100), Supplier varchar(100), Type_of_Product varchar(100) ) The ( 1st ... there are many )statement that raises the error is: update tmp_AAPT_OnlineAnalyser_ChargeTypeSummary set Account_Number=replace(Account_Number, ' ', '') HOWEVER, the space inside the quotes is created in Perl with: my $space_thing = chr(160); We have to do this because the data is copied and pasted from a web page ( AAPT won't provide the data in any other format ). chr(160) is some 'alternative' space character or something. Anyway, this used to work fine, but now it gives: Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation 'replace' I have no idea what it's talking about. How do I fix it? Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange Illegal mix of collations error
Hello,
could someone help me to explain and resolve this error? Or maybe it is
a bug.
DROP TABLE IF EXISTS `test`.`karty`;
CREATE TABLE `test`.`karty` (
`ICO` char(12) collate latin2_czech_cs NOT NULL default '',
`CisloProvozu` char(6) collate latin2_czech_cs NOT NULL default '',
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default '',
`TypKarty` smallint(6) NOT NULL default '0',
`Vyhotoveni` smallint(6) NOT NULL default '0',
`Jmeno` char(20) collate latin2_czech_cs NOT NULL default '',
`CisloProgramu` smallint(6) NOT NULL default '0',
`Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) character set latin2 default NULL,
`Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
PRIMARY KEY (`ICO`,`CisloProvozu`,`CisPrac`,`TypKarty`,`Vyhotoveni`),
UNIQUE KEY `VnitrniCislo` (`VnitrniCislo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
DROP TABLE IF EXISTS `test`.`karty_imp_tmp`;
CREATE TABLE `test`.`karty_imp_tmp` (
`PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
INSERT INTO test.karty
values('','01','01',10,1,'test',0,'R0','9001AB12D3E','');
INSERT INTO test.karty_imp_tmp
values('test','9001AB12D3E','01');
SELECT k.*, kt.* FROM karty k JOIN karty_imp_tmp kt USING (vnitrnicislo);
returns error: Illegal mix of collations (latin2_general_ci,IMPLICIT)
and (latin2_czech_cs,IMPLICIT) for operation '='
I don't know where latin2_general_ci collation comes from.
OS WinXP, MySQL 4.1.15-nt-log
Thanks in advance
Dusan Pavlica
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Illegal mix of collations error
Dušan Pavlica schrieb:
Hello,
could someone help me to explain and resolve this error? Or maybe it is
a bug.
DROP TABLE IF EXISTS `test`.`karty`;
CREATE TABLE `test`.`karty` (
`ICO` char(12) collate latin2_czech_cs NOT NULL default '',
`CisloProvozu` char(6) collate latin2_czech_cs NOT NULL default '',
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default '',
`TypKarty` smallint(6) NOT NULL default '0',
`Vyhotoveni` smallint(6) NOT NULL default '0',
`Jmeno` char(20) collate latin2_czech_cs NOT NULL default '',
`CisloProgramu` smallint(6) NOT NULL default '0',
`Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) character set latin2 default NULL,
`Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
PRIMARY KEY (`ICO`,`CisloProvozu`,`CisPrac`,`TypKarty`,`Vyhotoveni`),
UNIQUE KEY `VnitrniCislo` (`VnitrniCislo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
DROP TABLE IF EXISTS `test`.`karty_imp_tmp`;
CREATE TABLE `test`.`karty_imp_tmp` (
`PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
INSERT INTO test.karty
values('','01','01',10,1,'test',0,'R0','9001AB12D3E','');
INSERT INTO test.karty_imp_tmp
values('test','9001AB12D3E','01');
SELECT k.*, kt.* FROM karty k JOIN karty_imp_tmp kt USING (vnitrnicislo);
returns error: Illegal mix of collations (latin2_general_ci,IMPLICIT)
and (latin2_czech_cs,IMPLICIT) for operation '='
I don't know where latin2_general_ci collation comes from.
Well one table or the column has this collation.
Check your structures.
= `VnitrniCislo` char(15) character set latin2 default NULL,
shouldn it be
= `VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Illegal mix of collations error
flyerheaven - Barry Krein napsal(a):
Dušan Pavlica schrieb:
Barry napsal(a):
Dušan Pavlica schrieb:
Hello,
could someone help me to explain and resolve this error? Or maybe
it is a bug.
DROP TABLE IF EXISTS `test`.`karty`;
CREATE TABLE `test`.`karty` (
`ICO` char(12) collate latin2_czech_cs NOT NULL default '',
`CisloProvozu` char(6) collate latin2_czech_cs NOT NULL default '',
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default '',
`TypKarty` smallint(6) NOT NULL default '0',
`Vyhotoveni` smallint(6) NOT NULL default '0',
`Jmeno` char(20) collate latin2_czech_cs NOT NULL default '',
`CisloProgramu` smallint(6) NOT NULL default '0',
`Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) character set latin2 default NULL,
`Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
PRIMARY KEY
(`ICO`,`CisloProvozu`,`CisPrac`,`TypKarty`,`Vyhotoveni`),
UNIQUE KEY `VnitrniCislo` (`VnitrniCislo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
DROP TABLE IF EXISTS `test`.`karty_imp_tmp`;
CREATE TABLE `test`.`karty_imp_tmp` (
`PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
INSERT INTO test.karty
values('','01','01',10,1,'test',0,'R0','9001AB12D3E','');
INSERT INTO test.karty_imp_tmp
values('test','9001AB12D3E','01');
SELECT k.*, kt.* FROM karty k JOIN karty_imp_tmp kt USING
(vnitrnicislo);
returns error: Illegal mix of collations
(latin2_general_ci,IMPLICIT) and (latin2_czech_cs,IMPLICIT) for
operation '='
I don't know where latin2_general_ci collation comes from.
Well one table or the column has this collation.
Check your structures.
= `VnitrniCislo` char(15) character set latin2 default NULL,
shouldn it be
= `VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
Barry
I have seen it too and checked both structures in MySQL Table Editor
and both columns had exactly same definitions. And both tables have
same character set and collation
Dusan
Nah look at your structrue:
CisloProgramu` smallint(6) NOT NULL default '0',
`Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) character set latin2 default NULL,
`Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
Here its just character set latin2
CREATE TABLE `test`.`karty_imp_tmp` (
`PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
And here is it collate latin2_czech_cs
That's causing the error.
Barry
You are right. If I create tables where both columns VnitrniCislo have
exactly same definitions, it doesn't throw the error.
Bug must be in the MySQL Table Editor which shows in column details
wrong collations
Thanks, Barry.
Dusan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION' -- again
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]
MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION' -- again
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
Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'
Hello. What is the value of the default_charset variable in your php.ini file? What version of MySQL do you use? Florian Burkart [EMAIL PROTECTED] wrote: What might help as well is another problem I have: Somehow, the data I am getting out of mysql and php and is being served by = apache is still in iso format, and not utf8. Which leads to bad displaying = (unless i switch back to iso in the browser, but then the html stuff in utf= 8 gets strange (which is served correctly in utf8).=20 Is that related? If not, still someone with hints on it? On Wed, 24 Aug 2005 20:55:21 +0300 Gleb Paharenko [EMAIL PROTECTED] wrote: -- 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]
Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'
Hey Gleb, thanks for taking your time! php.ini: ; As of 4.0b4, PHP always outputs a character encoding by default in ; the Content-type: header. To disable sending of the charset, simply ; set it to be empty. ; ; PHP's built-in default is text/html default_mimetype = text/html default_charset = utf-8 mysql version: mysql Ver 14.7 Distrib 4.1.13, for pc-linux-gnu (i686) using readline 5.0 php version: PHP 4.4.0 (cli) (built: Aug 23 2005 14:55:11) Copyright (c) 1997-2004 The PHP Group Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies apache2: Server version: Apache/2.0.54 Server built: Aug 24 2005 13:41:09 Server's Module Magic Number: 20020903:9 Architecture: 32-bit Server compiled with -D APACHE_MPM_DIR=server/mpm/prefork -D APR_HAS_SENDFILE -D APR_HAS_MMAP -D APR_HAVE_IPV6 (IPv4-mapped addresses enabled) -D APR_USE_SYSVSEM_SERIALIZE -D APR_USE_PTHREAD_SERIALIZE -D SINGLE_LISTEN_UNSERIALIZED_ACCEPT -D APR_HAS_OTHER_CHILD -D AP_HAVE_RELIABLE_PIPED_LOGS -D HTTPD_ROOT=/usr -D SUEXEC_BIN=/usr/sbin/suexec2 -D DEFAULT_PIDLOG=/var/run/httpd.pid -D DEFAULT_SCOREBOARD=logs/apache_runtime_status -D DEFAULT_LOCKFILE=/var/run/accept.lock -D DEFAULT_ERRORLOG=logs/error_log -D AP_TYPES_CONFIG_FILE=/etc/apache2/mime.types -D SERVER_CONFIG_FILE=/etc/apache2/httpd.conf The text in the browser coming from PHP is as well as the plain HTML utf-8 encoded, it is only the mysql-queries in php which return iso - besides the original problem that the query sends strings as iso... :/ Thanks again, Florian On Thu, 25 Aug 2005 10:58:07 +0300 Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. What is the value of the default_charset variable in your php.ini file? What version of MySQL do you use? Florian Burkart [EMAIL PROTECTED] wrote: What might help as well is another problem I have: Somehow, the data I am getting out of mysql and php and is being served by = apache is still in iso format, and not utf8. Which leads to bad displaying = (unless i switch back to iso in the browser, but then the html stuff in utf= 8 gets strange (which is served correctly in utf8).=20 Is that related? If not, still someone with hints on it? On Wed, 24 Aug 2005 20:55:21 +0300 Gleb Paharenko [EMAIL PROTECTED] wrote: -- 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] -- Zur Zeit befinde ich mich hinter einer Firewall welche das versenden von E-Mails über die Adresse [EMAIL PROTECTED] verhindert. Ihr erhaltet Antworten auf E-Mails an [EMAIL PROTECTED] daher ebenfalls von [EMAIL PROTECTED] Es macht keinen Unterschied welche der beiden Adressen ihr anschreibt. Meine alte Handynummer +49-151-11616247 ist gekündigt. Hier in Guadalajara, Mexico, erreicht ihr mich unter der +52-3310650934. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'
Hey everyone,
can't figure this one out, might be easy for one of you.
This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This error message is returned on the php website:
Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'UNION'
There is no error message (query executes successfully) in phpmyadmin or when
connecting by mysql.
Following query always executes successfully (no long term solution though):
(SELECT utf8_'Neue Gruppe' AS gruppenstring, utf8_'-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This is the output of
mysql show variables like %character%; show variables like %collation%;
+--++
| Variable_name| Value |
+--++
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results| utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--++
7 rows in set (0.00 sec)
+--+-+
| Variable_name| Value |
+--+-+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+--+-+
3 rows in set (0.00 sec)
This is my.cnf
...
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations.
[mysqld]
character-set-server= utf8
default-character-set = utf8
...
Probably easy, but help please :)
Florian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'
Hey everyone,
can't figure this one out, might be easy for one of you.
This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This error message is returned on the php website:
Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'UNION'
There is no error message (query executes successfully) in phpmyadmin or when
connecting by mysql.
Following query always executes successfully (no long term solution though):
(SELECT utf8_'Neue Gruppe' AS gruppenstring, utf8_'-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This is the output of
mysql show variables like %character%; show variables like %collation%;
+--++
| Variable_name| Value |
+--++
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results| utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--++
7 rows in set (0.00 sec)
+--+-+
| Variable_name| Value |
+--+-+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+--+-+
3 rows in set (0.00 sec)
This is my.cnf
...
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations.
[mysqld]
character-set-server= utf8
default-character-set = utf8
...
Probably easy, but help please :)
Florian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'
Hello.
Your character_set_xxx variables is ok. The problem could be in
the table definition. Send the output of
SHOW CREATE TABLE tbl_gruppen;
Florian Burkart [EMAIL PROTECTED] wrote:
Hey everyone,
can't figure this one out, might be easy for one of you.
This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This error message is returned on the php website:
Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'UNION'
There is no error message (query executes successfully) in phpmyadmin or when
connecting by mysql.
Following query always executes successfully (no long term solution though):
(SELECT utf8_'Neue Gruppe' AS gruppenstring, utf8_'-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This is the output of
mysql show variables like %character%; show variables like %collation%;
+--++
| Variable_name| Value |
+--++
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results| utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--++
7 rows in set (0.00 sec)
+--+-+
| Variable_name| Value |
+--+-+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+--+-+
3 rows in set (0.00 sec)
This is my.cnf
...
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations.
[mysqld]
character-set-server= utf8
default-character-set = utf8
...
Probably easy, but help please :)
Florian
--
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]
Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'
mysql show create table tbl_gruppen;
+-++
| Table | Create Table
|
+-++
| tbl_gruppen | CREATE TABLE `tbl_gruppen` (
`gruppe_id` smallint(4) unsigned NOT NULL auto_increment,
`gruppe` varchar(50) NOT NULL default '',
`kommentar` varchar(255) NOT NULL default '',
`geändert` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
PRIMARY KEY (`gruppe_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-++
1 row in set (0.00 sec)
On Wed, 24 Aug 2005 20:55:21 +0300
Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
Your character_set_xxx variables is ok. The problem could be in
the table definition. Send the output of
SHOW CREATE TABLE tbl_gruppen;
Florian Burkart [EMAIL PROTECTED] wrote:
Hey everyone,
can't figure this one out, might be easy for one of you.
This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This error message is returned on the php website:
Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'UNION'
There is no error message (query executes successfully) in phpmyadmin or
when connecting by mysql.
Following query always executes successfully (no long term solution though):
(SELECT utf8_'Neue Gruppe' AS gruppenstring, utf8_'-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This is the output of
mysql show variables like %character%; show variables like %collation%;
+--++
| Variable_name| Value |
+--++
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results| utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--++
7 rows in set (0.00 sec)
+--+-+
| Variable_name| Value |
+--+-+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+--+-+
3 rows in set (0.00 sec)
This is my.cnf
...
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
# add a section [mysqld-4.1
Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'
What might help as well is another problem I have:
Somehow, the data I am getting out of mysql and php and is being served by
apache is still in iso format, and not utf8. Which leads to bad displaying
(unless i switch back to iso in the browser, but then the html stuff in utf8
gets strange (which is served correctly in utf8).
Is that related? If not, still someone with hints on it?
On Wed, 24 Aug 2005 20:55:21 +0300
Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
Your character_set_xxx variables is ok. The problem could be in
the table definition. Send the output of
SHOW CREATE TABLE tbl_gruppen;
Florian Burkart [EMAIL PROTECTED] wrote:
Hey everyone,
can't figure this one out, might be easy for one of you.
This is the query:
(SELECT 'Neue Gruppe' AS gruppenstring, '-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This error message is returned on the php website:
Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'UNION'
There is no error message (query executes successfully) in phpmyadmin or
when connecting by mysql.
Following query always executes successfully (no long term solution though):
(SELECT utf8_'Neue Gruppe' AS gruppenstring, utf8_'-1' AS gruppe_id)
UNION ALL
(SELECT CONCAT( gruppe, ' (', kommentar, ')' ) AS gruppenstring, gruppe_id
FROM tbl_gruppen
ORDER BY gruppe);
This is the output of
mysql show variables like %character%; show variables like %collation%;
+--++
| Variable_name| Value |
+--++
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results| utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--++
7 rows in set (0.00 sec)
+--+-+
| Variable_name| Value |
+--+-+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+--+-+
3 rows in set (0.00 sec)
This is my.cnf
...
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations.
[mysqld]
character-set-server= utf8
default-character-set = utf8
...
Probably easy, but help please :)
Florian
--
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]
--
Zur Zeit befinde ich mich hinter einer Firewall welche das versenden von
E-Mails über die Adresse [EMAIL PROTECTED] verhindert. Ihr erhaltet Antworten
auf E-Mails an [EMAIL PROTECTED] daher ebenfalls von [EMAIL PROTECTED] Es macht
keinen Unterschied welche der beiden Adressen ihr anschreibt.
Meine alte Handynummer +49-151-11616247 ist gekündigt. Hier in Guadalajara,
Mexico, erreicht ihr mich unter der +52-3310650934.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
Here's how the problem starts. 1. Entered Korean characters in a form. 2. Press submit and I receive that error. Programming Language: PHP 5.0.4 Database: MySQL 4.1.13 Operating System: Windows XP SP2 Charset for the page: UTF-8 Charset for the table: UTF-8 Charset for the database: UTF-8 So where does latin1_swedish_ci come from? I tried entering the same string in phpMyAdmin and I didn't receive the error. SNIPPET: $query = SELECT `id` FROM `table1` WHERE `column1`='$var'; $result = mysql_query ($query, $link); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
Hello. Please, send the output of the following statements: show variables like '%char%'; show create table table1; Tulong! [EMAIL PROTECTED] wrote: Here's how the problem starts. 1. Entered Korean characters in a form. 2. Press submit and I receive that error. Programming Language: PHP 5.0.4 Database: MySQL 4.1.13 Operating System: Windows XP SP2 Charset for the page: UTF-8 Charset for the table: UTF-8 Charset for the database: UTF-8 So where does latin1_swedish_ci come from? I tried entering the same string in phpMyAdmin and I didn't receive the error. SNIPPET: $query = SELECT `id` FROM `table1` WHERE `column1`='$var'; $result = mysql_query ($query, $link); -- 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]
Re: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
SHOW VARIABLES LIKE ''%char%; character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_results utf8 character_set_server latin1 character_set_system utf8 SHOW CREATE TABLE `table1`; CREATE TABLE `main_peeps` ( `id` int(11) unsigned NOT NULL auto_increment COMMENT 'Primary Key', `test` varchar(255) NOT NULL default '' COMMENT 'Test', PRIMARY KEY (`id`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='COMMENT HERE' Please, send the output of the following statements: show variables like '%char%'; show create table table1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
Mark Dacasco wrote: SHOW VARIABLES LIKE ''%char%; character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_results utf8 character_set_server latin1 character_set_system utf8 SHOW CREATE TABLE `table1`; CREATE TABLE `main_peeps` ( `id` int(11) unsigned NOT NULL auto_increment COMMENT 'Primary Key', `test` varchar(255) NOT NULL default '' COMMENT 'Test', PRIMARY KEY (`id`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='COMMENT HERE' Please, send the output of the following statements: show variables like '%char%'; show create table table1; adding to the section [mysqld] of the file my.cnf character-set-server = utf8 solves the problem ? -- . These pages are best viewed by coming to my house and looking at . . my monitor. [S. Lucas Bergman (on his website)]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
I didn't find any option for character-set-server so I add it under mysqld as you've stated. I also found default-character-set so I also set its value to utf8. It didn't work. I tried adding a dash (utf-8) to it. It didn't work also. Yes, I did restart MySQL after saving the configuration. What does that error mean, by the way? adding to the section [mysqld] of the file my.cnf character-set-server = utf8 solves the problem ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
useful links: http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html http://dev.mysql.com/doc/mysql/en/Charset-server.html http://dev.mysql.com/doc/mysql/en/Charset-map.html http://dev.mysql.com/doc/mysql/en/Option_files.html http://bugs.mysql.com/bug.php?id=3611 I've found this in my php.ini, should _not_ be related but it can worth a try (apache restart required). === php.ini === [PHP] default_charset = utf-8 [mbstring] mbstring.internal_encoding = utf-8 === php.ini === and finally SHOW VARIABLES LIKE '%char%'; look like this now character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_results utf8 character_set_server utf8 character_set_system utf8 right ? Mark wrote: I didn't find any option for character-set-server so I add it under mysqld as you've stated. I also found default-character-set so I also set its value to utf8. It didn't work. I tried adding a dash (utf-8) to it. It didn't work also. Yes, I did restart MySQL after saving the configuration. What does that error mean, by the way? The first link provided answer to this question. adding to the section [mysqld] of the file my.cnf character-set-server = utf8 solves the problem ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '=' (SOLVED)
You're right. It is not related. The same output are shown as before. However, the links you gave me was quite useful. I re-read and re-read them over and over until this little query solved my dilemma, sort of: SET NAMES 'utf8'; Thanks for the help! ^_^ useful links: http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html http://dev.mysql.com/doc/mysql/en/Charset-server.html http://dev.mysql.com/doc/mysql/en/Charset-map.html http://dev.mysql.com/doc/mysql/en/Option_files.html http://bugs.mysql.com/bug.php?id=3611 I've found this in my php.ini, should _not_ be related but it can worth a try (apache restart required). === php.ini === [PHP] default_charset = utf-8 [mbstring] mbstring.internal_encoding = utf-8 === php.ini === and finally SHOW VARIABLES LIKE '%char%'; look like this now character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_results utf8 character_set_server utf8 character_set_system utf8 right ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations
Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default What default charset do your slave's tables have? The coercibility value of system constants has changed in 4.1.11. Though it seems not related to your problem I recommend you to upgrade to 4.1.12. The default charset of the slave table is latin1, the same on the master. Slave Table create statement: = CREATE TABLE `sum_day_key_requests` ( ... `key` varchar(255) character set utf8 collate utf8_bin NOT NULL default '', ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1; which is identical with the Master create statement: CREATE TABLE `sum_day_key_requests` ( ... `key` varchar(255) character set utf8 collate utf8_bin NOT NULL default '', ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Marco Marco Poehler http://www.kontaktlinsen-preisvergleich.de Am Montag, den 18.07.2005, 21:34 +0300 schrieb Gleb Paharenko: Hello. Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default What default charset do your slave's tables have? The coercibility value of system constants has changed in 4.1.11. Though it seems not related to your problem I recommend you to upgrade to 4.1.12. Marco P$hler [EMAIL PROTECTED] wrote: Hello List, I've tried to set up a slave from an existing master using innobackup. The copy of the database was successful, but when I started the replication, the following error occured: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default database: 'report'. Query: 'UPDATE sum_day_key_requests SET sum = sum + 1 WHERE day = '2005-7-16' AND type = 'redirect' AND client = '1234' AND channel = '43' AND campaign = '...' AND grouping = 'Singleb$se' AND `key` = 'Single Berlin' AND afftraf = 'NULL' I read the docs and compared charset/collation configuration as you can see below, but it seems to be the same on both servers. any hints welcome ! thanks in advance Marco = Master: MySQL 4.1.10 mysql show variables like 'coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) ps ax | grep mysql 7349 ?Sl 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HAL2005.pid --skip-locking --open-files-limit=8192 --port=3306 --socket=/var/lib/mysql/mysql.sock (no --default-character-set=... or --default-collation=...) /etc/my.cnf doesn't contain any charset or collation information = Slave: MySQL 4.1.10 --- mysql show variables like 'coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) ps ax | grep mysql 23970 pts/0Sl 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HAL2006.pid --skip-locking --open-files-limit=8192 --port=3306 --socket=/var/lib/mysql/mysql.sock (no --default-character-set=... or --default-collation=...) /etc/my.cnf doesn't contain any charset or collation information -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations
Hello. `key` varchar(255) character set utf8 collate utf8_bin NOT NULL ^^^ Field's character set should be latin1 as well. Change it. The default charset of the slave table is latin1, the same on the master. Slave Table create statement: = CREATE TABLE `sum_day_key_requests` ( ... `key` varchar(255) character set utf8 collate utf8_bin NOT NULL default '', ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1; which is identical with the Master create statement: CREATE TABLE `sum_day_key_requests` ( ... `key` varchar(255) character set utf8 collate utf8_bin NOT NULL default '', ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Marco Marco Poehler http://www.kontaktlinsen-preisvergleich.de Am Montag, den 18.07.2005, 21:34 +0300 schrieb Gleb Paharenko: -- 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]
Illegal mix of collations
Hello List, I've tried to set up a slave from an existing master using innobackup. The copy of the database was successful, but when I started the replication, the following error occured: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default database: 'report'. Query: 'UPDATE sum_day_key_requests SET sum = sum + 1 WHERE day = '2005-7-16' AND type = 'redirect' AND client = '1234' AND channel = '43' AND campaign = '...' AND grouping = 'Singleb�se' AND `key` = 'Single Berlin' AND afftraf = 'NULL' I read the docs and compared charset/collation configuration as you can see below, but it seems to be the same on both servers. any hints welcome ! thanks in advance Marco = Master: MySQL 4.1.10 mysql show variables like 'coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) ps ax | grep mysql 7349 ?Sl 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HAL2005.pid --skip-locking --open-files-limit=8192 --port=3306 --socket=/var/lib/mysql/mysql.sock (no --default-character-set=... or --default-collation=...) /etc/my.cnf doesn't contain any charset or collation information = Slave: MySQL 4.1.10 --- mysql show variables like 'coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) ps ax | grep mysql 23970 pts/0Sl 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HAL2006.pid --skip-locking --open-files-limit=8192 --port=3306 --socket=/var/lib/mysql/mysql.sock (no --default-character-set=... or --default-collation=...) /etc/my.cnf doesn't contain any charset or collation information Marco Poehler http://www.kontaktlinsen-preisvergleich.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations
Hello. Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default What default charset do your slave's tables have? The coercibility value of system constants has changed in 4.1.11. Though it seems not related to your problem I recommend you to upgrade to 4.1.12. Marco P$hler [EMAIL PROTECTED] wrote: Hello List, I've tried to set up a slave from an existing master using innobackup. The copy of the database was successful, but when I started the replication, the following error occured: Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default database: 'report'. Query: 'UPDATE sum_day_key_requests SET sum = sum + 1 WHERE day = '2005-7-16' AND type = 'redirect' AND client = '1234' AND channel = '43' AND campaign = '...' AND grouping = 'Singleb$se' AND `key` = 'Single Berlin' AND afftraf = 'NULL' I read the docs and compared charset/collation configuration as you can see below, but it seems to be the same on both servers. any hints welcome ! thanks in advance Marco = Master: MySQL 4.1.10 mysql show variables like 'coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) ps ax | grep mysql 7349 ?Sl 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HAL2005.pid --skip-locking --open-files-limit=8192 --port=3306 --socket=/var/lib/mysql/mysql.sock (no --default-character-set=... or --default-collation=...) /etc/my.cnf doesn't contain any charset or collation information = Slave: MySQL 4.1.10 --- mysql show variables like 'coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) ps ax | grep mysql 23970 pts/0Sl 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HAL2006.pid --skip-locking --open-files-limit=8192 --port=3306 --socket=/var/lib/mysql/mysql.sock (no --default-character-set=... or --default-collation=...) /etc/my.cnf doesn't contain any charset or collation information Marco Poehler http://www.kontaktlinsen-preisvergleich.de -- 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]
Re: [PARTIALLY SOLVED] Illegal mix of collations for operation IN
I did more investigation and found out that
1) with MySQL 4.1.10-nt both queries executed from Query Browser work fine
(no error) - I haven't chance to test it with my application through ODBC
connection
2) when I set initial statement in MyODBC SET CHARACTER SET 'cp1250'
instead of SET NAMES 'cp1250' for connection to MySQL 4.1.9-nt-log queries
from my application work fine and that is what I need
mysql show create table files \G
*** 1. row ***
Table: files
Create Table: CREATE TABLE `files` (
`FileName` char(100) collate latin2_czech_cs NOT NULL default '',
`CommandType` enum('NONE','ON_SUCCESS','ON_NOTRANSMIT') collate
latin2_czech_cs NOT NULL default 'NONE',
`Command` char(128) collate latin2_czech_cs default NULL,
PRIMARY KEY (`FileName`,`CommandType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs
1 row in set (0.01 sec)
mysql show variables like '%char%';
+--+-+
| Variable_name| Value
|
+--+-+
| character_set_client | cp1250
|
| character_set_connection | latin2
|
| character_set_database | latin2
|
| character_set_results| cp1250
|
| character_set_server | latin2
|
| character_set_system | utf8
|
| character_sets_dir | D:\Program Files\MySQL\MySQL Server
4.1\share\charsets/ |
+--+-+
7 rows in set (0.00 sec)
mysql show variables like '%col%';
+--+---+
| Variable_name| Value |
+--+---+
| collation_connection | cp1250_general_ci |
| collation_database | latin2_czech_cs |
| collation_server | latin2_general_ci |
| protocol_version | 10|
+--+---+
4 rows in set (0.00 sec)
I am still wondering why
SELECT * FROM files WHERE filename = 'file1.txt' OR filename = 'file2.txt'
was working and
SELECT * FROM files WHERE filename IN('file1.txt', 'file2.txt')
wasn't
Dusan
- Original Message -
From: Gleb Paharenko [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, June 02, 2005 2:13 PM
Subject: Re: Illegal mix of collations for operation IN
Hello.
Usually debugging of such kind of problems starts with examination of
the output of:
show variables like '%char%';
show variables like '%colla%';
Send the output of:
show create table your_table;
Hello,
I have problem which I don't understand.
if I send command (from Query Browser or from C++ Builder application)
SELECT FileName FROM Files WHERE FileName = 'FILE1.TXT' OR FileName =
'FILE2.TXT'
query executes without any problem but command
SELECT FileName FROM Files WHERE FileName IN ('FILE1.TXT', 'FILE2.TXT')
throws error illegal mix of collations for operation 'IN'
I thought that IN is somehow by optimizer translated to ORs
Could someone explain me why first query is OK and second not? Please.
I'm using WinXP SP2 and MySQL 4.1.9-nt-log
databases and tables use CHARSET=latin2 COLLATE=latin2_czech_cs
I already read some articles about the topic in MySQL forum but they
didn't helped me
much.
Thanks in advance
Dusan Pavlica
Du$an Pavlica [EMAIL PROTECTED] wrote:
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations for operation IN
Hello,
I have problem which I don't understand.
if I send command (from Query Browser or from C++ Builder application)
SELECT FileName FROM Files WHERE FileName = 'FILE1.TXT' OR FileName =
'FILE2.TXT'
query executes without any problem but command
SELECT FileName FROM Files WHERE FileName IN ('FILE1.TXT', 'FILE2.TXT')
throws error illegal mix of collations for operation 'IN'
I thought that IN is somehow by optimizer translated to ORs
Could someone explain me why first query is OK and second not? Please.
I'm using WinXP SP2 and MySQL 4.1.9-nt-log
databases and tables use CHARSET=latin2 COLLATE=latin2_czech_cs
I already read some articles about the topic in MySQL forum but they didn't
helped me much.
Thanks in advance
Dusan Pavlica
Re: Illegal mix of collations for operation IN
Hello.
Usually debugging of such kind of problems starts with examination of
the output of:
show variables like '%char%';
show variables like '%colla%';
Send the output of:
show create table your_table;
Hello,
I have problem which I don't understand.
if I send command (from Query Browser or from C++ Builder application)
SELECT FileName FROM Files WHERE FileName = 'FILE1.TXT' OR FileName =
'FILE2.TXT'
query executes without any problem but command
SELECT FileName FROM Files WHERE FileName IN ('FILE1.TXT', 'FILE2.TXT')
throws error illegal mix of collations for operation 'IN'
I thought that IN is somehow by optimizer translated to ORs
Could someone explain me why first query is OK and second not? Please.
I'm using WinXP SP2 and MySQL 4.1.9-nt-log
databases and tables use CHARSET=latin2 COLLATE=latin2_czech_cs
I already read some articles about the topic in MySQL forum but they
didn't helped me
much.
Thanks in advance
Dusan Pavlica
Du$an Pavlica [EMAIL PROTECTED] wrote:
--
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]
Re: Illegal mix of collations - new twist on a familiar problem...
Hello. Please, send us an output of the following statements: show variables like '%char%'; show variables like '%collation%'; Stembridge, Michael [EMAIL PROTECTED] wrote: When running this simple query: SELECT fileid FROM test WHERE ecn='0' MySQL yields this error: #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' The table collation is latin1_swedish_ci. I ran SHOW CREATE TABLE fileid and found this: DEFAULT CHARSET=latin1 at the end. Here is the table layout: fileid int(10) datereceiveddate scn varchar(11) latin1_swedish_ci ecn varchar(11) latin1_swedish_ci Here is the problem: The table collation was once utf8_general_ci (due to mistake), it was switched back to latin1_swedish_ci. Ever since then I've had this error. Suggestions for getting the table back in line? Thanks, Michael -- 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]
Illegal mix of collations - new twist on a familiar problem...
When running this simple query: SELECT fileid FROM test WHERE ecn='0' MySQL yields this error: #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' The table collation is latin1_swedish_ci. I ran SHOW CREATE TABLE fileid and found this: DEFAULT CHARSET=latin1 at the end. Here is the table layout: fileid int(10) datereceiveddate scn varchar(11) latin1_swedish_ci ecn varchar(11) latin1_swedish_ci Here is the problem: The table collation was once utf8_general_ci (due to mistake), it was switched back to latin1_swedish_ci. Ever since then I've had this error. Suggestions for getting the table back in line? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error 1271 (HY000) - Illegal mix of collations
It means you have a problem with the way either your tables, columns, database, or connection Firstly do a show create table and check that the character set for the table(s) concerned are the same and that any 'character' column has the same set as the table. If you set the default char set at database level and the default on the table is different you'll need to wrap your query with a set character set pair - the first to set it to what you need and the second to set it back to the default. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Eli [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 05:47 To: mysql@lists.mysql.com Subject: Error 1271 (HY000) - Illegal mix of collations Hi, I'm running a query using UNION, where all parts of the union are queries from the same syntax and from tables with same definition, and the select is same too. Each of the union parts is a query with JOINs. I got this error: ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION' I tried to look after it on the net, but couldn't find anything meaningful. thanks in advance, -Eli. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1271 (HY000) - Illegal mix of collations
Hello. See: http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html Probably, you should carefully read the parts of manual related to the character sets. See: http://dev.mysql.com/doc/mysql/en/charset.html Eli [EMAIL PROTECTED] wrote: Hi, I'm running a query using UNION, where all parts of the union are queries from the same syntax and from tables with same definition, and the select is same too. Each of the union parts is a query with JOINs. I got this error: ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION' I tried to look after it on the net, but couldn't find anything meaningful. thanks in advance, -Eli. -- 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]
Error 1271 (HY000) - Illegal mix of collations
Hi, I'm running a query using UNION, where all parts of the union are queries from the same syntax and from tables with same definition, and the select is same too. Each of the union parts is a query with JOINs. I got this error: ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION' I tried to look after it on the net, but couldn't find anything meaningful. thanks in advance, -Eli. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: illegal mix of collations(latin2_general_ci Implicit)and(latin1_s wedish_ci IMPLICIT) for operation=
Hello, I think your tables have a collation different from the connection collation. Open mysql client: mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.04 sec) and if are different set the collation in your my.cnf file. Santino At 1:25 + 25-01-2005, Cecep Rosuludin wrote: Dear All, I found an error in mysql message, when I tried to excute this sql in mysqlfront: select dsr2.mother_vessel,mother_vessel_voy,voyage.voy_aims,voyage.td, poo.loading_port_name,pod.disch_name from dsr2 left join voyage on voyage.voy=dsr2.mother_vessel_voy left join poo on poo.loading_port=dsr2.pol left join pod on pod.disch_code=dsr2.pod the error message is : illegal mix of collation(latin2_general_ci Implicit)and(latin1_swedish_ci IMPLICIT) for operation= please advice regarding this error,...how to solve this problem..! -- ___ Find what you are looking for with the Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: illegal mix of collations(latin2_general_ci Implicit)and(latin1_s wedish_ci IMPLICIT) for operation=
Hello. See: http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html Cecep Rosuludin [EMAIL PROTECTED] wrote: Dear All, I found an error in mysql message, when I tried to excute this sql in mysql= front: select dsr2.mother_vessel,mother_vessel_voy,voyage.voy_aims,voyage.td, poo.loading_port_name,pod.disch_name from dsr2 left join voyage on voyage.voy=3Ddsr2.mother_vessel_voy left join poo on poo.loading_port=3Ddsr2.pol left join pod on pod.disch_code=3Ddsr2.pod the error message is : illegal mix of collation(latin2_general_ci Implicit)and(latin1_swedish_ci I= MPLICIT) for operation=3D please advice regarding this error,...how to solve this problem..! --=20 ___ Find what you are looking for with the Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.as= p?SRC=3Dlycos10 -- 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]
illegal mix of collations(latin2_general_ci Implicit)and(latin1_s wedish_ci IMPLICIT) for operation=
Dear All, I found an error in mysql message, when I tried to excute this sql in mysqlfront: select dsr2.mother_vessel,mother_vessel_voy,voyage.voy_aims,voyage.td, poo.loading_port_name,pod.disch_name from dsr2 left join voyage on voyage.voy=dsr2.mother_vessel_voy left join poo on poo.loading_port=dsr2.pol left join pod on pod.disch_code=dsr2.pod the error message is : illegal mix of collation(latin2_general_ci Implicit)and(latin1_swedish_ci IMPLICIT) for operation= please advice regarding this error,...how to solve this problem..! -- ___ Find what you are looking for with the Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More Illegal mix of collations trouble.
Hello. Put in you [client] section of my.cnf character_set=latin1 [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Last week we upgraded from mysql 4.0 to 4.1.7. Since then some queries on newly created tables (ie created since the upgrade) are failing with the error: #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' I have been doing a lot of reading. (Until this problem I had never heard of a character set collation.) It looks like some database connections are specifying UTF-8 and over-ridding the default global setting of latin1. See the mysql command line tool log below to see that the variables don't match the global variables. Where do I look to see what is causing this? The /etc/my.cnf already contains this line in the [mysqld] section: default-character-set=latin1 The error message occurs both when I use the mysql command line tool from the local host (mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686)) and also from PHP on a different server (phpinfo() reports Client API version 3.23.56). When I add the following line to /etc/my.cnf on the same machine as the server runs on and connect with the command line tool I don't have the same problem but this does not fix the connections from the PHP client. (Added to the [mysql] section.) default-character-set=latin1 I don't understand how or why the default connection data encoding would be different from the default table encoding. Maybe someone can explain this to me as well. By using SET CHARACTER SET latin1; in the command line tool the problem goes away but it's silly to have to tell all my clients they have to explicitly set the character set every time they connect. What settings should be adjusted to prevent the errors from occuring? Thanks, /Chad mysql SELECT SUM( Credits ) - FROM Acct_Payments - WHERE Void = 'No'; ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' mysql show variables like 'char%'; + -- 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]
More Illegal mix of collations trouble.
Last week we upgraded from mysql 4.0 to 4.1.7. Since then some queries on newly created tables (ie created since the upgrade) are failing with the error: #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' I have been doing a lot of reading. (Until this problem I had never heard of a character set collation.) It looks like some database connections are specifying UTF-8 and over-ridding the default global setting of latin1. See the mysql command line tool log below to see that the variables don't match the global variables. Where do I look to see what is causing this? The /etc/my.cnf already contains this line in the [mysqld] section: default-character-set=latin1 The error message occurs both when I use the mysql command line tool from the local host (mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686)) and also from PHP on a different server (phpinfo() reports Client API version 3.23.56). When I add the following line to /etc/my.cnf on the same machine as the server runs on and connect with the command line tool I don't have the same problem but this does not fix the connections from the PHP client. (Added to the [mysql] section.) default-character-set=latin1 I don't understand how or why the default connection data encoding would be different from the default table encoding. Maybe someone can explain this to me as well. By using SET CHARACTER SET latin1; in the command line tool the problem goes away but it's silly to have to tell all my clients they have to explicitly set the character set every time they connect. What settings should be adjusted to prevent the errors from occuring? Thanks, /Chad mysql SELECT SUM( Credits ) - FROM Acct_Payments - WHERE Void = 'No'; ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' mysql show variables like 'char%'; +-- ++ | Variable_name| Value | +-- ++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-max-4.1.7-pc-linux-i686/share/mysql/charsets/ | +-- ++ 7 rows in set (0.00 sec) mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) mysql show global variables like 'char%'; +-- ++ | Variable_name| Value | +-- ++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-max-4.1.7-pc-linux-i686/share/mysql/charsets/ | +-- ++ 7 rows in set (0.00 sec) mysql show global variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) mysql show create table Acct_Payments \G
Re: Illegal mix of collations with 4.1.7
Hello. Use Perl, DBI :) Frederic Wenzel [EMAIL PROTECTED] wrote: On Tue, 30 Nov 2004 19:24:05 +0200, Gleb Paharenko [EMAIL PROTECTED] wrote: The first impression is that you forgot to convert character columns. See: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html http://dev.mysql.com/doc/mysql/en/Charset-conversion.html Once the Character Sets are set up (everything is utf8 now on my installation), how can it be achieved to convert *ALL* columns in *ALL* tables to the same, new COLLATION value? Changing them by hand would lead to admin's fun for, say, weeks ;) Thanks in advance, Fred -- 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]
Re: Illegal mix of collations with 4.1.7
On Tue, 30 Nov 2004 19:24:05 +0200, Gleb Paharenko [EMAIL PROTECTED] wrote: The first impression is that you forgot to convert character columns. See: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html http://dev.mysql.com/doc/mysql/en/Charset-conversion.html Once the Character Sets are set up (everything is utf8 now on my installation), how can it be achieved to convert *ALL* columns in *ALL* tables to the same, new COLLATION value? Changing them by hand would lead to admin's fun for, say, weeks ;) Thanks in advance, Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations with 4.1.7
Hello. The first impression is that you forgot to convert character columns. See: http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html http://dev.mysql.com/doc/mysql/en/Charset-conversion.html V. M. Brasseur [EMAIL PROTECTED] wrote: Ever since we upgraded to 4.1.7, we've been seeing a lot of errors similiar to this one: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' The query which generated this particular error is this: SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced = 2004113004 AND INSTR( sPatronName, 'bubba' ) != 0; But other queries have also been kicking this error out as well. The server is being started with the following options (some altered to protect the innocent): mysqld would have been started with the following arguments: --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data --port=portnum --socket=/dbs/tpp/mysql-4.1/mysql.sock --user=username --log-error=/dbs/tpp/mysql-4.1/logs/ping.err --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8 The problem, I'm sure, is that --default-character-set=utf8 option, but I don't know much beyond that. Google searches aren't helping much with this one. Something is out of alignment somewhere, I'm just not sure what it is. Any help? Thanks in advance, --V -- 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]
Illegal mix of collations with 4.1.7
Ever since we upgraded to 4.1.7, we've been seeing a lot of errors similiar to this one: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' The query which generated this particular error is this: SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced = 2004113004 AND INSTR( sPatronName, 'bubba' ) != 0; But other queries have also been kicking this error out as well. The server is being started with the following options (some altered to protect the innocent): mysqld would have been started with the following arguments: --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data --port=portnum --socket=/dbs/tpp/mysql-4.1/mysql.sock --user=username --log-error=/dbs/tpp/mysql-4.1/logs/ping.err --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8 The problem, I'm sure, is that --default-character-set=utf8 option, but I don't know much beyond that. Google searches aren't helping much with this one. Something is out of alignment somewhere, I'm just not sure what it is. Any help? Thanks in advance, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations with 4.1.7
Hello, I think your tables have a collation different from the connection collation. Open mysql client: mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.04 sec) and if are different set the collation in your my.cnf file. Santino At 8:59 -0800 30-11-2004, V. M. Brasseur wrote: Ever since we upgraded to 4.1.7, we've been seeing a lot of errors similiar to this one: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' The query which generated this particular error is this: SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced = 2004113004 AND INSTR( sPatronName, 'bubba' ) != 0; But other queries have also been kicking this error out as well. The server is being started with the following options (some altered to protect the innocent): mysqld would have been started with the following arguments: --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data --port=portnum --socket=/dbs/tpp/mysql-4.1/mysql.sock --user=username --log-error=/dbs/tpp/mysql-4.1/logs/ping.err --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8 The problem, I'm sure, is that --default-character-set=utf8 option, but I don't know much beyond that. Google searches aren't helping much with this one. Something is out of alignment somewhere, I'm just not sure what it is. Any help? Thanks in advance, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations with 4.1.7
Ah! Many thanks. That appears to be our problem here: mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +--+---+ I'll work on getting all the connections onto the same collation page. Very many thanks again! --V Santino wrote: Hello, I think your tables have a collation different from the connection collation. Open mysql client: mysql show variables like 'colla%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.04 sec) and if are different set the collation in your my.cnf file. Santino At 8:59 -0800 30-11-2004, V. M. Brasseur wrote: Ever since we upgraded to 4.1.7, we've been seeing a lot of errors similiar to this one: ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' The query which generated this particular error is this: SELECT COUNT(*) FROM holdsplaced WHERE timestampDatePlaced = 2004113004 AND INSTR( sPatronName, 'bubba' ) != 0; But other queries have also been kicking this error out as well. The server is being started with the following options (some altered to protect the innocent): mysqld would have been started with the following arguments: --basedir=/dbs/tpp/mysql-4.1 --datadir=/dbs/tpp/mysql-4.1/data --port=portnum --socket=/dbs/tpp/mysql-4.1/mysql.sock --user=username --log-error=/dbs/tpp/mysql-4.1/logs/ping.err --log=/dbs/tpp/mysql-4.1/logs/ping.log --default-character-set=utf8 The problem, I'm sure, is that --default-character-set=utf8 option, but I don't know much beyond that. Google searches aren't helping much with this one. Something is out of alignment somewhere, I'm just not sure what it is. Any help? Thanks in advance, --V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication
Hello all, I'm having a very peculiar and nerve racking experience with MySQL 4.1.7 running on Fedora Core 2. On the master the query executes just fine, however on the slave I get: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default database: 'BRITEERNO'. Query: 'Update contacts set firstname = '',lastname = '',email = '' ,company = '',address1 = '',address2 = '' ,address3 = '',city = '',state = 'XX' ,zip = '', country = 'XX',phone = '' ,fax = '',user = '',pass = '',changed_on = now() ,status = 'X' where RNK_id = ''' I'm completely and uterly stumped by this, I have no idea of where to go from here. Any help will be greatly appreciated. Thank, Michael Grubb 000domains.com Below is output from my master and slave show variables command. Show variables on the master: character_set_client latin1 character_set_connection latin1 character_set_database latin1 character_set_results latin1 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci Show variables on the slave: character_set_client latin1 character_set_connection latin1 character_set_database latin1 character_set_results latin1 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci signature.asc Description: PGP signature signature.asc Description: OpenPGP digital signature
RE: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication
Michael, Normally I would let a fellow domain registrar fend for themselves, but I'm feeling nice today. :) Do a show create table contacts and see what the charset is set too. I bet the character set on the slave is different. Are you running this from the command line? Or are you getting this error in the error_log? Donny -Original Message- From: Michael Grubb [mailto:[EMAIL PROTECTED] Sent: Monday, November 15, 2004 2:49 PM To: [EMAIL PROTECTED] Subject: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication Hello all, I'm having a very peculiar and nerve racking experience with MySQL 4.1.7 running on Fedora Core 2. On the master the query executes just fine, however on the slave I get: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default database: 'BRITEERNO'. Query: 'Update contacts set firstname = '',lastname = '',email = '' ,company = '',address1 = '',address2 = '' ,address3 = '',city = '',state = 'XX' ,zip = '', country = 'XX',phone = '' ,fax = '',user = '',pass = '',changed_on = now() ,status = 'X' where RNK_id = ''' I'm completely and uterly stumped by this, I have no idea of where to go from here. Any help will be greatly appreciated. Thank, Michael Grubb 000domains.com Below is output from my master and slave show variables command. Show variables on the master: character_set_client latin1 character_set_connection latin1 character_set_databaselatin1 character_set_results latin1 character_set_server latin1 character_set_systemutf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci Show variables on the slave: character_set_client latin1 character_set_connectionlatin1 character_set_database latin1 character_set_results latin1 character_set_server latin1 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection latin1_swedish_ci collation_database latin1_swedish_ci collation_server latin1_swedish_ci -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication
Donny,
I certainly appreciate your help.
"show create table contacts" shows the same output on both the master
and the slave ('latin1').
This is being run by the slave's SQL thread, and the Error is shown in
the "Last error" line of
"show slave status" and the error log.
Thanks again,
Michael
Donny Simonton wrote:
Michael,
Normally I would let a fellow domain registrar fend for themselves, but I'm
feeling nice today. :)
Do a "show create table contacts" and see what the charset is set too.
I bet the character set on the slave is different. Are you running this
from the command line?
Or are you getting this error in the error_log?
Donny
-Original Message-
From: Michael Grubb [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 15, 2004 2:49 PM
To: [EMAIL PROTECTED]
Subject: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) and Replication
Hello all,
I'm having a very peculiar and nerve racking experience with MySQL 4.1.7
running on Fedora Core 2.
On the master the query executes just fine, however on the slave I get:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='' on query. Default
database: 'BRITEERNO'. Query: 'Update contacts
set firstname = '',lastname = '',email =
'' ,company = '',address1 = '',address2 = ''
,address3 = '',city = '',state =
'XX' ,zip = '', country = 'XX',phone = ''
,fax = '',user = '',pass =
'',changed_on = now() ,status = 'X'
where RNK_id = '''
I'm completely and uterly stumped by this, I have no idea of where to go
from here.
Any help will be greatly appreciated.
Thank,
Michael Grubb
000domains.com
Below is output from my master and slave "show variables" command.
Show variables on the master:
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir
/usr/share/mysql/charsets/
collation_connection
latin1_swedish_ci
collation_database
latin1_swedish_ci
collation_server latin1_swedish_ci
Show variables on the slave:
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results
latin1
character_set_server
latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection latin1_swedish_ci
collation_database
latin1_swedish_ci
collation_server
latin1_swedish_ci
signature.asc
Description: OpenPGP digital signature
RE: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) and Replication
Got me stumped on this one, I've been using 4.1 for over a year now and I
haven't run into this problem except somebody was using phpmyadmin or
something. Maybe somebody else can help with this one, sorry.
Donny
_
From: Michael Grubb [mailto:[EMAIL PROTECTED]
Sent: Monday, November 15, 2004 4:23 PM
To: Donny Simonton
Cc: [EMAIL PROTECTED]
Subject: Re: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) and Replication
Donny,
I certainly appreciate your help.
show create table contacts shows the same output on both the master and
the slave ('latin1').
This is being run by the slave's SQL thread, and the Error is shown in the
Last error line of
show slave status and the error log.
Thanks again,
Michael
Donny Simonton wrote:
Michael,
Normally I would let a fellow domain registrar fend for themselves, but I'm
feeling nice today. :)
Do a show create table contacts and see what the charset is set too.
I bet the character set on the slave is different. Are you running this
from the command line?
Or are you getting this error in the error_log?
Donny
-Original Message-
From: Michael Grubb [mailto:[EMAIL PROTECTED]
Sent: Monday, November 15, 2004 2:49 PM
To: [EMAIL PROTECTED]
Subject: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) and Replication
Hello all,
I'm having a very peculiar and nerve racking experience with MySQL 4.1.7
running on Fedora Core 2.
On the master the query executes just fine, however on the slave I get:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='' on query. Default
database: 'BRITEERNO'. Query: 'Update contacts
set firstname = '',lastname = '',email =
'' ,company = '',address1 = '',address2 = ''
,address3 = '',city = '',state =
'XX' ,zip = '', country = 'XX',phone = ''
,fax = '',user = '',pass =
'',changed_on = now() ,status = 'X'
where RNK_id = '''
I'm completely and uterly stumped by this, I have no idea of where to go
from here.
Any help will be greatly appreciated.
Thank,
Michael Grubb
000domains.com
Below is output from my master and slave show variables command.
Show variables on the master:
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir
/usr/share/mysql/charsets/
collation_connection
latin1_swedish_ci
collation_database
latin1_swedish_ci
collation_server latin1_swedish_ci
Show variables on the slave:
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results
latin1
character_set_server
latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection latin1_swedish_ci
collation_database
latin1_swedish_ci
collation_server
latin1_swedish_ci
Problem with Mysql 4.1.3: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
Hi All I am very confused about MySQL 4.1.3. I have problems with the character set and the collation. Under 4.0.20 I had never such problems. I downloaded the beta-files from version 4.1.3b and zipped it to c:\mysql. Then I loaded my data within a script with load data infile into my tables... The point is, that all tables have now the collation 'latin1_swedish_ci'?!!? Is this the default collation? Why does this collation appear? Interesting is, that the meta-data (all tables in the mysql-db) does have the 'latin1_swedish_ci' - collation too. The problem is now with phpmyadmin, if I make a select like SUBSTRING_INDEX( domain, '.', -2 ) from url_cat, the following error-message appears: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE). I found out, that if I use the following statement select SUBSTRING_INDEX( domain, _latin1'.', -2 ) from url_cat it works But the Problem is, i won't change all my old scripts from 4.0!!! I found out, that if I use mysql in DOS, everything works without error!!! Do I have to change something in phpmyadmin (i have version 2.6.0rc1)? I am very confused about it and i don't know how and which character-set and collation I have to choose. I live in Switzerland... Which character-set and collation I have to choose? Is there a 'standard' character-set/collation I should use? Do I have to set parameters in the my.cnf - file? My system runs under Windows XP with SP2. Thank you in advance for your help. Yours Martin Rytz DBA from Switzerland
RE: Problem with Mysql 4.1.3: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
This is not a mysql problem, it is a phpmyadmin problem. You must be using 2.6.0 rc1, download 2.6.0 beta 2 and you won't have the problem any more. I have complained to them about it, but not exactly sure what they are going to do. Donny -Original Message- From: Martin Rytz [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 9:35 AM To: [EMAIL PROTECTED] Subject: Problem with Mysql 4.1.3: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) Hi All I am very confused about MySQL 4.1.3. I have problems with the character set and the collation. Under 4.0.20 I had never such problems. I downloaded the beta-files from version 4.1.3b and zipped it to c:\mysql. Then I loaded my data within a script with load data infile into my tables... The point is, that all tables have now the collation 'latin1_swedish_ci'?!!? Is this the default collation? Why does this collation appear? Interesting is, that the meta-data (all tables in the mysql-db) does have the 'latin1_swedish_ci' - collation too. The problem is now with phpmyadmin, if I make a select like SUBSTRING_INDEX( domain, '.', -2 ) from url_cat, the following error-message appears: Error #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE). I found out, that if I use the following statement select SUBSTRING_INDEX( domain, _latin1'.', -2 ) from url_cat it works But the Problem is, i won't change all my old scripts from 4.0!!! I found out, that if I use mysql in DOS, everything works without error!!! Do I have to change something in phpmyadmin (i have version 2.6.0rc1)? I am very confused about it and i don't know how and which character-set and collation I have to choose. I live in Switzerland... Which character-set and collation I have to choose? Is there a 'standard' character-set/collation I should use? Do I have to set parameters in the my.cnf - file? My system runs under Windows XP with SP2. Thank you in advance for your help. Yours Martin Rytz DBA from Switzerland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
