Freegle / Iznik / 25558

72%

Build Type

push

circleci

Committed by invalid-email-address

Commit Message

Fix collation crash in bulk-offer stats generation; backfill 07-06/07-07

messages_bulk_items and messages_bulk_items_interest were created on production
with a bare CREATE TABLE that inherited the MySQL 8 server default
(utf8mb4_0900_ai_ci) instead of the schema-wide utf8mb4_unicode_ci. Stats
generation joins `items i ON i.name = bi.name` and items.name is
utf8mb4_unicode_ci, so the mismatch threw SQLSTATE[HY000] 1267 "Illegal mix of
collations" and aborted the whole day's stats for all groups. Daily stats were
missing for 2026-07-06 and 2026-07-07.

- StatsGenerationService: force the join collation with
  `i.name = bi.name COLLATE utf8mb4_unicode_ci` in all four bulk-weight queries
  (buildDailyContext + regenerateWeightForRange). Works against the current
  mis-collated prod table without any DDL.
- Root-cause fix: the two create migrations now pin charset/collation
  explicitly so fresh/CI databases can never inherit the server default. Paired
  2026_07_08 migration (.php for dev/CI, idempotent .sql for manual prod apply)
  converts the existing tables to utf8mb4_unicode_ci.
- Tests: guard that both code paths emit the COLLATE clause (the runtime
  reproduction isn't possible under DatabaseTransactions, since an ALTER forces
  an implicit commit that breaks isolation).

Prod backfill for 2026-07-06 and 2026-07-07 already run (stats:generate-daily).
The .sql table conversion still needs a manual one-off apply on prod.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Ka6SXbZyznGYXyCyC9C86h

Coverage Stats

12897 of 16982 branches covered (75.95%)

Branch coverage included in aggregate %.

4 of 4 new or added lines in 1 file covered. (100.0%)

1 existing line in 1 file now uncovered.

134689 of 186167 relevant lines covered (72.35%)

38.41 hits per line