search

WiseLibs / better-sqlite3

The fastest and simplest library for SQLite3 in Node.js.
MIT License
5.23k stars 390 forks source link

SqliteError: database is locked error in Next.js + Docker #1155

Closed deadcoder0904 closed 4 months ago

deadcoder0904 commented 4 months ago

I face this error randomly once in a while.

And idk how to debug it. I tried making a singleton.

I wanted to see if WAL mode solves it & I tried WAL mode as recommended at https://til.simonwillison.net/sqlite/enabling-wal-mode

But for some reason, I faced data loss issues which I've described in the README of the linked project below. However, I solved the data loss issues by adding Litestream as a SQLite Database Backup solution with Cloudflare R2 so whenever data is lost, Litestream recovers it with its own WAL mode. The full problem is explained well in the README. I was doing something nasty that probably won't happen in production but yeah it did have some problems.

Reproduction -> https://github.com/deadcoder0904/easypanel-nextjs-sqlite

Anyways, how can I solve this error permanently?

I've looked into it & there is one answer that you must close your database but better-sqlite3 doesn't allow to close database.

I've thought that 2 processes access the same database when in api/ route in Next.js so I tried adding export const dynamic = "force-dynamic" on every api route but that didn't work.

I tried using a default export for db instead of a named export but that didn't work.

I tried using a singleton with the help of https://github.com/epicweb-dev/remember but that didn't work either.

The problem with this error is it happens randomly once in a while. But for the last few days, it has been happening 3-4 times sometimes which is why I opened the issue now.

I'm not doing anything different. Just my big project has like 10 api routes but only 5 routes importing the db but I still get that error when the next build appears in Dockerfile on this line:

RUN pnpm build # this runs `next build` & throws an error

The error looks like:

#23 1.034 > next build
#23 1.034
#23 2.276    ▲ Next.js 14.1.1
#23 2.279    - Environments: .env.production
#23 2.279
#23 2.308    Creating an optimized production build ...
#23 40.71  ✓ Compiled successfully
#23 40.71    Linting and checking validity of types ...
#23 57.94    Collecting page data ...
#23 58.71 SqliteError: database is locked
#23 58.71     at Database.pragma (/app/node_modules/.pnpm/better-sqlite3@9.4.3/node_modules/better-sqlite3/lib/methods/pragma.js:11:44)
#23 58.71     at 57528 (/app/.next/server/app/api/admin/route.js:1:5066)
#23 58.71     at t (/app/.next/server/webpack-runtime.js:1:127)
#23 58.71     at 31062 (/app/.next/server/app/api/admin/route.js:1:4253)
#23 58.71     at t (/app/.next/server/webpack-runtime.js:1:127)
#23 58.71     at 85898 (/app/.next/server/app/api/admin/route.js:1:1156)
#23 58.71     at t (/app/.next/server/webpack-runtime.js:1:127)
#23 58.71     at r (/app/.next/server/app/api/admin/route.js:1:9460)
#23 58.71     at /app/.next/server/app/api/admin/route.js:1:9508
#23 58.71     at t.X (/app/.next/server/webpack-runtime.js:1:1191) {
#23 58.71   code: 'SQLITE_BUSY'
#23 58.71 }
#23 58.71
#23 58.71 > Build error occurred
#23 58.71 Error: Failed to collect page data for /api/admin
#23 58.71     at /app/node_modules/.pnpm/next@14.1.1_@babel+core@7.24.0_react-dom@18.2.0_react@18.2.0/node_modules/next/dist/build/utils.js:1258:15
#23 58.71     at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
#23 58.71   type: 'Error'
#23 58.71 }
#23 59.06  ELIFECYCLE  Command failed with exit code 1.
#23 ERROR: process "/bin/sh -c pnpm build" did not complete successfully: exit code: 1
------
 > [web builder 7/7] RUN pnpm build:
58.71   code: 'SQLITE_BUSY'
58.71 }
58.71
58.71 > Build error occurred
58.71 Error: Failed to collect page data for /api/admin
58.71     at /app/node_modules/.pnpm/next@14.1.1_@babel+core@7.24.0_react-dom@18.2.0_react@18.2.0/node_modules/next/dist/build/utils.js:1258:15
58.71     at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
58.71   type: 'Error'
58.71 }
59.06  ELIFECYCLE  Command failed with exit code 1.
------
failed to solve: process "/bin/sh -c pnpm build" did not complete successfully: exit code: 1
make: *** [Makefile:3: build-development] Error 17

This happens when I try make build-production in the Terminal.

I solve it by running make build-production 3 or 4 times but I am really annoyed by this error because its happening a lot of times now.

Any ideas?

My intuition is when next build runs, all routes in api folder access the same file which open's sqlite database , i.e, ./db/index.ts & it throws this error. They all access it in milliseconds but somehow still fail.

What's the solution?

neoxpert commented 4 months ago

The error "database is locked" or "SQLITE_BUSY" is not exclusive to better-sqlite3 but sqlite3 itself.

I would guess that something within all of these uncontrollable steps and tools is doing some concurrent access to the same database file without properly closing it or finishing started transactions before the next process is trying to do its stuff.

Have you tried to limit next.js to only use 1 Process while building https://docs.uniform.dev/sitecore/deploy/how-tos/how-to-control-nextjs-threads/ ?

mceachen commented 4 months ago

Besides just enabling WAL, you may want to adjust the busy_timeout PRAGMA.

But as @neoxpert states, this is (expected!) SQLite behavior.

deadcoder0904 commented 4 months ago

@neoxpert thanks for the idea, i'll test it out. i didn't know that i could do it.

@mceachen yeah, i did see that somewhere online but didn't know it was a PRAGMA i can set. what's the catch though? is it ok to set? is it recommended?

deadcoder0904 commented 4 months ago

bdw, can you guys also answer why docker + sqlite wal mode gives data loss issues as stated in my readme. i'm using windows (wsl2) + docker (without docker desktop). i'm assuming its something to do with windows filesystem?

mceachen commented 4 months ago

is it recommended?

That's up to you. I linked to the SQLite docs.

i'm using windows (wsl2) + docker

I've not had success with SQLite on WSL. It assumes a well-behaved local POSIX filesystem, which is a bit much for WSL to claim.

You may avoid WSL filesystem madness by not using WAL and disabling SHM, but at some point it's better to just run linux and route around 30 years of technical debt.

deadcoder0904 commented 4 months ago

cool.

i think this worked as i haven't faced any issues so far:

/** @type {import('next').NextConfig} */
const nextConfig = {
    experimental: {
        // recommended to solve https://github.com/WiseLibs/better-sqlite3/issues/1155
        workerThreads: false,
        cpus: 1,
    },
    .
    .
    .
}