sqlite3.OperationalError: unable to open database file
sqlite3.OperationalError: unable to open database file
sqlite3.OperationalError: unable to open database file covers five different filesystem faults, and only one of them is about the database file. A missing parent directory, a parent directory the process cannot write to, a path that is a directory, a symlink loop, and a path whose parent component is a regular file all raise the identical string — while an unwritable file in a writable directory raises something else entirely (attempt to write a readonly database). Repairing the file is the standard first move and it fixes nothing.
The failure usually appears months into production, right after a volume is remounted, a container image is rebuilt with a new USER, or a bind mount is pointed at a file that does not exist yet. SQLite has no way to phrase the difference: at the C level every one of those five cases collapses into SQLITE_CANTOPEN, and the Python binding surfaces it as one exception with one message.
Everything below was executed on one host — arm64 Linux 6.17.0-1019-oracle, Python 3.13.5, SQLite 3.53.4, uid 10000, umask 0022. Each transcript is the unedited stdout of the block directly above it, and every block rebuilds its own fixtures, so a second run reproduces the same bytes. The Docker section is the exception: this host has no Docker daemon, so that behaviour is quoted from Docker's manual and labelled as such.
TL;DR — what the message means, and where to look first
- The message names the wrong object. In every case measured here the database file was readable; what SQLite could not do was create its journal next to it, or parse the path at all.
- The kernel knows which fault you have; SQLite does not tell you. The same
SQLITE_CANTOPENhidesENOENT,EACCES,EISDIR,ENOTDIRandELOOP. A five-linestat/os.accesscheck separates them in one second. - A read-only directory is not a read-only file. Directory mode
0555with a writable database file yieldsSQLITE_READONLY_DIRECTORY(1544), whose documented meaning is exactly the journal problem. File mode0444in a writable directory yieldsSQLITE_READONLY(8). - Where the exception appears tells you which branch you are on. A missing or unwritable directory fails at
connect(); the read-only branches connect cleanly and fail at the first write, which is why a health check that only connects passes while the app still cannot save anything. - In containers this is nearly always a uid problem, not a path problem. The container runs as uid 1000, the bind-mounted directory is owned by the host user, and SQLite needs write permission on that directory for every commit.
- Check it before the first query. The preflight below turns
SQLITE_CANTOPENintoEACCES-or-ENOENTplus the exact path that needs achown, and exits non-zero so a container entrypoint can refuse to start.
Thirty seconds of triage before you touch the file
Before changing any permission, answer one question: can the user that runs SQLite create a file in the directory that holds the database? That is the real precondition for every write, because SQLite creates the rollback journal and, in WAL mode, the -wal and -shm sidecars there. If the answer is no, the mode of the database file itself is irrelevant.
The block below builds both shapes and asks the question with test -w, which is the same check the kernel performs when SQLite calls open(). Note what the two SQLite outcomes are: not the message from the bug report, but its neighbour, because in this fixture the database file exists and is writable.
#!/usr/bin/env bash
# 30-second triage: can the process that runs SQLite write the DIRECTORY?
set -u
D=/tmp/sqtriage
rm -rf "$D"
mkdir -p "$D/data"
touch "$D/data/app.db"
chmod 0755 "$D" "$D/data"
chmod 0644 "$D/data/app.db"
echo "--- first attempt: directory 0755 ---"
stat -c '%A %u:%g %n' "$D/data" "$D/data/app.db"
if [ -w "$D/data" ]; then echo "dir writable by uid $(id -u)"; else echo "dir NOT writable by uid $(id -u)"; fi
echo "--- second attempt: directory 0555 (the bind-mount accident) ---"
chmod 0555 "$D/data"
stat -c '%A %u:%g %n' "$D/data" "$D/data/app.db"
if [ -w "$D/data" ]; then echo "dir writable by uid $(id -u)"; else echo "dir NOT writable by uid $(id -u)"; fi
echo "--- what SQLite does with that directory ---"
python3 - "$D/data/app.db" <<'PY'
import sqlite3, sys
try:
con = sqlite3.connect(sys.argv[1])
con.execute("create table if not exists t(x)")
con.commit()
print("write OK")
except sqlite3.Error as exc:
print("%s: %s [%s]" % (type(exc).__name__, exc, getattr(exc, "sqlite_errorname", "?")))
PY
chmod 0755 "$D/data"--- first attempt: directory 0755 ---
drwxr-xr-x 10000:10000 /tmp/sqtriage/data
-rw-r--r-- 10000:10000 /tmp/sqtriage/data/app.db
dir writable by uid 10000
--- second attempt: directory 0555 (the bind-mount accident) ---
dr-xr-xr-x 10000:10000 /tmp/sqtriage/data
-rw-r--r-- 10000:10000 /tmp/sqtriage/data/app.db
dir NOT writable by uid 10000
--- what SQLite does with that directory ---
OperationalError: attempt to write a readonly database [SQLITE_READONLY_DIRECTORY]The last line is the useful surprise. A directory at 0555 holding a perfectly writable app.db does not produce unable to open database file at all — it produces attempt to write a readonly database, and the extended result code is SQLITE_READONLY_DIRECTORY. That code exists precisely for this case, and it is the one most likely to appear in a container whose volume is mounted read-only or owned by another uid.
The conditions that raise the identical message
The next fixture puts the competing path shapes side by side and opens each the way an application would: sqlite3.connect() with the default flags, then a CREATE TABLE and an INSERT. The sqlite_errorcode and sqlite_errorname attributes come straight from SQLite, so nothing here is inferred from the message text.
#!/usr/bin/env python3
"""Core probe: which filesystem condition yields 'unable to open database file'.
Deterministic: fixed base dir, recreated from scratch, no absolute paths printed.
Run: python3 probe_open.py (must be run from the directory that should hold nothing)
"""
import os
import shutil
import sqlite3
import stat
BASE = "/tmp/sqopen"
def reset(base):
"""Clean tree, even if a previous run left parts at mode 0555 or a symlink loop."""
for root, dirs, files in os.walk(base, topdown=False):
for name in dirs + files:
p = os.path.join(root, name)
if not os.path.islink(p):
os.chmod(p, 0o755)
os.chmod(root, 0o755)
shutil.rmtree(base, ignore_errors=True)
if os.path.isdir(base):
raise SystemExit("reset failed: %s survived" % base)
os.makedirs(base)
reset(BASE)
def mode(path):
return "%04o" % (stat.S_IMODE(os.stat(path).st_mode),)
def show(label, fn):
try:
fn()
print("%-34s OK" % label)
except sqlite3.Error as exc:
code = getattr(exc, "sqlite_errorcode", None)
name = getattr(exc, "sqlite_errorname", None)
print("%-34s %s: %s [%s %s]" % (label, type(exc).__name__, exc, name, code))
except OSError as exc:
print("%-34s OSError %s: %s [errno %s]" % (label, type(exc).__name__, exc, exc.errno))
# --- 1. parent directory does not exist --------------------------------
def run_missing_parent():
con = sqlite3.connect(os.path.join(BASE, "nodir", "app.db"))
con.execute("create table t(x)")
con.close()
# --- 2. parent directory exists but is not writable --------------------
os.makedirs(os.path.join(BASE, "rodir"))
os.chmod(os.path.join(BASE, "rodir"), 0o555)
def run_readonly_dir():
con = sqlite3.connect(os.path.join(BASE, "rodir", "app.db"))
con.execute("create table t(x)")
con.close()
# --- 3. db file exists and is writable, directory is not ---------------
wdb = os.path.join(BASE, "rodir2", "app.db")
os.makedirs(os.path.dirname(wdb))
con = sqlite3.connect(wdb)
con.execute("create table t(x)")
con.commit()
con.close()
os.chmod(os.path.dirname(wdb), 0o555)
def run_writable_file_readonly_dir():
con = sqlite3.connect(wdb)
con.execute("insert into t values (1)")
con.commit()
con.close()
def run_read_from_readonly_dir():
con = sqlite3.connect(wdb)
print(" read-only read ->", con.execute("select count(*) from t").fetchone())
con.close()
# --- 4. db file is read-only, directory is writable --------------------
rdb = os.path.join(BASE, "file444.db")
con = sqlite3.connect(rdb)
con.execute("create table t(x)")
con.commit()
con.close()
os.chmod(rdb, 0o444)
def run_readonly_file():
con = sqlite3.connect(rdb)
con.execute("insert into t values (1)")
con.commit()
con.close()
# --- 5. the path is a directory ----------------------------------------
os.makedirs(os.path.join(BASE, "isdir.db"))
def run_path_is_dir():
con = sqlite3.connect(os.path.join(BASE, "isdir.db"))
con.execute("create table t(x)")
con.close()
# --- 6. read-only URI against each of the above ------------------------
def run_ro_uri_readonly_dir():
con = sqlite3.connect("file:%s?mode=ro" % wdb, uri=True)
print(" mode=ro read ->", con.execute("select count(*) from t").fetchone())
con.close()
def run_ro_uri_missing():
con = sqlite3.connect("file:%s?mode=ro" % os.path.join(BASE, "nodir", "app.db"), uri=True)
con.execute("select 1")
con.close()
def run_immutable_readonly_dir():
con = sqlite3.connect("file:%s?immutable=1" % wdb, uri=True)
print(" immutable=1 ->", con.execute("select count(*) from t").fetchone())
con.close()
print("dir modes:", mode(BASE), "| rodir:", mode(os.path.join(BASE, "rodir")),
"| rodir2:", mode(os.path.join(BASE, "rodir2")), "| file444.db:", mode(rdb))
show("missing parent dir", run_missing_parent)
show("unwritable parent dir", run_readonly_dir)
show("unwritable file, rw open", run_readonly_file)
show("path is a directory", run_path_is_dir)
show("writable file, ro dir (write)", run_writable_file_readonly_dir)
show("writable file, ro dir (read)", run_read_from_readonly_dir)
show("mode=ro on read-only dir", run_ro_uri_readonly_dir)
show("mode=ro on missing file", run_ro_uri_missing)
show("immutable=1 on read-only dir", run_immutable_readonly_dir)dir modes: 0755 | rodir: 0555 | rodir2: 0555 | file444.db: 0444
missing parent dir OperationalError: unable to open database file [SQLITE_CANTOPEN 14]
unwritable parent dir OperationalError: unable to open database file [SQLITE_CANTOPEN 14]
unwritable file, rw open OperationalError: attempt to write a readonly database [SQLITE_READONLY 8]
path is a directory OperationalError: unable to open database file [SQLITE_CANTOPEN 14]
writable file, ro dir (write) OperationalError: attempt to write a readonly database [SQLITE_READONLY_DIRECTORY 1544]
read-only read -> (0,)
writable file, ro dir (read) OK
mode=ro read -> (0,)
mode=ro on read-only dir OK
mode=ro on missing file OperationalError: unable to open database file [SQLITE_CANTOPEN 14]
immutable=1 -> (0,)
immutable=1 on read-only dir OKFour rows, one message. Reading them in order: the parent directory does not exist; the parent directory exists but refuses new files; the path is a directory rather than a file; the parent directory does not exist under the second fixture either, because rodir names a directory the fixture never created. The two rows in the middle of the table are the ones that keep people busy for an afternoon — unwritable file, rw open and writable file, ro dir — and neither of them is the error in the title.
The bottom four rows are the escape hatches, and they matter for read replicas. file:...?mode=ro opens successfully even when the containing directory is 0555, because a read-only connection never needs the journal. The same URI against a path that does not exist still fails, which is the documented behaviour of mode=ro: it will not create the file. immutable=1 also opens, and SQLite's URI documentation is explicit about the price: an immutable database "skips all file locking and change detection", and if the file changes anyway "SQLite might return incorrect query results and/or SQLITE_CORRUPT errors". Use it for a baked-in, never-written dataset, not for a volume another container also mounts.
What the kernel says, and why SQLite will not say it
SQLite's own documentation for SQLITE_CANTOPEN (14) is deliberately vague: the code "indicates that SQLite was unable to open a file. The file in question might be a primary database file or one of several temporary disk files." That sentence is honest and useless at 3 a.m., because the same code also covers the journal it could not create.
The kernel, by contrast, distinguishes everything. The probe below replays each fixture's open() call directly with os.open(), and separately probes the containing directory by trying to create and delete a temporary file in it — which is exactly what SQLite must be able to do before any transaction can commit.
#!/usr/bin/env python3
"""Why 'unable to open database file' hides five different kernel errors.
Deterministic: fixed base dir, recreated, relative labels only.
"""
import errno
import os
import shutil
import sqlite3
import stat
BASE = "/tmp/sqerrno"
def reset(base):
"""Clean tree, even if a previous run left parts at mode 0555 or a symlink loop."""
for root, dirs, files in os.walk(base, topdown=False):
for name in dirs + files:
p = os.path.join(root, name)
if not os.path.islink(p):
os.chmod(p, 0o755)
os.chmod(root, 0o755)
shutil.rmtree(base, ignore_errors=True)
if os.path.isdir(base):
raise SystemExit("reset failed: %s survived" % base)
os.makedirs(base)
reset(BASE)
ERR = {getattr(errno, n): n for n in dir(errno) if n.startswith("E") and isinstance(getattr(errno, n), int)}
def kernel_open(path):
"""What the kernel says about the *file*."""
try:
fd = os.open(path, os.O_RDWR | os.O_CREAT, 0o644)
os.close(fd)
return "open(O_RDWR|O_CREAT) OK"
except OSError as exc:
return "open -> errno %d %s" % (exc.errno, ERR.get(exc.errno, "?"))
def check_dir(path):
"""What the kernel says about the *directory* SQLite must write journals into."""
try:
st = os.stat(path)
except OSError as exc:
return "stat -> errno %d %s" % (exc.errno, ERR.get(exc.errno, "?"))
if not stat.S_ISDIR(st.st_mode):
return "stat -> not a directory"
probe = os.path.join(path, ".writetest.%d" % os.getpid())
try:
fd = os.open(probe, os.O_WRONLY | os.O_CREAT | os.O_EXCL, 0o600)
os.close(fd)
os.unlink(probe)
return "dir writable by uid %d" % os.getuid()
except OSError as exc:
return "dir NOT writable by uid %d -> errno %d %s" % (os.getuid(), exc.errno, ERR.get(exc.errno, ":"))
def sqlite_says(target):
try:
con = sqlite3.connect(target, uri=target.startswith("file:"))
con.execute("create table if not exists t(x)")
con.execute("insert into t values (1)")
con.commit()
con.close()
return "OK"
except sqlite3.Error as exc:
return "%s: %s [%s]" % (type(exc).__name__, exc, getattr(exc, "sqlite_errorname", "?"))
cases = {}
# A. parent directory missing
cases["A parent dir missing"] = os.path.join(BASE, "gone", "app.db")
# B. parent directory present, mode 0555
rod = os.path.join(BASE, "ro"); os.makedirs(rod); os.chmod(rod, 0o555)
cases["B parent dir mode 0555"] = os.path.join(rod, "app.db")
# C. the path itself is a symlink loop
os.symlink("loop", os.path.join(BASE, "loop"))
cases["C symlink loop"] = os.path.join(BASE, "loop")
# D. the path is an existing directory
os.makedirs(os.path.join(BASE, "dir.db"))
cases["D path is a directory"] = os.path.join(BASE, "dir.db")
# E. a component of the path is a regular file
open(os.path.join(BASE, "notadir"), "w").close()
cases["E component is a file"] = os.path.join(BASE, "notadir", "app.db")
print("%-24s | %-22s | %-46s | %s" % ("case", "sqlite3", "kernel open()", "kernel journal dir"))
print("-" * 122)
for label, target in cases.items():
parent = os.path.dirname(target)
print("%-24s | %-22s | %-46s | %s" % (label, sqlite_says(target), kernel_open(target), check_dir(parent)))
print()
print("euid/uid:", os.geteuid(), os.getuid(), "| umask: %04o" % 0o022)
# F. the same directory, owned by the process: proof the mode is the blocker
okd = os.path.join(BASE, "ok"); os.makedirs(okd)
print("A/B repeat with a 0755 dir the process owns:", sqlite_says(os.path.join(okd, "app.db")))case | sqlite3 | kernel open() | kernel journal dir
--------------------------------------------------------------------------------------------------------------------------
A parent dir missing | OperationalError: unable to open database file [SQLITE_CANTOPEN] | open -> errno 2 ENOENT | stat -> errno 2 ENOENT
B parent dir mode 0555 | OperationalError: unable to open database file [SQLITE_CANTOPEN] | open -> errno 13 EACCES | dir NOT writable by uid 10000 -> errno 13 EACCES
C symlink loop | OperationalError: unable to open database file [SQLITE_CANTOPEN] | open -> errno 40 ELOOP | dir writable by uid 10000
D path is a directory | OperationalError: unable to open database file [SQLITE_CANTOPEN] | open -> errno 21 EISDIR | dir writable by uid 10000
E component is a file | OperationalError: unable to open database file [SQLITE_CANTOPEN] | open -> errno 20 ENOTDIR | stat -> not a directory
euid/uid: 10000 10000 | umask: 0022
A/B repeat with a 0755 dir the process owns: OKFive different kernel errors, one SQLite string. This table is the whole diagnostic method: when you see unable to open database file, run the same two probes against the path from your configuration and you will have errno 13 EACCES or errno 2 ENOENT in front of you instead of a guess. Case C symlink loop is the one that costs the most time in practice, because a deployment that swaps a symlinked release directory can leave the link pointing at itself, and ELOOP is not a permission problem at all.
The last two lines set the baseline: the process is uid 10000, its umask is 0022, and the identical fixture under a directory the process owns opens without complaint. Nothing else changed between the failing and passing runs except the mode of one directory.
The neighbouring branch, and the sidecar files nobody thinks about
Two SQLite result codes point in different directions and it is worth knowing which is which before you start changing modes. SQLITE_READONLY_DIRECTORY (1544) is documented as "the database is read-only because process does not have permission to create a journal file in the same directory as the database and the creation of a journal file is a prerequisite for writing". SQLITE_READONLY (8) is the plain case: the file itself cannot be opened for writing, and SQLite has already downgraded the handle to read-only to keep the connection alive. That silent downgrade is the reason an application can boot, serve reads, and fail on the first write.
The probe below shows the sidecars appearing and disappearing, what a read-only directory does to WAL, and the one branch that has nothing to do with permissions — a file of the right size with the wrong content.
#!/usr/bin/env python3
"""Sidecar files, WAL, and the not-a-database branch.
Deterministic: fixed base dir, recreated, relative labels only.
"""
import os
import shutil
import sqlite3
BASE = "/tmp/sqwal"
def reset(base):
"""Clean tree, even if a previous run left parts at mode 0555 or a symlink loop."""
for root, dirs, files in os.walk(base, topdown=False):
for name in dirs + files:
p = os.path.join(root, name)
if not os.path.islink(p):
os.chmod(p, 0o755)
os.chmod(root, 0o755)
shutil.rmtree(base, ignore_errors=True)
if os.path.isdir(base):
raise SystemExit("reset failed: %s survived" % base)
os.makedirs(base)
reset(BASE)
def try_sql(fn, label):
try:
print("%-42s %s" % (label, fn()))
except sqlite3.Error as exc:
print("%-42s %s: %s [%s]" % (label, type(exc).__name__, exc, getattr(exc, "sqlite_errorname", "?")))
def sidecars(path):
d = os.path.dirname(path)
return "sidecars: %s" % sorted(f for f in os.listdir(d) if f.startswith(os.path.basename(path)))
def journal_mode(path):
con = sqlite3.connect(path)
rows = con.execute("pragma journal_mode").fetchall()
con.close()
return "journal_mode -> %s" % (rows[0][0],)
def wal_write(path):
con = sqlite3.connect(path)
con.execute("pragma journal_mode=wal").fetchall()
con.execute("create table if not exists t(x)")
con.execute("insert into t values (1)")
con.commit()
con.close()
return "wal write OK"
def read_count(path):
con = sqlite3.connect(path)
n = con.execute("select count(*) from sqlite_master").fetchall()
con.close()
return n
# 1. DELETE-mode journal appears for the transaction, gone after commit
p1 = os.path.join(BASE, "del.db")
con = sqlite3.connect(p1)
con.execute("create table t(x)")
con.execute("insert into t values (1)")
print("%-42s %s" % ("journal during transaction", sidecars(p1)))
con.commit()
con.close()
print("%-42s %s" % ("journal after commit", sidecars(p1)))
# 2. WAL leaves sidecars WHILE THE CONNECTION IS OPEN; the last clean close
# checkpoints and removes them, and a read-only directory cannot create them
p2 = os.path.join(BASE, "wal.db")
try_sql(lambda: journal_mode(p2), "journal_mode on a writable dir")
con = sqlite3.connect(p2)
con.execute("pragma journal_mode=wal").fetchall()
con.execute("create table if not exists t(x)")
con.execute("insert into t values (1)")
con.commit()
print("%-42s %s" % ("wal sidecars, connection open", sidecars(p2)))
con.close()
print("%-42s %s" % ("wal sidecars, connection closed", sidecars(p2)))
rod = os.path.join(BASE, "ro")
os.makedirs(rod)
p3 = os.path.join(rod, "wal.db")
con = sqlite3.connect(p3)
con.execute("create table t(x)")
con.commit()
con.close()
os.chmod(rod, 0o555)
try_sql(lambda: journal_mode(p3), "journal_mode, read-only dir")
try_sql(lambda: wal_write(p3), "wal write, read-only dir")
try_sql(lambda: read_count(p3), "read count, read-only dir")
# 3. not-a-database, and a zero-byte file
pjunk = os.path.join(BASE, "junk.db")
with open(pjunk, "wb") as fh:
fh.write(b"SQLite format 3\x00" + b"\x00" * 84 + b"not really a database")
try_sql(lambda: read_count(pjunk), "corrupt magic header in the file")
ptext = os.path.join(BASE, "text.db")
with open(ptext, "w") as fh:
fh.write("hello, this is not a database\n")
try_sql(lambda: read_count(ptext), "plain text file as a database")
pzero = os.path.join(BASE, "zero.db")
open(pzero, "wb").close()
try_sql(lambda: read_count(pzero), "zero-byte file as a database")journal during transaction sidecars: ['del.db', 'del.db-journal']
journal after commit sidecars: ['del.db']
journal_mode on a writable dir journal_mode -> delete
wal sidecars, connection open sidecars: ['wal.db', 'wal.db-shm', 'wal.db-wal']
wal sidecars, connection closed sidecars: ['wal.db']
journal_mode, read-only dir journal_mode -> delete
wal write, read-only dir OperationalError: attempt to write a readonly database [SQLITE_READONLY_DIRECTORY]
read count, read-only dir [(1,)]
corrupt magic header in the file DatabaseError: file is not a database [SQLITE_NOTADB]
plain text file as a database DatabaseError: file is not a database [SQLITE_NOTADB]
zero-byte file as a database [(0,)]Two details in that transcript are worth carrying into an incident review. First, the WAL sidecars exist only while a connection is open: wal.db-wal and wal.db-shm appear beside wal.db and are checkpointed away when the last connection closes cleanly — a crash, not a tidy shutdown, is what leaves them behind for a read-only mount to block. Second, the read-only directory still allowed a read ([(1,)]) — so a monitoring probe that runs SELECT 1 against this volume is green while every write path in the application is dead.
The bottom three rows separate a content problem from a permission problem. A file with a valid 16-byte magic header and a corrupt body, and a plain text file with no header at all, both raise DatabaseError: file is not a database with SQLITE_NOTADB (26) — a different exception class from OperationalError, which is a cheap discriminator to put in your error mapping. A zero-byte file is not an error at all: SQLite treats it as a valid empty database and returns one row containing the count 0.
Where the failure lands: at connect(), or at the first write
This is the difference that decides how you monitor the service. The probe below separates the two stages explicitly: it first attempts sqlite3.connect(), then, only if that returns, runs the first write in its own try block.
#!/usr/bin/env python3
"""Where the failure lands: connect() vs first statement, and what to check instead.
Deterministic: fixed base dir, recreated, relative labels only.
"""
import os
import shutil
import sqlite3
import stat
BASE = "/tmp/sqwhere"
def reset(base):
"""Clean tree, even if a previous run left parts at mode 0555 or a symlink loop."""
for root, dirs, files in os.walk(base, topdown=False):
for name in dirs + files:
p = os.path.join(root, name)
if not os.path.islink(p):
os.chmod(p, 0o755)
os.chmod(root, 0o755)
shutil.rmtree(base, ignore_errors=True)
if os.path.isdir(base):
raise SystemExit("reset failed: %s survived" % base)
os.makedirs(base)
reset(BASE)
def stages(path, uri=False):
"""Report which of the two stages fails, and with what."""
first = None
try:
con = sqlite3.connect(path, uri=uri, timeout=1.0)
except sqlite3.Error as exc:
return "connect() FAILED -> %s [%s]" % (exc, getattr(exc, "sqlite_errorname", "?"))
try:
con.execute("create table if not exists t(x)")
con.execute("insert into t values (1)")
con.commit()
first = "first write OK"
except sqlite3.Error as exc:
first = "first write FAILED -> %s [%s]" % (exc, getattr(exc, "sqlite_errorname", "?"))
finally:
con.close()
return "connect() ok -> " + first
# 1. directory missing entirely
print("missing dir ", stages(os.path.join(BASE, "app", "data.db")))
# 2. directory present, mode 0555, no database file yet
rod = os.path.join(BASE, "ro"); os.makedirs(rod); os.chmod(rod, 0o555)
p2 = os.path.join(rod, "data.db")
print("dir 0555, no file ", stages(p2))
# 3. database file exists (0644), directory then turned 0555
ok = os.path.join(BASE, "ok"); os.makedirs(ok)
p3 = os.path.join(ok, "data.db")
con = sqlite3.connect(p3); con.execute("create table t(x)"); con.commit(); con.close()
os.chmod(ok, 0o555)
print("file 0644, dir 0555", stages(p3))
os.chmod(ok, 0o755)
# 4. file 0444, directory writable
p4 = os.path.join(ok, "ro.db")
con = sqlite3.connect(p4); con.execute("create table t(x)"); con.commit(); con.close()
os.chmod(p4, 0o444)
print("file 0444, dir 0755", stages(p4))
# 5. same two conditions, but opened read-only on purpose
print("file 0444, mode=ro ", stages("file:%s?mode=ro" % p4, uri=True))
print("dir 0555, mode=ro ", stages("file:%s?mode=ro" % p2, uri=True))missing dir connect() FAILED -> unable to open database file [SQLITE_CANTOPEN]
dir 0555, no file connect() FAILED -> unable to open database file [SQLITE_CANTOPEN]
file 0644, dir 0555 connect() ok -> first write FAILED -> attempt to write a readonly database [SQLITE_READONLY_DIRECTORY]
file 0444, dir 0755 connect() ok -> first write FAILED -> attempt to write a readonly database [SQLITE_READONLY]
file 0444, mode=ro connect() ok -> first write FAILED -> attempt to write a readonly database [SQLITE_READONLY]
dir 0555, mode=ro connect() FAILED -> unable to open database file [SQLITE_CANTOPEN]Read the first two rows against the next three. A missing directory and a directory at 0555 both fail inside connect() — the open call cannot create the file, so the connection object is never returned. The other three cases return a live connection, and the failure arrives one statement later, at the first write. That is why "the app starts, the health check passes, and nothing can be saved" is a coherent failure mode rather than a contradiction, and why a preflight belongs before the connection pool is built, not after the first failed request.
The last row is the read-only URI variant: mode=ro against a 0555 directory still fails at connect, because the file does not exist and mode=ro will not create it. Pair mode=ro with a path you have already provisioned.
The production case: a bind mount, a root-owned directory, a file that became a directory
Nearly every report of this error in a container stack has the same shape, and it is a uid mismatch rather than a typo: the process runs as a non-root user (uid 1000 in most base images) while the bind-mounted directory is owned by the host account that created it, often root, at mode 0755. Mode 0755 is not writable by uid 1000, so the first commit cannot create app.db-journal, and SQLite answers with the title of this article — or with attempt to write a readonly database, depending on whether the file already existed.
There is a second, nastier shape, and Docker's own documentation describes the mechanism without naming the symptom: if you use --volume to bind-mount a path "that does not yet exist on the Docker host, Docker automatically creates the directory on the host for you. It's always created as a directory." Mount ./data/app.db before that file exists and you get a directory named app.db, owned by root — and a directory at the database path is one of the four conditions measured above, which raises exactly SQLITE_CANTOPEN. The --mount form fails differently and more loudly, with the daemon's own error: docker: Error response from daemon: invalid mount config for type "bind": bind source path does not exist: /dev/noexist. Both quotes are from the Bind mounts page of the Docker Engine manual.
The configuration that avoids both is to create the directory in the image and hand it to the numeric uid the process will run as. Nothing here depends on a named account: Docker's USER accepts a bare uid:gid, so the base image does not need a useradd step and the container does not need a passwd entry:
FROM python:3.12-slim
# A numeric uid needs no passwd entry, so the image stays lean; what matters is
# that the data directory is owned by the same number the process runs as.
RUN mkdir -p /var/lib/app && chown 1000:1000 /var/lib/app
WORKDIR /srv/app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY . .
USER 1000:1000
ENV APP_DB=/var/lib/app/app.db
CMD ["python", "-m", "app.worker"]With Compose, the equivalent is a named volume plus the same numeric user. A named volume is initialised from the image's directory ownership, so the chown in the Dockerfile is what makes it writable at first start:
services:
app:
build: .
user: "1000:1000"
environment:
APP_DB: /var/lib/app/app.db
volumes:
- app-data:/var/lib/app
restart: unless-stopped
volumes:
app-data:If you must use a bind mount, the host side is a one-time chown against the container's uid and gid; doing it from inside the container cannot work, because the container has no privilege over the host inode. Two failure modes belong in the runbook: an SELinux host also needs the :Z label, and a :ro mount converts every write into the read-only branch, so reads keep working while every commit fails.
This section is documentation-cited, not measured: the host that produced the transcripts above has no Docker daemon, so the container behaviour described here was read from Docker's manual and is labelled as such. Everything before and after it was executed.
A preflight that reports the real reason
Given that the message is useless and the kernel has the answer, the fix that pays for itself is a startup check that produces the kernel's answer in human form. The tool below is deliberately small and has no dependencies; it inspects the parent directory first, because that is where the journal is created, and it never raises on a permission problem — it returns GO or FAIL with an exit code so a container entrypoint or a systemd ExecStartPre can act on it.
#!/usr/bin/env python3
"""Turn SQLITE_CANTOPEN into the kernel-level reason, before the app starts.
Deterministic: fixed base dir, recreated, relative labels only.
"""
import errno
import os
import stat
import sys
REASONS = {
errno.ENOENT: "no such file or directory",
errno.EACCES: "permission denied",
errno.ENOTDIR: "a path component is not a directory",
errno.EISDIR: "the path is a directory",
errno.ELOOP: "too many levels of symbolic links",
errno.EROFS: "read-only filesystem",
}
def explain_cantopen(db_path):
"""Return (ok, message). Never raises on a permission problem."""
db_path = os.path.abspath(db_path)
parent = os.path.dirname(db_path)
try:
st = os.stat(parent)
except OSError as exc:
return False, "%s: %s - create it before opening the database" % (
parent, REASONS.get(exc.errno, exc.strerror))
if not stat.S_ISDIR(st.st_mode):
return False, "%s is not a directory" % parent
if not os.access(parent, os.W_OK | os.X_OK):
return False, "%s is not writable by uid %d (mode %04o, owner uid %d) - " \
"SQLite must create the journal there" % (
parent, os.geteuid(), stat.S_IMODE(st.st_mode), st.st_uid)
if os.path.lexists(db_path):
try:
dst = os.stat(db_path)
except OSError as exc: # dangling symlink, symlink loop, ...
return False, "%s: %s" % (db_path, REASONS.get(exc.errno, exc.strerror))
if stat.S_ISDIR(dst.st_mode):
return False, "%s is a directory, not a database file" % db_path
if not os.access(db_path, os.R_OK | os.W_OK):
return False, "%s exists but is not readable+writable by uid %d " \
"(mode %04o, owner uid %d)" % (
db_path, os.geteuid(), stat.S_IMODE(dst.st_mode), dst.st_uid)
return True, "preflight OK: %s writable by uid %d, %s usable" % (
parent, os.geteuid(), db_path)
def main(argv):
if len(argv) != 2:
print("usage: sqlite-preflight <database-path>", file=sys.stderr)
return 2
ok, message = explain_cantopen(argv[1])
print("%-4s %s" % ("GO" if ok else "FAIL", message))
return 0 if ok else 1
if __name__ == "__main__":
raise SystemExit(main(sys.argv))Run it with no argument and it refuses to guess; the usage line and the non-zero exit are the contract:
usage: sqlite-preflight <database-path>The exit codes are the interface — 0 for GO, 1 for FAIL, 2 for a usage error, all three visible in the source above. Wire it into the entrypoint before the application starts, so the container refuses to boot with FAIL /var/lib/app is not writable by uid 1000 (mode 0755, owner uid 0) instead of serving requests that name the wrong file in every traceback.
Fixes that hold, and the case a mode cannot repair
- Fix the directory, not the file. SQLite needs write permission on the containing directory for the duration of every transaction, so directory mode
0770and file mode0660are the right shapes — you do not need0777, which hands the journal path to every account on the host. - But a mode is not a fix when the uid is wrong. A directory at
0775gives write access to its owner and group only; a container running as uid 1000 on a directory owned by someone else falls into the other class and getsr-x. That is why the repair ischown -R 1000:1000 ./data(orchgrp 1000plus0770) and never a barechmod. Avoiduser: rootas a workaround: it makes the volume work and leaves root-owned files the next deploy cannot rewrite. - Pre-create the file when you bind-mount a file.
install -m 0660 /dev/null ./data/app.dbbeforedocker compose upprevents Docker from creating a directory at that path. - For read-only replicas, use the URI parameters rather than
romounts if you can.file:...?mode=roneeds no write access at all, andimmutable=1additionally needs no locking — with the documented risk that a changed file returns wrong results instead of an error. - Treat
SQLITE_NOTADBas data loss, not as permissions. "file is not a database" is a different exception class; a restore, not a chmod, is the answer. - Do not silence the failure with
journal_mode=OFF. It removes the journal requirement and therefore hides the permissions problem while disabling atomic commit — the trade SQLite's own documentation describes as dropping rollback capability entirely.
Two cases share one rule: the volume must be writable by the uid that runs SQLite. When that uid owns the directory, an ordinary mode is enough — the block below runs as the owner (uid 10000 on this host), the preflight returns GO, and the WAL commit succeeds. When the owner is a different uid — the container case — no mode on this host can repair it: the fix is a root-side chown -R 1000:1000 ./data (or chgrp 1000 plus 0770), which the container cannot perform on a host inode. The script prints the permission class each uid falls into so the arithmetic is explicit: 0775 is rwx for the owner uid 10000 and r-x for a uid-1000 container, which is exactly the mismatch a chmod cannot repair.
#!/usr/bin/env bash
# Owner-matched volume: the process's uid owns the directory, so the mode
# and the journal both work. A uid mismatch (container uid != volume owner)
# is repaired by a root-side `chown -R 1000:1000 <volume>`, never by a mode:
# 0775 is still r-x for any uid outside the owner/group.
set -u
D=/tmp/sqfix
rm -rf "$D"
mkdir -p "$D/data"
touch "$D/data/app.db"
chmod 0755 "$D"
chmod 0775 "$D/data"
chmod 0660 "$D/data/app.db"
echo "--- before the app starts ---"
stat -c '%A %u:%g %n' "$D/data" "$D/data/app.db"
python3 /opt/data/c2cz/lab/20261008-sqlitecantopen/pub/b05.py "$D/data/app.db"
echo "preflight exit=$?"
echo "--- which permission class does each uid fall into? ---"
python3 - "$D/data" <<'PY'
import os, sys
st = os.stat(sys.argv[1])
for uid, gid, who in ((os.getuid(), os.getgid(), "this process"),
(1000, 1000, "a uid-1000 container")):
cls, shift = ("owner", 6) if uid == st.st_uid else \
(("group", 3) if gid == st.st_gid else ("other", 0))
bits = (st.st_mode >> shift) & 0o7
print("mode %04o owner uid %d | %-22s uid %-5d -> %-6s class, bits %s%s%s, writable=%s"
% (st.st_mode & 0o7777, st.st_uid, who, uid, cls,
"r" if bits & 4 else "-", "w" if bits & 2 else "-", "x" if bits & 1 else "-",
bool(bits & 2)))
PY
echo "--- the app then opens it ---"
python3 - "$D/data/app.db" <<'PY'
import sqlite3, sys
con = sqlite3.connect(sys.argv[1], timeout=5.0)
con.execute("pragma journal_mode=wal").fetchall()
con.execute("create table if not exists events(id integer primary key, at text)")
con.execute("insert into events(at) values (datetime('now'))")
con.commit()
print("rows:", con.execute("select count(*) from events").fetchone())
con.close()
PY
echo "--- files in the volume afterwards ---"
stat -c '%A %s %n' "$D/data" "$D/data/app.db"--- before the app starts ---
drwxrwxr-x 10000:10000 /tmp/sqfix/data
-rw-rw---- 10000:10000 /tmp/sqfix/data/app.db
GO preflight OK: /tmp/sqfix/data writable by uid 10000, /tmp/sqfix/data/app.db usable
preflight exit=0
--- which permission class does each uid fall into? ---
mode 0775 owner uid 10000 | this process uid 10000 -> owner class, bits rwx, writable=True
mode 0775 owner uid 10000 | a uid-1000 container uid 1000 -> other class, bits r-x, writable=False
--- the app then opens it ---
rows: (1,)
--- files in the volume afterwards ---
drwxrwxr-x 4096 /tmp/sqfix/data
-rw-rw---- 8192 /tmp/sqfix/data/app.dbField notes
- Reproduce before you reason: five faults wear one message, so a fix applied without the
stat/accessprobes is a coin flip. - Check the directory of the path in your configuration, not the path you think you configured — they differ more often than anyone admits behind a symlinked release directory.
- A connection that opens is not a writable database: the read-only branches survive
connect()and fail at the first commit. - In containers, read the uid from
/proc/self/statusinside the running process and compare it withstat -c '%u %a %n'on the host directory; guessing it is how the incident recurs after every image rebuild. - Related reading on this blog: the sibling SQLite failure SQLite
database is lockedwith four writers, the container permission model behind most of these cases in “Operation not permitted” in Docker is a capability wall, and the file-descriptor limits that surface when a write path retries in a loop in Too many open files (EMFILE).