HagensTechiBlog
DeVelOPerS CrnR/LinuXUsrBloG
0
I just came across a very strange Mysql-Behaviour, which i want to discuss with the community.
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 'test1': 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) mysql> SHOW VARIABLES LIKE 'char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 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 on some machines and not on others. At least with data in it on every machine the same errors 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!
If you need to convert all tables of a database from MyISAM (or somewhat else) to InnoDB, you can use the following Shell-Skript (Download-Link ):
#!/bin/sh
for tbl in `cat tables`
do
echo “ALTER TABLE \`$tbl\` ENGINE = InnoDB ”
done
Please take care! Backup your database first!
Now we want to create a list containing all Tables of a specified database. At shell prompt, execute the following line:
mysql -uUSER -pPASSWD -D DATABASE_NAME -e ‘SHOW TABLES’ > tables
To process all tables, type in the following command into shell prompt:
mysql -uuser -ppasswd -D database < `convert_to_innodb.sh`
Thats all.
Back to top