HPOS Background Sync Stalls & Migration Drift: Reconciling Stuck Orders Between wp_posts and wp_wc_orders

Illustration of WooCommerce wp_posts and wp_wc_orders database tables synchronizing across an HPOS reconciliation bridge while quarantining corrupted order records.

Why a stalled HPOS sync is an operations incident

The admin notice reads “There are X orders pending sync from the posts table to the orders table.” It looks like a UI nuisance. It is actually the visible edge of a data-integrity problem: two order stores, wp_posts and wp_wc_orders, have diverged and the background reconciler is no longer closing the gap. Engineers usually land on this page through a search like woocommerce hpos pending sync stuck orders drift, typically with a pending count that has not moved in days.

The commercial impact shows up in three places:

  • The data store toggle is locked. WooCommerce refuses to switch the authoritative order store unless the posts and orders tables are in sync. You cannot finish a migration or roll one back from the settings screen.
  • REST analytics are wrong. Dashboards and BI pipelines consuming /wp-json/wc/v3/reports/orders/totals can return stale counts while sync is stalled or disabled.
  • Legacy integrations act on old data. 3PL, ERP, and shipping plugins that have not yet been refactored from direct wp_postmeta queries to the HPOS CRUD API see whatever the posts side last received. Orders ship with outdated statuses, addresses, or line items.
Dimension Summary
Target keyword woocommerce hpos pending sync stuck orders drift
Primary search intent Emergency diagnostics and data migration engineering
Operational and financial risk High: locked store toggle, corrupted order totals, stale order states reaching fulfillment systems

This article covers why the sync stalls, how to measure the drift precisely, and a must-use plugin that drains the queue without halting on a single bad order. Test everything on a staging copy of your database first.

The AI shortcut versus architectural reality

Generic AI guidance for a stuck sync usually falls into two buckets: flip compatibility mode off directly with update_option( 'woocommerce_custom_orders_table_data_sync_enabled', 'no' ), or copy order IDs between wp_posts and wp_wc_orders by hand. Both make the banner go away and both make the data worse.

WooCommerce deliberately blocks changing the authoritative store while drift exists. Writing the option directly sidesteps the settings UI, but it does not remove the drift. It only stops the reconciler from running. From that moment, new writes land only in the authoritative store while the backup copy becomes stale, creating split-brain order records. An HPOS-aware integration may read current order information from wp_wc_orders, while a legacy plugin retrieves outdated metadata from wp_posts or wp_postmeta. This data divergence introduces conflicting order states across applications, even though the configured authoritative store remains the source of truth. Hand-copying rows has the same failure mode plus skipped meta, addresses, and operational data tables.

The supported mechanism is background reconciliation. Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer identifies orders that differ between the two stores, and BatchProcessingController (watchdog action wc_schedule_pending_batch_processes) schedules Action Scheduler background queue jobs that call the synchronizer in batches, 250 orders by default. With compatibility mode on, each order write goes to the authoritative store (OrdersTableDataStore or WC_Order_Data_Store_CPT) and is then either backfilled synchronously or queued for a later batch. This HPOS compatibility mode dual-write architecture maintains a secondary copy of WooCommerce order data across the legacy WordPress posts tables and the dedicated HPOS tables. Depending on the synchronization configuration, updates may be propagated immediately or through deferred background processing, allowing older integrations to access synchronized records during migration. When a batch throws, Action Scheduler records a failure, the watchdog reschedules, and the same batch fails again. That loop is what a stalled sync looks like from the outside. DataSynchronizer batch reconciliation is the process WooCommerce uses to identify and synchronize order records that differ between the authoritative and backup data stores. Through Action Scheduler-backed batch processing, the system attempts to reconcile pending order IDs, while repeated exceptions or failed background actions can prevent the migration queue from completing.

Three root causes account for most stalls.

Root cause 1: fatal errors during order hydration (WooCommerce issue #61627)

To sync an order, the synchronizer hydrates a full WC_Order—much like hydrating fresh HPOS order objects inside asynchronous Action Scheduler email workers. If a historical order has a line item pointing to a permanently deleted product, the item's get_product() returns false. When any code path then calls $order->needs_shipping() (a shipping plugin's order action, for example), the loop inside WC_Order::needs_shipping() ends up invoking needs_shipping() on false. PHP throws an uncaught Error, the batch dies, and because the queue is ordered, the same 250 orders come back on the next run.

A caveat on versions: core has reportedly hardened this method in later releases, and at least one shipping plugin has shipped its own fix. Check your WooCommerce version and the changelogs before assuming you are exposed. Stores running older builds, or third-party code that touches products directly, remain at risk.

Root cause 2: unmigrated transactional metadata and property drift

Transactional metadata that was historically stored in wp_postmeta needs to be moved by wc_update_810_migrate_transactional_metadata_for_hpos(). If that update routine never completed, or only partially ran, verification keeps flagging orders as different. The same happens with properties listed in the woocommerce_hpos_sync_ignored_order_props filter and with transaction isolation levels that let verification read a row mid-update. The result is a pending count that refuses to reach zero even though nothing is crashing.

Root cause 3: REST totals reading the wrong table (WooCommerce issue #45354)

When HPOS is enabled and compatibility sync is off or stalled, /wp-json/wc/v3/reports/orders/totals has been reported to rely on wp_count_posts() against wp_posts. The endpoint then returns stale totals until the synchronization finishes. Current core sources build these counts through OrderUtil::get_count_for_type( 'shop_order' ), so on an up-to-date install the patch below is a defensive no-op. On older builds it is the difference between correct and wrong numbers in your dashboards.

Flowchart comparing stalled HPOS DataSynchronizer batches and deleted-product fatals against a fault-tolerant WP-CLI per-order recovery loop.

Diagnostic workflow

Start by quantifying drift in both directions, then check the batch processor state, then let WP-CLI verification identify the specific orders that fail. The queries assume the default wp_ prefix, so adjust it for your install.

Query 1: forward drift (posts ahead of orders)

-- Orders present in wp_posts but missing or older in wp_wc_orders.
SELECT
    COUNT(p.ID) AS drifted_order_count,
    MIN(p.ID)   AS first_drifted_order_id,
    MAX(p.ID)   AS last_drifted_order_id
FROM wp_posts AS p
LEFT JOIN wp_wc_orders AS o ON p.ID = o.id
WHERE p.post_type = 'shop_order'
  AND p.post_status <> 'auto-draft'
  AND (
        o.id IS NULL
        OR p.post_modified_gmt > o.date_updated_gmt
      );

The auto-draft exclusion removes abandoned admin order screens, which are never synchronized and would inflate the count.

Query 2: reverse drift (orders ahead of posts)

-- Orders in wp_wc_orders that are missing from wp_posts or still placeholders.
SELECT
    COALESCE(p.post_type, '(missing in wp_posts)') AS post_type,
    COUNT(o.id) AS pending_backfill_count
FROM wp_wc_orders AS o
LEFT JOIN wp_posts AS p ON o.id = p.ID
WHERE o.type = 'shop_order'
  AND (
        p.ID IS NULL
        OR p.post_type = 'shop_order_placehold'
      )
GROUP BY COALESCE(p.post_type, '(missing in wp_posts)');

The shop_order_placehold post type is an intentional HPOS placeholder record that reserves the corresponding order ID in wp_posts when immediate synchronization is disabled. These placeholder records prevent WordPress from assigning the same ID to an unrelated post. Their presence does not automatically indicate database corruption or a failed migration; administrators should evaluate them alongside compatibility mode settings and pending synchronization counts.

Query 3: batch processor and sync flags

-- Active batch processors and the two HPOS state flags.
SELECT option_name, option_value
FROM wp_options
WHERE option_name IN (
    'woocommerce_pending_batch_processes',
    'woocommerce_custom_orders_table_enabled',
    'woocommerce_custom_orders_table_data_sync_enabled'
);

How to read the results: woocommerce_custom_orders_table_enabled = 'yes' means wp_wc_orders is authoritative. A serialized entry for DataSynchronizer in woocommerce_pending_batch_processes means the watchdog still believes work is outstanding. If Query 1 is non-zero while HPOS is authoritative and sync is 'yes', your legacy readers are already behind.

WP-CLI verification

# Run as the web user. Use --allow-root only inside disposable containers.
cd /var/www/html

# Current HPOS state and pending counts
wp wc hpos status

# Compare both stores and surface the specific order IDs that differ
wp wc hpos verify_cot_data --batch-size=250 --verbose 2>&1 | tee /tmp/hpos-verify.log

# Processor and flag state (wp option get exits non-zero if an option is absent)
wp option get woocommerce_pending_batch_processes --format=json
wp option get woocommerce_custom_orders_table_enabled
wp option get woocommerce_custom_orders_table_data_sync_enabled

# Are the batch jobs failing or stuck in-progress?
wp action-scheduler action list --hook=wc_run_batch_process --status=failed --format=table
wp action-scheduler action list --hook=wc_run_batch_process --status=in-progress --format=table

# Look for the fatal signature in PHP and WooCommerce logs
grep -R 'needs_shipping' wp-content/debug.log wp-content/uploads/wc-logs/ 2>/dev/null | tail -n 20

# Stock synchronization, for comparison with the recovery command below
wp wc hpos sync --batch-size=250

On older WooCommerce releases these commands live under the previous namespace: wp wc cot verify_cot_data and wp wc cot sync. Newer releases use wp wc hpos, so run wp help wc to see which your site exposes. If verify_cot_data or sync dies on the same order ID every time, you are looking at root cause 1.

Production-ready fix: a must-use plugin

Save the following as wp-content/mu-plugins/hpos-drift-reconciler.php. It does three things:

  1. Corrects /wc/v3/reports/orders/totals when HPOS is authoritative.
  2. Prevents the deleted-product fatal during synchronization, and logs which orders reference deleted products.
  3. Adds wp wc-enterprise hpos-recover, a recovery loop that isolates each order in its own try / catch ( Throwable ), quarantines failures, and keeps going.
<?php
/**
 * Plugin Name: Enterprise HPOS Sync Recovery & Drift Reconciler
 * Description: Isolates fatal orders during HPOS sync, repairs REST report totals, and provides CLI recovery.
 * Version:     1.1.0
 */

declare(strict_types=1);

namespace EnterpriseWC\HPOSSync;

use Automattic\WooCommerce\Internal\BatchProcessing\BatchProcessingController;
use Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer;
use Automattic\WooCommerce\Utilities\OrderUtil;
use Throwable;
use WC_Abstract_Order;
use WC_Order_Item_Product;
use WC_Product;
use WC_Product_Simple;
use WP_CLI;
use WP_REST_Response;

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

final class HPOSDriftReconciler {

    private const LOG_SOURCE = 'hpos-drift-reconciler';

    /** True while the CLI recovery loop is running. */
    private static bool $recovery_mode = false;

    /** Order IDs already logged for deleted-product references. */
    private static array $audited = [];

    public static function init(): void {
        // 1. REST totals read from wp_wc_orders when HPOS is authoritative (#45354).
        add_filter( 'woocommerce_rest_prepare_report_orders_count', [ self::class, 'fix_hpos_rest_order_totals' ], 10, 3 );

        // 2. Deleted-product guards for the synchronization path (#61627).
        add_filter( 'woocommerce_order_item_product', [ self::class, 'stub_deleted_product' ], 10, 2 );
        add_filter( 'woocommerce_order_needs_shipping', [ self::class, 'audit_needs_shipping' ], 10, 2 );

        // 3. Fault-tolerant recovery command.
        if ( defined( 'WP_CLI' ) && WP_CLI ) {
            WP_CLI::add_command( 'wc-enterprise', RecoveryCommand::class );
        }
    }

    /**
     * Replace stale wp_count_posts()-style totals with a direct wp_wc_orders count.
     *
     * @param WP_REST_Response $response Prepared report row.
     * @param object           $report   Raw report object (slug, name, total).
     * @param mixed            $request  REST request.
     * @return WP_REST_Response
     */
    public static function fix_hpos_rest_order_totals( $response, $report, $request ) {
        global $wpdb;

        if ( ! $response instanceof WP_REST_Response ) {
            return $response;
        }

        if ( ! class_exists( OrderUtil::class ) || ! OrderUtil::custom_orders_table_usage_is_enabled() ) {
            return $response;
        }

        $data = $response->get_data();
        if ( ! is_array( $data ) || empty( $data['slug'] ) ) {
            return $response;
        }

        $slug   = sanitize_key( (string) $data['slug'] );
        $status = 0 === strpos( $slug, 'wc-' ) ? $slug : 'wc-' . $slug;

        $data['total'] = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(id) FROM {$wpdb->prefix}wc_orders WHERE type = 'shop_order' AND status = %s",
                $status
            )
        );

        $response->set_data( $data );
        return $response;
    }

    /**
     * During synchronization only, hand callers a virtual stand-in when an order item
     * points at a permanently deleted product. needs_shipping() then returns false for
     * that item instead of being called on `false`.
     *
     * @param WC_Product|false $product Resolved product, or false if deleted.
     * @param mixed            $item    Order item.
     * @return WC_Product|false
     */
    public static function stub_deleted_product( $product, $item ) {
        if ( $product instanceof WC_Product || ! self::in_sync_context() ) {
            return $product;
        }

        $stub = new WC_Product_Simple();
        $stub->set_name( 'Deleted product' );
        $stub->set_virtual( true );

        return $stub;
    }

    /**
     * Observability hook on woocommerce_order_needs_shipping. This filter runs after the
     * item loop, so it cannot prevent the fatal on its own; the stub above does that.
     * Here we record which orders reference deleted products so they can be cleaned up.
     *
     * @param bool              $needs_shipping Computed value.
     * @param WC_Abstract_Order $order          Order being evaluated.
     * @return bool
     */
    public static function audit_needs_shipping( $needs_shipping, $order ) {
        if ( ! self::in_sync_context() || ! $order instanceof WC_Abstract_Order ) {
            return (bool) $needs_shipping;
        }

        $order_id = (int) $order->get_id();
        if ( isset( self::$audited[ $order_id ] ) ) {
            return (bool) $needs_shipping;
        }

        foreach ( $order->get_items() as $item ) {
            if ( ! $item instanceof WC_Order_Item_Product ) {
                continue;
            }

            $product_id = $item->get_variation_id() ? $item->get_variation_id() : $item->get_product_id();
            if ( $product_id && ! wc_get_product( $product_id ) ) {
                self::$audited[ $order_id ] = true;
                wc_get_logger()->warning(
                    sprintf( 'Order #%d references deleted product #%d.', $order_id, $product_id ),
                    [ 'source' => self::LOG_SOURCE ]
                );
                break;
            }
        }

        return (bool) $needs_shipping;
    }

    /**
     * True inside the CLI recovery loop and inside WooCommerce batch-processing actions.
     */
    private static function in_sync_context(): bool {
        return self::$recovery_mode
            || doing_action( 'wc_run_batch_process' )
            || doing_action( 'wc_schedule_pending_batch_processes' );
    }

    /**
     * Delete stale batch-processing locks. The exact transient key varies by WooCommerce
     * version, so sweep by name. Sites with a persistent object cache keep transients
     * outside wp_options, so also try the documented key directly.
     */
    private static function clear_stuck_locks(): int {
        global $wpdb;

        $keys = [ 'wc_batch_processing_' . DataSynchronizer::class ];

        $like = $wpdb->esc_like( '_transient_' ) . '%' . $wpdb->esc_like( 'wc_batch_processing' ) . '%';
        $rows = $wpdb->get_col(
            $wpdb->prepare( 'SELECT option_name FROM ' . $wpdb->options . ' WHERE option_name LIKE %s', $like )
        );

        foreach ( (array) $rows as $option_name ) {
            $keys[] = preg_replace( '/^_transient_(timeout_)?/', '', (string) $option_name );
        }

        $cleared = 0;
        foreach ( array_unique( $keys ) as $key ) {
            if ( delete_transient( $key ) ) {
                $cleared++;
            }
        }

        return $cleared;
    }

    /**
     * Drain pending HPOS sync work one order at a time, quarantining failures.
     */
    public static function recover( int $batch_size ): void {
        global $wpdb;

        if ( ! function_exists( 'wc_get_container' ) ) {
            WP_CLI::error( 'WooCommerce is not loaded.' );
        }

        $container    = wc_get_container();
        $synchronizer = $container->get( DataSynchronizer::class );
        $controller   = $container->get( BatchProcessingController::class );

        self::$recovery_mode = true;

        try {
            if ( 'yes' !== get_option( 'woocommerce_custom_orders_table_data_sync_enabled' ) ) {
                WP_CLI::warning( 'Compatibility mode is off, so the synchronizer may report zero pending orders even though drift exists. Run the drift queries first.' );
            }

            WP_CLI::log( sprintf( 'Cleared %d stale batch-processing lock(s).', self::clear_stuck_locks() ) );

            $total = (int) $synchronizer->get_total_pending_count();
            WP_CLI::log( sprintf( 'Detected %d order(s) pending HPOS synchronization.', $total ) );

            if ( 0 === $total ) {
                $controller->remove_processor( DataSynchronizer::class );
                WP_CLI::success( 'Nothing pending. DataSynchronizer removed from the pending batch processors.' );
                return;
            }

            $attempted   = 0;
            $quarantined = []; // order_id => reason
            $previous    = [];

            while ( true ) {
                // Over-fetch by the quarantine size so poisoned orders cannot fill the window.
                $ids = array_map( 'intval', (array) $synchronizer->get_next_batch_to_process( $batch_size + count( $quarantined ) ) );
                $ids = array_values( array_diff( $ids, array_keys( $quarantined ) ) );

                // Anything we processed last pass that is still pending did not clear.
                foreach ( array_intersect( $ids, $previous ) as $stuck_id ) {
                    $quarantined[ $stuck_id ] = 'still pending after sync attempt';
                }

                $ids = array_values( array_slice( array_diff( $ids, array_keys( $quarantined ) ), 0, $batch_size ) );
                if ( empty( $ids ) ) {
                    break;
                }

                foreach ( $ids as $order_id ) {
                    try {
                        $synchronizer->process_batch( [ $order_id ] );
                        $attempted++;
                    } catch ( Throwable $e ) {
                        $quarantined[ $order_id ] = $e->getMessage();
                        wc_get_logger()->error(
                            sprintf( 'HPOS sync quarantined order #%d: %s', $order_id, $e->getMessage() ),
                            [ 'source' => self::LOG_SOURCE ]
                        );
                        WP_CLI::warning( sprintf( 'Order #%d failed (%s). Quarantined; continuing.', $order_id, $e->getMessage() ) );
                    }
                }

                $previous = $ids;

                // Keep the CLI process flat across batches.
                wp_cache_flush_runtime();
                $wpdb->queries = [];

                WP_CLI::log( sprintf( 'Attempted %d of ~%d orders; %d quarantined.', $attempted, $total, count( $quarantined ) ) );
            }

            $remaining = (int) $synchronizer->get_total_pending_count();
            if ( 0 === $remaining ) {
                $controller->remove_processor( DataSynchronizer::class );
            }

            WP_CLI::log( sprintf( 'Pending after recovery: %d.', $remaining ) );

            if ( ! empty( $quarantined ) ) {
                WP_CLI::warning( 'Quarantined order IDs: ' . implode( ', ', array_keys( $quarantined ) ) );
                WP_CLI::halt( 1 ); // Non-zero exit so cron and CI can alert.
            }

            WP_CLI::success( 'HPOS recovery complete with no quarantined orders.' );
        } finally {
            self::$recovery_mode = false;
        }
    }
}

/**
 * WP-CLI entry point. Kept separate so only intended methods become subcommands.
 */
final class RecoveryCommand {

    /**
     * Drain stalled HPOS synchronization, quarantining orders that cannot be synced.
     *
     * ## OPTIONS
     *
     * [--batch-size=<n>]
     * : Orders fetched per batch. Minimum 10.
     * ---
     * default: 250
     * ---
     *
     * ## EXAMPLES
     *
     *     wp wc-enterprise hpos-recover --batch-size=250
     *
     * @subcommand hpos-recover
     */
    public function hpos_recover( $args, $assoc_args ): void {
        $batch_size = max( 10, (int) ( $assoc_args['batch-size'] ?? 250 ) );
        HPOSDriftReconciler::recover( $batch_size );
    }
}

HPOSDriftReconciler::init();

Design notes

REST totals. The filter name woocommerce_rest_prepare_report_orders_count is the one the totals controller applies to each status row. The patch only activates when OrderUtil::custom_orders_table_usage_is_enabled() is true, so legacy-posts stores are untouched. Counts come straight from wp_wc_orders, which is why the endpoint stays correct even while the sync is stalled.

Deleted-product guard. The two filters have different jobs. woocommerce_order_item_product is where the fatal can actually be prevented: during synchronization it swaps false for a virtual, unsaved WC_Product_Simple, so needs_shipping() quietly returns false for that item. Scoping it to batch actions and the CLI loop means storefront and admin behavior elsewhere does not change. woocommerce_order_needs_shipping fires after the item loop, so on its own it cannot stop a fatal raised inside the loop. It is used here to log which orders reference deleted products, which gives you a cleanup list.

Recovery loop. Four details matter. First, each order is synchronized alone so one bad record cannot discard 249 good ones. Second, Throwable is caught rather than Exception, because the deleted-product failure is a PHP Error. Third, the loop over-fetches by the quarantine size and remembers what it just processed, so an order that stays pending without throwing cannot trap the loop in an endless retry. Fourth, wp_cache_flush_runtime() and the query-log reset keep memory flat across batches. The command exits non-zero when anything was quarantined.

When investigating a suspected BatchProcessingController transient lock, developers should first verify the locking mechanism used by their installed WooCommerce version—just as when auditing expired transients and option locks in wp_options. Batch processor coordination does not necessarily rely on WordPress transients; newer core development uses a short-lived options-table mutex, named wc_pending_batch_processes_lock, to protect updates to the pending processor list. Removing presumed transient keys without verifying the implementation may fail to resolve the stalled synchronization process.

Internal APIs. DataSynchronizer and BatchProcessingController live under Internal namespaces, which WooCommerce does not promise to keep stable. The batch action names and the lock key are version-dependent too. Pin this plugin to the WooCommerce versions you have tested, and re-verify after major upgrades.

Recovery runbook

# 1. Snapshot first. Recovery writes to both order stores.
wp db export /var/backups/pre-hpos-recovery-$(date +%F).sql

# 2. Deploy the must-use plugin.
cp hpos-drift-reconciler.php wp-content/mu-plugins/

# 3. Drain the queue.
wp wc-enterprise hpos-recover --batch-size=250

# 4. Re-run Queries 1 and 2, then verify.
wp wc hpos verify_cot_data --batch-size=250 --verbose

For any quarantined IDs, open the order, review its line items, and check the hpos-drift-reconciler source in WooCommerce logs. Orders referencing deleted products can usually be fixed by restoring or recreating the product, or by correcting the line item. Run the command again afterward.

Once Queries 1 and 2 return zero, verification is clean, and woocommerce_pending_batch_processes no longer lists the synchronizer, change the data store from WooCommerce → Settings → Advanced → Features. Do not set the option by hand. When the stores are in sync, the guard that stalled you is no longer in your way.

Closing notes

A stalled HPOS sync is rarely a mystery once you measure both directions of drift and read the batch failures. Most cases trace to one malformed historical order, an incomplete metadata migration, or a reporting endpoint reading from the wrong table. Fix those, let the reconciler finish, and then change the data store through the supported path. Forcing the compatibility flag off trades a visible warning for silent data divergence, and the divergence is far more expensive to repair later.

0 Comments