Thread tagged as: Error, Addons, Twitter

Twitter shortcode fails when emoji in tweet

[Perch Runway 3.0.11, Perch Twitter 4.0]

I haven't done a lot of testing, but it seems that a SQL query is failing when there is an emoji in the tweet retrieved by a Perch Twitter shortcode, and the content is failing to save.

Here is one such tweet: [cms:tweet 933051700319055873].

Perch debug output is Invalid query: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x98\x8D p...' for column 'itemSearch' at row 1, so I think the conversion of the emoji to text for the search index is breaking the query.

Can anyone confirm?

Edit: I copy-pasted the full SQL query from the Perch debug output, removed the emoji and ran it directly on the db, and it succeeded, which is what leads me to believe it's the emoji causing the problem.

Simon Ansell

Simon Ansell 1 points

  • 3 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

Are you using UTF8?

I just checked and the db tables have CHARSET=utf8 set if that's what you mean, yes.

I've tried different editors (markitup, redactor) too with html="true" both on and off, all show the same behaviour.

Drew McLellan

Drew McLellan 2638 points
Perch Support

Sorry, where do editors come into this? What am I missing?

They probably don't, but I thought it worth checking in case the editor was converting the emoji to some string before save.

Can you reproduce the behaviour?

Drew McLellan

Drew McLellan 2638 points
Perch Support

If you're using CHARSET=utf8 you'll need to update your tables and connection to use CHARSET=utf8mb4 if your MySQL server has that available. Otherwise the MySQL UTF8 implementation doesn't have the available space to save high-up unicode characters.

Once you've updated your tables (and columns in the tables) you can set the connection in your Perch config using:

define('PERCH_DB_CHARSET', 'utf8mb4');

Unfortunately we can't do this by default because not all MySQL servers have utf8mb4 available.

That's great, does the trick on my dev server anyway. Thanks for your help Drew.