MySQL pitfalls and production audit, issue #39
This existing-content audit applies #31 to #39. The baseline is 7a9913800bb15b2d0d76a0fcc80febbbcc6c53a8. The completion comment records the checked commits, inventory counts, execution results, and reader-QA verdict. Raw inventories, baseline snapshots, correspondence records, assertion mutations, and execution evidence remain in temporary storage for the review handoff.
Coverage and occurrence identity
The assigned surface has seven lessons, fifteen compendium entries, six alerting signals, seven triage rows, five YAML Scenarios, five generated Transcript families, five ledger records, and five llms sections. Coverage includes chapter explanations and summaries, source declarations, setup, ordered steps, notes, comments, timeline labels, assertions, displayed results, and generated descriptions. The MySQL chapter 7/8 sidebar labels and README coverage cells identify topics, not additional behavioral guarantees.
The inventories retain exact line occurrences and nested YAML field paths. Ordered correspondences associate each generated statement, note, pending resolution, and timeline label with its own source step and Session. The complete corresponding Transcript must occur in its own llms section; the ledger must retain the exact source claim and version. Counts are inclusive occurrence units, not independent guarantees. Returned fields not named in a subset assertion remain observations. Rewritten paragraphs supersede the baseline without an invented one-to-one paragraph mapping.
The baseline inventory contains 939 nonblank occurrences and 127 nested source fields. The final inventory contains 998 nonblank occurrences and 147 nested source fields: 228 lesson lines, 231 Scenario-source lines, 262 Transcript/timeline lines, five ledger records, and 272 llms lines. There are 75 ordered statement, note, pending-resolution, and timeline correspondences. The checker compares complete SQL/comments and asserted output fields, not just matching counts or first-line SQL.
Executed scope is MySQL 8.4.11 InnoDB with local root access, Performance Schema available, default REPEATABLE READ, deadlock detection enabled, and the deadlock metric enabled. The two report Scenarios explicitly assert REPEATABLE READ. Timeout settings are set in their own sessions. @session_name is a lesson tag, not an assumed production identifier. Official contracts target MySQL 8.4 under their documented operation/configuration conditions.
Semantic dispositions
| Decision | Assigned subject | Evidence and disposition |
|---|---|---|
| A1 | Compendium 1, lost updates | Linked RC/RR schedules assert stale overwrites. Arithmetic, participating locking readers, and checked versions repair the shown single-row deposits; remove any-level and arbitrary-rule promises. |
| A2 | Compendium 2, uniqueness | Plain checks without UNIQUE yield two emails. The linked unique-key schedule arbitrates its declared identity; multiple unique indexes and client affected-row flags need separate handling. |
| A3 | Compendium 3, idempotency | Linked committed/in-flight duplicates skip database balance changes with retained keys and CLIENT_FOUND_ROWS absent. Remote charges and lost responses are not executed. |
| A4 | Compendium 4, cross-row rule | Linked RR doctor schedule leaves nobody on call; SERIALIZABLE produces 1213 and rollback. All relevant writers must coordinate over the rule, not only their individual rows. |
| A5 | Compendium 5, mixed visibility | The linked DELETE affects zero rows while a consistent SELECT still sees the old row. Own writes and locking/current operations prevent a universal frozen-state description. |
| A6 | Compendium 6, timeout recovery | Linked 1205 schedule asserts earlier work/locks surviving with innodb_rollback_on_timeout OFF. ON is manual scope. Whole-operation retry requires rollback first; remove an all-errors rule and an unexecuted double-application outcome. |
| A7 | Compendium 7, migration | CREATE INDEX commits the preceding INSERT, retained after explicit rollback. No migration exception runs, and not every DDL statement has this behavior. One-DDL advice does not establish atomicity. |
| A8 | Compendium 8, insert wait | Linked PRIMARY-index range schedule blocks slot 15 at RR and permits slot 17 at RC. Scope the isolation change to its writer rule; retain documented foreign-key/duplicate-check gap exceptions. |
| A9 | Compendium 9, gap-cycle symptom | Official gap-lock compatibility/insertion inhibition is Documented. The linked gap schedule executes one blocked insertion, not two gap-holding inserters deadlocking. Remove the claimed reproduction and label this execution gap. |
| A10 | Compendium 10, DDL queue | Linked metadata-lock schedule asserts one later query queuing behind ALTER. lock_wait_timeout bounds acquisition, not every later query or full migration duration. |
| A11 | Compendium 11, pool/idle | Detector asserts one idle transaction. Locks, read history, and metadata depend on prior operations. No pool, ORM, I/O delay, or pool exhaustion runs. Idle connection timeout affects healthy pooled connections too. |
| A12 | Compendium 12, retained undo | Linked schedules assert retained snapshot values and history-list lower bounds, not disk bytes. Transaction age is not read-view age; no immediate drainage, universal purge prohibition, shrinkage, or warning-time promise remains. |
| A13 | Compendium 13, delivery | Linked database stand-ins demonstrate mismatched local records. Outbox proves a database-local commit/rollback boundary and reselection, not process crash, broker transport, receiver effects, or unconditional at-least-once delivery. |
| A14 | Compendium 14, XA | Linked detached branch remains prepared after session loss and holds a NOWAIT-conflicting lock; another connection commits by XID. Coordinator decisions, expiry, multi-resource agreement, and restart are not demonstrated. |
| A15 | Compendium 15, opposite order | Linked deadlock and ordered-lock schedules support the shown cycle and avoidance. Bounded safe retries and shorter transactions are conditional advice, not zero-deadlock promises. |
| P1 | Blocker diagnosis and KILL | Assert B/A wait edge, Sleep/null, blocked UPDATE resumption with affected count 1, row 1 balance 300, and separate row 2 balance 100 after A changed it to 200. Row 2 makes rollback observable without B overwriting the evidence. Connection termination differs from KILL QUERY; privileges, rollback delay, and client/external effects constrain production use. No whole-queue guarantee remains. |
| P2 | Long/idle detector | Assert REPEATABLE-READ, two orders, A/RUNNING/age lower bound, A/Sleep, and zero modified rows. Remove the magic transaction-ID threshold and claimed purge diagnosis. Tagged oldest transaction is not necessarily globally oldest or oldest read view. RUNNING is not an active-statement indicator. |
| P3 | Timeout guardrails | Assert SLEEP interruption, a usable A connection afterward, A's idle connection error, and B's original balance. No exact elapsed duration or general SELECT timeout error is asserted. max_execution_time applies to eligible read-only SELECTs, excluding stored programs; wait_timeout is an idle connection limit, not a transaction-only deadline. OFF/ON rollback scope and metadata waits are separated. |
| P4 | Deadlock counter and logs | Assert detection=1, metric enabled, 1213, and delta at least one. Remove permanent/forever claims. Enabled/reset/restart collection intervals are Documented conditions. Latest-deadlock and error-log reporting are manual-only; no retained report, log delivery, restart, or overhead measurement runs. Slow-log shape is a clue, not proof of a blocker or missing-index exclusion. |
| P5 | History health | Assert R REPEATABLE-READ, R v=0 before/after 150 updates, A v=150, history lower bound 150, and oldest tagged R with transaction-age lower bound. Remove definitive culprit and automatic drainage claims. Background purge, other readers, and write workload constrain cleanup; bytes, latency, shrinkage, and warning time are unmeasured. |
| P6 | Alerting and triage | All six signals and seven symptoms are investigation aids. Error/configuration scope, row/metadata waits, counter intervals, and application invariants are distinct. Timeouts without deadlock counts do not prove acyclic waits when detection is disabled. No alert accuracy, threshold suitability, incident frequency, or automatic recovery is measured. |
Demonstrated behavior means executed assertions under the specified schedule. Documented contracts use exact versioned manual support and remain distinct from execution. This slice introduces no new Entailed guarantee; conditional operational recommendations and hypothetical symptoms are not universal promises. The inherited #33, #35, #37, and #38 handoffs assigned to this surface are reconciled through these decisions and the corrected source/mirror families. Earlier audit documents remain historical records.
Official support
MySQL's documentation llms endpoint was inaccessible, so the versioned HTML manual supplies support: gap locks, error handling, transaction fields and privileges, row-lock wait view, connection/query termination, metrics collection and resets, deadlock reports, disabled detection, slow-query logging, SELECT timeout, idle connection timeout, and purge scheduling and workload. The retained registry maps exact source occurrences and assertions to these subjects. Quotations in lessons were checked against those pages.
Outside-slice reconciliation
These baseline locations go to the shared-content audit #40. They are not unresolved #39 claims.
| Baseline locations | Required reconciliation |
|---|---|
docs/errors/1205.md:3,11,27,35 | Scope statement rollback to InnoDB row-lock timeout with innodb_rollback_on_timeout OFF; distinguish metadata waits, whole-operation retry, and the shown transaction's actual rollback-before-retry schedule. |
docs/errors/57014.md:33 | Scope MySQL max_execution_time to eligible read-only SELECTs outside stored programs; SLEEP interruption does not emit 3024 here. The PostgreSQL transaction-state correction remains with the shared audit. |
docs/errors/1213.md:41 | Deadlock detection depends on configuration; a shown opposite-order cycle does not establish all timing or all cross-engine differences. |
docs/concepts/what-is-a-transaction.md:38-39; docs/concepts/transactional-outbox.md:2,7-25,44-55,59-65; docs/faq.md:28,36 | Separate statement/transaction failure, writer/operation scope, modeled local effects, remote delivery, and conditional retries. Shared summaries must not restore removed universal guarantees. |
README.md:6,15-22,55-56; CONTRIBUTING.md:3-10; docs/about/methodology.md:11,20,62,70,75,84-90; scripts/gen-transcripts.ts:152-153; docs/public/llms.txt:4-5; docs/.vitepress/config.ts:237,291-293; docs/.vitepress/theme/components/HomeCurriculum.vue:16 | Reconcile asserted schedules, manual contracts, derivations, source-versus-CI execution promises, and Python parity limited to YAML. The provenance footer's all-Transcript driver claim is shared work. |
Generated artifacts belonging to the five assigned sources are corrected here, including claims in ledger/llms records and derived lesson descriptions. No practice, guide, Mechanism diagram, failure lab, delivery integration, or alternate harness is added.
Verification boundaries
The completion comment separates real-database execution, applicable Python YAML checks, mutation evidence, inspected first generation, stable second generation, static checks, and fresh-context built-reader QA. Inventories and correspondence checks support the semantic audit, not replace it. Manual-only and model-only limits are stated in the lessons.
No unsupported in-scope claim is intentionally left unresolved. Code review and shipping are separate stages; the parent audit gate remains open for other slices and shared reconciliation.