#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
*************************** 1. row ***************************
Table: Acct_Payments
Create Table: CREATE TABLE `Acct_Payments` (
`ID` bigint(12) unsigned NOT NULL auto_increment,
`UserID` bigint(12) unsigned NOT NULL default '0',
`Credits` smallint(4) unsigned NOT NULL default '0',
`Cost` decimal(6,2) NOT NULL default '0.00',
`Tax` decimal(6,2) NOT NULL default '0.00',
`CC_Last4` varchar(4) NOT NULL default '',
`PaymentTime` datetime NOT NULL default '0000-00-00 00:00:00',
`ExpiryDate` date NOT NULL default '0000-00-00',
`CreditExpiryID` int(10) unsigned NOT NULL default '0',
`BillingName` varchar(255) default NULL,
`BillingAddress` varchar(255) default NULL,
`BillingCity` varchar(255) default NULL,
`BillingStateProv` varchar(255) default NULL,
`BillingCountry` varchar(255) default NULL,
`BillingPostalCode` varchar(30) default NULL,
`BillingPhone` varchar(30) default NULL,
`TransactionID` varchar(50) default NULL,
`Void` enum('Yes','No') NOT NULL default 'No',
`PaymentType` enum('creditcard','ppal','check','po') default 'creditcard',
`PaymentIdentification` varchar(255) default NULL,
`IPAddress` varchar(15) default NULL,
`EmailAddress` varchar(255) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `purchaseID` (`ID`),
KEY `purchaseID_2` (`ID`),
KEY `userID` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
**** ALSO NOTE: I DID NOT SPECIFY ANY CHARSET OR COLLATION WHEN I CREATED THE TABLES. ***
mysql> SET CHARACTER SET latin1 ; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT SUM( Credits ) FROM Acct_Payments WHERE Void = 'No'; +----------------+ | SUM( Credits ) | +----------------+ | NULL | +----------------+ 1 row in set (0.00 sec)
Also because I looked into it the output from the "locale" tool is as follows:
LANG=en_US LC_CTYPE="en_US" LC_NUMERIC="en_US" LC_TIME="en_US" LC_COLLATE=C LC_MONETARY="en_US" LC_MESSAGES="en_US" LC_PAPER="en_US" LC_NAME="en_US" LC_ADDRESS="en_US" LC_TELEPHONE="en_US" LC_MEASUREMENT="en_US" LC_IDENTIFICATION="en_US" LC_ALL=
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
