November 5th, 2012, 11:02 PM
Illegal mix of collations for operation 'like'... ?
Scratching my head over a collation issue. I've googled it to death but can't figure it out. The collations issue is affecting 3 columns in this particular table - date, aid and lastmodified. As far as I know I've changed the database, table and columns to be utf8mb4, but for some reason it seems they still aren't. Can anyone shed some light a solution to this one? Happy to provide more info - let me know what's required.
When I manually force mysql to convert the column in the statement it works without an error.
CREATE TABLE `announcements` (
`id` int(32) NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`body` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`date` datetime DEFAULT NULL,
`aid` int(32) DEFAULT NULL,
`lastmodified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=177 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
select * from announcements a where a.body like '%journėe%'
No errors, 1 row.
select * from announcements a where a.date like '%journėe%'
Illegal mix of collations for operation 'like'
show variables like '%character_set%';
But I can't figure out why it's not utf8mb4 format already - thought it was?...
select * from announcements a where CONVERT(a.date USING utf8mb4) like '%journėe%'
No errors; 0 rows;
November 13th, 2012, 10:56 AM
No a DATETIME does not have a collation.
It's like an INT doesn't have a collation either because it's a numeric value.
But when you query or insert to a DATETIME you use a string formatted in special way which makes it possible for an implicit conversion between the string in your query and the DATETIME value in the database.
And this implicit conversion apparently causes problems in your case.
IIRC there was some bug filed about this, but I don't remember which version or if it's fixed.