EC
SQLITE_READONLY_ROLLBACK
SQLiteERRORCriticalAccess ControlHIGH confidence

attempt to write a readonly database (hot journal present)

Production Risk

SQLITE_READONLY_ROLLBACK in production means the database pages are in an indeterminate state. Do not serve data from the database until the journal has been properly rolled back via a writable connection.

What this means

SQLITE_READONLY_ROLLBACK (extended code 264) occurs when SQLite opens a read-only database file but finds a hot journal (an unfinished rollback journal from a previously interrupted write transaction). To recover, SQLite must replay the journal to roll back the incomplete transaction — but because the database is read-only it cannot do so, leaving the database in an uncertain state.

Why it happens
  1. 1A write transaction was interrupted (crash, power loss) leaving a -journal file
  2. 2The database and journal files were then moved to a read-only location
  3. 3The application opened the file with SQLITE_OPEN_READONLY but the journal exists
How to reproduce

A journal file exists from a previous crash but the database is now read-only.

trigger — this will error
trigger — this will error
# 1. Create a database and simulate a crash (leave the journal)
import sqlite3, os
conn = sqlite3.connect('demo.db')
conn.execute('CREATE TABLE t (x INTEGER)')
# Simulate crash: kill the process before commit leaves demo.db-journal

# 2. Make the file read-only
os.chmod('demo.db', 0o444)

# 3. Try to open read-only — triggers SQLITE_READONLY_ROLLBACK
conn2 = sqlite3.connect('demo.db')
conn2.execute('SELECT * FROM t')

expected output

sqlite3.OperationalError: attempt to write a readonly database

Fix

Restore write permissions, recover, then re-apply read-only

WHEN When the database must be recovered before being served as read-only.

Restore write permissions, recover, then re-apply read-only
import sqlite3, os, stat

# Temporarily restore write permission
os.chmod('demo.db', stat.S_IRUSR | stat.S_IWUSR)

# Open as writable — SQLite will automatically replay the journal
conn = sqlite3.connect('demo.db')
conn.execute('SELECT * FROM t')  # journal rolled back transparently
conn.close()

# Re-apply read-only permission
os.chmod('demo.db', stat.S_IRUSR)

Why this works

When SQLite opens a writable connection and finds a hot journal, it automatically replays the journal to roll back the incomplete transaction. After this the database is consistent. The file can then be made read-only again.

What not to do

✕ Delete the journal file manually to get past the error

Deleting the journal without replaying it leaves the database pages in a partially-written state from the interrupted transaction. The database will appear to open successfully but contain corrupted or incomplete data.

Version notes
SQLite 3.8.0+

Extended code 264 introduced to distinguish this specific readonly scenario.

Sources

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All SQLite errors