MySQL naar Postgres: valkuilen die we elke migratie zien

De MySQL-dump speelt door in Postgres. De app start. De smoke test gaat groen. Dan, op dinsdagavond om 23 uur, loopt de support-inbox vol omdat de zoekbalk niets teruggeeft voor namen met een 'ç' of 'ñ' erin. Je doet er twee uur over om de oorzaak te vinden, en de migratie was vier dagen eerder al klaar.

We hebben deze migratie inmiddels vaak genoeg gedaan om een eigen cheatsheet bij te houden. De lijst hieronder is wat ons echt laat struikelen bij een overstap van MySQL naar Postgres. Niets ervan is exotisch. Alles ervan heeft ons minstens één avond gekost.

ENUM's die de dump niet overleven

MySQL laat je een ENUM inline declareren in een kolomdefinitie. Postgres wil eerst dat het type bestaat, als eigen object. pg_dump en pgloader regelen dit allebei, maar zodra je een eigen migratiescript hebt verandert de inline-definitie stilletjes in een varchar zonder check constraint. Een jaar later schrijft iemand dan 'actve' in plaats van 'active' in de tabel.

De oplossing is om het type expliciet te declareren vóór de tabel:

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'refunded');

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  status      order_status NOT NULL DEFAULT 'pending',
  created_at  timestamptz  NOT NULL DEFAULT now()
);

Een waarde later toevoegen is één regel (ALTER TYPE order_status ADD VALUE 'cancelled'), maar verwijderen of herordenen is echt vervelend. Als de kolom regelmatig nieuwe statussen krijgt, sla ENUM dan helemaal over en gebruik een lookup-tabel met een foreign key. De Postgres-documentatie is eerlijk over de trade-off.

Nuldatums en de stille NULL

MySQL accepteert '0000-00-00' standaard als datum. Postgres niet, en heeft dat ook nooit gedaan. Als je bronschema is gebouwd voordat NO_ZERO_DATE standaard werd, staan er rijen met 0000-00-00 00:00:00 in created_at-kolommen, en de migratie faalt op de eerste.

De snelle fix is één update aan de MySQL-kant vóór de dump:

UPDATE users
SET created_at = NULL
WHERE created_at = '0000-00-00 00:00:00';

De langere fix is je afvragen wat die rijen eigenlijk voorstellen. Wij hebben 'onbekende aanmelddatum', 'soft-deleted gebruiker' en 'de testrij van de dev uit 2014' allemaal achter dezelfde nul zien schuilen. Behandel de nul als een vraag, niet als data.

De collation die LIKE sloopt

Deze noemt niemand in de migratiegidsen. De standaardcollation van MySQL voor utf8mb4 is hoofdletter- en accent-ongevoelig (meestal utf8mb4_0900_ai_ci). Postgres gebruikt standaard een deterministische, hoofdlettergevoelige, locale-aware collation. Het gedrag van LIKE 'jose%' verandert van de ene op de andere dag.

'José' matcht niet meer met 'jose'. 'MÜLLER' matcht niet meer met 'muller'. De applicatie geeft geen enkele fout. De zoekbalk geeft gewoon stilletjes minder terug.

Je hebt drie opties, in volgorde van hoe leuk wij ze vinden:

1. Gebruik ILIKE voor hoofdletter-ongevoelige matches en accepteer dat je accenten daarnaast nog apart moet afhandelen.
2. Gebruik unaccent aan beide kanten: WHERE unaccent(name) ILIKE unaccent('jose%'). Zet er een functionele index op unaccent(name) bij, anders wordt dit op schaal traag.
3. Maak een kolom met een niet-deterministische ICU-collation en gebruik die voor doorzoekbare tekst. De Postgres collation-docs lopen door de syntax heen, en zijn duidelijker dan vroeger.

Sla je deze stap over, dan kun je een supportticket verwachten van de eerste gebruiker met een trema in zijn naam.

tinyint(1) en de boolean-illusie

MySQL bewaart booleans als tinyint(1). De meeste ORM's verbloemen dit. pgloader wil behulpzaam zijn en zet tinyint(1) om naar boolean, wat meestal is wat je wilt. Behalve als je tinyint(1) gebruikte om een klein integer (0 tot en met 9) op te slaan, want dan heb je nu een boolean die alle waarden boven 1 kwijt is.

Audit elke tinyint(1)-kolom voordat de dump draait. Slaat hij echt 0 of 1 op, laat de conversie dan gewoon gebeuren. Slaat hij een klein bereik integers op, verander de MySQL-kolom dan eerst naar tinyint zonder breedte-hint, dan houdt de conversie hem aan de Postgres-kant als klein integer.

AUTO_INCREMENT en de off-by-one

Deze bijt iedereen precies één keer. De AUTO_INCREMENT van MySQL hoort bij de tabel. Postgres gebruikt sequences, die losse objecten zijn. pg_dump regelt de conversie wanneer je van Postgres naar Postgres gaat, maar bij een MySQL-bron is de sequence-waarde jouw verantwoordelijkheid. Migreer je de data met een eigen script en voeg je voor de test handmatig een rij toe, dan staat de sequence nog steeds op 1. Jouw handmatige insert werkt. De eerste echte gebruikersaanmelding klapt er vervolgens uit met een duplicate key.

Reset na elke datamigratie elke sequence naar de max-id in zijn tabel:

SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE(MAX(id), 1)
) FROM users;

Script dit voor elke tabel. Wij houden het als post-migratiestap in de runbook en draaien het tweemaal: één keer na de bulk load en één keer na een reconciliatie-ronde.

ON UPDATE CURRENT_TIMESTAMP

MySQL kent een kolom-attribuut genaamd ON UPDATE CURRENT_TIMESTAMP. Zet je dat op updated_at, dan updatet de kolom zichzelf bij elke rij-write. Postgres heeft dit niet. Postgres heeft triggers.

Je hebt een functie en een trigger per tabel nodig:

CREATE OR REPLACE FUNCTION touch_updated_at()
RETURNS TRIGGER AS $
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER users_touch_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

Of je zet updated_at = now() in de update-statement van de applicatie en stopt met op de database leunen. Wij kiezen standaard voor de trigger, omdat de applicatielaag het uiteindelijk altijd vergeet, vooral wanneer iemand een ruw SQL-script schrijft voor een eenmalige opschoning.

Hoofdletters, quotes en de stille hernoeming

MySQL op Linux is hoofdlettergevoelig wat tabelnamen betreft. MySQL op macOS niet. Postgres zet elke ongequote identifier in lowercase, dus Users en users zijn dezelfde tabel, tenzij je ze quote, dan weer niet.

Als je MySQL-schema tabellen heeft als User en UserSession in CamelCase, moet je tijdens de migratie één beslissing nemen. Of je hernoemt alles naar snake_case en past de applicatie aan, of je zet voor altijd elke referentie tussen dubbele quotes. Wij hernoemen altijd. De eerste keer dat je een quote vergeet in een handmatig getypte query, ben je het met ons eens.

GROUP BY en de striktere regels

Oudere MySQL voerde rustig SELECT id, name, count(*) FROM orders GROUP BY id uit en gaf een willekeurige name terug. Postgres weigert dat. Elke aggregate query moet niet-geaggregeerde kolommen óf in de GROUP BY zetten, óf in een aggregate-functie verpakken.

Als de bron-applicatie geschreven is voordat ONLY_FULL_GROUP_BY aanstond, brengt de migratie een lange staart queries naar boven die 'werkten' maar undefined waren. Grep de codebase op GROUP BY vóór de cutover en fix ze terwijl je tijd hebt, niet terwijl er gebruikers meekijken.

De pre-flight audit van vijf minuten

Voordat je aan de echte cutover begint, draai deze queries tegen de MySQL-bron. Ze duren een minuut per stuk en vertellen je wat er gaat breken:

-- Inline ENUM's die je opnieuw als type moet declareren
SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE data_type = 'enum'
  AND table_schema = DATABASE();

-- Datumkolommen die mogelijk nuldatums bevatten
SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type IN ('date','datetime','timestamp')
  AND table_schema = DATABASE();

-- tinyint(1)-kolommen om te triageren als bool of klein integer
SELECT table_name, column_name
FROM information_schema.columns
WHERE column_type = 'tinyint(1)'
  AND table_schema = DATABASE();

-- CamelCase-tabelnamen die hernoemd moeten worden
SELECT table_name
FROM information_schema.tables
WHERE table_name REGEXP '[A-Z]'
  AND table_schema = DATABASE();

-- Grootste tabellen, zodat je weet wat het dump-venster domineert
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY table_rows DESC
LIMIT 10;

Print de output. Plak hem op de muur naast de runbook. Elke rij is een beslissing die iemand moet nemen voordat het cutover-venster opengaat.

Wat wij doen aan het begin van elke migratie

Toen we onlangs een legacy-migratie deden voor een Nederlandse SaaS die afstapte van een PHP/MySQL-stack uit 2014, was de collation het probleem dat de meeste uren opslokte. Search werkte in staging omdat staging Amerikaans-Engelse namen had. De eerste week in productie liep de support-inbox vol met Duitse en Turkse gebruikers wier namen niet meer met zichzelf matchten. We hebben het opgelost door de doorzoekbare kolommen over te zetten naar een niet-deterministische ICU-collation en daarna de functionele indexes toe te voegen.

De audit van vijf minuten hierboven is het kleinste nuttige dat je vandaag kunt doen. Draai hem tegen elke MySQL-database waar je ook maar over een migratie nadenkt, en je weet binnen het uur of de cutover een weekend of een maand kost.