attempt to write a readonly database (hot journal present)

Category: Access ControlVersions: SQLite 3.8.0+

🔴 Production Risk Error

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

1A write transaction was interrupted (crash, power loss) leaving a -journal file
2The database and journal files were then moved to a read-only location
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

# 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')

sqlite3.OperationalError: attempt to write a readonly database

Fix 1: Restore write permissions, recover, then re-apply read-only

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

fix

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

Why it's wrong: 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.

Dangerous variant

⚠️ Warning

Manually deleting the hot journal without rollback leaves the database in a corrupted state that may not be immediately detectable by PRAGMA integrity_check.

Sources

📚 Official docs: https://www.sqlite.org/rescode.html#readonly_rollback

🔧 Source ref: sqlite3.h — SQLITE_READONLY_ROLLBACK = 264

📖 Further reading: SQLite how to corrupt a database file

Confidence assessment

✅ HIGH confidence

Well-documented. The hot journal rollback mechanism is described in the atomic commit documentation.