Breekt CONVERT TO CHARACTER SET mijn bestaande indexen?

Dat kan, als de nieuwe charset een kolom over de InnoDB-keylengtelimiet duwt. Draai het eerst op een kopie en controleer dat innodb_large_prefix aanstaat en de tabel ROW_FORMAT=DYNAMIC is.

Is utf8mb4_unicode_520_ci of utf8mb4_0900_ai_ci de veiligere default voor WordPress?

Blijf op utf8mb4_unicode_520_ci voor elke WP ouder dan 5.6 of elke site die nog op MySQL 5.7 draait. WP 5.6+ op MySQL 8 kan met 0900_ai_ci overweg, maar overal omschakelen is een migratie op zich.

Hoe forceer ik een collation in één query zonder de tabel te wijzigen?

Plak COLLATE utf8mb4_unicode_520_ci achter de kolomreferentie in de WHERE of ON clause. Handig als hotfix terwijl je de echte schemawijziging plant.

MySQL collation-mismatches: een veldgids voor lege resultaten

Het ticket komt binnen om 16:02. Een kleine webshop op WooCommerce, PHP 7.4 (ja, nog steeds). "Zoeken op Café Noir in de storefront geeft niets terug. Het product staat er gewoon." De ontwikkelaar gaat via SSH binnen, draait SELECT * FROM wp_posts WHERE post_title = 'Café Noir' AND post_status = 'publish', krijgt één rij. Schakelt over op de zoekopdracht die het theme aan de voorkant genereert, krijgt nul rijen. Zelfde database. Zelfde kolom. Zelfde string.

Zo ziet een MySQL collation-mismatch eruit in het wild. De data is intact. Het schema is intact. Het query plan klopt. De twee strings die vergeleken worden zijn het simpelweg oneens over wat "gelijk" betekent, omdat ze in verschillende collations leven en MySQL stilletjes één kant aanpast tot de vergelijking faalt, of slaagt op een manier die niemand verwacht had.

Wat collation eigenlijk bepaalt

Een character set bepaalt welke bytes geldig zijn. Een collation bepaalt of twee geldige bytereeksen dezelfde string zijn. utf8mb4_unicode_ci behandelt 'café' en 'cafe' als verschillend, omdat het accent behouden blijft. utf8mb4_unicode_520_ci met de _ai_ variant ziet ze als identiek, omdat accenten worden weggelaten in de vergelijking. utf8mb4_general_ci sorteert karakters op codepoint en gaat de mist in bij een handvol edge cases (Duitse ß, Poolse ł, alles rond de Turkse dotless-i). De MySQL 8 standaard utf8mb4_0900_ai_ci is alweer een andere regelset, met een eigen sorteervolgorde en eigen incompatibiliteiten.

Niets hiervan maakt uit, totdat twee kolommen met verschillende collations elkaar tegenkomen in dezelfde expressie. Dan moet MySQL aanpassen. Als de coercibility-waarden niet netjes overeenkomen, gooit MySQL Illegal mix of collations. Komen ze wel overeen, dan draait de vergelijking en krijg je een antwoord. Misschien niet het antwoord dat je wilde.

De scène van 16:00, ontleed

De WooCommerce-zoekopdracht die het theme genereerde joinde wp_posts met een eigen wc_products_search_index tabel die de vorige ontwikkelaar had toegevoegd. Op het eerste gezicht zagen de twee tabellen er identiek uit:

SHOW CREATE TABLE wp_posts;
-- ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

SHOW CREATE TABLE wc_products_search_index;
-- ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

De ene was in 2018 aangemaakt door de WordPress-installer (toen utf8mb4_unicode_520_ci de WP-standaard was, volgens de utf8mb4 upgrade notes). De andere kwam in 2022 uit een plugin die utf8mb4_unicode_ci hardcoded had staan. De JOIN op post_title = search_term vergeleek twee strings die de database in verschillende alfabetten plaatste. Voor pure ASCII paste MySQL zich aan tot het juiste antwoord. Voor 'Café' met de e-accent gaf MySQL het op en kwam er voor die ene rij niets terug.

Elke mismatch vinden met één query

Voor je iets gaat oplossen, eerst het terrein verkennen. Dit is de query die ik als eerste draai op elke database die ik niet ken:

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  COLLATION_NAME,
  CHARACTER_SET_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLLATION_NAME IS NOT NULL
ORDER BY COLLATION_NAME, TABLE_NAME;

Meestal zie je een van drie patronen:

Schoon. Eén collation voor alle tekstkolommen. Zeldzaam op alles wat ouder is dan twee jaar.
Tweelagig. De CMS-kerntabellen op één collation, een paar plugin- of custom-tabellen op een andere. Het normale geval.
Archeologisch. Drie of vier collations, inclusief een latin1_swedish_ci kolom waarvan niemand zich herinnert dat hij is aangemaakt. Meestal een teken dat de database minstens één migratie heeft overleefd.

Voor het zicht op tabelniveau wissel je COLUMNS voor TABLES en kijk je naar TABLE_COLLATION. Let op: de collation op tabelniveau is alleen de standaard voor nieuwe kolommen. De waarheid zit op kolomniveau.

Converteren zonder dataverlies

De veilige route is één tabel per keer, met een back-up aan het begin en een verificatiequery aan het eind. De naïeve ALTER ziet er zo uit:

ALTER TABLE wc_products_search_index
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_520_ci;

CONVERT TO herschrijft elke tekstkolom ter plekke en hercodeert de data. Op een productindex van 50.000 rijen kost dat een paar seconden. Op een wp_postmeta van 12 miljoen rijen op de bak van een klant duurt het zo lang dat je het binnen een onderhoudsvenster moet doen, met een binlog-back-up paraat.

Waarschuwing. CONVERT TO CHARACTER SET verandert stilletjes kolomtypes wanneer een index te lang is voor de nieuwe charset. Een VARCHAR(255) geïndexeerd onder utf8 (max 765 bytes) past niet onder utf8mb4 (max 1020 bytes) bij de oude 767-byte InnoDB-keylimiet. Controleer innodb_large_prefix en ROW_FORMAT=DYNAMIC op elke tabel voor je converteert, anders krijg je cryptische key-length errors en een half geconverteerde tabel.

De stille fouten, gerangschikt op vervelendheid

Errors zijn makkelijk. Collation-bugs overleven in productie juist omdat sommige er nooit een gooien.

Indexen worden koud

Een WHERE indexed_column = ? vergelijking waarbij de parameter met een andere collation binnenkomt, forceert een impliciete conversie op de kolomkant. De index is niet meer bruikbaar. De query gaat van 2ms naar 800ms, en het enige signaal is je APM-tool die mompelt over een trage query die ooit snel was. Draai EXPLAIN op de boosdoener en kijk naar Using where zonder de index in key.

UNIQUE constraints accepteren duplicaten

Als je unique index op een kolom met _ai_ci staat (accent-insensitive, case-insensitive) en je applicatiecode behandelt 'CAFÉ' en 'cafe' als verschillende gebruikers, doet de database dat ook bij insert. Totdat iemand de collation wijzigt, waarna een van de twee rijen bij de volgende migratie een duplicate-key error wordt.

JOINs leveren spookrijen op

Een LEFT JOIN tussen twee tabellen op een string-key, waarbij de collations precies genoeg verschillen, kan rijen teruggeven waarbij de rechterkant NULL is terwijl er wel degelijk een match bestaat. De snelle fix is de collation forceren in de ON clause:

LEFT JOIN wc_products_search_index s
  ON p.post_title COLLATE utf8mb4_unicode_520_ci = s.search_term

Dit is dragende ducttape. De echte fix is het schema convergeren.

Het kleinste wat je vandaag kan doen

Open de database van de site waar je het laatst ruzie mee had. Draai de information_schema.COLUMNS query van hierboven. Tel hoeveel verschillende collations terugkomen. Als het er meer dan één zijn, heb je een kandidaat voor de volgende keer dat een zoekopdracht niets oplevert om redenen die nergens op slaan.

Toen we de MySQL editor in Pier bouwden, kwamen we precies dit tegen op de legacy site van een Nederlands bureau: een zoekopdracht die werkte op staging maar niet in productie, terug te leiden tot één plugin die utf8mb4_general_ci hardcoded had op één enkele kolom. We hebben het uiteindelijk zo opgelost dat de collation zichtbaar is op elke kolom in de schema-browser, en dat elke ALTER landt in de version history zodat de rollback één klik is wanneer de prefix-length check je alsnog te pakken krijgt.