#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    7
    Rep Power
    0

    Illegal mix of collations for operation 'like'... ?


    Hi,

    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.

    Code:
    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%';
    character_set_client	utf8
    character_set_connection	utf8
    character_set_database	utf8
    character_set_filesystem	binary
    character_set_results	utf8
    character_set_server	utf8mb4
    character_set_system	utf8
    character_sets_dir	/usr/local/mysql-5.5.14-osx10.6-x86_64/share/charsets/
    When I manually force mysql to convert the column in the statement it works without an error.

    Code:
    select * from announcements a where CONVERT(a.date USING utf8mb4) like '%journėe%'
    No errors; 0 rows;
    But I can't figure out why it's not utf8mb4 format already - thought it was?...

    Thanks.
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    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.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo