Database Table Size

Hi,

I inherited a wordpress site with huge mailpoet table sizes. I see from comments etc there is no easy way to fix this other than manual deletes. I’ve created a script to do clean ups. Would like to

A) offer it to the community to be used

B) get you guys to check if it is safe (ie doesnt delete stuff it shouldn’t). Its set to 6 months lof logs and 24 months of stats. It assumes Updraft backups but thats easy to change.

<?php
/**
 * MailPoet Database Cleanup Script
 *
 * Cleans up old MailPoet data to reduce database size
 * - Tasks/Queues/Logs: 6 months retention
 * - Statistics: 2 years retention
 *
 * Run via server cron
 *
 * Assumes you have Updraft installed 
 *   - but can be changed for other backup solutions
 *
 */

// Configuration
// Set WP_Path to your WordPress folder
define('WP_PATH', '/home/user/htdocs/url');

// Set LOG_FILE to your Logs folder
define('LOG_FILE', '/home/user/logs/mailpoet-cleanup.log');

// Start logging
$start_time = microtime(true);
log_message("=== MailPoet Cleanup Started ===");

// Load WordPress
if (!file_exists(WP_PATH . '/wp-load.php')) {
    log_message("ERROR: WordPress not found at " . WP_PATH, true);
    exit(1);
}

define('WP_USE_THEMES', false);
require_once(WP_PATH . '/wp-load.php');

global $wpdb;

// Calculate date thresholds
$six_months_ago = date('Y-m-d H:i:s', strtotime('-6 months'));
$two_years_ago = date('Y-m-d H:i:s', strtotime('-24 months'));

log_message("Date thresholds:");
log_message("  6 months ago: $six_months_ago");
log_message("  2 years ago: $two_years_ago");

// Step 1: Run UpdraftPlus Backup
log_message("\n--- Step 1: Running UpdraftPlus Backup ---");
$backup_result = run_backup();

if ($backup_result !== true) {
    log_message("ERROR: Backup failed. Aborting cleanup.", true);
    exit(1);
}

log_message("Backup completed successfully");

// Step 2: Clean up scheduled_tasks with cascade
log_message("\n--- Step 2: Cleaning Scheduled Tasks (6 months) ---");
cleanup_scheduled_tasks($wpdb, $six_months_ago);

// Step 3: Clean up logs
log_message("\n--- Step 3: Cleaning Logs (6 months) ---");
cleanup_logs($wpdb, $six_months_ago);

// Step 4: Clean up statistics (2 years)
log_message("\n--- Step 4: Cleaning Statistics (2 years) ---");
cleanup_statistics($wpdb, $two_years_ago);

// Summary
$execution_time = round(microtime(true) - $start_time, 2);
log_message("\n=== Cleanup Completed in {$execution_time}s ===\n");

// ============================================================================
// FUNCTIONS
// ============================================================================

/**
 * Run UpdraftPlus backup via WP-CLI
 */
function run_backup() {
    $wp_dir = WP_PATH;
    $command = "cd $wp_dir && wp updraftplus backup --incremental 2>&1";

    log_message("Running: $command");
    exec($command, $output, $return_code);

    foreach ($output as $line) {
        log_message("  " . $line);
    }

    if ($return_code !== 0) {
        log_message("Backup command returned error code: $return_code");
        return false;
    }

    return true;
}

/**
 * Clean up scheduled_tasks with cascading deletes
 */
function cleanup_scheduled_tasks($wpdb, $date_threshold) {
    // Find old task IDs
    $old_task_ids = $wpdb->get_col($wpdb->prepare(
        "SELECT id FROM {$wpdb->prefix}mailpoet_scheduled_tasks
         WHERE (created_at < %s OR updated_at < %s)
         AND status IN ('completed', 'invalid')",
        $date_threshold,
        $date_threshold
    ));

    if (empty($old_task_ids)) {
        log_message("No old scheduled tasks found");
        return;
    }

    $task_count = count($old_task_ids);
    log_message("Found $task_count old tasks to delete");

    $task_ids_string = implode(',', array_map('intval', $old_task_ids));

    // Delete child records first (cascade)

    // 1. scheduled_task_subscribers
    $deleted = $wpdb->query(
        "DELETE FROM {$wpdb->prefix}mailpoet_scheduled_task_subscribers
         WHERE task_id IN ($task_ids_string)"
    );
    log_message("  Deleted $deleted records from scheduled_task_subscribers");

    // 2. sending_queues
    $deleted = $wpdb->query(
        "DELETE FROM {$wpdb->prefix}mailpoet_sending_queues
         WHERE task_id IN ($task_ids_string)"
    );
    log_message("  Deleted $deleted records from sending_queues");

    // 3. Finally delete the tasks themselves
    $deleted = $wpdb->query(
        "DELETE FROM {$wpdb->prefix}mailpoet_scheduled_tasks
         WHERE id IN ($task_ids_string)"
    );
    log_message("  Deleted $deleted records from scheduled_tasks");
}

/**
 * Clean up log tables
 */
function cleanup_logs($wpdb, $date_threshold) {
    // mailpoet_log
    $deleted = $wpdb->query($wpdb->prepare(
        "DELETE FROM {$wpdb->prefix}mailpoet_log
         WHERE created_at < %s",
        $date_threshold
    ));
    log_message("  Deleted $deleted records from mailpoet_log");

    // automation_run_logs
    $deleted = $wpdb->query($wpdb->prepare(
        "DELETE FROM {$wpdb->prefix}mailpoet_automation_run_logs
         WHERE started_at < %s OR updated_at < %s",
        $date_threshold,
        $date_threshold
    ));
    log_message("  Deleted $deleted records from automation_run_logs");
}

/**
 * Clean up statistics tables (2 years)
 */
function cleanup_statistics($wpdb, $date_threshold) {
    $tables = [
        'mailpoet_statistics_newsletters' => 'sent_at',
        'mailpoet_statistics_clicks' => 'created_at',
        'mailpoet_statistics_opens' => 'created_at',
        'mailpoet_statistics_bounces' => 'created_at',
        'mailpoet_statistics_unsubscribes' => 'created_at',
        'mailpoet_statistics_forms' => 'created_at',
        'mailpoet_statistics_woocommerce_purchases' => 'created_at',
    ];

    foreach ($tables as $table => $date_column) {
        $full_table = $wpdb->prefix . $table;

        // Check if table exists
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$full_table'");
        if (!$table_exists) {
            log_message("  Table $table does not exist, skipping");
            continue;
        }

        $deleted = $wpdb->query($wpdb->prepare(
            "DELETE FROM $full_table WHERE $date_column < %s",
            $date_threshold
        ));

        log_message("  Deleted $deleted records from $table");
    }
}

/**
 * Log a message to file and stdout
 */
function log_message($message, $error = false) {
    $timestamp = date('Y-m-d H:i:s');
    $log_line = "[$timestamp] $message\n";

    // Write to log file
    $log_dir = dirname(LOG_FILE);
    if (!is_dir($log_dir)) {
        mkdir($log_dir, 0755, true);
    }
    file_put_contents(LOG_FILE, $log_line, FILE_APPEND);

    // Also output to stdout
    echo $message . "\n";

    if ($error) {
        error_log($log_line);
    }
}