1267 Illegal mix of collations on a date field in MySQL
Posted by: Medolan Developer
Date: May 29, 2011 02:59PM

Dear mysql-users and -developers

we came across a strange behaviour of mysql with collations on a DATE-Field.

I postet a structured form of this question in my developer blog: http://blog.sky-bizz.com/2011/05/29/1267-illegal-mix-of-collations-on-a-date-field-in-mysql/.


In the following example there is a table with existing records. After altering the table and appending a DATE-Field, this field is NOT writeable in already existing records. The following error message occurs on every write to the field:

1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation ’=’



The connection is utf8, the Table was created with utf8 (except the ENUM-Fields), indeed the whole database was created with utf8.

In Detail:

This is the table (i renamed the table for security purposes):

CREATE TABLE `test1` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT(10) UNSIGNED NOT NULL,
`location_id` BIGINT(20) UNSIGNED DEFAULT NULL,
`for_time` datetime NOT NULL,
`value` DECIMAL(5,1) UNSIGNED NOT NULL,
`unit` enum('mg_dl','mmol_l') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`value_mgdl` DECIMAL(7,3) NOT NULL DEFAULT '0.000',
`value_mmoll` DECIMAL(7,3) NOT NULL DEFAULT '0.000',
`source` enum('import','test','user','support') CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'user',
`comment` VARCHAR(255) NOT NULL,
`created` datetime NOT NULL,
`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `fk_copd_location_id` (`location_id`),
KEY `for_time` (`for_time`),
KEY `value` (`value`,`unit`),
KEY `value_mgdl` (`value_mgdl`),
KEY `value_mmoll` (`value_mmoll`),
KEY `source` (`source`),
CONSTRAINT `fk_test1_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;


SHOW TABLE STATUS like 'bloodsugar':

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
bloodsugar InnoDB 10 Compact 8 2048 16384 0 114688 611319808 14 2011-05-29 20:28:54 NULL NULL utf8_general_ci NULL


mysql> show variables like '%colla%';
+----------------------+-----------------+
| 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 table contains some records, and has an overhead of 500 MB (i deleted a lot of records some days ago).

Then i entered the following statement:

ALTER TABLE `test1` ADD `test` DATE NULL DEFAULT NULL ;
Everything works fine right now.

But each of the following statements gives the error described above:

UPDATE test1 SET test=NULL;
or

UPDATE test1 SET test=DATE(for_time);
or

UPDATE test1 SET test='2011-01-01';
and so on….

Result:

1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation ’=’

Setting up a new table with all the steps described above works fine and no error will occure.

But altering the existing table gives this annoying error.
My main Problem is: on the production database there are a lot of records collected in the last 6 months. So i am looking for the underlying cause for such an error.

The MySQL-Version is “Ver 14.14 Distrib 5.1.56″ running an Debian Lenny 64bit.

Can anyone give me some hints or explain this issue?

Thanks in advance!

Options: ReplyQuote


Subject
Views
Written By
Posted
1267 Illegal mix of collations on a date field in MySQL
10258
May 29, 2011 02:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.