Forum
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.
Are you using UTF8?
I just checked and the db tables have
CHARSET=utf8set 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.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?
If you're using
CHARSET=utf8you'll need to update your tables and connection to useCHARSET=utf8mb4if 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:
Unfortunately we can't do this by default because not all MySQL servers have
utf8mb4available.That's great, does the trick on my dev server anyway. Thanks for your help Drew.