[spip-dev] Spip 3.2->3.3 : codage des caractères dans les tables

Dev
1

Bonjour,

J'ai un site Spip 3.2.7 que j'avais avec peine récupéré d'un 1.9.2, et que je désire passer en 3.3dev.

Pour tests, j'ai exporté par PhpMyAdmin le 3.2, installé à côté un Spip 3.3 neuf, importé la base 3.2, et lancé /écrire.
Il m'a mis à jour les tables. OK.

Mais j'ai encore des pb de caractères accentués !
En allant voir la base d'origine, j'ai encore des tables (dont spip_articles) en latin1_swedish_ci, avec site configuré en utf-8.

Je me rappelle que j'avais galéré avec ça pour passer de 1.9.2 à 3.2, et que j'avais entre autres utilisé le plugin Grenier.

Y a-t-il aujourd'hui une procédure plus simple pour passer toutes mes tables en utf8 ?

Merci

2

Bonjour,

Je regarde du côté de
https://docs.moodle.org/3x/fr/Support_unicode_complet_pour_MySQL

et vais essayer d'adapter ce script pour Spip :

https://github.com/moodle/moodle/blob/master/admin/cli/mysql_collation.php

Sans aucune certitude...

3

Bonjour,

En glanant des infos à droite et à gauche, j'ai trouvé qu'on peut convertir une table latin1 en convertissant d'abord en binaire, puis en utf8.

J'ai lancé les lignes SQL ci-dessous dans phpMyAdmin, et le site est devenu totalement opérationnel.

Il y a juste que les champs à l'origine de type text et longtext sont devenus blob et longblob.
Mais les espaces privé et public de Spip tournent très bien à première vue...

SET NAMES utf8;

ALTER TABLE www18_articles CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_articles CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_articles CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_auteurs CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_auteurs CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_auteurs CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_breves CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_breves CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_breves CONVERT TO CHARACTER SET utf8;
ALTER TABLE www18_documents CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_documents CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_documents CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_forum CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_forum CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_forum CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_groupes_mots CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_groupes_mots CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_groupes_mots CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_messages CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_messages CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_messages CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_meta CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_meta CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_meta CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_mots CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_mots CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_mots CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_ortho_cache CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_ortho_cache CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_ortho_cache CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_ortho_dico CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_ortho_dico CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_ortho_dico CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_petitions CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_petitions CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_petitions CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_referers CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_referers CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_referers CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_referers_articles CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_referers_articles CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_referers_articles CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_rubriques CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_rubriques CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_rubriques CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_signatures CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_signatures CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_signatures CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_syndic CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_syndic CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_syndic CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_syndic_articles CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_syndic_articles CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_syndic_articles CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_tickets CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_tickets CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_tickets CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_types_documents CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_types_documents CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_types_documents CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_versions CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_versions CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_versions CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_versions_fragments CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_versions_fragments CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_versions_fragments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_visites CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_visites CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_visites CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_visites_articles CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_visites_articles CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_visites_articles CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_zones CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_zones CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_zones CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE www18_zones_liens CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE www18_zones_liens CONVERT TO CHARACTER SET binary;
ALTER TABLE www18_zones_liens CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

4

Salut Stephane,

Je vois tardivement tes messages.
Je rajoute quand même quelques adresses pour les personnes qui
chercheront sur la liste
- https://contrib.spip.net/Convertir-un-site-SPIP-3-en-utf-8-avec-le-plugin
- https://contrib.spip.net/Passez-votre-base-SPIP-en-Unicode
- https://contrib.spip.net/Comment-passer-son-site-en-utf-8
- https://blog.sodifrance.fr/encodage-et-migration-de-la-base-de-donnees-de-spip-en-utf-8/
- http://zzz.rezo.net/Reparer-le-charset-d-une-base-SPIP.html
- etc.

5

Bonjour,

YouHooou !!

- https://blog.sodifrance.fr/encodage-et-migration-de-la-base-de-donnees-de-spip-en-utf-8/

Cette page est la solution !! Merci Gildas ! :-))

Alors je suis sous OVH mutualisé, j'ai utilisé Putty sous Windows, j'ai corrigé quelques options.
Ça donne :

mysqldump --host=exampletest.mysql.db --user=exampletest --password=example --quote-names --set-charset --default-character-set=latin1 exampletest > dump1.sql

cat dump1.sql |sed -e 's/latin1/utf8/g' > dump2.sql

mysql --host=exampletest.mysql.db --user=exampletest --password=example exampletest < dump2.sql

Ça roule ! Je vois tous mes caractères accentués en clair sous phpMyAdmin.
Bascule sous Spip 3.3 nickel.

Merci merci merci ! :smiley: