Quelle stratégie pour résoudre une erreur Illegal mix of collations (UTF8MB4_UNICODE_CI, COERCIBLE) ?

bonjour,


j'ai une erreur de type "Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE)" c-a-d un problème de mixité de codage de champs.
C'est la 1ere fois que je vois le terme COERCIBLE, ça m'intrigue ça....

sachant que ma requête est compliquée (une 30aines de UNION), y-a-t-il un moyen de cibler juste le tuple qui contient une valeur de champs non compatible ?
Ma relation est une vue de type :

select c1 as qui, c2 as evenement from table 1
union
select d1 as qui, d2 as evenement from table 2
union
select e1 as qui, e2 as evenement from table 3
..etc


à moins de rejouer au chat à la souris en enlevant une à une chaque table et tester, je voudrais bien trouver une autre solution plus directe pour résoudre ce souci !
une idée ?

quand je simplifie ma requête à :

Code:

---

SELECT * FROM `timeline` WHERE evenement like "%ric"

---

,

j'ai l'erreur, mais quand je la simplifie autrement :

Code:

---

SELECT * FROM `timeline` WHERE qui like "%ric"

---

,

je n'ai pas l'erreur.

ma conclusion c'est que l'un des champs que j'ai renommé en 'evenement" a un problème de codage. je me trompe ? mais lequel parmi celui des 30 tables en UNION dans la vue.... mystère!

Il suffit d'ajouter la clause COLLATE dans la clause SELECT comme suit :

Code:

---

1 2 3 4

select c1 collate utf8mb4_unicode_ci as qui, c2 as evenement from table 1 union select d1 collate utf8mb4_unicode_ci as qui, d2 as evenement from table 2 union ...

---

Pour info, les "champs" sont les zones des formulaires de saisie ou des états, dans les tables des bases de données relationnelles, il y a des colonnes ;)

oui mais plutôt que d'ajouter COLLATE partout sur une trentaine de requêtes mises en UNION, j'aurai aimé avoir une technique pour trouver le champs qui n'est pas nativement en utf8mb4_unicode_ci dans sa table sans avoir à vérifier 1à1 chacune d'elle.

au départ, l'union ne concernait que des données textuelles en UTF8 pur, puis ptit-à-ptit j'ai ajouté des unions de relations/tables en utf8mb4_unicode_ci et c'est là que le COLLATE est devenu nécessaire.

Vu qu'il y a déjà une ligne SELECT par table, ce n'est pas compliqué d'ajouter la clause COLLATE.
Sinon, il suffit d'interroger le catalogue relationnel : dans la table INFORMATION_SCHEMA.COLUMNS la collation de chaque colonne est précisée, à comparer avec la collation de la database ou du serveur ;)

Salut à tous.

Citation:

---

Envoyé par Escartefigue

Vu qu'il y a déjà une ligne SELECT par table, ce n'est pas compliqué d'ajouter la clause COLLATE.

---

Ce n'est pas la bonne façon de travailler car cela va alourdir la requête inutilement.

Citation:

---

Envoyé par Escartefigue

Sinon, il suffit d'interroger le catalogue relationnel : dans la table INFORMATION_SCHEMA.COLUMNS la collation de chaque colonne est précisée, à comparer avec la collation de la database ou du serveur

---

On peut vérifier que toutes les colonnes sont dans la même collation :

Code:

---

1 2 3 4 5 6 7 8 9

select table_schema, table_name, column_name, data_type, character_set_name, collation_name from columns where table_schema = 'base' and data_type in ('varchar');

---

Je me suis retrouvé dans un cas similaire, où bien sûr, vérifier la collation de toutes les colonnes n'était pas suffisant.
J'ai eu besoin d'ajouter ce paramètre afin de supprimer ce "Illegal mix of collations" :

Code:

---

set session collation_connection = "latin1_general_ci";

---

Cordialement.
Artemus24.
@+

merci escartefigue & Artemus24 pour vos compléments.


n'y-a-t-il pas un moyen en amont pour limiter le nombre de charset au moment de la creation de la database ? ou interdire la mixité ?

Quand on créé une base avec par exemple "CREATE DATABASE mydatabase CHARACTER SET utf8mb4_unicode_ci " , c'est dommage qu'il n'y ait pas un attribut "UNIQUE" ou "ONLY" pour interdire par la suite dans chaque table d'avoir un charset différent que celui initié à la base...

Citation:

---

Envoyé par clavier12AZQSWX

n'y-a-t-il pas un moyen en amont pour limiter le nombre de charset au moment de la creation de la database ? ou interdire la mixité ?

---

Si tu utilises un et un seul charset de bout en bout lors de la création de la base de données et des tables, oui, tu peux le faire.
En ce qui me concerne, j'utilise "latin1". Tu peux le faire en mettant à la place "utf8mb4".
Pour la création de la base de données :

Code:

---

1 2 3

CREATE DATABASE IF NOT EXISTS `base` DEFAULT CHARACTER SET `utf8mb4` DEFAULT COLLATE `utf8mb4_general_ci`;

---

Et pour la création d'une table :

Code:

---

1 2 3 4 5 6

create table `test` ( `id` integer unsigned not null auto_increment primary key, `chaine` varchar(255) null ) ENGINE=InnoDB DEFAULT CHARSET=`utf8mb4` COLLATE=`utf8mb4_general_ci` ROW_FORMAT=COMPRESSED;

---

Est-ce suffiant ? NON car il y a l'affichage de l'utf8 dans un batch windows :

Code:

---

chcp 65001 > nul

---

mais tu dois aussi le déclarer dans le fichier my.ini :

Code:

---

1 2 3 4 5 6 7 8 9

[mysqld]   # --------------- # # Charset # # --------------- #   character-set-server = utf8mb4 collation-server = utf8mb4_general_ci character-set-filesystem = utf8mb4

---

D'où vient mon problème de mixité alors que j'ai mis partout latin1 au lieu de utf8mb4 ?
Je n'ai pas pu changer le charset du coeur de mysql que l'on visualise dans les paramètres par :

Code:

---

character_set_system utf8mb3

---

A priori, cela ne pose aucun problème car utf8mb3 est un sous-ensemble de utf8mb4, mais pas si tu utilises latin1.
Il me semble que c'est un bug dans les anciennes versions de MySql. Depuis quelques temps, je ne le rencontre plus.

le fait de paramétrer les choses comme indiqué précédemment n'empêche pas une personne de créer une table dans un charset/collation différent , n'est-ce pas ?
ça ne restraint pas non plus les listes déroulantes de choix dans les outils clients qui s'y connectent, non ? Donc une personne maladroite pourra créer quand-même un champ ou une table de charset différent ?

Oui, tu as tout à fait raison, cela n'empêche pas la multiplicité des charset.

Le plus souvent, on n'utilise qu'un seul jeu de caractères (CHARSET) et une seule collation.
En ce cas, il est préférable de définir les éléments au niveau du serveur ou de la BDD plutôt que de les définir dans chaque colonne de chaque table.

Mais, si on stocke des données issues d'un système externe, on peut avoir besoin d'un autre jeu de caractères et d'une autre collation.
En ce cas, on peut avoir besoin de coder la clause COLLATE dans le SELECT comme proposé plus haut.

Citation:

---

Envoyé par clavier12AZQSWX

merci escartefigue & Artemus24 pour vos compléments.


n'y-a-t-il pas un moyen en amont pour limiter le nombre de charset au moment de la creation de la database ? ou interdire la mixité ?

Quand on créé une base avec par exemple "CREATE DATABASE mydatabase CHARACTER SET utf8mb4_unicode_ci " , c'est dommage qu'il n'y ait pas un attribut "UNIQUE" ou "ONLY" pour interdire par la suite dans chaque table d'avoir un charset différent que celui initié à la base...

---

Que ferez vous alors si l'on vous demande de créer une table d'utilisateurs avec LOGIN et PASSWORD ???
Sachant que pour un LOGIN il vaudrait mieux que ce soit CI et AI et pour un password CS et AS ???????????

A +

Citation:

---

Que ferez vous alors si l'on vous demande de créer une table d'utilisateurs avec LOGIN et PASSWORD ???
Sachant que pour un LOGIN il vaudrait mieux que ce soit CI et AI et pour un password CS et AS ???????????

A +

---

merci pour la remarque épineuse, du coup j'ai vérifé.
en fait ça ne pose pas de souci car le mdp est crypté md5. j'ai pas étudié le décryptage, mais je pense que la valeur hex 'A' est équivalent à celle de 'a' , non ? donc une valeur clé a145874 = A145784 ?

Absolument pas, d'ailleurs vous pouvez le tester ICI

Citation:

---

Envoyé par escartefigue

Absolument pas, d'ailleurs vous pouvez le tester ICI

---

non je parle de la valeur md5, elle est insensible à la casse :

A5 en md5 s'écrit (et se stocke) dans 2 versions :


c6f2f93133905f75da4b02ccc19ab66a
et
C6F2F93133905F75DA4B02CCC19AB66A

sont valides par la saisie d'un mdp 'A5'

La chaîne de caractères correspondant au mot de passe est insensible à la casse pour la simple raison que c'est de l'hexadécimal.

Citation:

---

Envoyé par clavier12AZQSWX

non je parle de la valeur md5, elle est insensible à la casse :

A5 en md5 s'écrit (et se stocke) dans 2 versions :


c6f2f93133905f75da4b02ccc19ab66a
et
C6F2F93133905F75DA4B02CCC19AB66A

sont valides par la saisie d'un mdp 'A5'

---

Commencez à apprendre les bases du SQL avant de dires des énormités !!!!! Un nombre est-il sensible à la casse ? Un booléen est-il sensible à la casse ? Une chaine binaire, quelle soit en hexadecimal en octal ou en binaire est-elle sensible à la casse ?

Je trouve hallucinant ces manques de savoir de base qui polluent les forums.....

A +

Citation:

---

Envoyé par SQLpro

Commencez à apprendre les bases du SQL avant de dires des énormités !!!!! Un nombre est-il sensible à la casse ? Un booléen est-il sensible à la casse ? Une chaine binaire, quelle soit en hexadecimal en octal ou en binaire est-elle sensible à la casse ?

Je trouve hallucinant ces manques de savoir de base qui polluent les forums.....

A +

---

Merci de votre considération.
Avant de répondre par mon exemple, j'ai bien entendu tester le reverse md5 de mon exemple, et pourtant "c6f2f93133905f75da4b02ccc19ab66a" et "C6F2F93133905F75DA4B02CCC19AB66A" valident toutes deux la valeur "A5" .
Je viens de faire sur 3 sites différents. bien-sûr je ne peux pas savoir derrière si le langage serveur fait un uppercase/lowercase de la valeur md5 saisie , donc effectivement ma conclusion est hâtive.
Merci pour votre recadrement.