## Production incident
HTS in `prod-ltx1` saturated and began refusing TCP connections at
~11:35 PDT on 2026-09-18, breaking Airflow partition sensors with
`Connection refused ... :4768`.
`user_table_row` has ~513,000 rows and a functional index:
```
idx_user_table_upper_db_table ON (upper(database_id), upper(table_id))
```
A functional index matches only the **exact** expression, so handwritten
`lower(...)` cannot use it. `getUserTable` runs at roughly 6,000 QPS; a
full scan per request exhausted MySQL connections and HTS worker
threads, and pods stopped accepting connections.
## What regressed
Before eca3ca3e (#696), `getUserTable` went through `findById(key)`, a
`default` method delegating to the Spring-derived
`findByDatabaseIdIgnoreCaseAndTableIdIgnoreCase`. Spring Data emits
`upper(...)` for `IgnoreCase`
(`JpaQueryCreator$PredicateBuilder.upperIfIgnoreCase`), so it matched
the index. That is why the index is on `upper`.
#696 replaced that call with a new explicit `@Query` using handwritten
`lower(...)`, following the file's existing convention for hand-written
queries. The method name still reads like the derived finder it
displaced, which is how it passed review.
The six pre-existing `lower(...)` queries were all on cold paths —
filters, search, rename. They had been scanning for a long time without
anyone noticing, because a 500ms scan at low QPS is invisible. Moving
the hot path onto that convention is what turned it into an outage.
## The fix
`lower(` → `upper(` at all 30 token occurrences across the 15 comparison
sites in `UserTableHtsJdbcRepository`, both sides of every comparison.
Nothing else.
Production `EXPLAIN ANALYZE`, measured on the real table:
| | plan | rows | time |
|---|---|---|---|
| before | `Table scan on user_table_row` | 513,418 | ~495ms |
| after | `Index lookup using idx_user_table_upper_db_table` | 1 |
~0.041ms |
The `entity_type` predicate is **not** the cause. In the fixed plan it
is demoted to a cheap residual filter above the index lookup. It is
implicated only because it arrived in the same commit.
### Why this is semantically safe
Production collation is confirmed `utf8mb4_0900_ai_ci`, which is case-
and accent-insensitive. Under it, `lower(a) = lower(b)`, `upper(a) =
upper(b)` and `a = b` are equivalent. The wrapping affects index
eligibility, not which rows match.
At the sole `LIKE` site both column and pattern fold with the same
function, and `%`, `_` and escapes are non-alphabetic so `upper()`
leaves them byte-identical — wildcard semantics are unchanged.
## Tests
Two tests added to `HtsRepositoryTest`, covering cross-case matching for
the filter and `LIKE` families. Those families were previously exercised
only with same-case data, so a botched substitution could have slipped
through; point reads, rename and deletes already had cross-case
coverage.
**These tests pass both before and after the change, and that is
deliberate.** There is no red phase because the change is
behaviour-neutral by design.
**What the tests prove:** behaviour preservation across case for the
affected query families.
**What they cannot prove:** index selection, scan avoidance, or latency.
Tests run against H2 in MySQL mode, which has no functional indexes and
no meaningful planner. The performance claim rests solely on the
production `EXPLAIN ANALYZE` above.
Suites pass on JDK 11: housetables 406, common 12, zero failures, errors
or skips.
## Deliberately out of scope
**`SoftDeletedUserTableHtsJdbcRepository`** has 24 `lower(` tokens
across 12 lines on `soft_deleted_user_table_row`. That is a different
physical table whose indexes are unconfirmed — flipping it blind could
be a no-op or a pessimisation. Its paths are cold (restore, purge,
querying deleted tables). Needs its own `SHOW INDEX` before anyone
touches it.
**The schema record is corrected in this PR.**
`services/housetables/ddl/0000__baseline.sql` previously recorded only
`PRIMARY KEY (database_id, table_id)` for `user_table_row`, and its own
header warned that a derived definition "cannot capture secondary
indexes". So the index this fix depends on was documented nowhere, and
anyone reconstructing the table from that file would have reintroduced
this outage.
`user_table_row` and `soft_deleted_user_table_row` are now transcribed
from production `SHOW CREATE TABLE`. For `user_table_row` that closed
more than the index: `database_id`/`table_id` were recorded as
`varchar(128)` but are `varchar(255)`, `metadata_location` as
`varchar(512)` but is `varchar(255)`, `version` as `NOT NULL` but is
nullable, `last_modified_time` was recorded but does not exist, and
`table_version` and `deleted_ts` exist but were absent. Engine, charset
and collation were missing from both tables.
`soft_deleted_user_table_row` has no secondary index, and that is now
recorded as the real state rather than an omission.
`job_row` and `table_toggle_rule` are deliberately untouched — no
production output was available for them, and guessing would recreate
exactly the failure this PR is fixing. The header now says which two
tables are verified and which two are not.
This also explains why no local or containerised MySQL could have caught
the regression: the `oh-only-mysql` recipe bootstraps from this DDL, so
a local database had no functional index and `lower()` versus `upper()`
was indistinguishable there.
---------
Co-authored-by: Copilot App <223556219+Copilot@users.noreply.github.com>
