How to fix SQLITE_READONLY_CANTLOCK: Unable to obtain a read lock in SQLite

# List all database-related files
ls -la /path/to/database.db*

# Example output (problematic permissions):
# -rw-r--r-- 1 user  group  8192 Dec 22 database.db
# -r--r--r-- 1 user  group   512 Dec 22 database.db-shm  <- READ-ONLY!
# -rw-r--r-- 1 user  group  4096 Dec 22 database.db-wal

# Test specific file permissions
ls -l /path/to/database.db-shm

# Check if process can write
if [ -w /path/to/database.db-shm ]; then
    echo "File is writable"
else
    echo "File is NOT writable - this is likely the problem"
fi

# Check directory permissions too
ls -ld /path/to/

# Get detailed permission info
stat /path/to/database.db-shm
stat /path/to/

# Look at "Access" permissions in output

# Add write permission to the shared memory file
chmod 664 /path/to/database.db-shm

# Verify the change
ls -l /path/to/database.db-shm
# Should now show: -rw-rw-r-- (0664)

# Ensure consistent permissions on all WAL-related files
chmod 664 /path/to/database.db
chmod 664 /path/to/database.db-wal
chmod 664 /path/to/database.db-shm

# Verify all files
ls -l /path/to/database.db*

# Check current ownership
ls -l /path/to/database.db*

# Change ownership to the user running the application
sudo chown myuser:mygroup /path/to/database.db*

# Also set directory ownership if needed
sudo chown myuser:mygroup /path/to/

# Verify
ls -l /path/to/

# For nginx/www-data user
sudo chown www-data:www-data /path/to/database.db*
sudo chmod 664 /path/to/database.db*
sudo chmod 755 /path/to/

# Verify the www-data user can access
su - www-data -s /bin/bash -c "ls -la /path/to/database.db"

import os
import stat

db_path = '/path/to/database.db'
shm_path = db_path + '-shm'

# Check if .db-shm is readable but not writable
if os.path.exists(shm_path):
    st = os.stat(shm_path)
    mode = stat.filemode(st.st_mode)
    print(f"Current permissions: {mode}")

    # Check if writable
    if not os.access(shm_path, os.W_OK):
        print("WARNING: .db-shm is not writable!")
        print("Try: chmod 664", shm_path)

# Check the directory's permissions
ls -ld /path/to/

# Good: drwxr-xr-x or drwxrwxr-x (755 or 775)
# Bad: dr-xr-xr-x (555 - read-only)

# Check if directory is writable by current user
if [ -w /path/to/ ]; then
    echo "Directory is writable"
else
    echo "Directory is NOT writable"
fi

# Add write permission to the directory
chmod 755 /path/to/

# Or with group write access
chmod 775 /path/to/

# Verify
ls -ld /path/to/

# Sometimes parent directories need permissions too
# Check each level
ls -ld /path/
ls -ld /path/to/

# Make sure each level is accessible and writable
chmod 755 /path/
chmod 755 /path/to/

# Check if filesystem is mounted read-only
mount | grep /path/

# Look for "ro" flag in output
# If found, the mount is read-only

# Remount as read-write (if you have permissions)
sudo mount -o remount,rw /path/

# Verify
mount | grep /path/

# First, make a backup
cp /path/to/database.db /path/to/database.db.backup

# Convert to DELETE mode (requires write access)
sqlite3 /path/to/database.db "PRAGMA journal_mode=DELETE;"

# Output should show: delete

# Verify the conversion
sqlite3 /path/to/database.db "PRAGMA journal_mode;"

# Checkpoint and cleanup
sqlite3 /path/to/database.db "PRAGMA wal_checkpoint(TRUNCATE);"
sqlite3 /path/to/database.db "VACUUM;"

# Remove the WAL files (they're no longer needed)
rm -f /path/to/database.db-wal
rm -f /path/to/database.db-shm

import sqlite3
import os

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

# Create backup first
import shutil
shutil.copy(db_path, db_path + '.backup')

try:
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Convert to DELETE mode
    cursor.execute("PRAGMA journal_mode=DELETE")
    mode = cursor.fetchone()[0]
    print(f"Journal mode: {mode}")

    # Checkpoint to clean up WAL
    cursor.execute("PRAGMA wal_checkpoint(TRUNCATE)")
    result = cursor.fetchone()
    print(f"Checkpoint: busy={result[0]}, log_size={result[1]}, checkpointed={result[2]}")

    # Optimize database
    cursor.execute("VACUUM")
    print("VACUUM complete")

    conn.close()

    # Remove WAL files
    for ext in ['-wal', '-shm']:
        path = db_path + ext
        if os.path.exists(path):
            os.remove(path)
            print(f"Removed {path}")

    print("Database converted successfully")
except Exception as e:
    print(f"Error: {e}")
    # Restore backup on failure
    shutil.copy(db_path + '.backup', db_path)

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

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

// Backup first
fs.copyFileSync(dbPath, dbPath + '.backup');

try {
    const db = new Database(dbPath);

    // Convert to DELETE mode
    const mode = db.pragma('journal_mode = DELETE');
    console.log('Journal mode:', mode);

    // Checkpoint
    const checkpoint = db.pragma('wal_checkpoint(TRUNCATE)');
    console.log('Checkpoint:', checkpoint);

    // Vacuum
    db.pragma('vacuum');
    console.log('VACUUM complete');

    db.close();

    // Remove WAL files
    ['-wal', '-shm'].forEach(ext => {
        const p = dbPath + ext;
        if (fs.existsSync(p)) {
            fs.unlinkSync(p);
            console.log('Removed ' + path.basename(p));
        }
    });

    console.log('Database converted successfully');
} catch (error) {
    console.error('Error:', error);
    fs.copyFileSync(dbPath + '.backup', dbPath);
}

# First, ensure no processes are using the database
# Use lsof to check
lsof | grep database.db

# If database is not in use, delete the .db-shm file
rm -f /path/to/database.db-shm

# SQLite will recreate it with proper permissions when next opened
# Try opening the database
sqlite3 /path/to/database.db "PRAGMA integrity_check;"

# Check if .db-shm was recreated
ls -l /path/to/database.db-shm

# Check which processes have database file open
lsof /path/to/database.db

# If processes are listed, stop them first
# Example for Node.js app
pkill -f "node app.js"

# Or graceful restart for web server
sudo systemctl restart nginx
sudo systemctl restart apache2

# Then delete and recreate
rm -f /path/to/database.db-shm

import os
import sqlite3
import time

db_path = '/path/to/database.db'
shm_path = db_path + '-shm'

# Check if process is using the database
def check_process_lock(db_path):
    try:
        conn = sqlite3.connect(db_path, timeout=1.0)
        conn.execute("PRAGMA query_only=1")
        conn.close()
        return False
    except sqlite3.OperationalError as e:
        if 'database is locked' in str(e):
            return True
        raise

# If safe, delete and recreate
try:
    if check_process_lock(db_path):
        print("Database is locked - wait for processes to finish")
        time.sleep(5)

    # Delete the .db-shm file
    if os.path.exists(shm_path):
        os.remove(shm_path)
        print(f"Deleted {shm_path}")

    # Open database to trigger recreation
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA integrity_check")
    conn.close()

    # Verify recreation
    if os.path.exists(shm_path):
        st = os.stat(shm_path)
        mode = oct(st.st_mode)[-3:]
        print(f".db-shm recreated with permissions {mode}")

except Exception as e:
    print(f"Error: {e}")

# Check if SELinux is enabled
getenforce
# Output: Enforcing, Permissive, or Disabled

# Check SELinux context of database files
ls -Z /path/to/database.db*

# Look for context like:
# unconfined_u:object_r:user_home_t:s0 database.db
# unconfined_u:object_r:user_home_t:s0 database.db-shm

# Set appropriate context for web application
# For Apache/httpd
sudo chcon -t httpd_sys_rw_content_t /path/to/database.db*
sudo chcon -t httpd_sys_rw_content_t /path/to/

# For systemd service
sudo chcon -t admin_home_t /path/to/database.db*

# Or use semanage for persistent rules
sudo semanage fcontext -a -t httpd_sys_rw_content_t "/path/to/database(/.*)?"
sudo restorecon -R /path/to/

# Verify changes
ls -Z /path/to/database.db*

# Check audit logs for recent SELinux denials
sudo ausearch -m avc -ts recent | grep database

# If denials found, generate policy
sudo ausearch -m avc -ts recent | audit2allow -M database_policy
sudo semodule -i database_policy.pp

# List active policies
sudo semodule -l | grep database

# Check if AppArmor is active
sudo aa-status

# Check your application's profile
sudo aa-status | grep "your-app"

# See denials
sudo tail -f /var/log/audit/audit.log | grep APPARMOR

# Edit the profile (example for Node.js)
sudo nano /etc/apparmor.d/usr.bin.node

# Add these lines to allow database access:
# /path/to/database.db rw,
# /path/to/database.db-wal rw,
# /path/to/database.db-shm rw,
# /path/to/ r,

# Reload the profile
sudo apparmor_parser -r /etc/apparmor.d/usr.bin.node

# For testing, use complain mode (logs but doesn't enforce)
sudo aa-complain /usr/bin/node

# Check for denials
sudo tail -f /var/log/audit/audit.log

# Dockerfile
FROM node:18

WORKDIR /app
COPY package*.json ./
RUN npm install

COPY . .

# Create database directory with proper permissions
RUN mkdir -p /app/data && chmod 755 /app/data

# Copy pre-converted database (DELETE mode, not WAL)
# COPY database.db /app/data/
# RUN chmod 644 /app/data/database.db

# Run as non-root user
RUN useradd -m dbuser
USER dbuser

CMD ["node", "server.js"]

version: '3.8'
services:
  app:
    build: .
    volumes:
      # Mount database directory as volume
      - ./database:/app/data
    environment:
      - DATABASE_PATH=/app/data/database.db
    user: "node"  # Run as specific user

# Before running container, ensure database directory on host is writable
chmod 755 ./database
chmod 644 ./database/database.db

# Or use Docker volume with specific permissions
docker volume create --opt type=tmpfs --opt device=tmpfs --opt o=rw my_db_vol

apiVersion: v1
kind: Pod
metadata:
  name: app
spec:
  containers:
  - name: app
    image: myapp:latest
    volumeMounts:
    - name: database-storage
      mountPath: /app/data
    securityContext:
      runAsUser: 1000
      runAsGroup: 1000
      fsGroup: 1000  # Ensures volume permissions match pod user
  volumes:
  - name: database-storage
    persistentVolumeClaim:
      claimName: database-pvc

[Unit]
Description=Database Application
After=network.target

[Service]
Type=simple
User=dbapp
Group=dbapp

# Use StateDirectory to auto-create with correct permissions
StateDirectory=dbapp
# This creates /var/lib/dbapp owned by dbapp:dbapp

WorkingDirectory=/opt/dbapp
Environment="DATABASE_PATH=/var/lib/dbapp/database.db"

ExecStart=/usr/bin/node server.js

[Install]
WantedBy=multi-user.target