Latin1 naar utf8mb4 op oude Joomla: zonder mojibake migreren

Een Nederlands bureau waar we mee samenwerken stuurde dinsdag om 23:41 een Loom. Op het scherm stond een artikelpreview in Joomla 3.10 met als kop "Café Plein 12, Nîmes, ouvert le dimanche", weergegeven als "Café Plein 12, Nîmes, ouvert le dimanche". De database was sinds de oorspronkelijke installatie in 2011 (Joomla 1.7, MySQL 5.1) byte voor byte meegenomen. Elk teken met een accent was inmiddels een Latin-1 vuilnispaar.

De site moest naar utf8mb4 voor een upgrade naar PHP 8.2. Het twee keer fout doen in productie is de snelste manier om drie lagen mojibake te krijgen zonder rollback. Hier is de routine die wij gebruiken, stap voor stap, om een latin1-Joomla-database te converteren naar utf8mb4 zonder één teken te verliezen.

De echte bytes lezen

Joomla-tabellen die er in phpMyAdmin uitzien als latin1_swedish_ci bevatten bijna nooit Zweeds. Op een installatie uit 2011 werd de als latin1 gedeclareerde kolom meestal beschreven door een PHP-laag die UTF-8 bytes verstuurde over een connectie zonder SET NAMES. MySQL bewaarde die rauwe bytes zonder ze te converteren. De kolom heeft een verkeerd label, geen verkeerde codering.

Voor je iets aanpast: dump één rij in hex en bekijk hem:

SELECT HEX(title), title
FROM jos_content
WHERE id = 412;

Staat "café" opgeslagen als 63 61 66 C3 A9, dan zijn de bytes geldige UTF-8 met een latin1-label. Dat is het makkelijke geval. Staat het opgeslagen als 63 61 66 C3 83 C2 A9, dan heeft iemand al een half kapotte conversie gedraaid en is de data dubbel ge-encodeerd. Repareer die dubbele encoding eerst door de extra slag terug te draaien, anders maakt alles wat hierna komt de schade alleen maar erger.

De veilige export

De truc zit hem in het uitlezen van de bytes in de charset waarin ze zijn geschreven, niet in de charset waarvan MySQL denkt dat ze die hebben. mysqldump met --default-character-set=latin1 zegt tegen de server: "converteer niets, geef me alleen de rauwe bytes". Het uitvoerbestand is dan byte voor byte UTF-8, ook al beweert de dump-header nog steeds latin1.

mysqldump \
  --default-character-set=latin1 \
  --skip-set-charset \
  --single-transaction \
  --routines --triggers \
  -u root -p \
  joomla_prod > joomla_latin1.sql

--skip-set-charset haalt de regel SET NAMES latin1 uit de dump-header. --single-transaction houdt je uit de problemen op InnoDB zonder dat tabellen gelockt worden. De mysqldump-handleiding behandelt de overige flags als je per database wilt splitsen.

Herschrijven en opnieuw importeren

Herschrijf nu de charset-declaraties en collations in de dump. De inhoud van het bestand is al geldige UTF-8; alleen de metadata moet aangepast worden.

sed -i.bak \
  -e 's/CHARSET=latin1/CHARSET=utf8mb4/g' \
  -e 's/COLLATE=latin1_swedish_ci/COLLATE=utf8mb4_unicode_ci/g' \
  -e 's/ DEFAULT CHARACTER SET latin1/ DEFAULT CHARACTER SET utf8mb4/g' \
  joomla_latin1.sql

mv joomla_latin1.sql joomla_utf8mb4.sql

Maak een nieuwe lege database met de juiste defaults en importeer daarin:

CREATE DATABASE joomla_new
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

mysql \
  --default-character-set=utf8mb4 \
  -u root -p \
  joomla_new < joomla_utf8mb4.sql

Query een bekende rij met accenten in de nieuwe database. Die hoort schoon ingelezen te worden.

De byte-voor-byte logica

Je vraagt MySQL niets te converteren. Je leest rauwe bytes uit die toevallig geldige UTF-8 waren, declareert ze ook zo bij het importeren, en de labels komen eindelijk overeen met de inhoud. De uitleg van Mathias Bynens blijft de helderste referentie over waarom utf8mb4 (niet utf8) de enige juiste keuze is op MySQL.

De index-lengtevalkuil van Joomla

De herimport faalt op minstens één tabel met ERROR 1071: Specified key was too long; max key length is 767 bytes. utf8mb4 is vier bytes per teken, dus een VARCHAR(255)-index komt uit op 1020 bytes. De oplossing hangt af van je MySQL-versie.

Op MySQL 5.7+ en MariaDB 10.2+ met InnoDB wordt de limiet 3072 bytes als aan drie voorwaarden is voldaan:

• innodb_file_per_table = ON
• innodb_file_format = Barracuda (alleen 5.7; in 8.0 is de schakelaar verdwenen)
• De tabel is aangemaakt met ROW_FORMAT=DYNAMIC of COMPRESSED

De dump van een Joomla-installatie uit 2011 staat vol met ROW_FORMAT=COMPACT, of helemaal geen row format. Patch de dump voor het importeren:

sed -i \
  -e 's/ENGINE=InnoDB/ENGINE=InnoDB ROW_FORMAT=DYNAMIC/g' \
  joomla_utf8mb4.sql

Struikelt er nog een verdwaalde ALTER over een sleutel, dan zijn de Joomla-specifieke boosdoeners meestal #__session.session_id en #__update_sites.location. Beide kunnen verkort worden naar VARCHAR(191) zonder dat er iets in de runtime van Joomla stuk gaat.

Configuratie en verificatie

Wijs Joomla naar de nieuwe database in configuration.php:

public $dbtype = 'mysqli';
public $host = 'localhost';
public $user = 'joomla';
public $password = '...';
public $db = 'joomla_new';
public $dbprefix = 'jos_';

Joomla zelf zette voor versie 3.5 geen connection-charset; op oudere sites moet je expliciet een mysqli_set_charset($conn, 'utf8mb4') toevoegen in libraries/joomla/database/driver/mysqli.php, of upgraden. Moderne Joomla doet het automatisch als de server het ondersteunt.

Controleer na de overstap drie dingen:

1. Een artikel met een accent, een typografisch aanhalingsteken en een emoji gaat door de editor en wordt identiek opgeslagen.
2. SHOW CREATE TABLE jos_content; toont CHARSET=utf8mb4 op elke tabel die je hebt gemigreerd.
3. Een vergelijking van het aantal rijen tussen oude en nieuwe database klopt exact.

SELECT table_name, table_collation
FROM information_schema.tables
WHERE table_schema = 'joomla_new'
  AND table_collation NOT LIKE 'utf8mb4%';

Geeft die query rijen terug, dan zijn die tabellen overgeslagen door de sed-stap. Converteer ze ter plekke met ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; en draai de check opnieuw.

Wat dit je bespaart bij de volgende site

Het meest voorkomende moment waarop dit werk uit de hand loopt, is wanneer de conversie live wordt gedaan, één ALTER TABLE tegelijk, op de productiedatabase. MySQL transcodeert vrolijk al-UTF-8-bytes nog een tweede keer, en zo lees je "café" terug waar je "café" hebt geschreven. De export-, herschrijf- en herimport-routine hierboven doet de conversie op een kopie en geeft je de kans om te verifiëren voor je overstapt.

Toen we Pier bouwden, liepen we in dezelfde week tegen precies dit probleem aan op een Magento 1.9-audit en een Drupal 6-opruiming. Hoe we het uiteindelijk in de app hebben opgelost: door de daadwerkelijk opgeslagen bytes naast de gerenderde tekst te tonen in de MySQL editor, zodat je een verkeerd gelabelde kolom kunt herkennen voor je één ALTER schrijft, en terug kunt rollen via de version history als een conversie misgaat.

Heb je een legacy site die nog op latin1 draait, dan is het kleinste zinvolle wat je vandaag kunt doen: die HEX()-query draaien op één rij met een accent. Het bytepatroon vertelt je binnen dertig seconden of je naar het makkelijke geval kijkt, of naar de dubbel ge-encodeerde variant.