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!

Copy and paste, now on an iPhone near you

If you don’t own an iPhone, you’ll probably fall into one of three categories regarding the topic of this post:

  1. You assume the iPhone already has copy-paste, and are surprised to learn it’s just been added as part of the new iPhone 3.0 software, released today.
  2. You think everything Apple makes is overhyped and under-featured, and the prior lack of copy-paste is a perfect example of that.
  3. Huh?

But for those of us who do have an iPhone, it’s been a long time coming, and now we can experience it. To understand the philosophy at Apple, and why this feature was previously unavailable, you need to realize that Apple thinks big, and “big picture.” They want the iPhone to be a success for the long haul, and they want to transform the very nature of the smartphone. Deny Apple’s impact if you like, but I think it’s hard to argue that they haven’t done exactly what they set out to do. The iPhone isn’t intended to do everything, and, if you want to tinker with the system, you already know Apple products are not necessarily for you. But what the iPhone does, and does well, is provide a consistent, polished, and intuitive interface, and it uses that interface to deliver a “game-changing” experience.

With that in mind, we can begin to understand why Apple hesitated to offer copy-paste. The iPhone completely breaks from traditional user interfaces in a number of key ways. And if that new interface is going to be a success, it demands a radical rethinking of how certain things work. There simply is no straightforward way of adapting a mouse-centered activity like copy-paste to the touch screen interface. So Apple decided to take their time to get it right.

Did they? I think so. Let’s take a look at how it works.

iPhone copy-paste #1

In the first image, we see the article I was reading — an RSS feed from Brand New, displayed on the Google Reader website in Mobile Safari.

iPhone copy-paste #2

One cool feature the iPhone interface has always had — but that you may not know about — is the magnifying glass. When you’re typing, you can touch and hold your finger on any spot in the text to bring up the magnifying glass, which makes it possible — and easy — to place the cursor in a particular location. Now the magnifying glass appears in text you’re reading as well. As you move it around, it highlights individual words.

iPhone copy-paste #3

When you release your finger on a particular word in the text, the magnifying glass disappears and is replaced with the copy block. Note the blue dots in the upper left and lower right corners. These are your drag points.

iPhone copy-paste #4

When you start dragging, the magnifying “strip” (as I’ll call it) appears, allowing you the same character-level precision as the cursor in the magnifying glass when editing text. Let go, and the copy button reappears. Just tap that button to add the highlighted text to the clipboard.

To paste the text (for example, into an email), you use the same touch-and-hold technique to bring up the magnifying glass. When you release, a new set of buttons (like the copy button) appear, allowing you to select, select all, or paste. Tap paste, and your copied text appears.

Overall, it’s pretty good. I’m not the most imaginative when it comes to things like this, so I would never have envisioned this solution, nor can I immediately think of ways to improve it. It feels perhaps a bit clunkier than I would like — and appears to still be a bit buggy; when I tried to recreate the process I had gone through taking these screenshots, I found the magnifying glass stubbornly refused to appear.

Assuming the problems I had getting copy-paste to work were a fluke (maybe a restart is in order), I would give Apple an A- for this implementation. If it is in fact buggy and not quite ready for prime time, I’d drop that to a B+. Still, if I had to come up with a solution for this problem myself, it would be worthy of an F-.

(Yes, I know there’s no such thing as an F-. You don’t need to point that out.)

Room 34: Unnatural Disasters now on iTunes (and Amazon MP3)

I’m pleased to announce that my 2008 RPM Challenge album Unnatural Disasters is now available on iTunes!

I worked with CDBaby to get the album on iTunes, Amazon MP3, Rhapsody, and several other digital distribution sources. So far, I am very pleased with the results. So please, if you’re considering buying my album digitally, support my presence on iTunes with a purchase… and a review!

Update June 18, 2008: The album is also now available for download on Amazon MP3.