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

# 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

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

# 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

# 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/

# 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/

# 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

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)}")

# 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

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")

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');
}

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

# 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*

# 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.*

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")

# 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

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)
}

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;
    }
}

# 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

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()

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();

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

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()