I am trying to delete a shared temporary storage for a key containing an en dash or an em dash.

$keyword = '2020–2021';
$tempstore = \Drupal::service('tempstore.shared')->get('my_module_name');

It throws this exception.

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '=': DELETE FROM {key_value_expire} WHERE ("name" IN (:db_condition_placeholder_0)) AND ("collection" = :db_condition_placeholder_1); Array ( [:db_condition_placeholder_0] => 2020—2021 [:db_condition_placeholder_1] => tempstore.shared.my_module_name ) in Drupal\Core\KeyValueStore\StorageBase->delete() (line 52 of core/lib/Drupal/Core/KeyValueStore/StorageBase.php).

As far as I can tell, it's not an issue with the database, but rather with having the en dashes or em dashes in the storage key. I've tried escaping it with Html::escape() and utf8_encode(), but it didn't resolve the issue.

EDIT: I checked what my local database collation is and it returns UTF8.

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME -> FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'drupal9'; +----------------------------+------------------------+ | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | +----------------------------+------------------------+ | utf8 | utf8_general_ci | +----------------------------+------------------------+


Running the following queries fixed the issue:

ALTER TABLE `key_value_expire` CHANGE `name` `name` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'The key of the key/value pair.';

ALTER TABLE `key_value_expire` CHANGE `collection` `collection` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'A named collection of key and value pairs.';

That happens because the character set the database is using isn't utf8mb4 or the collation isn't utf8mb4_unicode_ci (or utf8mb4_general_ci). Check also which collation the single database tables and the fields they contain use, and change it when necessary.

Install Drupal / Step 3: Create a database suggests those values for the database used from Drupal.

Note: The database should be created with UTF-8 (Unicode) encoding (utf8mb4) and either the utf8mb4_unicode_ci or the utf8mb4_general_ci collation. The difference between the two collations relates to how fast they are in character comparison and sorting. utf8mb4_general_ci is slightly faster and utf8mb4_unicode_ci is more accurate for a wider range of characters. If you are unsure use utf8mb4_unicode_ci.

If you are using UTF-8 characters for values that are saved in the database, you will have issues, if the database is set to use ASCII as character code for its tables.

  • I checked and it seems like my database is using utf8_general_ci. Added more details to the original post.
    – user105050
    Jul 16 at 13:13
  • 1
    I checked the affected table and you are correct. The database itself was using UTF-8 but the table 'key_value_expire' was using ASCII for both the 'name' and 'collection' columns. Not sure how that happened.
    – user105050
    Jul 16 at 14:08
  • We usually speak of database, but effectively the collation can be changed for single tables and even for single table fields. It's not that setting the collation for the database changes the collation of the existing tables; it just sets the default collation for tables created after changing collation. I updated the answer to make that clearer.
    – apaderno
    Jul 17 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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