Due to MySQL encoding, some emoji may break Mautic

[spdustin]

Please DO NOT report security vulnerabilities here. Send them to security@mautic.com instead.

What type of report is this:

Q	A
Bug report?	✅
Feature request?	❌
Enhancement?	❌

Description:

MySQL's default encoding for strings is utf8, but Mautic's database, tables and columns should be using utf8mb64, along with utf8mb4_unicode_ci collation.

See How to support full Unicode in MySQL databases for details on the correct configuration. Fixing this would require a migration of the database, its tables, and any column in which emoji may be stored.

If a bug:

Q	A
Mautic version	v2.13.1
PHP version	7.0.28

Steps to reproduce:

1. Attempt to use 💩 in an e-mail subject, or use add a Twitter profile name to a user and have the Twitter plugin configured if that user has used lots of emoji (not all emoji triggers this issue)
2. Fail

Log errors:

(...SNIP...)_ SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x9B\xA1\xE2\x9E...' for column 'social_cache' at row 1" at /var/www/cust/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 115 ((..SNIP..))

[spdustin]

Running the following SQL against the Mautic database fixes the emoji issue with the Twitter plugin:

ALTER TABLE `leads`
	CONVERT TO CHARACTER SET utf8mb4 
	COLLATE utf8mb4_unicode_ci;
ALTER TABLE `leads`
	CHANGE social_cache social_cache LONGTEXT
	CHARACTER SET utf8mb4;

[npracht]

Label: Bug

[npracht]

Hello @spdustin do you think your solution could also solve #5893 ?

Maybe you can address a PR and then debate with mautic team ? :)

[spdustin]

@npracht Yes, it likely would, if applied to the emails table and the columns used to store it. Well, partially, anyway—the plain-text generator would need to know how to deal with Unicode.

The 🕒 emoji is a four-byte character, and MySQL's default charset/collection only supports three-byte emoji by default. There would need to be a Doctrine mapping update and relevant migration in a future Mautic update to convert relevant tables to the utf8mb4 character set, set the table's collation to utf8mb4_unicode_ci, and convert any columns that might store emoji to the same character set.

In the mean time, back up your database (in case this wrecks it), and try running this SQL against it:

ALTER TABLE `emails`
	CONVERT TO CHARACTER SET utf8mb4 
	COLLATE utf8mb4_unicode_ci;
ALTER TABLE `emails`
	CHANGE plain_text plain_text LONGTEXT
	CHARACTER SET utf8mb4;
ALTER TABLE `emails`
	CHANGE custom_html custom_html LONGTEXT
	CHARACTER SET utf8mb4;
ALTER TABLE `emails`
	CHANGE subject subject LONGTEXT
	CHARACTER SET utf8mb4;

See if that fixes your immediate issue in #5893

[nickwild-999]

Running the following SQL against the Mautic database fixes the emoji issue with the Twitter plugin:

ALTER TABLE `leads`
	CONVERT TO CHARACTER SET utf8mb4 
	COLLATE utf8mb4_unicode_ci;
ALTER TABLE `leads`
	CHANGE social_cache social_cache LONGTEXT
	CHARACTER SET utf8mb4;

This is not working for me either against the plug-in or entering an emoji in the twitter name. The table has changed (all now utf8mb4_unicode_ci, except social cache utf8mb4_general_ci) , but it is still erroring with the same error. I have cleared the cache. Any thoughts? Do I need to do anything else

[nickwild-999]

@spdustin Just wondered if you had any thoughts on this please?

[spdustin]

Any other text columns would have to be changed as well, including whatever column holds the Twitter user's display name.

I'm not using Mautic any longer for other reasons; sorry I can't be of more help. This post may offer some needed guidance. Good luck!

[nickwild-999]

Thanks @spdustin. Great article!

[Prosp3r]

@nickwild-999 Change the column type to BLOB. Struggled for a few hours with this before I found this: https://stackoverflow.com/questions/39463134/how-to-store-emoji-character-in-mysql-database

It worked for me.
Had to come back here to make a comment in case someone else is still facing this.

[mautibot]

This issue has been mentioned on Mautic Community Forums. There might be relevant details there:

https://forum.mautic.org/t/twitter-integration-errors/15427/5