How to fix SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mode in SQLite

SQLiteINTERMEDIATEHIGHUpdated Dec 29, 2025· 181 views

The SQLITE_READONLY_ROLLBACK error occurs when SQLite detects a hot journal (an incomplete rollback journal from a previous crash) but cannot complete the rollback because the database is opened in read-only mode or the filesystem is read-only. This error prevents database access until the journal is either completed or removed.

Quick fix

Fixes SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mode

# List database files and check for journal
ls -la /path/to/database.db*

# Example output:
# -r--r--r-- 1 user group 8192 Dec 22 10:15 database.db
# -r--r--r-- 1 user group 2048 Dec 22 10:14 database.db-journal

# Check if journal is "hot" (modification time is recent and after database file)
stat /path/to/database.db-journal

Verify file permissions and identify journal files
Make database directory writable and fix file permissions
Complete the rollback by opening with write permissions
Remove stale hot journal if rollback is impossible

sqlite — error

$# List database files and check for journal
ls -la /path/to/database.db*
# Example output:
# -r--r--r-- 1 user group 8192 Dec 22 10:15 database.db
# -r--r--r-- 1 user group 2048 Dec 22 10:14 database.db-journal
# Check if journal is "hot" (modification time is recent and after database file)
stat /path/to/database.db-journal
SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mode

What this error means

The "SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mode" error is an extended result code of SQLITE_READONLY that indicates SQLite cannot complete critical database recovery operations. **Understanding Hot Journals**: A "hot journal" is a rollback journal file that remains after a database connection crashed or was terminated unexpectedly during a write operation. Unlike a normal journal that is deleted after a successful transaction, a hot journal indicates incomplete work that must be rolled back to restore the database to a consistent state. SQLite uses journals (also called "journal files") to implement ACID properties: 1. Before modifying the database, original pages are copied to the journal 2. If the transaction completes successfully, the journal is deleted 3. If the process crashes, the journal persists with the original data 4. On next open, SQLite replays the journal to restore the pre-transaction state **Why This Error Occurs**: This error happens when: 1. **Database opened read-only**: The database file was opened with read-only permissions (via URI parameter, application settings, or file permissions), and a hot journal exists that needs rollback. 2. **Filesystem is read-only**: The database files are on read-only media (CD-ROM, read-only mount, write-protected USB) where SQLite cannot access or modify the journal file. 3. **Insufficient permissions**: The process user has read permissions on the database file but cannot write to the journal file or the containing directory. 4. **Previous crash with hot journal**: A previous database connection crashed or was forcefully terminated, leaving the journal incomplete. **Why SQLite Can't Ignore It**: SQLite cannot simply ignore the hot journal and proceed because: - The database file may contain uncommitted changes that would violate data integrity - Readers might see partially written data from the failed transaction - Without completing the rollback, the database is in an inconsistent state - Later operations might corrupt the database further SQLite must either: 1. Complete the rollback to restore consistency, OR 2. Refuse access to protect database integrity Since it cannot complete the rollback (read-only restriction), it refuses the connection.

Typical symptoms

Database connection fails with "SQLITE_READONLY_ROLLBACK" error when opening a previously working database
Error occurs after the application or database process crashes unexpectedly
Database works with write permissions but fails when opened read-only
Error persists even after restarting the application
Seeing ".db-journal" file in the database directory alongside the main ".db" file
Network or mount point shows files as read-only
Mobile app fails to open bundled database file
Docker container cannot access database in mounted volume

Common causes

Database file opened in read-only mode (via URI parameters or application settings) while a hot journal exists
Database files located on read-only filesystem or media (CD-ROM, read-only mounted partition)
File permissions on database or journal files restrict write access for the process user
Previous database connection crashed or was terminated without properly closing, leaving incomplete journal
Database deployed to mobile app bundle, assets directory, or other read-only locations
Directory containing database files lacks write permissions (needed to delete/modify journal)
Database files on network filesystem (NFS, SMB) with permission or locking issues
Container or sandbox environment with read-only volume mount
Stale journal file from previous version or corrupted database state

How to fix "SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mode"

1Verify file permissions and identify journal files

First, check if a hot journal file exists and assess file permissions.

On Linux/macOS:

bash

# List database files and check for journal
ls -la /path/to/database.db*

# Example output:
# -r--r--r-- 1 user group 8192 Dec 22 10:15 database.db
# -r--r--r-- 1 user group 2048 Dec 22 10:14 database.db-journal

# Check if journal is "hot" (modification time is recent and after database file)
stat /path/to/database.db-journal

On Windows (Command Prompt):

cmd

# List files in database directory
dir C:\path\to\database.*

# Check file properties
wmic datafile where name="C:\\path\\to\\database.db-journal" get LastModified

Check directory permissions:

bash

# Check if directory is writable
ls -ld /path/to/
# Output should show 'w' permission for the appropriate user/group

# Test write permissions
touch /path/to/test-file.txt
rm /path/to/test-file.txt

Check if database is on read-only mount:

bash

# On Linux, check mount options
mount | grep /path/to

# Look for "ro" (read-only) in the output
# Example: /dev/sda1 on /media/data type ext4 (ro)

# Check current working filesystem
df /path/to/database.db

2Make database directory writable and fix file permissions

Ensure the database file, journal file, and containing directory have proper write permissions.

Linux/macOS - Fix file permissions:

bash

# Make database file writable
chmod 644 /path/to/database.db
chmod 644 /path/to/database.db-journal

# Make directory writable (execute permission allows listing/entering)
chmod 755 /path/to/

# If owned by different user, change ownership
sudo chown user:group /path/to/database.db*
sudo chown user:group /path/to/

For application servers (e.g., running as www-data or specific user):

bash

# Determine the application user
ps aux | grep your-app
# or
id your-app-user

# Set correct ownership and permissions
sudo chown www-data:www-data /path/to/database.db*
sudo chown www-data:www-data /path/to/
sudo chmod -R 775 /path/to/

Windows - Fix permissions via GUI:
1. Right-click the database file → Properties → Security
2. Click Edit → select your user → check "Full Control" → Apply

Windows - Fix via Command Prompt (run as Administrator):

cmd

# Grant write permissions to current user
icacls C:\path\to\database.db /grant %USERNAME%:F

# Grant write permissions to directory
icacls C:\path\to /grant %USERNAME%:F /T /C

Verify permissions programmatically:

python

import os

db_path = '/path/to/database.db'
journal_path = db_path + '-journal'
db_dir = os.path.dirname(db_path)

print(f"Database writable: {os.access(db_path, os.W_OK)}")
print(f"Journal writable: {os.access(journal_path, os.W_OK)}")
print(f"Directory writable: {os.access(db_dir, os.W_OK)}")

3Complete the rollback by opening with write permissions

Once file permissions are fixed, open the database with write access to complete the hot journal rollback.

Using sqlite3 command-line tool:

bash

# Simply opening and closing forces rollback completion
sqlite3 /path/to/database.db ".exit"

# Or run a dummy operation
sqlite3 /path/to/database.db "SELECT 1;"

# Verify journal was cleaned up
ls -la /path/to/database.db*
# The .db-journal file should be gone

Using Python:

python

import sqlite3
import os

db_path = '/path/to/database.db'

try:
    # Open database with write permissions (default mode)
    conn = sqlite3.connect(db_path)

    # Perform a simple query to complete recovery
    cursor = conn.cursor()
    cursor.execute("SELECT 1")
    result = cursor.fetchone()

    # Check database integrity
    cursor.execute("PRAGMA integrity_check")
    integrity = cursor.fetchone()[0]
    print(f"Database integrity: {integrity}")

    # Close connection (this will clean up journal)
    conn.close()

    print("Rollback completed successfully")

    # Verify journal is gone
    journal_path = db_path + '-journal'
    if os.path.exists(journal_path):
        print("Warning: journal file still exists")
    else:
        print("Journal file cleaned up")

except sqlite3.OperationalError as e:
    print(f"Failed to complete rollback: {e}")
    print("Ensure database directory has write permissions")

Using Node.js with better-sqlite3:

javascript

const Database = require('better-sqlite3');
const fs = require('fs');
const path = require('path');

const dbPath = '/path/to/database.db';

try {
  // Open database with write permissions (default)
  const db = new Database(dbPath);

  // Perform a simple query to trigger recovery
  const stmt = db.prepare('SELECT 1');
  const result = stmt.get();

  // Check integrity
  const integrity = db.pragma('integrity_check');
  console.log('Database integrity:', integrity);

  db.close();
  console.log('Rollback completed successfully');

  // Verify journal is gone
  const journalPath = dbPath + '-journal';
  if (fs.existsSync(journalPath)) {
    console.warn('Warning: journal file still exists');
  } else {
    console.log('Journal file cleaned up');
  }
} catch (error) {
  console.error('Failed to complete rollback:', error.message);
  console.error('Ensure database directory has write permissions');
}

Using Ruby:

ruby

require 'sqlite3'

db_path = '/path/to/database.db'

begin
  # Open database with write permissions
  db = SQLite3::Database.new db_path

  # Perform a simple operation to complete recovery
  result = db.execute("SELECT 1")

  # Check integrity
  integrity = db.execute("PRAGMA integrity_check")
  puts "Database integrity: #{integrity[0][0]}"

  db.close
  puts "Rollback completed successfully"

  # Verify journal is gone
  journal_path = db_path + '-journal'
  unless File.exist?(journal_path)
    puts "Journal file cleaned up"
  else
    puts "Warning: journal file still exists"
  end

rescue SQLite3::Exception => e
  puts "Failed to complete rollback: #{e}"
  puts "Ensure database directory has write permissions"
end

4Remove stale hot journal if rollback is impossible

If you cannot gain write permissions and need to proceed, you can forcefully remove the hot journal. Use with caution: this bypasses SQLite's safety checks and may result in data loss if the journal contains important uncommitted changes.

Only do this if:
- The database is backed up
- You're certain the journal is stale (not from a recent crash)
- The application has other recovery mechanisms
- You can afford potential data loss

On Linux/macOS:

bash

# Backup the database first
cp /path/to/database.db /path/to/database.db.backup
cp /path/to/database.db-journal /path/to/database.db-journal.backup

# Remove the hot journal
rm -f /path/to/database.db-journal

# Verify
ls -la /path/to/database.db*

On Windows:

cmd

# Backup the database first
copy C:\path\to\database.db C:\path\to\database.db.backup
copy C:\path\to\database.db-journal C:\path\to\database.db-journal.backup

# Remove the hot journal
del C:\path\to\database.db-journal

# Verify
dir C:\path\to\database.*

Using Python:

python

import os
import shutil
from datetime import datetime

db_path = '/path/to/database.db'
journal_path = db_path + '-journal'

if os.path.exists(journal_path):
    # Backup first
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    backup_journal = journal_path + f'.backup_{timestamp}'
    shutil.copy2(journal_path, backup_journal)
    print(f"Journal backed up to: {backup_journal}")

    # Remove journal
    os.remove(journal_path)
    print("Hot journal removed")
else:
    print("No journal file found")

After removing the journal:
- Open the database normally with write permissions to verify integrity
- Check the database is still accessible and data is intact
- Monitor for any anomalies or missing data
- Keep the backup until confident the database is stable

5Copy database from read-only location to writable location

If the database is on read-only media, copy it to a writable location and update your application configuration.

For read-only filesystem mounts:

bash

# Remount as read-write (requires appropriate permissions)
sudo mount -o remount,rw /path/to/mount

# Or identify a writable location
mkdir -p /var/lib/myapp/database
cp /path/to/database.db /var/lib/myapp/database/

# Update application configuration to use new location
# Edit config file or environment variable
export DATABASE_PATH=/var/lib/myapp/database/database.db

For mobile apps (iOS):

swift

import Foundation

func setupDatabaseFromBundle() -> URL? {
    let fileManager = FileManager.default
    let documentsURL = fileManager.urls(for: .documentDirectory, in: .userDomainMask)[0]
    let dbURL = documentsURL.appendingPathComponent("database.db")

    // Only copy if not already in Documents
    if !fileManager.fileExists(atPath: dbURL.path) {
        guard let bundleURL = Bundle.main.url(forResource: "database", withExtension: "db") else {
            print("Database not found in bundle")
            return nil
        }

        do {
            try fileManager.copyItem(at: bundleURL, to: dbURL)
            print("Database copied to: \(dbURL.path)")
        } catch {
            print("Failed to copy database: \(error)")
            return nil
        }
    }

    return dbURL
}

// Use the database from writable location
if let dbURL = setupDatabaseFromBundle() {
    // Open database from Documents directory
    let db = try Connection(dbURL.path)
}

For mobile apps (Android):

java

import android.content.Context;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class DatabaseSetup {
    public static File getDatabasePath(Context context) throws IOException {
        File writableDbFile = new File(context.getFilesDir(), "database.db");

        // Copy from assets if not already in app files
        if (!writableDbFile.exists()) {
            try (FileInputStream fis = new FileInputStream(
                    new File(context.getAssets(), "database.db"));
                 FileOutputStream fos = new FileOutputStream(writableDbFile)) {

                byte[] buffer = new byte[1024];
                int length;
                while ((length = fis.read(buffer)) > 0) {
                    fos.write(buffer, 0, length);
                }

                System.out.println("Database copied to: " + writableDbFile.getPath());
            }
        }

        return writableDbFile;
    }
}

For Docker volumes:

bash

# Copy database from read-only bind mount to writable volume
docker cp readonly_db:/data/database.db writable_volume:/database.db

# Or in docker-compose.yml, use volumes instead of bind mounts
volumes:
  - database_data:/data  # Named volume (writable)

# Instead of:
  # - ./readonly_db:/data:ro  # Read-only bind mount

6Open database explicitly with write permissions in application

Ensure your application code explicitly opens the database with write permissions rather than read-only mode.

Python - sqlite3:

python

import sqlite3

# INCORRECT: Opens read-only (causes SQLITE_READONLY_ROLLBACK)
# conn = sqlite3.connect('file:database.db?mode=ro', uri=True)

# CORRECT: Opens with write permissions (allows recovery)
conn = sqlite3.connect('database.db')  # Default is read-write

# Or explicitly specify URI mode
conn = sqlite3.connect('file:database.db?mode=rw', uri=True)

# After opening, verify you can write
cursor = conn.cursor()
cursor.execute("PRAGMA journal_mode")
journal_mode = cursor.fetchone()[0]
print(f"Journal mode: {journal_mode}")  # Should be "delete" or "wal"

conn.close()

Node.js - better-sqlite3:

javascript

const Database = require('better-sqlite3');

// INCORRECT: Opens read-only
// const db = new Database('database.db', { readonly: true });

// CORRECT: Opens with write permissions
const db = new Database('database.db');  // Default is read-write

// Or explicitly specify
const db = new Database('database.db', { readonly: false });

// Verify you have write access
const journalMode = db.pragma('journal_mode');
console.log('Journal mode:', journalMode);

db.close();

Ruby - sqlite3 gem:

ruby

require 'sqlite3'

# INCORRECT: Opens read-only
# db = SQLite3::Database.new 'database.db', {readonly: true}

# CORRECT: Opens with write permissions (default)
db = SQLite3::Database.new 'database.db'

# Verify journal mode
journal_mode = db.execute("PRAGMA journal_mode")[0][0]
puts "Journal mode: #{journal_mode}"

db.close

Go - mattn/go-sqlite3:

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
)

// INCORRECT: Opens read-only
// db, err := sql.Open("sqlite3", "file:database.db?mode=ro")

// CORRECT: Opens with write permissions
db, err := sql.Open("sqlite3", "database.db")  // Default is read-write
if err != nil {
    panic(err)
}

// Or explicitly specify
db, err := sql.Open("sqlite3", "file:database.db?mode=rw&_journal_mode=WAL")

defer db.Close()

Advanced notes

Journal File Mechanics:

SQLite uses a journal (rollback journal) to implement ACID properties. Before modifying the database, it follows this sequence:

1. Write original data to journal: Before changing any database page, the original content is written to the journal file
2. Modify database: The actual changes are written to the database file
3. Commit or rollback: On success, the journal is deleted; on failure or crash, the journal can replay the original data to undo changes

A "hot journal" is one that remains on disk after an unexpected termination, indicating incomplete work.

Different from WAL Mode:

In older DELETE journal mode, the journal file is always cleaned up when the transaction completes.

In WAL (Write-Ahead Logging) mode, a different mechanism is used (WAL file instead of journal), and hot journals don't apply in the same way. If you have SQLITE_READONLY_ROLLBACK with WAL mode, see the related error article on SQLITE_READONLY_RECOVERY.

Filesystem Considerations:

Hot journal rollback requires three operations:
1. Read the journal file
2. Modify the database file
3. Delete the journal file

If any of these fail, the rollback cannot complete. Common scenarios:

| Scenario | Issue | Solution |
|----------|-------|----------|
| Read-only filesystem | Cannot delete journal | Remount as read-write or copy to writable location |
| Insufficient file permissions | Cannot read/write/delete | Use chmod/chown to fix permissions |
| Network filesystem | Locking or permission issues | Check network connectivity and mount options |
| Mobile app bundle | Read-only location | Copy database to writable app directory |
| Docker volume | Ownership mismatch | Ensure volume owner matches container user |

Prevention Strategies:

1. Proper shutdown: Always close database connections gracefully
2. Error handling: Catch exceptions and ensure cleanup happens
3. Process management: Avoid force-killing database processes
4. Monitoring: Log any stale journal files detected
5. Regular maintenance: Periodically verify database integrity
6. Backup strategy: Keep recent backups for recovery

Example of proper resource management:

Python context manager:

python

# Always use context managers for resource cleanup
with sqlite3.connect('database.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM table")
    # Connection automatically closed and cleaned up

Node.js try-finally:

javascript

const db = new Database('database.db');
try {
  const stmt = db.prepare('SELECT * FROM table');
  const rows = stmt.all();
  console.log(rows);
} finally {
  db.close();  // Ensures cleanup even if error occurs
}

Corrupted Database After Failed Rollback:

If the database is corrupted after a failed rollback attempt, SQLite provides recovery options:

bash

# Check integrity
sqlite3 database.db "PRAGMA integrity_check;"
# Output: "ok" or list of errors

# If errors found, use the RECOVER function (SQLite 3.44.0+)
sqlite3 database.db "PRAGMA integrity_check;"
sqlite3 database.db "PRAGMA writable_schema = ON;"
sqlite3 database.db "PRAGMA writable_schema = OFF;"

# Or use .recover mode (SQLite 3.37.0+)
sqlite3 -recover database.db > recovered.db

Monitoring for Hot Journals:

Implement monitoring to detect stale hot journals:

python

import os
import time
from pathlib import Path

db_dir = Path('/path/to/database')

# Check for old hot journals
for db_file in db_dir.glob('*.db'):
    journal_file = Path(str(db_file) + '-journal')
    if journal_file.exists():
        age_seconds = time.time() - journal_file.stat().st_mtime
        age_hours = age_seconds / 3600
        if age_hours > 1:
            print(f"Stale hot journal detected: {journal_file} ({age_hours:.1f} hours old)")
            # Log for manual review or automated cleanup

Frequently asked questions

What does "SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mod" mean?: The "SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mode" error is an extended result code of SQLITE_READONLY that indicates SQLite cannot complete critical database recovery operations. Understanding Hot Journals: A "hot journal" is a rollback journal file that remains after a database connection crashed or was terminated unexpectedly during a write operation. Unlike a normal journal that is deleted after a successful transaction, a hot journal indicates incomplete work that must be rolled back to restore the database to a consistent state.
How do I fix "SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mod"?: The SQLITE_READONLY_ROLLBACK error occurs when SQLite detects a hot journal (an incomplete rollback journal from a previous crash) but cannot complete the rollback because the database is opened in read-only mode or the filesystem is read-only. This error prevents database access until the journal is either completed or removed.
What causes "SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mod"?: Database file opened in read-only mode (via URI parameters or application settings) while a hot journal exists Database files located on read-only filesystem or media (CD-ROM, read-only mounted partition) File permissions on database or journal files restrict write access for the process user
Is the "SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mod" error serious?: Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading

📚SQLite Official Documentation
📖
sqlite.org - rescode(Official Docs)
📖
sqlite.org - appfileformat(Official Docs)
📖
sqlite.org - wal(Official Docs)
📖
sqlite.org - howtocmpl(Official Docs)

How to fix SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mode in SQLite

Quick fix

What this error means

Typical symptoms

Common causes

How to fix "SQLITE_READONLY_ROLLBACK: Cannot roll back hot journal in readonly mode"

Advanced notes

Frequently asked questions

Related errors

Official resources & further reading