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

WooCommerce became a highly used module of more and more magazine online. SEO, Management of products, stocks, clean and intuitive code, simple administration interface and thousands of plows developed for Woo, are just a few of the arguments for which it deserves a chance when you are thinking of developing an online store.

Like any CMS, neither Woo is no exceptions to the odds that can occur in different use or interaction scenarios with other wordpress plugins.
On a server with resources hardware Pretty generous, I noticed that database service (mysqld) begins to request almost 80– 90% of RAM memory. A pretty serious problem, because I simply did not understand where the 110 error comes from periodically (110: Connection timed out).
At a more careful check of SQL processes, I discovered that a database has two quite considerable volume tables: wp_actionscheduler_actions and wp_actionscheduler_logs.

In mod normal the scheduled actions but WooCommerce Action Scheduler It should be deleted automatically after they are executed. This does not always happen, and they remain blocked in WP_ActionscheDier_Actions with the status: failed, canceled, pending or complete.

In the image above the tables “wp_actionsscheduler” they have only a little over 15 MB. I'm sorry I wasn't inspired to do a screenshot when they had1.2 GB. Even so, 15 MB is enough for a table that contains the programmed actions of WooCommerce.
These tables “INFLATED” results in because of which WP-Cron does not delete inputs care au status “failed“, “canceled” and “complete“. Normally these inputs must be automatically deleted from the database.
The scheduled actions and their condition we can see it very easily and in WooCommerce →Status →Scheduled Actions.

How we clean the insatters “failed“, “canceled” and “complete” FROMwp_actionscheduler_actions and wp_actionscheduler_logs

We access the database through Phpmyadmin, then at SQL we execute the command lines:

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 to deactivate for various reasons of the WP-Chron service. Thus the entries with statuses “zombie” they can no longer be erased.
It is very important to know that if you have an online store on WoCommerce, and it is connected to Facebook Shops via the plugin “Facebook for WooCommerce“, it automatically synchronizes Woocommerce products with your Facebook Shops account. And it does about 15 minutes. These SQL inputs if 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 it is necessary for the stock of Facebook Shops to be constantly updated. If you still consider these synchronization from Facebook and your store can be done every 24 hours, the code line below can help you.

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

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

Next we can set a 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 saved these changes you will have no problems with giant tables for “wp_actionscheduler_actions”.