browser-db-sqlite-persistence: closing a tab mid-write corrupts the OPFS database for tabs that stay open

Abierto
  • ¿Algo no está bien?
#2,357 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
48/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Activo
Stack tecnológico
sqlite, typescript
Área
database

Línea de trabajo

Start by reading the bundled OPFS worker and the wa-sqlite VFS fix linked in rhashimoto/wa-sqlite#393; the issue says the worker bundles wa-sqlite 1.5.0 and describes reproducing the failure with its Playwright harness. Check how the upstream fix applies to the bundled source, then run the OPFS harness and its existing Chromium suite. Done means the interrupted write recovers to the last committed state, and the reported regression cases and existing suite pass.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

  • [X ] I've validated the bug against the latest version of DB packages (@tanstack/[email protected] from npm, and main at 8feab6ea)

Describe the bug
Two tabs share one OPFS database. One tab is closed while it is in the middle of a write transaction. The tab that stays open then reads the half-finished write: its queries fail with database disk image is malformed, or return rows from a transaction that never committed. If the surviving tab writes anything afterwards, the damage becomes permanent, and reloading every tab does not bring the data back.

The cause is in wa-sqlite's OPFSCoopSyncVFS, which this package bundles into its OPFS worker. I have reported it upstream, with a fix:

  • rhashimoto/wa-sqlite#392
    (the bug, reproduced with wa-sqlite alone)
  • rhashimoto/wa-sqlite#393
    (pull request pending)

In short: SQLite recovers from an interrupted write by rolling back the journal the writer left behind. The VFS records which files exist once, when a connection opens, and never looks again. A journal left later by another tab is not in that record, so the VFS tells SQLite "no journal", and SQLite skips the rollback. A tab opened after the writer died recovers correctly; a tab that already had the database open does not.

I am filing here too because the upstream fix will not reach this package's users by itself. See "Why this needs a change here" below.

To Reproduce
No collections, sync engine, or settings are involved. The reproduction uses only openBrowserWASQLiteOPFSDatabase() and plain SQL at default settings.

Step Tab A (writer) Tab B (survivor)
1 Opens the database, commits 4,000 rows (~4 MB)
2 Opens the same database, reads 4,000 rows
3 BEGIN IMMEDIATE; UPDATE every row (no COMMIT)
4 Tab is closed
5 Reads again: database disk image is malformed

At step 5 the rollback journal is on disk (about 4.1 MB) with everything needed to undo step 3. Tab B never reads it.

install chromiumif Playwright's Chromium is missing. 3.bunx playwright test`

Result on 1.0.0-rc.1.3, identical in 5 of 5 runs:

✓ control: a tab opened after the writer died sees the committed state
✘ bug: a tab that was already open sees a half-written database
✘ bug: the survivor's next write makes the damage permanent

-   "integrity_check": "ok",
+   "integrity_check": "*** in database main ***
+ Tree 2 page 521 cell 14: Rowid 2060 out of order",
-   "n": 4000,
+   "error": "OPFSWorkerRequestError: database disk image is malformed",
The five files

package.json

{
  "private": true,
  "type": "module",
  "dependencies": {
    "@journeyapps/wa-sqlite": "1.7.2",
    "@tanstack/browser-db-sqlite-persistence": "1.0.0-rc.1.3",
    "@tanstack/db": "1.0.0-rc.1.3"
  },
  "devDependencies": {
    "@playwright/test": "1.64.1",
    "vite": "8.3.6"
  }
}

index.html

<!doctype html>
<meta charset="utf-8" />
<title>OPFS hot-journal repro</title>
<script type="module" src="/main.ts"></script>

main.ts

// The whole "app": open one OPFS SQLite database through the public API.
import { openBrowserWASQLiteOPFSDatabase } from '@tanstack/browser-db-sqlite-persistence'

const databaseName =
  new URLSearchParams(location.search).get(`db`) ?? `repro.sqlite`

;(window as any).db = await openBrowserWASQLiteOPFSDatabase({ databaseName })

playwright.config.ts

import { defineConfig } from '@playwright/test'

const port = 5179

export default defineConfig({
  testDir: `.`,
  testMatch: `*.spec.ts`,
  timeout: 60_000,
  workers: 1,
  use: { baseURL: `http://localhost:${port}` },
  webServer: {
    command: `vite --port ${port} --strictPort`,
    url: `http://localhost:${port}`,
    reuseExistingServer: false,
  },
})

repro.spec.ts

import { expect, test } from '@playwright/test'
import type { BrowserContext, Page } from '@playwright/test'

type Row = Record<string, unknown>

async function openTab(context: BrowserContext, db: string): Promise<Page> {
  const page = await context.newPage()
  await page.goto(`/?db=${db}`)
  await page.waitForFunction(() => (window as any).db !== undefined)
  return page
}

const sql = (page: Page, statement: string): Promise<Array<Row>> =>
  page.evaluate((s) => (window as any).db.execute(s), statement)

/** First row of a query, or the error it failed with. */
const ask = (page: Page, statement: string): Promise<Row> =>
  page.evaluate(
    (s) =>
      (window as any).db.execute(s).then(
        (rows: Array<Row>) => rows[0],
        (error: unknown) => ({ error: String(error) }),
      ),
    statement,
  )

/** Size of an OPFS file as the browser reports it, independent of SQLite. */
const fileBytes = (page: Page, file: string): Promise<number> =>
  page.evaluate(async (name) => {
    const root = await navigator.storage.getDirectory()
    return (await (await root.getFileHandle(name)).getFile()).size
  }, file)

// Committed state: 4,000 rows, each holding a 1,000-character value (~4 MB).
const SEED = `
  CREATE TABLE t (id INTEGER PRIMARY KEY, value TEXT);
  CREATE TABLE note (text TEXT);
  WITH RECURSIVE n(x) AS (VALUES (1) UNION ALL SELECT x + 1 FROM n WHERE x < 4000)
  INSERT INTO t SELECT x, printf('%01000d', x) FROM n;
`

// A transaction that is never committed. It rewrites more data than SQLite's
// default 2 MB page cache holds, so SQLite has to start writing changed pages
// into the database file before COMMIT. That is normal, and it is exactly what
// the rollback journal exists to undo.
const WRITE_WITHOUT_COMMIT = `
  BEGIN IMMEDIATE;
  UPDATE t SET value = printf('%02000d', id);
`

const COMMITTED_STATE = {
  integrity: { integrity_check: `ok` },
  rows: { n: 4000 },
  rowsWithUncommittedValue: { n: 0 },
}

async function observe(page: Page) {
  return {
    integrity: await ask(page, `PRAGMA integrity_check(1)`),
    rows: await ask(page, `SELECT count(*) AS n FROM t`),
    rowsWithUncommittedValue: await ask(
      page,
      `SELECT count(*) AS n FROM t WHERE length(value) <> 1000`,
    ),
  }
}

test(`control: a tab opened after the writer died sees the committed state`, async ({
  context,
}) => {
  const db = `control-${Date.now()}.sqlite`
  const writer = await openTab(context, db)
  await sql(writer, SEED)

  await sql(writer, WRITE_WITHOUT_COMMIT)
  await writer.close()

  const survivor = await openTab(context, db)
  expect(await fileBytes(survivor, `${db}-journal`)).toBeGreaterThan(0)
  expect(await observe(survivor)).toEqual(COMMITTED_STATE)
})

test(`bug: a tab that was already open sees a half-written database`, async ({
  context,
}) => {
  const db = `survivor-${Date.now()}.sqlite`
  const writer = await openTab(context, db)
  await sql(writer, SEED)

  const survivor = await openTab(context, db)
  expect(await observe(survivor)).toEqual(COMMITTED_STATE)

  await sql(writer, WRITE_WITHOUT_COMMIT)
  await writer.close()

  // The rollback journal is on disk, holding the original pages.
  expect(await fileBytes(survivor, `${db}-journal`)).toBeGreaterThan(0)
  expect(await observe(survivor)).toEqual(COMMITTED_STATE)
})

test(`bug: the survivor's next write makes the damage permanent`, async ({
  context,
}) => {
  const db = `permanent-${Date.now()}.sqlite`
  const writer = await openTab(context, db)
  await sql(writer, SEED)

  const survivor = await openTab(context, db)
  expect(await observe(survivor)).toEqual(COMMITTED_STATE)

  await sql(writer, WRITE_WITHOUT_COMMIT)
  await writer.close()

  // Any committed write by the survivor reuses and then empties the journal,
  // destroying the only copy of the original pages.
  await sql(survivor, `INSERT INTO note VALUES ('written by the survivor')`)
  expect(await fileBytes(survivor, `${db}-journal`)).toBe(0)
  await survivor.close()

  // A brand-new tab has nothing cached and opens the database from scratch.
  const later = await openTab(context, db)
  expect(await observe(later)).toEqual(COMMITTED_STATE)
})

Expected behavior
After tab A is closed, tab B reads the last committed state: 4,000 rows with their original values, and PRAGMA integrity_check returns ok. The unfinished transaction is lost, which the README already documents for pagehide. It must not be half-applied. The control test shows that the same interrupted write is fully recoverable when the journal is seen.

Desktop (please complete the following information):

  • OS: Ubuntu 26.04 (arm64)
  • Browser: Chromium
  • Version: 156.0.8078.4 (Playwright 1.64.1, standalone reproduction) and
    148.0.7778.96 (Playwright 1.60.0, this repository's OPFS harness)

Firefox and Safari are untested. The faulty logic is plain JavaScript in the VFS, so I expect them to behave the same.

Additional context

Why this needs a change here, and not only upstream.

This package makes the trigger ordinary. The connection terminates its worker synchronously on pagehide, so closing a tab stops SQLite wherever it is. The database is exposed during any transaction larger than the page cache (2 MB by default), such as an initial sync; that is the case the reproduction exercises. It is also exposed briefly during every COMMIT. I did not test that window directly, it follows from SQLite's commit sequence. In our application's two-tab leader-close stress test, 12 of 80 runs failed, logging database disk image is malformed thousands of times.

  • Apps cannot fix it by upgrading wa-sqlite. The published dist/assets/opfs-worker.js has wa-sqlite 1.5.0 compiled in. The @journeyapps/wa-sqlite version an app installs as a peer does not change
    the worker's code.

I can confirm that the upstream fix also fixes this package. I applied the change from rhashimoto/wa-sqlite#393 to the 1.5.0 source that main bundles (6 lines added, 5 removed) and changed nothing in this package. In this repository's OPFS harness the two failing cases then passed in 3 of 3 runs, and the
existing Chromium OPFS suite still passed, 48 of 48. With the original file restored, both cases failed again.

How would you like me to create a PR for this? Based on the rules for PR - might be easiest for now to do this as a patch on @journeyapps/[email protected] (pnpm patchedDependencies) and drop it when a fixed release is adopted. No source file in this package should need any change?.

Alternatively - we wait and update the @journeyapps/wa-sqlite release? is there a reason we have basically fixed it at ^1.4.1 ? Its currently at 2.0.10?

Lenguaje dominante
TypeScript
Estrellas
4k
Forks
277
Merge medio
1 d 11 h
PR fusionados (30 d)
148

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de TanStack/db

Todos los issues de TanStack/db

Issues similares

Más issues de TypeScript

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.