---

date: 2026-07-12 tags: [testing, sqlite, mysql, migrations, foreign-keys, false-green] status: graduated graduated_to: .claude/rules/database.md

SQLite-green hides a MySQL-invalid migration — a table rename frees an FK constraint name a later table reuses

Symptom — a spike reproduced it: two migrations (rename a table carrying an FK, then create a new table reusing the freed name) pass migrate:fresh on SQLite :memory: — the test engine — but fail on MySQL 8 with SQLSTATE[HY000]: 1826 Duplicate foreign key constraint name. The full suite is green locally while the schema is invalid on the production DB. (Prompted by the same class hitting another team in the wild.)

Root cause — tests run on SQLite :memory: (phpunit.xml) while prod is MySQL. InnoDB keeps a renamed table's FK constraint name; SQLite has no global FK-constraint namespace, so the collision is invisible. The same divergence class also hides strict-typing rejects (SQLite stores a non-numeric in an int column; MySQL errno 1366 rejects it), FK-enforcement-off-by-default, ENUM/UNSIGNED, and JSON handling — so a migrations-only check isn't enough; the whole suite must run on MySQL.

Fix — a MySQL gate that runs the suite against the production engine before merge: compose.ci.yml (throwaway MySQL, tmpfs + healthcheck) + .github/scripts/test-mysql.sh (boot → run → teardown, teardown on failure via a trap), wired as composer test:mysql (full suite) and composer test:mysql:migrations (migrate:fresh — forward DDL). SQLite :memory: stays the fast inner loop. Wiring the gate surfaced that the migration set didn't roll back cleanly on MySQL, which prompted adopting the Spatie no-down() convention: all down() methods were removed, so migrate:refresh isn't a supported path — the gate runs migrate:fresh only, which never calls down(). A data migration's up() is tested by reconstructing pre-migration state and running it directly ((require base_path($migration))->up()) — tests/Feature/SplitBirthdayMigrationTest.php.

Guard — the SQLite tests ≠ MySQL production rule in .claude/rules/database.md; run composer test:mysql on any schema/DB change. Benchmarked: MySQL runs ~2.5× slower than SQLite :memory: and the gap is architectural (socket + SQL protocol + per-worker migration), so keep the two-tier split — don't move the inner loop to MySQL. Making the MySQL run a required CI check is tracked as a follow-up.