0

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 ?

5
  • 2
    You need to change your tables' character set to utf8mb4 and collation to utf8mb4_unicode_ci, or similar, to make them compatible with multibyte chars
    – Clive
    Dec 1, 2022 at 15:48
  • You need to have a different DB collation for Emojis to work I think. Or workaround drupal.org/project/strip_utf8mb4.
    – leymannx
    Dec 1, 2022 at 15:52
  • @Clive ibb.co/263brfF It's weird because in PHPmyAdmin, I have utf8_general_ci and utf8mb4_general_ci I don't know if I should choose utf8mb4_unicode_ci or utf8mb4_general_ci it's for a French website with special characters and accents. Can I convert the tables with PHPmyAdmin or is there something else to do?
    – Mathieu
    Dec 1, 2022 at 17:12
  • Also, I am certain "Invalid datetime format" is a PDO library bug as it is totally misleading. This is nothing to do with "datetime".
    – cilefen
    Dec 1, 2022 at 20:28
  • You can use the CKEditor Emojione module and configure the used text format to include emoji characters.
    – user109294
    Dec 2, 2022 at 5:39

1 Answer 1

2

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;
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Clive
    Dec 2, 2022 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.