test: migrator FK constraint cleanup is non-idempotent — fails on MySQL/H2/SQL Server reruns

[bpamiri]

Describe the bug

wheels.tests.specs.migrator.migratorSpec (the migrateTo :: uses specified versions table name suite) creates a foreign key constraint named fk_wheels_level but does not drop it on teardown. When the spec runs against the same database a second time (which happens inside one matrix job because the spec executes the migration up and re-executes it), the constraint creation errors:

• MySQL: Duplicate foreign key constraint name 'fk_wheels_level'
• H2: Constraint "FK_WHEELS_LEVEL" already exists; SQL statement: ALTER TABLE c_o_r_e_migrator_versions ADD CONSTRAINT fk_wheels_level FOREIGN KEY ...
• SQL Server: There is already an object named 'fk_wheels_level' in the database.
• Oracle: ORA-02264: name already used by an existing constraint (counted in ci: Oracle datasource broken across all engines — Invalid URL, missing DBMS_LOCK, constraint cleanup #2663)

Evidence

Compat-matrix run #25837380625 (2026-05-13).

• Lucee 6: 1 error each on h2, mysql, sqlserver (3 total).
• Same pattern appears in BoxLang results (Oracle path counts in ci: Oracle datasource broken across all engines — Invalid URL, missing DBMS_LOCK, constraint cleanup #2663).
• Cockroach + Postgres: pass — they tolerate IF NOT EXISTS-style behavior or the constraint name handling differs.

Expected behavior

The migrator spec should run repeatedly within the same test session without leaving constraints behind. Either:

1. Drop the constraint in the suite's afterEach / afterAll, or
2. Use a uniquely-named constraint per spec run (timestamp suffix), or
3. Use the down-migration to clean up after the up.

Root cause

Pure test-cleanup bug. The migrator spec uses a hardcoded constraint name and re-runs without teardown. Cockroach/Postgres silently tolerate the redefinition; MySQL/H2/SQL Server/Oracle correctly reject it.

Suggested fix shape

Add a teardown step that drops fk_wheels_level (and any other named constraints created in the spec) before each run, or rewrite to use a per-test-run unique name. Low effort.

Additional context

• Part of the Core test suite has high failure/error rate on BoxLang (35 failures, 125 errors, 3526 passing) #2649 decomposition (Tier 3 — test cleanup, not framework).
• This is engine-agnostic and predates BoxLang's matrix participation — it would have shown up on the Lucee 6 + MySQL row since matrix runs started, but was masked by continue-on-error: true at the workflow level.
• Worth pairing with ci: Oracle datasource broken across all engines — Invalid URL, missing DBMS_LOCK, constraint cleanup #2663's Oracle constraint cleanup since the underlying spec fix is the same.

Related: #2649, #2663

[wheels-bot]

Wheels Bot — Triage

Classified as bug.

Suspected layer

migrator — the failing test is in vendor/wheels/tests/specs/migrator/migratorSpec.cfc (the "uses specified versions table name" case at line 148, inside the "Tests that migrateTo" describe block). The constraint name fk_wheels_level originates in vendor/wheels/Migrator.cfc:467-470 when the migrator builds the versions table with a FK to wheels_levels. The reporter's classification ("pure test-cleanup bug") matches what the code shows: production migrator logic is correct; the spec just doesn't tear down what it created.

Fix sketch

Likely a single-file change in vendor/wheels/tests/specs/migrator/migratorSpec.cfc. The neighbouring "F15 Phase 1" describe block already has the right shape (beforeEach / afterEach at lines 163-182 that drop wheels_migrator_versions, c_o_r_e_migrator_versions, wheels_levels, c_o_r_e_levels with try { migration.dropTable(local.t); } catch (any e) {}). The cleanest fix is to extend or add an equivalent teardown to the "Tests that migrateTo" describe block so the c_o_r_e_migrator_versions table (which owns fk_wheels_level) is dropped after the spec runs — dropping the table drops the FK with it, which sidesteps per-DB DROP CONSTRAINT syntax differences. Also restore application.wheels.migratorTableName to its original value. The reporter's option (3) — relying on a down-migration — is less ergonomic given the spec only calls migrateTo(001) without a paired down step.

Confidence: medium

Report is clear and the fix shape is mechanical, but the migrator subsystem is involved (auto-downgrade rule) and there's a minor choice between the three suggested cleanup strategies; pairing with #2663's Oracle path also wants coordination.

<!-- wheels-bot:triage:2664 -->
<!-- wheels-bot:triage-class:bug -->
<!-- wheels-bot:triage-confidence:medium -->

[wheels-bot]

Wheels Bot — Fix on hold for human review

Triage classified this as a pure test-cleanup bug ("Likely a single-file change in vendor/wheels/tests/specs/migrator/migratorSpec.cfc") and flagged that the migrator subsystem is involved (auto-downgrade rule). The bot's TDD discipline requires every auto-PR to touch at least one file outside vendor/wheels/tests/ / tests/ / .ai/ / CHANGELOG.md / docs/ (enforced by .github/workflows/bot-tdd-gate.yml). A pure test-cleanup fix can't satisfy that rule, so this issue is being handed to a human.

Verified fix (186 pass / 0 fail / 0 error on Lucee 7 + SQLite)

In vendor/wheels/tests/specs/migrator/migratorSpec.cfc, extend the "Tests that migrateTo" describe block's beforeEach / afterEach (line 59-74) with the same cleanup pattern the neighbouring "F15 Phase 1" block already uses (line 163-182). Versions tables first, levels tables last, so the fk_wheels_level back-reference is dropped before its parent:

 			beforeEach(() => {
 				for (local.table in ["c_o_r_e_bunyips", "c_o_r_e_dropbears", "c_o_r_e_hoopsnakes", "migrations"]) {
 					migration.dropTable(local.table)
 				}
+				// #2664: drop the migrator system tables (which own fk_wheels_level)
+				// so re-runs don't collide with "duplicate constraint name" on
+				// MySQL / H2 / SQL Server / Oracle. Child tables first so the
+				// FK back-reference is gone before the parent levels table.
+				for (local.t in ["wheels_migrator_versions", "c_o_r_e_migrator_versions", "wheels_levels", "c_o_r_e_levels"]) {
+					try { migration.dropTable(local.t); } catch (any e) {}
+				}
 				deleteMigratorVersions(2);
 				$cleanSqlDirectory()
 				originalWriteMigratorSQLFiles = Duplicate(application.wheels.writeMigratorSQLFiles)
 				originalMigratorTableName = Duplicate(application.wheels.migratorTableName)
 			})

 			afterEach(() => {
 				$cleanSqlDirectory()
 				// revert to orginal values
 				application.wheels.writeMigratorSQLFiles = originalWriteMigratorSQLFiles
 				application.wheels.migratorTableName = originalMigratorTableName
+				for (local.t in ["wheels_migrator_versions", "c_o_r_e_migrator_versions", "wheels_levels", "c_o_r_e_levels"]) {
+					try { migration.dropTable(local.t); } catch (any e) {}
+				}
 			})

And a regression spec immediately after "uses specified versions table name" (line 158) — currently fails with Expected [0] but received [1], passes with the cleanup applied:

// Regression for #2664. Without per-spec cleanup the previous test
// leaves c_o_r_e_migrator_versions + the fk_wheels_level FK behind;
// matrix re-runs then collide with "duplicate constraint name" on
// MySQL / H2 / SQL Server / Oracle (engine-scoped FK namespaces).
it("drops the migrator system tables between specs (regression: ##2664)", () => {
    if (_isCockroachDB) return;
    var info = g.$dbinfo(datasource = application.wheels.dataSourceName, type = "tables", pattern = "c_o_r_e_migrator_versions")
    expect(listFindNoCase(ValueList(info.table_name), "c_o_r_e_migrator_versions")).toBe(0)
})

Why this fix shape (vs the issue's other options)

• Option 1 (drop in teardown) — chosen. Mechanical, matches the F15 Phase 1 / Phase 2 blocks already in this file, no production-code touch.
• Option 2 (unique constraint name per run) — would require vendor/wheels/Migrator.cfc:470 to accept a configurable FK name, which is a real API change to migrator behavior.
• Option 3 (down migration) — the spec only calls migrateTo(001) without a paired down step, so this would need a new fixture.

Notes for the human picking this up

• Pair with ci: Oracle datasource broken across all engines — Invalid URL, missing DBMS_LOCK, constraint cleanup #2663's Oracle constraint cleanup — same describe block, same fix shape.
• Cockroach + Postgres tolerate the redefinition today, but the cleanup also fixes them for consistency.
• The try { ... } catch (any e) {} wrapper mirrors the existing "F15 Phase 1" / "F15 Phase 2" blocks so first-run-on-fresh-DB doesn't blow up.