When I add an emoji in full HTML with CKeditor 5, I get a PHP error

Question

I created a custom block with Full HTML text format and CKeditor 5. When I add an emoji, I have a PHP error in my Drupal 9 logs :

Drupal\Core\Entity\EntityStorageException : SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\xAB\xB6 V...' for column z33ygz_app_drupal.ymce_block_content__body.body_value at row 1: INSERT INTO "ymce_block_content__body" ("entity_id", "revision_id", "bundle", "delta", "langcode", "body_value", "body_summary", "body_format") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7); Array ( [:db_insert_placeholder_0] => 20 [:db_insert_placeholder_1] => 93 [:db_insert_placeholder_2] => basic [:db_insert_placeholder_3] => 0 [:db_insert_placeholder_4] => fr [:db_insert_placeholder_5] =>

🫶 Vous êtes producteur et vous proposez la vente à la ferme.

[:db_insert_placeholder_6] => [:db_insert_placeholder_7] => full_html ) dans Drupal\Core\Entity\Sql\SqlContentEntityStorage->save() (ligne 811 de /home/clients/588444ff5555fg4g4g4g5396/sites/www.example.com/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

Why can't I use emoji in the text box ?

Answer 1

The problem is probably that the Drupal site was setup some years ago when they still used UTF-8 with three bytes per character because many versions of MYSQL didn't support more than that.

You need to change the column in the database to a mb4 collation for multibyte strings with 4 bytes per character, like this emoji \xF0\x9F\xAB\xB6.

Whether you use utf8mb4_general_ci or utf8mb4_unicode_ci doesn't matter for storing the emoji, this is important for sorting and filtering. The first one has a better performance, the second one can sort and filter in multiple languages. If in doubt use the second one because the performance disadvantage is negligible in most cases.

The current Drupal 9 version is using utf8mb4_0900_ai_ci if the MySQL database is supporting this, which has improved language support.

Example:

mysql -u mysqluser -p
Enter password:
use my_drupal_database;
ALTER TABLE block_content__body MODIFY body_value LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE block_content_revision__body MODIFY body_value LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;