Bonjour,
Je migre un site SPIP1.9.2 vers SPIP4.1.5 (en changeant de serveur) et je rencontre un problème d’encodage.
En bref, si les titres s’affichent correctement, les textes ne s’affichent pas correctement.
Si change l’encodage (iso-8859-1 → vers utf-8) sur la page /ecrire/?exec=config_lang la langue c’est le contraire.
Les erreurs de visualisation se produisent aussi bien sur le site public et sur l’interface administration.
Voici comment j’ai fait la migration :
Dump de la base
mysqldump -h localhost -u user -p base_spip > /home/utilisateur/backup_base.sql
(sans spécifier de collation)
Je charge le DUMP sur le nouveau serveur
sudo mysql;
use base_spip;
mysql> source backup_base.sql
Je charge les fichiers spip 4.1.15 sur le nouveau serveur
Je charge les répertoires IMG et squelettes de l’ancien site
Je lance le site /ecrire
La mise à jour se passe bien (je dois créer la table spip_urls) et une table de jonction pour les documents.
Site de départ
dans /ecrire/?exec=config_lang la langue est iso-8859-1
SPIP 1.9.2
PHP 5.1.6
LANG => fr_FR.UTF-8
sur certains squelettes il y a un charset=iso-8859-1"
voici la description de la table spip_articles
mysql> SHOW FULL COLUMNS FROM spip_articles ;
±---------------±-------------------------------±------------------±-----±----±--------------------±---------------±--------------------------------±--------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
±---------------±-------------------------------±------------------±-----±----±--------------------±---------------±--------------------------------±--------+
| id_article | bigint(21) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| surtitre | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| titre | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| soustitre | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| id_rubrique | bigint(21) | NULL | NO | MUL | 0 | | select,insert,update,references | |
| descriptif | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| chapo | mediumtext | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| texte | longblob | NULL | NO | | NULL | | select,insert,update,references | |
| ps | mediumtext | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| date | datetime | NULL | NO | | 0000-00-00 00:00:00 | | select,insert,update,references | |
| statut | varchar(10) | latin1_swedish_ci | NO | MUL | 0 | | select,insert,update,references | |
| id_secteur | bigint(21) | NULL | NO | MUL | 0 | | select,insert,update,references | |
| maj | timestamp | NULL | NO | | CURRENT_TIMESTAMP | | select,insert,update,references | |
| export | varchar(10) | latin1_swedish_ci | YES | | oui | | select,insert,update,references | |
| date_redac | datetime | NULL | NO | | 0000-00-00 00:00:00 | | select,insert,update,references | |
| visites | int(11) | NULL | NO | | 0 | | select,insert,update,references | |
| referers | int(11) | NULL | NO | | 0 | | select,insert,update,references | |
| popularite | double | NULL | NO | | 0 | | select,insert,update,references | |
| accepter_forum | char(3) | latin1_swedish_ci | NO | | | | select,insert,update,references | |
| date_modif | datetime | NULL | NO | MUL | 0000-00-00 00:00:00 | | select,insert,update,references | |
| lang | varchar(10) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | |
| langue_choisie | char(3) | latin1_swedish_ci | YES | | non | | select,insert,update,references | |
| id_trad | bigint(21) | NULL | NO | MUL | 0 | | select,insert,update,references | |
| nom_site | tinytext | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| url_site | varchar(255) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | |
| extra | longblob | NULL | YES | | NULL | | select,insert,update,references | |
| idx | enum(’’,‹ 1 ›,‹ non ›,‹ oui ›,‹ idx ›) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | |
| id_version | int(10) unsigned | NULL | NO | | 0 | | select,insert,update,references | |
| url_propre | varchar(255) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | |
±---------------±-------------------------------±------------------±-----±----±--------------------±---------------±--------------------------------±--------+
mysql> show variables like ‹ char% ›;
±-------------------------±---------------------------+
| Variable_name | Value |
±-------------------------±---------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
±-------------------------±---------------------------+
Quand je fais un select mysql sur un enregistrement de spip_articles, le titre est correctement visualisé avec les accentes. Par contre les caractères accentués du texte apparaissent comme des points d’interrogation entourés d’un carré
Site d’arrivée
SPIP 4.1.5
PHP 7.4.30
$_SERVER[‹ LANG ›] => en_US.UTF-8
je n’ai pas touché aux squelettes avec charset=iso-8859-1"
voici la description de la table spip_articles
mysql> SHOW FULL COLUMNS FROM spip_articles ;
±---------------±-------------------------------±-------------------±-----±----±--------------------±------------------------------±--------------------------------±--------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
±---------------±-------------------------------±-------------------±-----±----±--------------------±------------------------------±--------------------------------±--------+
| id_article | bigint(21) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| surtitre | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| titre | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| soustitre | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| id_rubrique | bigint(21) | NULL | NO | MUL | 0 | | select,insert,update,references | |
| descriptif | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| chapo | mediumtext | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| texte | longblob | NULL | NO | | NULL | | select,insert,update,references | |
| ps | mediumtext | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| date | datetime | NULL | NO | | 0000-00-00 00:00:00 | | select,insert,update,references | |
| statut | varchar(10) | latin1_swedish_ci | NO | MUL | 0 | | select,insert,update,references | |
| id_secteur | bigint(21) | NULL | NO | MUL | 0 | | select,insert,update,references | |
| maj | timestamp | NULL | NO | | current_timestamp() | on update current_timestamp() | select,insert,update,references | |
| export | varchar(10) | latin1_swedish_ci | YES | | oui | | select,insert,update,references | |
| date_redac | datetime | NULL | NO | | 0000-00-00 00:00:00 | | select,insert,update,references | |
| visites | int(11) | NULL | NO | | 0 | | select,insert,update,references | |
| referers | int(11) | NULL | NO | | 0 | | select,insert,update,references | |
| popularite | double | NULL | NO | | 0 | | select,insert,update,references | |
| accepter_forum | char(3) | latin1_swedish_ci | NO | | | | select,insert,update,references | |
| date_modif | datetime | NULL | NO | MUL | 0000-00-00 00:00:00 | | select,insert,update,references | |
| lang | varchar(10) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | |
| langue_choisie | char(3) | latin1_swedish_ci | YES | | non | | select,insert,update,references | |
| id_trad | bigint(21) | NULL | NO | MUL | 0 | | select,insert,update,references | |
| nom_site | tinytext | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | |
| url_site | text | latin1_swedish_ci | NO | MUL | ‹ › | | select,insert,update,references | |
| extra | longblob | NULL | YES | | NULL | | select,insert,update,references | |
| idx | enum(’’,‹ 1 ›,‹ non ›,‹ oui ›,‹ idx ›) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | |
| id_version | int(10) unsigned | NULL | NO | | 0 | | select,insert,update,references | |
| url_propre | varchar(255) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | |
| virtuel | text | latin1_swedish_ci | NO | | ‹ › | | select,insert,update,references | |
±---------------±-------------------------------±-------------------±-----±----±--------------------±------------------------------±--------------------------------±--------+
MariaDB [(none)]> show variables like ‹ char% ›;.
±-------------------------±---------------------------+
| Variable_name | Value |
±-------------------------±---------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
±-------------------------±---------------------------+
