Craft 2.6 incorrect string value SQL error for 4 byte UTF-8 characters when saving entries

Question

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 utf8mb4?

Answer 1

So it looks like our Craft site had an older version of the Dukt Twitter plugin installed. In a newer version it looks like the Twitter keywords have been handled better with:

return StringHelper::encodeMb4($keywords);

Which is similar to the same work done in the Craft core files for emojis.

https://github.com/craftcms/cms/issues/1627

Updating the plugin seems to have solved the issue saving on my local development environment, so not a direct answer, but a better solution than messing around with the the DB schema/collation.