Lesson: mysql2 promise/callback API boundary caused production crash

[chent1996]

Background

PR #121 fixed a MySQL timezone offset bug. During code review, a simplification was suggested that inadvertently introduced a runtime crash. The crash was only discovered after deploying to a real MySQL environment.

What Happened

Original code (worked):

const rawPool = (pool as any).pool;
if (rawPool?.on) {
  rawPool.on("connection", (conn: any) => {
    conn.query("SET time_zone = '+00:00'");  // callback-style, fire-and-forget
  });
}

After review simplification (crashed):

pool.on("connection", (conn) => {
  conn.query("SET time_zone = '+00:00'").catch((err) => {  // ← crash here
    console.error("[db] Failed to set session timezone:", err);
  });
});

Root Cause

mysql2/promise's pool.on("connection") event provides a callback-style connection object, not a promise-wrapped one. Calling .catch() on conn.query() fails because the return value is not a Promise.

This crashed on the first database connection during initSchema, taking down the entire Gateway.

Fix

pool.on("connection", (conn) => {
  conn.query("SET time_zone = '+00:00'", (err: unknown) => {
    if (err) console.error("[db] Failed to set session timezone:", err);
  });
});

Why It Wasn't Caught Earlier

1. Local/test environments use SQLite — the MySQL pool code path is never exercised
2. TypeScript didn't flag it — PoolConnection.query() has overloaded signatures supporting both callback and return value patterns, so .catch() appeared valid to tsc
3. No integration test with real MySQL — the change was pushed without verifying against an actual MySQL connection

Lessons Learned

• mysql2 mixes callback and promise APIs in non-obvious ways. The connection event from a PromisePool still provides a callback-style connection. Type signatures alone are not reliable at this boundary.
• Code touching database connection setup must be tested against a real MySQL instance before merging, not just type-checked.
• When simplifying as any casts during review, verify that the "cleaner" API actually behaves the same way at runtime.