DEV Community

Cover image for WooCommerce maintenance: 8 checks that keep payment processing alive (with SQL queries)
devautomation
devautomation

Posted on

WooCommerce maintenance: 8 checks that keep payment processing alive (with SQL queries)

#woocommerce #wordpress #webdev #freelance

A WooCommerce store going down at 11pm on a Friday is a different emergency than a brochure site going down.

Missed orders are lost revenue. Failed payments are angry customers. A hacked checkout is a PCI incident.

After managing WooCommerce stores for multiple clients, I've narrowed the monthly maintenance down to 8 checks that prevent the most expensive problems. Each one has a real failure story behind it.

1. Test a real payment (every month, no exceptions)

The most common thing WooCommerce maintainers skip is actually testing checkout. They update plugins, check the dashboard, and assume everything works.

Checkout breaks in ways that look fine from the admin panel:

  • Payment gateway webhook URL changed (after migration or domain change)
  • SSL certificate expired on a subdomain used for payment callbacks
  • Plugin conflict that only surfaces at the payment step
  • Stripe API key rotated but not updated in WooCommerce settings

Monthly protocol:

  1. Go through checkout as a real customer
  2. Use the gateway's test mode (Stripe: use card 4242 4242 4242 4242)
  3. Verify the order appears in WooCommerce -> Orders
  4. Verify the customer receives a confirmation email

If any of these fail: you found a problem before your client did.

2. Hunt stuck pending orders

Pending orders that never complete are a silent revenue drain. A customer's bank approves the charge, something breaks in the callback, WooCommerce never marks it complete.

SELECT ID, post_status, post_date, post_modified 
FROM wp_posts 
WHERE post_type = 'shop_order' 
AND post_status = 'wc-pending' 
AND post_date < DATE_SUB(NOW(), INTERVAL 24 HOUR)
ORDER BY post_date DESC;
Enter fullscreen mode Exit fullscreen mode

More than a handful of these? Something is wrong with your payment gateway webhook. Check WooCommerce -> Status -> Logs for payment errors from the past 30 days.

3. Clean WooCommerce sessions before they eat your database

Active WooCommerce stores accumulate tens of thousands of abandoned sessions in wp_woocommerce_sessions. Each one is a database row. After a year, this table can be hundreds of megabytes.

This single query reclaims most of it:

DELETE FROM wp_woocommerce_sessions 
WHERE session_expiry < UNIX_TIMESTAMP();
Enter fullscreen mode Exit fullscreen mode

Run it monthly. On a busy store, you'll see meaningful query performance improvement within a few days as the database cache stops fighting session bloat.

Follow with:

DELETE FROM wp_options 
WHERE option_name LIKE '_transient_wc_%' 
AND option_name NOT LIKE '_transient_timeout_wc_%';
Enter fullscreen mode Exit fullscreen mode

4. Audit shop manager accounts

This one gets skipped because it feels administrative. It's not.

Go to Users -> filter by "Shop Manager" role. Review every account:

  • Is this still an active employee/contractor?
  • When did they last log in?
  • Do they need shop manager access or could editor work?

I found a former contractor with shop manager access 8 months after they stopped working with a client. They had full order visibility and could issue refunds.

Also check WooCommerce -> Settings -> Advanced -> REST API. Remove any API keys that aren't actively used.

5. Verify product stock integrity

WooCommerce can oversell if inventory management is misconfigured or if there's a plugin conflict. Negative stock happens more than you'd think.

SELECT posts.post_title, meta.meta_value as stock
FROM wp_posts posts
JOIN wp_postmeta meta ON posts.ID = meta.post_id
WHERE meta.meta_key = '_stock'
AND CAST(meta.meta_value AS SIGNED) < 0
AND posts.post_type = 'product';
Enter fullscreen mode Exit fullscreen mode

Also check for:

  • Sale prices with expired end dates still active (WooCommerce sometimes doesn't clear these cleanly)
  • Products marked in-stock with stock management enabled but quantity set to 0
  • Variable product variations showing incorrect availability

6. Send a test email through every WooCommerce notification

Email delivery is the most common WooCommerce complaint clients bring up. "Customers say they're not getting order confirmations."

Default WordPress mail (wp_mail with no SMTP) fails silently on most hosting environments. Spam filters eat it. If you haven't set up SMTP, do it now -- it's a 15-minute fix that prevents weeks of support tickets.

Monthly check:

  1. WooCommerce -> Settings -> Emails -- click "Send test email" on: New Order, Order Processing, Order Complete
  2. Check spam folder for each
  3. Verify the from address is your client's domain, not wordpress.com or the hosting platform

Test with mail-tester.com once a quarter -- it scores your deliverability and tells you exactly what to fix.

7. Check WooCommerce autoloaded data

Slow admin and slow frontend can both trace back to autoloaded options table bloat. WooCommerce adds to this.

SELECT option_name, LENGTH(option_value)/1024 as kb
FROM wp_options 
WHERE autoload = 'yes' 
AND option_name LIKE 'wc_%'
ORDER BY kb DESC 
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

Anything over 100KB in a single option is worth investigating. Some plugins create massive autoloaded entries that load on every page request.

Total autoloaded data over 1MB will noticeably slow your site. Over 3MB, it's severe.

8. Review the fraud signals

Card testing fraud (bots testing stolen credit cards with small purchases) shows up as:

  • Unusual spike in failed transactions
  • Multiple orders from new accounts with different emails but similar patterns
  • Small value orders (under $5) that all fail

Check WooCommerce -> Status -> Logs for payment errors. A pattern of card_declined errors from different IPs in a short window is a card testing attack.

Countermeasures:

  • Enable Google reCAPTCHA on checkout (free, WooCommerce built-in)
  • Set minimum order amount if your products allow it
  • Your payment gateway (Stripe, PayU) has fraud rules -- check they're enabled

The checklist version

I turned these 8 checks plus about 50 more into a complete monthly WooCommerce maintenance checklist -- free download.

It covers updates, orders, payments, inventory, performance, security, email, compliance (GDPR), and backups, with the SQL queries and WP-CLI commands built in so you can move through it fast.

Free: WooCommerce Monthly Maintenance Checklist ->

If you manage multiple WooCommerce stores, the automation side -- running these checks across all sites automatically -- is covered in the WordPress Agency Automation Bundle ->

What's the WooCommerce issue you've spent the most time debugging? Payments, performance, or something else?

Managing WooCommerce stores commercially? The Agency Starter Kit has service agreement templates, proposal formats, and pricing calculators built for maintenance contracts.

More in this series: WordPress Agency Toolkit

All tools and templates: devautomation.gumroad.com

Top comments (0)