During a recent Roundcube webmail issue on a cPanel server, users experienced login errors or ERR_TOO_MANY_REDIRECTS in their browsers. Upon investigation, the root cause was traced to missing carddav_accounts table or missing columns in the carddav_accounts table within Roundcube’s user-level .rcube.db SQLite files.

This issue did not affect email delivery (IMAP/SMTP continued to function normally), but it broke webmail access for users relying on Roundcube.

 

The Problem

Roundcube (and specifically the CardDAV plugin) attempts to run SQL queries like:

INSERT INTO carddav_accounts("accountname", "username", ..., "discovery_url", "rediscover_time")
SELECT ... FROM carddav_accounts;

Errors observed in logs:

DB Error: [1] table carddav_accounts has no column named rediscover_time
DB Error: [1] table carddav_accounts has no column named discovery_url

This happened because .rcube.db files for some mail accounts were created with an outdated or incomplete schema, missing key columns or entire table.

The Fix

We developed two scripts:

1. Single DB Patch Script
patch_rcube_db.sh
This script:

• Creates the full carddav_accounts table if missing.
• Adds missing columns (rediscover_time, discovery_url, etc.) if the table exists.

2. Bulk Patch Script
patch_all_rcube_dbs.sh
This script:

• Scans all /home/*/etc/*/*.rcube.db files.
• Runs the single patch script on each database.

Logs results to patched_rcube_dbs.txt.

Outcome

• Webmail (Roundcube) access was restored for all users.
• No mail delivery was interrupted.
• Patched DBs were backed up and validated.

Reference
Scripts:

• patch_rcube_db.sh

#!/bin/bash

# Exit on error
set -euo pipefail

# Usage check
if [ -z "${1:-}" ]; then
  echo "❌ Usage: $0 /path/to/file.rcube.db"
  exit 1
fi

DB="$1"

if [ ! -f "$DB" ]; then
  echo "❌ File not found: $DB"
  exit 1
fi

# Full CREATE TABLE schema (includes all necessary columns)
CREATE_SQL=$(cat <<'EOF'
CREATE TABLE carddav_accounts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    url TEXT NOT NULL,
    username TEXT,
    password TEXT,
    active SMALLINT DEFAULT 1,
    settings TEXT,
    last_error TEXT,
    accountname TEXT,
    last_discovered INTEGER,
    rediscover_time INTEGER,
    presetname TEXT,
    flags INTEGER,
    discovery_url TEXT
);
CREATE UNIQUE INDEX idx_user_name ON carddav_accounts(user_id, name);
EOF
)

# Map of required columns and their SQL types
declare -A REQUIRED_COLUMNS=(
  ["accountname"]="TEXT"
  ["last_discovered"]="INTEGER"
  ["rediscover_time"]="INTEGER"
  ["presetname"]="TEXT"
  ["flags"]="INTEGER"
  ["discovery_url"]="TEXT"
)

echo "🔍 Checking $DB..."

# If table doesn't exist, create full schema
if ! sqlite3 "$DB" ".tables" | grep -qw carddav_accounts; then
  echo "➡️  Creating full carddav_accounts table..."
  echo "$CREATE_SQL" | sqlite3 "$DB"
  echo "✅ Table created."
else
  echo "🔧 Table exists — checking for missing columns..."

  patched=false

  for col in "${!REQUIRED_COLUMNS[@]}"; do
    datatype="${REQUIRED_COLUMNS[$col]}"
    found=$(sqlite3 "$DB" "PRAGMA table_info(carddav_accounts);" | awk -F'|' -v col="$col" '$2 == col {print $2}')
    if [ -z "$found" ]; then
      echo "➕ Adding missing column: $col $datatype"
      sqlite3 "$DB" "ALTER TABLE carddav_accounts ADD COLUMN $col $datatype;"
      patched=true
    fi
  done

  if [ "$patched" = true ]; then
    echo "✅ Schema patched successfully."
  else
    echo "✅ Schema is already complete."
  fi
fi

patch_all_rcube_dbs.sh

#!/bin/bash

# Exit on any unhandled error
set -euo pipefail

# Path to the single-db patching script
PATCH_SCRIPT="./patch_rcube_db.sh"

# Output log file
PATCHED_LIST="patched_rcube_dbs.txt"
> "$PATCHED_LIST"  # Clear previous content

# Check if patching script exists and is executable
if [ ! -x "$PATCH_SCRIPT" ]; then
  echo "❌ Required script $PATCH_SCRIPT not found or not executable."
  exit 1
fi

echo "🔎 Scanning for .rcube.db files..."

# Loop through all home directories
for user_dir in /home/*; do
  [ -d "$user_dir" ] || continue
  etc_dir="$user_dir/etc"
  [ -d "$etc_dir" ] || continue

  # Loop through each domain directory
  for domain_dir in "$etc_dir"/*; do
    [ -d "$domain_dir" ] || continue

    # Look for .rcube.db files
    for db in "$domain_dir"/*.rcube.db; do
      [ -f "$db" ] || continue

      echo "🔧 Patching $db..."
      if "$PATCH_SCRIPT" "$db"; then
        echo "$db" >> "$PATCHED_LIST"
      else
        echo "⚠️ Failed to patch $db" >&2
      fi
    done
  done
done

echo "📄 List of patched DBs saved to: $PATCHED_LIST"
echo "✅ Bulk patching complete."

Thank you