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
