Drupal — Illegal mix of collations exception dans une View Autocomplete Entity Reference
J’ai récemment rencontré ce problème en travaillant sur le projet e-commerce de Cartographie de la Confédération Suisse — https://shop.swisstopo.admin.ch.
Context
Afin de simplifier l’utilisation de Drupal, nous avons mis en place une recherche Autocomplete des Produits.
Cette recherche permet alors de trouver des produits selon les critères suivants:
- Type de produit (carte, accessoire …)
- SKU de produit
- Nom du Produit
La recherche fonctionne à merveille, mais il semblerait qu’elle ne retourne plus aucun résultat dès que l’on recherche des produits contenant des caractères spéciaux.
En effet, la recherche Brünigpass engendrait l’erreur suivante:
Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Commerce Products Autocomplete[commerce_products_autocomplete]
: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'like': SELECT "commerce_product_field_data"."langcode" AS "commerce_product_field_data_langcode", "commerce_product__field_map_number"."field_map_number_value" AS "commerce_product__field_map_number_field_map_number_value",
"commerce_product_field_data"."product_id" AS "product_id", "commerce_product_field_data"."type" AS "commerce_product_field_data_type", "commerce_product_field_data"."title" AS "commerce_product_field_data_title"
FROM "commerce_product_field_data" "commerce_product_field_data"
LEFT JOIN "commerce_product__field_map_number" "commerce_product__field_map_number" ON commerce_product_field_data.product_id = commerce_product__field_map_number.entity_id
AND commerce_product__field_map_number.deleted = :views_join_condition_0
WHERE (("commerce_product_field_data"."type" LIKE :db_condition_placeholder_1 ESCAPE '\\')
OR ("commerce_product__field_map_number"."field_map_number_value" LIKE :db_condition_placeholder_2 ESCAPE '\\')
OR ("commerce_product_field_data"."title" LIKE :db_condition_placeholder_3 ESCAPE '\\'))
AND ("commerce_product_field_data"."default_langcode" = :db_condition_placeholder_4)
ORDER BY "commerce_product__field_map_number_field_map_number_value" ASC LIMIT 10 OFFSET 0; Array ( [:db_condition_placeholder_1] => %Brünigpass% [:db_condition_placeholder_2] => %Brünigpass% [:db_condition_placeholder_3] => %Brünigpass% [:db_condition_placeholder_4] => 1 [:views_join_condition_0] => 0 ) in main() (line 19 of /var/www/web/index.php).
Investigation
Tout premièrement, il m’a fallu comprendre qu’est-ce que c’était que cette erreur MySQL mix of collations.
L’erreur “illegal mix of collations” se produit lorsqu’une expression compare deux chaînes de caractères de collations différentes, mais de coercibilité égale et que les règles de coercibilité ne peuvent pas aider à résoudre le conflit. - Stackoverflow
L’exception MySQL m’explique donc que je tente de recherche dans au moins 2 colonnes qui n’utilisent pas la même collation (ascii_general_ci et utf8mb4_general_ci).
À première vue, le caractère ü de Brünigpasssemble engendrer un problème.
En regardant de plus près la requête SQL, nous pouvons voir que les 3 éléments suivants sont impactés par la recherche utilisateur:
- db_condition_placeholder_1:
commerce_product_field_data.type - db_condition_placeholder_2:
commerce_product__field_map_number.field_map_number_value - db_condition_placeholder_3:
commerce_product_field_data.title
Il nous faut donc demander à notre moteur de base de données quelles sont les collations utilisées pour chacun de ces champs
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='commerce_product_field_data' AND COLUMN_NAME IN ('type')UNIONSELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='commerce_product__field_map_number' AND COLUMN_NAME IN ('field_map_number_value')UNIONSELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='commerce_product_field_data' AND COLUMN_NAME IN ('title')
et le résultat fût sans équivoque:
Tada ! La colonne type de la table commerce_product_field_data utilise la collation ascii_general_ci.
Pour que notre recherche de saisie semi-automatique fonctionne, toutes les colonnes devraient avoir la même collation ou il nous faudrait modifier la requête SQL pour spécifier la collation à utiliser pour chaque condition.
Solution
Avant de commencer à modifier la requête SQL, je me suis demandé si nous avions réellement besoin de recherche dans le champ type.
En effet, nous avions ajouté ce champ afin de l’afficher dans les résultats de recherche, mais il n’était pas nécessaire de recherche par ce dernier. Nous avions simplement, sans réfléchir, configuré la View en cochant par défaut le type en tant que paramètre de la recherche.
En réalité, notre client ne se souciait pas vraiment de recherche par type (carte, accessoire ...) et nous avons donc simplement décoché le champ type des paramètres de recherches textuels.
Et voilà, vous pouvez à nouveau entrer des chaînes avec des trémas dans la saisie semi-automatique de votre projet.
Sources
Pour les plus curieux d’entre vous, voici quelques sources d’informations complémentaires ayant inspiré la création de cet article.
Patrick Fey (Août, 2017). Illegal mix of collations exception caused by Drupal 8 Autocomplete with Entity Reference View.
https://blog.werk21.de/../..//illegal-mix-collations-exception...
