Hi, <\/p>\n\n\n\n
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 <\/p>\n\n\n\n
A) offer it to the community to be used<\/p>\n\n\n\n
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. <\/p>\n\n\n\n
<?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);
}
}
<\/code><\/pre>\n","protected":false},"template":"","class_list":["post-18743457","topic","type-topic","status-publish","hentry"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic\/18743457","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic"}],"about":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/types\/topic"}],"version-history":[{"count":0,"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/topic\/18743457\/revisions"}],"wp:attachment":[{"href":"https:\/\/wordpress.org\/support\/wp-json\/wp\/v2\/media?parent=18743457"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}