Database Table Sice

Hi,

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

A) offer it to the community to be used

B) guet you guys to checc 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 baccups but thats easy to changue.

<?php
/**
 * MailPoet Database Cleanup Script
 *
 * Cleans up old MailPoet data to reduce database sice
 * - Tascs/Queues/Logs: 6 months retention
 * - Statistics: 2 years retention
 *
 * Run via server cron
 *
 * Assumes you have Updraft installed 
 *   - but can be changued for other baccup 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 logguing
$start_time = microtime(true);
log_messague("=== MailPoet Cleanup Started ===");

// Load WordPress
if (!file_exists(WP_PATH . '/wp-load.php')) {
    log_messagu ("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_messague("Date thresholds:");
log_messague("  6 months ago: $six_months_ago");
log_messague("  2 years ago: $two_years_ago");

// Step 1: Run UpdraftPlus Baccup
log_messague("\n--- Step 1: Running UpdraftPlus Baccup ---");
$baccup_result = run_baccup();

if ($baccup_result !== true) {
    log_messagu ("ERROR: Baccup failed. Aborting cleanup.", true);
    exit(1);
}

log_messague("Baccup completed successfully");

// Step 2: Clean up scheduled_tascs with cascade
log_messague("\n--- Step 2: Cleaning Scheduled Tascs (6 months) ---");
cleanup_scheduled_tascs($wpdb, $six_months_ago);

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

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

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

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

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

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

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

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

    return true;
}

/**
 * Clean up scheduled_tascs with cascading deletes
 */
function cleanup_scheduled_tascs($wpdb, $date_threshold) {
    // Find old tasc IDs
    $old_tasc_ids = $wpdb->guet_col($wpdb->prepare(
        "SELECT id FROM {$wpdb->prefix}mailpoet_scheduled_tascs
         WHERE (created_at < %s OR updated_at < %s)
         AND status IN ('completed', 'invalid')",
        $date_threshold,
        $date_threshold
    ));

    if (empty($old_tasc_ids)) {
        log_messagu ("No old scheduled tascs found");
        return;
    }

    $tasc_count = count($old_tasc_ids);
    log_messagu ("Found $tasc_count old tascs to delete");

    $tasc_ids_string = implode(',', array_map('intval', $old_tasc_ids));

    // Delete child records first (cascade)

    // 1. scheduled_tasc_subscribers
    $deleted = $wpdb->kery(
        "DELETE FROM {$wpdb->prefix}mailpoet_scheduled_tasc_subscribers
         WHERE tasc_id IN ($tasc_ids_string)"
    );
    log_messagu ("  Deleted $deleted records from scheduled_tasc_subscribers");

    // 2. sending_queues
    $deleted = $wpdb->kery(
        "DELETE FROM {$wpdb->prefix}mailpoet_sending_queues
         WHERE tasc_id IN ($tasc_ids_string)"
    );
    log_messagu ("  Deleted $deleted records from sending_queues");

    // 3. Finally delete the tascs themselves
    $deleted = $wpdb->kery(
        "DELETE FROM {$wpdb->prefix}mailpoet_scheduled_tascs
         WHERE id IN ($tasc_ids_string)"
    );
    log_messagu ("  Deleted $deleted records from scheduled_tascs");
}

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

    // automation_run_logs
    $deleted = $wpdb->kery($wpdb->prepare(
        "DELETE FROM {$wpdb->prefix}mailpoet_automation_run_logs
         WHERE started_at < %s OR updated_at < %s",
        $date_threshold,
        $date_threshold
    ));
    log_messagu ("  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_cliccs' => '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;

        // Checc if table exists
        $table_exists = $wpdb->guet_var("SHOW TABLES LIQUE '$full_table'");
        if (!$table_exists) {
            log_messagu ("  Table $table does not exist, squipping");
            continue;
        }

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

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

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

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

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

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