Migrating an old WordPress and handling character set, UTF8, latin1, latin1_swedish_ci

Sharing a quick one because it took some research for me to figure this one, not too long, but I don’t have to search again :D

Basically, old WordPress from years ago, were using default character set called « latin1_swedish_ci » as default collation name, latin1 in short.

When you must migrate to a newer system because you were running an old CentOs which is no longer supported, and you start to migrate to a Debian based machine, the new default is UTF8.

So you backup your WordPress, (and ideally you used a dedicated machine for the database, so as you can have more flexibility in your workloads and yet can still backup the full DB server), and you restore it on your new server (a big tar.gz of you site content that you copied with scp by example).

You’ll have to fix all the glitches linked to PHP version change, sometimes the front end won’t work, but the admin page of your WordPress should still load. Web server logs will tell you what is wrong (a theme in my case was using functions that were no longer supported). If you can’t even get to the admin page, wp command line utility should help you update the instance from command line (another topic)

Anyway, while you log in the admin section, you will have to apply updates (as you could not on the old system, stuck with an older PHP version and other libs).

Suddenly you realize that all the accents (yes, French content and more) are totally messed up ! Yes, on the new server, everything is assumed by default to be UTF8 !

So you need to figure out what was the previous encoding of your content in the database.

You have to log on your mysql / mariadb server, as root of mysql and run the following command :

SELECT * FROM information_schema.SCHEMATA;

This will show up all your databases and their actual default character set name and default collation name !

As you can see, a lot of my old sites and databases are in latin1.

Because your WordPress has been updated, the wp-config.php file doesn’t contain all the new options, and it uses the default, including the charset default. In my case UTF8 in the new version, but as you can see, my data is in latin1 encoding.

So the fix was to add in the wp-config.php the DB_CHARSET value, to match with the database :

define ('DB_CHARSET', 'latin1');

Once you did this, all your accents and special chars should display properly !

Memo for myself mainly, but maybe it will help others.

Loading

mercredi, octobre 13th, 2021 GNU - Linux, wordpress

1 Commentaire to Migrating an old WordPress and handling character set, UTF8, latin1, latin1_swedish_ci

  • Alex dit :

    Because it might be useful, here is a great resource to update your wordpress from command line :
    https://www.liquidweb.com/kb/what-are-common-commands-to-update-wordpress-using-wp-cli/

    Always keep backups of both site and database before you update (or a snapshot if it’s a VM)

    When you migrate a wordpress from an old server to a new one, it may happen that it doesn’t even load the site, but, using wp command line tool, you can actually update the core as follow from the wordpress site folder :
    wp –allow-root core version
    wp –allow-root core update (might give an error)
    wp –allow-root core download –force (download latest version)
    wp –allow-root core update (make sure latest update is applied)

    Then when you go on the site, it will offer to update the database, accept, and you should be good to go.

  • Laisser un commentaire à Alex

    Not f'd — you won't find me on Facebook
    octobre 2021
    L M M J V S D
     123
    45678910
    11121314151617
    18192021222324
    25262728293031
     

     
    Suivez moi sur twitter - follow me on twitter
     
    Follow on LinkedIn
    [FSF Associate Member]
     
    Free Software, Free Society
    VIRTUALISATION :
    Compacter une image virtualbox VDI
    Bon petit tutoriel esxi
    Marche d'appliances vmware
    Installer ESXi sur un disque IDE
    Installer ESXi 3.5 sur un disque USB
    Installer proxmox avec DRBD et migration / réplication à chaud
    Installer OSSEC avec VMware
    Information sur le VDI
    SECURITE - FIREWALL :
    Ouvrir des ports dynamiquement iptables - knockd
    Autre tres bon tuto knockd
    Docs Arp poisoning - Anglais
    Metasploit test de pénétration
    Zone H - sites piratés en temps réel
    Blog invisible things
    Tips protection sécurité wordpress
    Pfsense - distribution firewall opensource - adsl internet failover
    Iproute 2 mini how to - linux advanced routing
    ClearOS - la passerelle sécuritaire lan - wan
    HAUTE DISPONIBILITE :
    CDN - Accélération de la distribution de données
    drbd iscsi ocfs2 dm multipath tutoriel
    Load balancing LVS
    Load balancing opensource list
    HA-Proxy :
    HAproxy - http load balancer
    Simple tutoriel HAproxy
    HAproxy - debian tutoriel
    Centos - Ip failover
    Configuratoin DM-Multipath Redhat
    VMware Doubletake - continuité
    Quelques liens sur la réplication MySQL : Manuel MySQL, chapitre sur la réplication
    Manuel MySQL, Tutoriel clair sur la mise en place
    Autre tuto sur la mise en place de la réplication MySQL
    Références pour optimisation du serveur MySQL
    Utilisation de EXPLAIN mysql pour optimiser vos bases
    optimiser vos bases - requetes et index
    STOCKAGE RESEAU :
    Un outil de clonage disque en reseau
    Internet NAS 250Go 250 accès VPN
    Server ISCSI avec Ubuntu tuto
    ISCSI centos redhat tutoriel
    Gérer et étendre un LVM
    Créer sa piratebox ! trop cool
    Deaddrops, les clés USB dans les murs, aussi cool !
    OPTIMISATION WORDPRESS :
    Télécharger Xenu
    Comment utiliser Xenu
    optimisation hébergement wordpress
    Super howto wordpress (En)
    Test de charge serveur web - Load impact
    VPN - ROUTEUR - LAN:
    Zeroshell - le mini-routeur wifi tout en un
    Retroshare, votre réseau d'échange crypté!
    Openvpn sur centos redhat
    Intégrer Linux dans active directory
    Routage inter-vlan avec Linux
    Routage avec OSPF
    Network Weathermap
    TENDANCES - WEB:
    Boutons twitter
    Analyser les tendances des recherches Google
    Protocole sitemap - robots.txt
    Creer des animations CSS3
    Code php pour interagir avec twitter
    E reputation
    Jquery
    TRUCS ET ASTUCES GNU/LINUX :
    Tuxmachines.org - Actus et tips linux
    Configurer GRUB2 et grub2 ici
    Panoet - en anglais - tips & tricks
    Readylines tips and trick pertinents
    Squid Clamav - proxy antivirus
    Apprendre Unix en 10 minutes
    13 tips sur les expressions régulières
    IE Sous linux IES
    LDAP 2.4 Quickstart guide
    Tutoriel LDAP
    Installation annuaire LDAP
    Serveur Mail Postfix - Dovecot - LDAP - MDS
    Créer un linux personnalisé en ligne - custom linux
    Super site sur linux - en
    Capistrano - déploiement automatisé
    MONITORING :
    Nagios tutoriel et doc
    Nagios plugin NRPE tuto
    Nagios plugin NRPE autre tuto
    Nagios plugin NRPE officiel
    Zabbix - fonctionnalités
    Zabbix - installation
    Guide MRTGsys - grapher la charge locale
    MRTGsys - ajouter des graphs
    MRTGsys - interpréter les données
    Shinken - Monitoring
    Thruk Monitoring webinterface
    Shinken - Tutoriel
    Shinken - Référence chez Nicolargo
    AUTRES LIENS :
    RemixJobs IT jobs
    USB Multiboot
    Reset mot de passe windows
    Java python et autres tips, intéressant !
    Forum inforeseau
    Open Clipart
    Excellent comic en ligne
    Inforeseau.fr