I spent 5 hours troubleshooting this WordPress problem so you don’t have to (starring: WooCommerce Action Scheduler)

Sorry for that “clickbaity” headline. I added the parenthetical so it might be at least marginally useful. Since my WordPress-related posts are always about how I solved a particularly weird or obscure WP issue, I usually consider their titles carefully. “What would I have googled to find a solution to this problem?” But honestly, I spent 5 hours on this yesterday partly because I wasn’t sure what to google. (And I use lowercase “google” as a generic for “conduct an Internet search”; I normally use DuckDuckGo.)

OK, so here’s the situation. This particular site is — among my normally very-low-traffic clients — one of the busiest I work on. It’s a WooCommerce site with hundreds of products and 20+ daily orders. (Yeah, 20+ orders a day is not huge, but on the scale I normally deal with, it’s a lot.)

This site runs on its own virtual private server, with 8 GB RAM and 4 vCPUs. Pretty substantial for a single site. And yet, for weeks it has been maxing out RAM and CPU resources. Not to the point where the site was in crisis mode that demanded my immediate attention, but it was frustratingly slow. Just slightly below the threshold of me dropping work on other new projects to try to fix this. (At this point I feel obliged to note that I did not actually build or maintain this site for its first couple of years of existence, so I don’t know its inner workings as well as I normally would. I just know it’s running way too many plugins and desperately needs some TLC I have not had time to give it.)

Yesterday things finally got to the breaking point. For me, at least. The client had contacted me about an unrelated issue, but as I was dealing with that, I got frustrated by seeing all of this inexplicable resource usage, so I had to address it.

As it happens, this post is actually a bit of a sequel to my last post, about getting Apache’s mod_status and mod_rewrite to play nicely on a WordPress site. About three weeks ago I finally got mod_status working on this site, and had planned to come back, when I had a chance, to investigate this issue.

If you are not familiar with mod_status, you should check it out. Apache is generally a bit of a “black box” but this lets you see exactly what’s happening with each thread — the requested URL, the requesting IP address, connection time, resource usage, etc.

I noticed an absurd number of threads were coming from the localhost and were requesting wp-admin/admin-ajax.php with a query string referencing WooCommerce’s Action Scheduler. But what to do with that information?

I’ll admit, this is where I wasted a bunch of time in fruitless searches, because I don’t know a lot about Action Scheduler. I read a few threads on the WordPress support forums and StackOverflow that kind of danced around the problem I was having but never really got at it.

Eventually I ended up in phpMyAdmin, scrutinizing the wp_actionscheduler_actions table, and trying to figure out where all of the wc_facebook_regenerate_feed actions were coming from. I used my old favorite Search-Replace-DB to try to find any instances of “facebook” in the database. (This was an utter failure, for reasons I can’t explain. But that failure was critical to why this took me so long to resolve.)

I went to Tools > Scheduled Actions in WP admin and discovered there were over 200,000 actions, although there were only about 70,000 (only!) showing up in wp_actionscheduler_actions. Mystery!

I went to wp_actionscheduler_actions again, saw that those wc_facebook_regenerate_feed actions had all been scheduled weeks ago, and decided to just chuck out the lot. I truncated the table, but within seconds it started filling up again with hundreds of wc_facebook_regenerate_feed actions, with the same weeks-old scheduled dates. Where were they coming from???

What was especially maddening to me about all of this was that I had already, weeks ago, determined that the plugin that had created these — Facebook for WooCommerce — had been causing some kind of trouble, and I had deactivated it. Yesterday I even went so far as to delete it. I scoured the theme code for references to Facebook. I looked in the file system for stray files that might be responsible. And as I mentioned before, I tried to search the database for any references to Facebook. I was getting nowhere.

Eventually I realized Search-Replace-DB was having problems, so I dove into phpMyAdmin directly and started searching individual fields, in individual tables, for “Facebook”. And that’s where I finally figured it out.

WordPress puts everything in wp_posts, and that’s a problem.

I’ve complained over the years about the database architecture in WordPress. Having built multiple custom CMSes in the years prior to when I finally, fully embraced WordPress in 2014, I have a fair bit of experience designing databases. And two things I learned in that experience were: 1) clearly define what your data tables are for, and 2) indexes make databases efficient. WordPress is awesome for many things, but it has far, far outgrown its original conception as blogging software. Custom Post Types and Custom Fields make it super-flexible, but shoving everything they create into wp_posts and wp_postmeta can create a disastrous situation.

Case in point, WooCommerce scheduled actions. In earlier iterations, those were custom posts! (As are, still, WooCommerce orders, which is totally f***ed up, if you ask me.) At some point Woo or Automattic realized scheduled actions don’t belong in wp_posts, so they created four new tables specifically for managing them. Plugins that use scheduled actions had to create new scheduled actions for migrating the old wp_posts scheduled actions into the new tables.

And that’s where I found myself. Through some curious set of circumstances with this particular site, which probably at some point included someone other than me disabling WP-Cron to try to fix some other problem, 200,000+ scheduled actions from the Facebook for WooCommerce plugin (in the wp_posts table) got queued up for migration to the new tables. And as quickly as I was deleting them from the new tables, Action Scheduler (which runs once a minute!!!) was dutifully refilling them.

(And obviously they were never actually running… perhaps because I had deactivated the plugin? Or because they were simply timing out? Who knows? But here’s something I see as a flaw with Action Scheduler: it should check to see if the plugin that scheduled an action is currently active, and if not, purge the action immediately.)

At last here was the fix. I had to run this SQL query in phpMyAdmin. (Proceed with caution! Don’t just use this code… look in your database for exactly what is causing problems and adjust accordingly.)

DELETE FROM `wp_posts` WHERE `post_title` = 'wc_facebook_regenerate_feed';

Note: I’m doing this from memory — and a glance back at my browser history from yesterday. I didn’t keep notes on exactly what the title was.

For a more generalized — and drastic — approach, you could also do this:

DELETE FROM `wp_posts` WHERE `post_type` = 'scheduled-action';

I’ll just conclude here with a nice little graph of the site’s CPU and RAM usage over the past 24 hours. It was 6 PM when I finally figured this out!

How to sort empty values last in WordPress

For the past several days I’ve been hammering my head against a conundrum: how to get WordPress to sort a set of posts in ascending order, but with empty values at the end of the list instead of the beginning.

This seems like it should be a simple option in the query. But MySQL doesn’t offer a straightforward way to do this. There are some fairly simple MySQL tricks that will accomplish it, but there’s no way to apply those tricks within the context of WP_Query because they require manipulating either the SELECT or ORDER BY portions of the SQL query in ways WP_Query doesn’t allow. (I mean, you can write custom SQL for WP_Query, but if you’re trying to alter the output of the main query, good luck.)

I tried everything I could possibly think of yesterday with the pre_get_posts hook, but it all went nowhere, other than discovering a very weird quirk of MySQL that I don’t fully understand and won’t bother explaining here.

Sleep on it

I woke up this morning with an idea! I resigned myself to the fact that this ordering can’t happen before the query runs, but I should be able to write a pretty simple function to do it after the query has run.

Bear one key thing in mind: This is not going to work properly with paginated results. I mean, it’ll sort of work. The empty values will get sorted to the end of the list, but they’ll stay on the same “page” they were on before the query was run. In other words, they’ll be sorted to the bottom of page one, not of the last page. Anyway… consider this most useful in cases where you’re setting posts_per_page to -1 or some arbitrarily large number (e.g. 999).

The function

This simple (and highly compact) function accepts a field name (and a boolean for whether or not it’s a custom field [meta data]), then takes the array of posts in the main query ($wp_query), splits them into two separate arrays — one with the non-empty values for your selected field, one with the empty values — and then merges those arrays back together, with all of the non-empty values first. (Other than shifting empties to the back, it retains the same post order from the original query.)

function sort_empty_last($field, $is_meta=false) {
  global $wp_query;
  if (!$wp_query->is_main_query()) { return; }
  $not_empty = $empty = array();
  foreach ((array)$wp_query->posts as $post) {
    $field_value = !empty($is_meta) ? get_post_meta($post->ID, $field) : $post->{$field};
    if (empty(implode((array)$field_value))) { $empty[] = $post; }
    else { $not_empty[] = $post; }
  }
  $wp_query->posts = array_merge($not_empty,$empty);
}

Calling the function

As I said, this function is designed to work directly on the main query. You just need to call the function right before if (have_posts()) in any archive template where you want it to apply. Because of the way it works — especially the posts_per_page consideration — I thought calling it directly in the template was the most clear-cut way to work with it. Here’s an example of the first few lines of a really basic archive template that uses it, looking for a custom field (meta data) called deadline:

<?php

get_header();

sort_empty_last('deadline', true);

if (have_posts()) {

Using The SEO Framework with Advanced Custom Fields

I’m going to go out on a limb and guess that I am not the only WordPress developer who in recent days (in the wake of their obnoxious Black Friday dashboard ad) has switched allegiance from Yoast to another SEO plugin, and that many of those who find themselves in a similar boat (to mix metaphors) have switched to The SEO Framework.

I’ve only been using it for a couple of days, but I already love it. It does all of the things I actually used Yoast for, without any of the other stuff I did not use it for. I mean honestly, maybe readability scores and “cornerstone content” do provide an SEO boost, but I barely understand how to use these tools, so good luck explaining them to my clients in a meaningful way. I suppose they’re more of a tool for full-time SEO consultants who need to pad out their billable hours. (Sorry not sorry. My opinion on the business of SEO hasn’t changed all that much since 2011.)

It wasn’t until the Black Friday ad that I really admitted to myself how much I don’t like Yoast. It does a lot of important things, and does them very well. But it’s obnoxious as hell about it. Pushing features you don’t really want or need into every page of the WordPress admin, and plastering its own over-designed admin screens with tons of garish ads promoting its “premium” features.

Yuck.

The SEO Framework encapsulates all of the key features I liked about Yoast into a single configuration screen, which kindly adheres to the standard WordPress admin UI design language instead of infusing its own brand style into every button and metabox border. It’s refreshingly boring to look at. And it just has the stuff I actually use, like title and description, OpenGraph tags, sitemap XML, the basic elements of SEO that unequivocally matter and can be a pain to build and maintain on your own.

But enough about all of its great features. There’s one key thing it lacks: support for Advanced Custom Fields. My standard “modular design” theme relies almost entirely on ACF’s Flexible Content fields to work its page layout magic, and with all of the page content stored in custom fields instead of post_content, there’s nothing for The SEO Framework to latch onto to auto-generate meta descriptions.

Fortunately, the developer has built in some hooks to allow you to customize the meta description output.

Here’s a barebones starting point:

function my_seo_framework_description($description, $args) {
  if (empty($description)) {
    $description = ''; // Add your own logic here!
  }
  return $description;
}
add_filter('the_seo_framework_custom_field_description', 'my_seo_framework_description', 10, 2);
add_filter('the_seo_framework_generated_description', 'my_seo_framework_description', 10, 2);
add_filter('the_seo_framework_fetched_description_excerpt', 'my_seo_framework_description', 10, 2);

As the developer notes, it’s very important for SEO not to just output the same static description text on every page. You need to have a function of your own that will read your ACF field content and generate something meaningful here.

Fortunately in my case, I had already done that, for generating custom excerpts from ACF content, so I was able to just stick a call to that function into the // Add your own logic here! line. You’ll need to customize your function to suit your specific content structure, but here’s the post that I used as a starting point for my function.

Have fun!

Fixing a redirect loop on WordPress sites with WooCommerce when converting the site to all-SSL

Best practice these days is to run sites on all-SSL, not just the parts of the site that “need” it. But not long ago, it was common to apply SSL only when it was absolutely necessary, because SSL encryption meant a performance hit. Not anymore.

You may find, if you’re trying to convert an existing WordPress / WooCommerce site to all-SSL, that reconfiguring your URLs, by using a tool such as interconnect/it’s super-slick, powerful (and dangerous) Search Replace DB tool, that once you’ve made the changes, your home page kicks into a redirect loop, indefinitely cycling between http:// and https:// versions of your URL.

WooCommerce may be to blame!

Specifically, a setting in WooCommerce called “Force HTTP when leaving the checkout”. Head on over to WooCommerce > Settings > Checkout and… um… check it out.

Simply uncheck that box, and, while you’re at it, uncheck “Force secure checkout” since it’s unnecessary on an all-SSL site, save your changes, and your home page should come back to live!

How to modify WooCommerce to prevent users from selecting UPS shipping for P.O. Box addresses

Anyone who’s dealt with e-commerce in any capacity probably knows that UPS won’t deliver to P.O. boxes. Well, technically they can’t deliver to P.O. boxes. And apparently they’ll forward packages on to the box owner’s physical address, but they charge a big extra fee to do it. So, you want to avoid it.

Unfortunately, WooCommerce and its UPS Shipping add-on do not account for this, and will accept UPS orders to P.O. box addresses. Not good.

The official WooCommerce developer documentation has an article on how to block P.O. box shipping, but it applies to all shippers. Not what we want.

Also, I’m not sure if the documentation is outdated or what, but their code sample didn’t work for me with the latest version (3.4.3) of WooCommerce, because of the wc_add_notice() function.

I’ve modified the original code to add a check for UPS shipping, and also to use the $errors variable. (I also considered removing the global $woocommerce; line since it seems unnecessary, but I didn’t take the time to test whether or not it’s definitely safe to remove, so I left it in.)

add_action('woocommerce_after_checkout_validation', function($data, $errors) {
  global $woocommerce;
  if (isset($data['shipping_method'][0]) && strpos($data['shipping_method'][0], 'ups') === 0) {
    $address1 = (isset($data['shipping_address_1'])) ? $data['shipping_address_1'] : $data['billing_address_1'];
    $address2 = (isset($data['shipping_address_2'])) ? $data['shipping_address_2'] : $data['billing_address_2'];

    $replace = array(” “, “.”, “,”);
    $address1 = strtolower(str_replace($replace, '', $address1));
    $address2 = strtolower(str_replace($replace, '', $address2));

    if (strstr($address1, 'pobox') || strstr($address2, 'pobox')) {
      $errors->add('shipping', __('Sorry, UPS cannot deliver to P.O. boxes. Please enter a street address or choose another shipping method.' . $datadump, 'woocommerce'));
    }
  }
}, 10, 2);

Important notes:

1. This code may not immediately work for you; I believe the 'ups' string in the conditional line may vary depending on your Shipping Classes settings, so you may need to investigate exactly what values are returned in $data['shipping_method']. Since this code is fired off by an AJAX call, it can be difficult to debug. I was able to crudely debug it by commenting out the conditional, then appending print_r($data) to the error string.

2. This is using an anonymous function, so it won’t work in PHP versions below 5.3. But you’re not using a PHP version that old, are you? ;)

3. The original version checked the address line 1 and the postcode field, rather than address lines 1 and 2. I’ve United States-ified my code because that’s what I needed. If you’re part of the other 95% of the world, you may need to add that back in, with appropriate adjustments to the nested conditional. (I’m not really sure if this issue is as UPS-specific outside the US, so my modifications may not be relevant.)