FIX HUGE SQL tables: wp_actionscheduler_actions & wp_actionscheduler_logs [WooCommerce Tips]

WooCommerce it has become a widely used module online shops. Search Engine Optimization, product, inventory management, clean and intuitive code, interface adminSimple instructions and the thousands of plugins developed for Woo are just some of the reasons why it's worth a try when you think of developing an online store.

Like any CMS, Woo is no exception to the oddities that can occur in various usage or interaction scenarios with other plugins. WordPress.
On a resource server hardware quite generous, I noticed that database service (mysqld) begins to require almost 80-90% of RAM. A pretty serious problem, because I just didn't understand where error 110 comes from periodically (110: Connection timed out).
On closer inspection of SQL processes, I found that a database has two tables with fairly large volumes: wp_actionscheduler_actions si wp_actionscheduler_logs.

Normally scheduled actions but by WooCommerce Action Scheduler they should be deleted automatically after they are executed. This doesn't always happen, and they get stuck in wp_actionsscheduler_actions with the status: failed, canceled, pending or complete.

In the image above, the tables "wp_actionsscheduler"They only have just over 15 MB. I'm sorry I wasn't inspired to take a screenshot when they had 1.2 GB. Even so, 15 MB is enough for a table that contains the programmed actions of WooCommerce.
These "swollen" tables result from that WP-Cron does not delete entries who have status "failed","canceled"And"complete“. Normally these entries should be automatically deleted from the database.
The scheduled actions and their state can be seen very easily and in WooCommerce →  Status →  Scheduled Actions.

How to clean the gutters "failed","canceled"And"complete"In wp_actionscheduler_actions si wp_actionscheduler_logs

We access the database through phpMyAdmin, then at SQL we execute the command lines in turn:

DELETE FROM `wp_actionscheduler_actions` WHERE `status` = 'canceled'
DELETE FROM `wp_actionscheduler_actions` WHERE `status` = 'complete'
DELETE FROM `wp_actionscheduler_actions` WHERE `status` = 'failed'

Once this table is cleaned, it does not mean that the problem is solved. As I said above, the main cause is the disabling of the WP-Cron service for various reasons. Thus entries with "zombie" status can no longer be deleted.
It is very important to know that if you have an online store on WooCommerce, and it is connected to Facebook Shops through the plugin "Facebook for WooCommerce", It automatically syncs WooCommerce products with your Facebook Shops account. And he does it in about 15 minutes. If these SQL entries are not controlled, you can get several hundred thousand lines. "wc_facebook_regenerate_feed"In"wp_actionscheduler_actions".

This interval is ok for stores that have a large number of orders and the stock of Facebook Shops products needs to be constantly updated. If you still think that these syncs between Facebook and your store can be done once every 24 hours, the line of code below can help.

Open the functions.php file of the theme WordPress / WooCommerce running your store and add:

add_filter( 'wc_facebook_feed_generation_interval', function(){ return HOUR_IN_SECONDS * 24; } );

Next we can set an interval of one week for automatic cleaning:

add_filter( 'action_scheduler_retention_period', 'wpb_action_scheduler_purge' );
function wpb_action_scheduler_purge() {
 return WEEK_IN_SECONDS;
}

Once you save these changes you will have no problems with giant tables for "wp_actionscheduler_actions".

how to » Curiosity » FIX HUGE SQL tables: wp_actionscheduler_actions & wp_actionscheduler_logs [WooCommerce Tips]

Passionate about technology, I like to test and write tutorials about operating systems macOS, Linux, Windows, about WordPress, WooCommerce and configure LEMP web servers (Linux, NGINX, MySQL and PHP). I write on StealthSettings.com since 2006, and a few years later I started writing on iHowTo.Tips tutorials and news about devices in the ecosystem Apple: iPhone, iPad, Apple Watch, HomePod, iMac, MacBook, AirPods and accessories.

1 thought on “FIX HUGE SQL tables: wp_actionscheduler_actions & wp_actionscheduler_logs [WooCommerce Tips]”

  1. @Stealth
    Thanks for the help. Du redder min dag og shop. I discovered first dette efter et nedbrud af min webshop. Mange plugins er begged at dumpe deres output i den tabel og det fylder… Wooping 9.2GB tabel fik min host til at opgive hans ellers fine opp-tid. No trimmer jeg med den fine kode!
    Lige et spørgsmål på falde rebet denne tabel “wpb_action_scheduler_purge” default i db'en eller skal jeg evt. rename den til noget i min (default hedder min db ikke 'wp_') eller er det måske en funktion?
    Thanks for the help and the super fine article

    Greeting
    Network

    Reply

Leave a Comment