Summary
Three migrations landed on main claiming version 0049. The runner refuses to load the directory at all when versions collide, so npm run migrate and npm run migrate:status both fail and no migration can be applied — not the colliding three, not anything added after them. Separately, 0049_coupons_schema.sql ends with ALTER TABLE ... ADD COLUMN IF NOT EXISTS, which is MariaDB syntax that MySQL 8 rejects.
The collision
$ ls migrations | tail -4
0048_newsletter_spent_confirm_token.sql
0049_coupons_schema.sql
0049_fraud_monitoring_queue.sql
0049_product_search_fulltext.sql
backend/scripts/migrate.js (loadMigrations):
if (versions.has(version)) {
throw new MigrationError(
`Duplicate migration version ${version}: ${versions.get(version)} and ${filename}. ` +
`Renumber one of them so the order is unambiguous.`
);
}The throw happens while building the file list, before anything is compared against schema_migrations, so the failure is total:
MigrationError: Duplicate migration version 0049: 0049_coupons_schema.sql and
0049_fraud_monitoring_queue.sql. Renumber one of them so the order is unambiguous.
migrations/README.md calls this out by name — "a collision takes the whole sequence down and not just the two files involved" — but the three files merged from separate branches (#1666, #1667, #1671) that each looked fine in isolation.
The invalid ALTER
migrations/0049_coupons_schema.sql, last line:
ALTER TABLE coupons ADD COLUMN IF NOT EXISTS expires_at DATETIME NULL;
ADD COLUMN IF NOT EXISTS is a MariaDB extension. MySQL 8.0 answers:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual ...
near 'IF NOT EXISTS expires_at DATETIME NULL'
This repo runs MySQL (mysql2, utf8mb4_unicode_ci, ENGINE=InnoDB). The statement is also redundant — expires_at DATETIME NULL is already declared in the CREATE TABLE three lines above it — so once the collision is resolved this migration would still abort partway through on a fresh MySQL database, leaving coupons created but the migration unrecorded, and the next run would then hit the "never edit an applied migration" checksum rule.
Impact
- No schema change can be applied to any environment.
coupons (needed by couponService), fraud_monitoring_queue and the products full-text index are all unreachable, which means coupon validation, the fraud queue and product autocomplete all fall back to their error paths against a database that will never have the tables.
- Nothing in CI runs the migration runner, so
main is green while the schema pipeline is fully blocked.
Steps to reproduce
cd backend && npm run migrate:status
# MigrationError: Duplicate migration version 0049 ...
Expected
- One migration per version number: renumber two of the three to
0050 / 0051, keeping 0049 for whichever merged first.
0049_coupons_schema.sql contains only MySQL-valid syntax — drop the redundant MariaDB-only ALTER.- A test asserts the migrations directory has no duplicate version prefix and that no migration uses
ADD COLUMN IF NOT EXISTS, so the next collision is caught in CI rather than at deploy time.
Summary
Three migrations landed on
mainclaiming version0049. The runner refuses to load the directory at all when versions collide, sonpm run migrateandnpm run migrate:statusboth fail and no migration can be applied — not the colliding three, not anything added after them. Separately,0049_coupons_schema.sqlends withALTER TABLE ... ADD COLUMN IF NOT EXISTS, which is MariaDB syntax that MySQL 8 rejects.The collision
$ ls migrations | tail -4 0048_newsletter_spent_confirm_token.sql 0049_coupons_schema.sql 0049_fraud_monitoring_queue.sql 0049_product_search_fulltext.sqlbackend/scripts/migrate.js(loadMigrations):The throw happens while building the file list, before anything is compared against
schema_migrations, so the failure is total:migrations/README.mdcalls this out by name — "a collision takes the whole sequence down and not just the two files involved" — but the three files merged from separate branches (#1666, #1667, #1671) that each looked fine in isolation.The invalid ALTER
migrations/0049_coupons_schema.sql, last line:ADD COLUMN IF NOT EXISTSis a MariaDB extension. MySQL 8.0 answers:This repo runs MySQL (
mysql2,utf8mb4_unicode_ci,ENGINE=InnoDB). The statement is also redundant —expires_at DATETIME NULLis already declared in theCREATE TABLEthree lines above it — so once the collision is resolved this migration would still abort partway through on a fresh MySQL database, leavingcouponscreated but the migration unrecorded, and the next run would then hit the "never edit an applied migration" checksum rule.Impact
coupons(needed bycouponService),fraud_monitoring_queueand the products full-text index are all unreachable, which means coupon validation, the fraud queue and product autocomplete all fall back to their error paths against a database that will never have the tables.mainis green while the schema pipeline is fully blocked.Steps to reproduce
Expected
0050/0051, keeping0049for whichever merged first.0049_coupons_schema.sqlcontains only MySQL-valid syntax — drop the redundant MariaDB-onlyALTER.ADD COLUMN IF NOT EXISTS, so the next collision is caught in CI rather than at deploy time.