Repair the 14 U+FFFD-substituted compilation_track_artist values

[jakebromberg]

Problem

14 values in wxyc_schema.compilation_track_artist hold a literal U+FFFD REPLACEMENT CHARACTER where tubafrenzy's MySQL holds the real character:

Table.column	Rows
wxyc_schema.compilation_track_artist.track_title	11
wxyc_schema.compilation_track_artist.artist_name	3

Verified at the byte level, not inferred from a terminal — client_encoding and server_encoding are both UTF8, so this is stored data, not a display artifact. Found by the first end-to-end catalog-parity run against the live pair on 2026-08-13 (WXYC/discogs-etl#346 step-8a measurement).

CTA has never been swept for this corruption class. #863's table set was rotation / library / flowsheet; #2114's was library + artists. The Phase 4 script's own informational postlude reads CTA and expected 0 against the 2026-08-12 clone — the live count is not 0, so that expectation was wrong and this is the follow-up it asked for.

Scope: this is the uncovered half of a 28-value sweep

The 2026-08-13 sweep found 28 U+FFFD values catalog-wide. Half are already covered; this ticket is the other half.

Values	Covered by
library.artist_name 10 + library.album_title 1 + artists.artist_name 1 (id 656)	#2114 → PR #2121, merged 2026-08-12. Merged ≠ applied — see #2153
artists.artist_name 2 (ids 22025, 23162) + artists.alphabetical_name 1 (id 22025)	#2124
compilation_track_artist 11 + 3 = 14	this ticket

rotation and flowsheet need no sweep here — the Phase 4 script already measured them: flowsheet 0, rotation 5 (ids 10789, 13703, 16683, 21149, 21335), and all five are in Phase 3.5's deliberately-unrecovered bucket (empty curated-canonical column in audit/bs_replacement_char_phase35.csv). Do not write an acceptance criterion asserting rotation reaches 0; that bucket is intentional.

Why this is not #1996

#1996 is double-encoding in the same table — the original bytes survive, so it is mechanically reversible, and the damage is upstream in MySQL. This is substitution: the source byte is gone (ef bf bd is the UTF-8 encoding of U+FFFD itself). No Backend-side transform recovers it, and unlike #1996 the corruption is Backend-only — tubafrenzy holds the true character.

The two overlap on one table and must not fight each other. #1996's fix is overwhelmingly delete the mangled twin; this one is rewrite the substituted character. Sequencing note under Constraints.

This is frozen residue, not an active corruption source

The lossy read was fixed on 2026-04-24 by commit 1eab07f5 ("Fix Unicode garbling in MirrorSQL by setting utf8 client charset", #454) — MirrorSQL.makeSqlCommand now passes --default-character-set=utf8, so the live ETL reads the true bytes. #2114 independently confirmed the per-row (not per-column) damage pattern: La Forêt survives with the same 0xEA byte that was destroyed in La Bête.

Two consequences, both load-bearing:

• No treadmill, and no blocked-by on WXYC/wiki#89 step 3. This is the opposite of Repair 5,310 double-encoded (CP1252 mojibake) rows in compilation_track_artist #1996, which is blocked on stopping library-etl because its corruption is live upstream. Do not inherit that block by analogy.
• The repair is durable. importCompilationTracks (jobs/library-etl/job.ts:765) inserts CTA rows with .onConflictDoNothing() on cta_unique_idx. Post-ETL: Missing --default-character-set=utf8 in MirrorSQL causes Unicode garbling #454 the ETL derives the correct string, so once a row is repaired the ETL's next insert conflicts with it and no-ops.

The trap: cta_unique_idx makes a blanket UPDATE unsafe

cta_unique_idx is UNIQUE on (library_id, artist_name, track_title) (shared/database/src/schema.ts:731, and per #801 D7 it is permanent — do not touch the index). If a corrupt row's correctly-spelled twin already exists in the same compilation, UPDATE-ing the corrupt row to the true value raises a unique violation instead of repairing anything.

This is not hypothetical for this table: #1996 measured that 98.5% of its damaged CTA rows already have a correctly-encoded twin at the same track position, because the insert-only writer kept both copies whenever the strings differed. The same double-ingest shape can apply here.

So the repair is twin-aware, per row:

• twin exists → DELETE the U+FFFD row (the clean row already carries the truth)
• no twin → UPDATE in place

Also note compilation_track_artist.artist_name is free text, not an FK to artists — so neither fold_artist_name (migration 0134) nor migration 0060's cascade_library_artist_name trigger reaches these rows. There is no cascade to lean on the way PR #2121 did for library.artist_name; every CTA row must be written directly.

The corrupt side needs a live prod read — the local clone cannot see it

dev_env/seed-clone.sql contains no compilation_track_artist rows. The table appears exactly once in that file, in the TRUNCATE list at line 39; there is no COPY wxyc_schema.compilation_track_artist … FROM stdin block. The populated tables are artists, format, genre_artist_crossreference, library, and rotation.

That explains the discrepancy in the Phase 4 script's postlude, and it matters for anyone reusing that script's method: its "0 in the 2026-08-12 clone" for CTA was a false negative measured against an empty table, not evidence of a clean table. The live count is 14. Do not treat the clone as covering CTA.

Consequences for implementation:

• Corrupt side (which 14 rows) — requires a read against prod PG, or another snapshot that actually carries CTA rows. This is the one step that cannot be done from the repo.

• Ground truth (what the strings should be) — read Kattare wxycmusic.COMPILATION_TRACK_ARTIST directly. --default-character-set=utf8 is mandatory (without it the server returns latin1 and reintroduces the corruption), and it needs a MariaDB client — the Homebrew MySQL 9.x client segfaults against the 5.1 server.

  A library.db snapshot will not serve as ground truth here, contrary to an earlier revision of this ticket. Every local snapshot checked (lml-cutover-snapshots/prod-20260719, lml-cutover-snapshots/staging-20260718, library-metadata-lookup/library.db, discogs-etl/library.db) contains only library, streaming_links, and the FTS shadow tables — one flat row per release, no per-track or per-compilation-credit data at all. If a current daily build turns out to carry CTA rows, it is usable; do not assume it from these.

So the script and its test can be built and verified in full against synthetic fixtures in a throwaway schema; the 14 (row, true value) pairs are a separate capture step. Do not invent row ids or values to fill that gap — an unresolved pair is a blocker to report, not a guess to make.

Enumeration predicate:

SELECT cta.library_id, l.legacy_release_id, cta.track_position, cta.artist_name, cta.track_title
FROM wxyc_schema.compilation_track_artist cta
JOIN wxyc_schema.library l ON l.id = cta.library_id
WHERE cta.artist_name LIKE E'%�%' OR cta.track_title LIKE E'%�%'
ORDER BY l.legacy_release_id, cta.track_position;

cta.library_id is a Backend library.id; tubafrenzy keys on LIBRARY_RELEASE.ID, so every join to ground truth routes through library.legacy_release_id.

Approach

Follow the Phase 1/2, Phase 3.5, and Phase 4 shape — a hand-applied operator script under scripts/audit/, not a Drizzle migration (docs/migrations.md keeps migrations DDL-only; this is DML), plus an integration spec that executes the script's real statements against a throwaway schema.

1. Enumerate the 14 rows with their legacy_release_id and track_position.
2. Resolve each true string from ground truth. Read it; never reconstruct it — these are exactly the diacritic-bearing names where a plausible guess is wrong (Csillagrablók, Bête, µ-Ziq). Record the codepoint: PR fix(library): repair the 11 residual U+FFFD mojibake rows from #863 #2121's µ is U+00B5 MICRO SIGN, not U+03BC GREEK SMALL LETTER MU.
3. Classify each row twin / no-twin against cta_unique_idx.
4. Human-review the 14 pairs before writing the script literals.
5. Script with an audit prelude and postlude, matching bs_replacement_char_phase4.sql.
6. ANALYZE wxyc_schema.compilation_track_artist outside any transaction (#934 — omitting it after Mojibake recovery: U+FFFD-form rows in Backend PG (rotation, library, flowsheet) #863's migration regressed /flowsheet/suggest/* to 5s timeouts).

Constraints

• Capturing the 14 true strings is the only deadline-bound step. Once they are recorded in the script literals, the rest of this ticket is deadline-free and the deadline:tubafrenzy-turndown label can come off. Note the deadline is softer than it reads elsewhere: 2026-09-07 is a self-imposed target, Kattare hosting does not end on that date, and a MySQL dump capture is planned under the data-finalization umbrella. Capture the values early anyway — it costs an hour and removes the question entirely.
• Sequencing against Repair 5,310 double-encoded (CP1252 mojibake) rows in compilation_track_artist #1996. Both rewrite CTA rows. This repair is small, frozen, and unblocked; Repair 5,310 double-encoded (CP1252 mojibake) rows in compilation_track_artist #1996 is large and blocked on library-etl stopping. Land this first, and record the 14 repaired rows so Repair 5,310 double-encoded (CP1252 mojibake) rows in compilation_track_artist #1996's twin analysis re-derives after, not before.
• Do not change cta_unique_idx ([Epic] Per-track artist identity on compilation_track_artist (cross-cache-identity §3.2 step 2) #801 D7).
• Run a SELECT with the same WHERE clause before any UPDATE/DELETE, per the org data-safety convention.
• Per WXYC/wiki#90's Phase 1 freeze, the repair belongs in Backend; do not add a tubafrenzy write path.

Acceptance criteria

• The 14 rows enumerated with legacy_release_id, track_position, and column, recorded in the script's audit prelude.
• Each replacement traced to its tubafrenzy-sourced string, with the codepoint named.
• Each row classified twin (DELETE) or no-twin (UPDATE); no statement can raise a cta_unique_idx violation.
• ANALYZE wxyc_schema.compilation_track_artist, outside any transaction.
• Integration spec executes the script's real statements against a throwaway schema.
• Post-run: both CTA columns return 0 rows for the U+FFFD predicate.
• The script is executed against prod and its postlude output recorded on this issue before it closes — merging the PR is not the fix (see #2153).

Related

• WXYC/Backend-Service#2114 — the library/artists half, repaired by PR #2121
• WXYC/Backend-Service#2153 — that script is merged but not applied
• WXYC/Backend-Service#2124 — the two remaining artists rows
• WXYC/Backend-Service#1996 — double-encoded CP1252 in the same table; different mechanism, coordinate ordering
• WXYC/Backend-Service#863 — the original U+FFFD recovery, and #934 — the missing-ANALYZE regression it caused
• WXYC/Backend-Service#454 — the ETL charset bug that produced this corruption, fixed 2026-04-24
• WXYC/discogs-etl#346 — the parity run where this surfaced

---

Milestone 1 moved from 2026-08-31 to 2026-09-07 on 2026-08-28 — 24 scheduled DJs still lack a usable Backend account and the classic flowsheet UI is their only surface. Engineering readiness was not the gate. See WXYC/wiki#125.

[jakebromberg]

Rescoped from the original 28-value filing. Reconciling that sweep against existing work: 11 library + 1 artists (id 656) are #2114/PR #2121 — merged 2026-08-12 but not applied to prod, now tracked as #2153; 2 artists + 1 alphabetical_name are #2124. That leaves the 14 compilation_track_artist values, which no ticket covers — this issue.

Also corrected in the rewrite: the rotation/flowsheet sweep was already done by the Phase 4 script (flowsheet 0; rotation 5, all in Phase 3.5's deliberately-unrecovered bucket), so it is no longer an open acceptance criterion; the corruption is frozen residue from the pre-#454 charset bug fixed 2026-04-24, so this is not blocked on stopping library-etl the way #1996 is; and a blanket UPDATE is unsafe here because cta_unique_idx will reject any row whose correctly-spelled twin already exists.

[jakebromberg]

PR up: #2154

Data-driven script + paired integration spec, following the Phase 4 shape. The 14 real corrupt/correct pairs are not captured yet (no prod access, and dev_env/seed-clone.sql carries no compilation_track_artist rows), so the script ships with an empty pending-capture block and the spec's synthetic fixtures carry the full verification burden — see the PR body for the twin-aware DELETE/UPDATE mechanism and what the spec covers.

Also checked both local lml-cutover-snapshots/ library.db snapshots (prod-20260719, staging-20260718) for a usable ground-truth extract per the ticket's "optional and useful if cheap" ask: neither has per-track/compilation-credit data at all (library.db's only table is a flat per-release row), so there's nothing to commit as a supporting artifact.

Merging the PR does not repair prod. Capture + fill-in + prod run + postlude-on-this-issue is still the closing step, per the ticket's own framing.

[jakebromberg]

Capture-mode ticket filed: WXYC/discogs-etl#382 — a --capture-fffd-cta-pairs mode on scripts/catalog_parity_diff.py that pairs every corrupt Backend CTA row against tubafrenzy MySQL truth from CI (where both credentials and a working MySQL client already live), so this issue's 14 pending rows can be filled in without laptop prod access. PR in progress.