MySQL naar Postgres: 14 valkuilen die pgloader stil oversloeg

Vrijdag, 16:40, Eindhoven. De samenvatting van pgloader rolde groen voorbij: 240 tabellen, 31 miljoen rijen, elf minuten, nul fatale fouten. De nieuwe RDS Postgres-instance was warm. Smoketests groen. Laptop dicht.

Maandag, 08:12. Een Teams-bericht van de controller bij de Brabantse industriële leverancier die we net hadden omgezet. De facturatiemodule toont nul openstaande facturen. Ze ziet ze gewoon in pgAdmin staan. De PHP weigert ze te vinden.

Wat volgt is het spiekbriefje dat we zelf hadden willen hebben. Veertien plekken waar de output van pgloader schoon leek, maar het systeem van begin tot eind dat niet was. Gegroepeerd naar waar de stilte zit: eerst de valkuilen die het dichtst bij pgloaders eigen werk liggen, daarna die op het applicatieniveau waar MySQL simpelweg vergevingsgezinder was.

Een opmerking vooraf. pgloader is uitstekend in het mechanische werk: schemavertaling, datakopie, sequence-creatie. Het is geen vertaler van de applicatielaag, en pretendeert dat ook niet te zijn. Het meeste hieronder leeft in de kloof tussen "de data staat in Postgres" en "de PHP-applicatie leest correct uit Postgres". Met dat uit de weg:

Schema- en typevertalingen die groen oogden

Het eerste cluster ligt het dichtst bij pgloaders eigenlijke werk. De vertalingen slagen en de data komt aan. Het vertaalde artefact is vervolgens fout op een manier die smoketests niet oppikken, omdat de applicatie precies de kolommen leest die ze verwacht te lezen. Dit zijn er volgens ons veertien waarvan pgloader er een aantal het hardst had moeten markeren en het minst markeerde.

1. Nuldata's stil op NULL gezet

   MySQL met een soepele sql_mode sloeg vrolijk '0000-00-00 00:00:00' op in NOT NULL DATETIME-kolommen. Er waren 17.400 van zulke rijen, voornamelijk uit een importscript uit 2017 dat nooit een due_date zette. pgloader verving ze bij het kopiëren door NULL en liet onderweg stilletjes de NOT NULL vallen. De query voor "openstaande facturen" deed ORDER BY due_date ASC, en Postgres sorteert NULLs standaard achteraan. De facturen waren niet weg. Ze stonden onder de vouw van een interne lijst waar niemand naar beneden scrollde. Oplossing: ORDER BY due_date ASC NULLS FIRST, plus een audit van elke kolom waar pgloader een nuldatum op NULL had gezet.

2. TINYINT(1) omgezet naar BOOLEAN, maar net niet helemaal

   De regel cast type tinyint when (= 1 precision) to boolean van pgloader pakt de meeste tinyint(1)-kolommen. Hij miste er twee waar de oorspronkelijke DDL met de hand was aangepast naar gewone tinyint zonder precisie. Bestaande PHP schreef WHERE active = 1 en bleef werken tegen de smallint-kolommen. Nieuwe code die we bovenop het gemigreerde schema schreven gebruikte WHERE active IS TRUE, wat tegen die twee een fout gaf. We hebben een halve dag besteed aan iets dat op een intermitterende driverbug leek.

3. AUTO_INCREMENT-sequence niet voorbij MAX(id) gezet

   De klassieker. pgloader vult de sequence vanuit de auto_increment-teller van MySQL, wat betrouwbaar is tot iemand rijen heeft bijgevuld met expliciet hoge id's en de teller is achtergebleven. De eerste INSERT na cutover botst op de primary key.

   SELECT setval(
     pg_get_serial_sequence('invoice', 'id'),
     (SELECT MAX(id) FROM invoice)
   );

   Draai dat voor elke tabel met een serial-kolom voordat je de app ook maar één rij laat schrijven.

4. ON UPDATE CURRENT_TIMESTAMP geruisloos verdwenen

   De MySQL-constructie updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP wordt updated_at timestamp DEFAULT now() in de DDL die pgloader produceert. De ON UPDATE-clausule heeft geen equivalent op kolomniveau in Postgres. Hij verdwijnt gewoon. Zes dagen later vroeg de sales lead waarom de kolom "laatst aangeraakt" op de klantlijst niet meer opliep. Het antwoord was: er was niets meer dat hem ophoogde. We hebben een generieke BEFORE UPDATE-trigger toegevoegd die updated_at bijwerkt, toegepast over 41 tabellen.

5. utf8mb3-bytesequenties die Postgres-clients bij het lezen weigeren

   De oudste tabellen waren als utf8 aangemerkt op een MySQL-server van vóór utf8mb4 de standaard werd. Een handvol rijen had slimme aanhalingstekens die in 2014 uit Word waren geplakt en zelfs onder de soepele regels van MySQL al twijfelachtig waren. pgloader kopieerde ze gewoon mee. Postgres bewaarde ze probleemloos op schijf. Eén clientbibliotheek (de audit-log SDK, in Go) wees ze bij een SELECT af omdat haar UTF8-decoder strenger was dan die van PDO. Met Postgres zelf was niets aan de hand. De SDK kon er alleen niet meer mee praten.

6. UNSIGNED INT verbreed naar BIGINT, daarna door de applicatie afgekapt

   Bereik-behoudend en correct: pgloader mapt INT UNSIGNED naar BIGINT. Onze PHP deed (int) $row['quantity'] op waardes die, zodra ze legitiem boven de 2^31 mochten komen, dat ook deden. Een ETL-feed naar een Sage X3-instance liep stil over. Niets in de output van pgloader wees erop dat je de consumers moest controleren.

7. ENUMs worden CHECK-constraints met automatisch gegenereerde namen

   pgloader vertaalt MySQL-ENUM naar ofwel een eigen Postgres-TYPE ofwel een CHECK-constraint, afhankelijk van een flag. De naam die voor de constraint gekozen wordt, sluit niet aan op de naming convention die je migratietool verwacht. Een half jaar later wilden we een waarde aan status toevoegen en kon onze Phinx-migratie de constraint niet vinden om te droppen. Hernoem ze na de migratie als je het schema later nog wilt laten meebewegen. Dat half uur hernoemen scheelt je later een frustrerende middag wanneer het schema moet schuiven.

Applicatiesemantiek die MySQL stil vergaf

Voorbij dit punt heeft pgloader niets meer te markeren. De data heeft de juiste vorm. Wat fout is, is dat de applicatie geschreven is in het MySQL-dialect van SQL: lossere typing, eigenwijze stringbehandeling, en shortcuts die Postgres niet biedt. Deze breken op runtime, in de PHP, vaak weken na cutover wanneer een codepad dat nog niet was gebruikt eindelijk geraakt wordt.

8. Gereserveerde woorden als identifiers die in MySQL prima werkten

   Tabellen die user, order of group heten. pgloader quote ze in het schema, dus de relaties bestaan. De DAO in PHP quote ze niet, want MySQL dwingt dat niet af. SELECT * FROM user WHERE id = ? geeft in Postgres een foutmelding: relation "user" does not exist. Een grep over de hele codebase vond 312 van zulke queries. Een paar zaten in template-strings die op runtime aan elkaar geknoopt werden, en die zag de grep niet.

9. Impliciete typeconversie in WHERE-clausules

   MySQL evalueert WHERE invoice_number = 12345 tegen een VARCHAR-kolom door het integer naar een string te casten. Postgres gooit operator does not exist: character varying = integer. Dit is puur een issue op de applicatielaag. Voor pgloader is er niets te markeren. Zoek de codebase af op elke WHERE die een literal van het ene type vergelijkt met een kolom van het andere, en cast de literal.

10. GROUP BY zonder elke niet-geaggregeerde kolom

    MySQL met de standaard sql_mode staat SELECT a, b, COUNT(*) FROM t GROUP BY a toe. Postgres vereist b in de GROUP BY of in een aggregaatfunctie. Het interne salesdashboard had acht queries die dit deden. Vier ervan faalden alleen onder bepaalde WHERE-filters door de manier waarop MySQL een willekeurige rij voor b had gekozen. De cijfers klopten al jaren niet. Postgres bracht het aan het licht.

11. CONCAT geeft NULL terug als één argument NULL is

    De MySQL-uitdrukking CONCAT('Hello, ', NULL) geeft onder sommige modes een lege string. Postgres geeft NULL. De mailmerge voor facturen gebruikte CONCAT(first_name, ' ', last_name) voor de aanhef. 380 historische contacten hadden geen first_name. Een week lang kregen die klanten "Beste ," boven hun betalingsherinnering. Stap over op de kleine-letter Postgres-functie concat(), die NULLs negeert, of wikkel elk argument in COALESCE.

12. ON DUPLICATE KEY UPDATE heeft geen directe tegenhanger

    De PHP gebruikte het op veertien plekken, de meeste voor de dagelijkse voorraadsync van producten. Het Postgres-equivalent is INSERT ... ON CONFLICT (column) DO UPDATE SET ..., gedocumenteerd op postgresql.org/docs. Je moet expliciet het conflicttarget benoemen, wat betekent dat je met de hand een unique constraint of index kiest. Dat kan pgloader niet voor je raden. Port elk geval één voor één en schrijf voor elk een test.

13. Backticks versus dubbele aanhalingstekens, en de hoofdletterval

    Het MySQL-fragment SELECT `order`.`status` FROM `order` wordt in Postgres SELECT "order"."status" FROM "order". Mechanisch een sed-actie. De valkuil is dat gequote identifiers in Postgres hoofdlettergevoelig zijn: "Order" en "order" zijn verschillende relaties. Mengt je codebase Order en order over verschillende bestanden, kies dan één vorm, normaliseer het schema, en draai dan pas de sed.

14. FULLTEXT-indexen stil gedropt

    pgloader vertaalt geen FULLTEXT-indexen of MATCH () AGAINST ()-queries, en zou dat eigenlijk ook niet moeten proberen. De productzoekfunctie op het salesportaal gaf twee dagen lang nul resultaten voordat iemand in het magazijn er terloops iets over zei. De oplossing in Postgres is een gegenereerde tsvector-kolom met een GIN-index, en de queries herschreven naar plainto_tsquery of websearch_to_tsquery. Full-text search in Postgres is prima, als je het eenmaal staan hebt. Het is niet gratis op de dag van cutover.

De SQL-audit vóór pgloader

Voordat pgloader überhaupt draait, doe je een SQL-grep. Zoek in de applicatiecode op: tinyint, boolean, CONCAT, IFNULL, ON DUPLICATE, INSERT IGNORE, FULLTEXT, MATCH (, backtick-identifiers, elk gereserveerd woord, en elke WHERE die twee verschillende typen vergelijkt. Die grep, niet de output van pgloader, is je migratieplan. Twee dagen vooraf goed besteed scheelt je later een week aan maandagochtend-Teams-berichten.

Toen we de cutover bij de Brabantse leverancier draaiden (een custom PHP-ERP van 240 tabellen die zestien jaar lang was gegroeid), deed niet het schema het meeste pijn. Het was de aanname dat een groene pgloader-samenvatting betekende dat het systeem ook correct was. Ons werk aan legacy-migraties opent nu elke cutover met een SQL-audit van twee weken voordat pgloader één DDL-bestand te zien krijgt.

Audit van vijf minuten die je vandaag op je eigen applicatierepo kunt draaien: grep -RIn --include='*.php' -E 'FULLTEXT|MATCH \(|ON DUPLICATE KEY|INSERT IGNORE' .. Het aantal hits is de omvang van het werk dat pgloader niet voor je gaat doen.