How to fix SQLITE_IOERR_SHORT_READ: Read returned less data than requested in SQLite

# Check file size
ls -lh /path/to/your/database.db

# Get detailed file information
stat /path/to/your/database.db

# Check if file is empty or very small
du -h /path/to/your/database.db

# Compare with expected size (if you have a backup)
ls -lh /path/to/backup.db /path/to/your/database.db

# SQLite header should start with "SQLite format 3"
head -c 16 /path/to/your/database.db | xxd

# Or use sqlite3 to check
sqlite3 /path/to/your/database.db "PRAGMA integrity_check;"

# Try to dump the database schema and data
sqlite3 /path/to/corrupted.db ".dump" > recovery.sql

# Check if dump was successful
head -20 recovery.sql

# Try to dump specific tables that might still be readable
sqlite3 /path/to/corrupted.db ".schema" > schema_only.sql

# Attempt to read individual tables
for table in $(sqlite3 /path/to/corrupted.db ".tables"); do
  echo "Attempting to dump table: $table"
  sqlite3 /path/to/corrupted.db ".dump $table" >> partial_recovery.sql 2>/dev/null || echo "Failed to dump $table"
done

# If you got any data, create a new database
sqlite3 new_database.db < recovery.sql

# Verify the new database
sqlite3 new_database.db "SELECT count(*) FROM sqlite_master;"

PRAGMA integrity_check;

PRAGMA cell_size_check = ON;
PRAGMA foreign_key_check;

# Check WAL mode status
sqlite3 database.db "PRAGMA journal_mode;"

# If using WAL, try checkpoint and mode change
sqlite3 database.db "PRAGMA wal_checkpoint(TRUNCATE);"
sqlite3 database.db "PRAGMA journal_mode = DELETE;"
sqlite3 database.db "PRAGMA journal_mode = WAL;"

# Look for sqlite3_recover or similar
which sqlite3_recover

# Usage example
sqlite3_recover database.db recovered.db

# Clone the database (may skip corrupted pages)
sqlite3 corrupted.db ".clone new.db"

# Or use the backup API
sqlite3 corrupted.db ".backup backup.db"

# If you have regular backups
cp /path/to/backup/database.db /path/to/production/database.db

# Or restore from automated backup system
# (implementation depends on your backup strategy)

# 1. Move corrupted database aside
mv database.db database.db.corrupted

# 2. Let application create new database
# (Start your application - it should create new tables)

# 3. If application doesn't auto-create, restore schema
sqlite3 new_database.db < schema.sql

# 4. Import any recovered data
sqlite3 new_database.db < partial_recovery.sql

# Recreate from migration files
npm run db:migrate:fresh
# or
npx prisma db push --force-reset

# Linux: Check filesystem for errors
sudo fsck /dev/sdX

# Check disk SMART status
sudo smartctl -a /dev/sdX

# Monitor disk I/O errors
dmesg | grep -i error
journalctl -k | grep -i "disk|io|error"

df -h /path/to/database
df -i /path/to/database  # Check inode usage

# Memory errors
dmesg | grep -i "memory|ecc"

# RAID status (if applicable)
cat /proc/mdstat
sudo mdadm --detail /dev/mdX

#!/bin/bash
# Simple backup script
BACKUP_DIR="/backups/sqlite"
DB_PATH="/app/data/database.db"

# Daily backup with retention
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
sqlite3 "$DB_PATH" ".backup $BACKUP_DIR/db_backup_$TIMESTAMP.db"

# Keep last 7 days
find "$BACKUP_DIR" -name "db_backup_*.db" -mtime +7 -delete

#!/bin/bash
# Cron job to check database integrity
DB_PATH="/app/data/database.db"
LOG_FILE="/var/log/db-integrity.log"

INTEGRITY_CHECK=$(sqlite3 "$DB_PATH" "PRAGMA integrity_check;" 2>&1)

if [[ "$INTEGRITY_CHECK" != "ok" ]]; then
    echo "$(date): Database integrity check failed: $INTEGRITY_CHECK" >> "$LOG_FILE"
    # Alert system administrator
    echo "Database corruption detected!" | mail -s "DB Alert" [email protected]
fi

-- Always use transactions for multiple operations
BEGIN TRANSACTION;
-- Your operations here
COMMIT;

-- Consider synchronous settings for durability
PRAGMA synchronous = NORMAL;  -- Good balance of speed and safety
-- Or for critical data:
PRAGMA synchronous = FULL;    -- Maximum durability, slower