On a Craft site that uses the Dukt Twitter plugin I have started seeing SQL errors when saving certain entries that use Twitter data:
CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x93\xB8co...' for column 'keywords' at row 1. The SQL statement executed was: INSERT INTO `craft_searchindex` (`elementId`, `attribute`, `fieldId`, `locale`, `keywords`) VALUES (:elementId, :attribute, :fieldId, :locale, :keywords) ON DUPLICATE KEY UPDATE `keywords` = :keywords. Bound with :elementId='10', :attribute='field', :fieldId='111', :locale='en_gb', :keywords=' lots of keywords here... '
Within the keywords field is an emoji character that actually shows up when Twig throws the error. I've been researching the issue and it appears to be related to the collation of the DB tables and 4 byte UTF-8 characters, currently all the DB tables are
utf8_unicode_ci, potentially changing this to something like
utf8mb4 should work in theory.
However, what doesn't make sense is the same collation is being used on our staging environment and the error does not occur when saving and its the exact same data. Initially I thought this was due to the schema of the database being different, but I tried recreating my local DB with the same schema matching our staging and re-importing the tables, but the error still happens.
So I'm slightly confused as what the key difference is with our staging environment compared to local dev. Looking at our hosting provider, it seems the MySQL version if 5.6, where as on my local dev its 5.7.22, so this is a key area potentially.
But it does raise the overall question as what is the best course of action for emoji support on Craft 2, should all the tables be changed to