Orphaned order data after restoring WooCommerce SQL dump to HPOS-enabled site

Answer to the Question

After importing a legacy WooCommerce dump into a site that has High‑Performance Order Storage (HPOS) enabled, the main risk is that some order‑related rows remain in the legacy tables while the HPOS tables are partially populated. This creates orphaned order items, missing stats, and mismatched operational data that can break admin lists and reports.

Which tables must be compared?

• wp_woocommerce_order_items → wp_posts (post_type = 'shop_order')
• wp_woocommerce_order_itemmeta → wp_woocommerce_order_items
• wp_woocommerce_order_stats → wp_posts (post_type = 'shop_order')
• Optional: wp_postmeta rows that reference legacy order IDs should be checked against wp_posts if the migration flag is missing.

Confirmed Facts (from WooCommerce 8.x schema)

• HPOS stores orders in wp_posts with post_type = 'shop_order' and moves items to wp_woocommerce_order_items and meta to wp_woocommerce_order_itemmeta.
• Operational stats live in wp_woocommerce_order_stats and reference the same order ID.
• The migration flag is stored in wp_options under woocommerce_use_hpos (value 1 when enabled).

Recommended Verification Steps

1. Check the migration flag

   SELECT option_value FROM wp_options WHERE option_name='woocommerce_use_hpos';
   

   It must return 1. If it is 0 or missing, the site will treat orders as legacy and you will see split storage.

2. Validate order‑to‑item linkage

   SELECT COUNT(*) FROM wp_woocommerce_order_items WHERE order_id NOT IN (
       SELECT ID FROM wp_posts WHERE post_type='shop_order'
   );
   

   Result should be 0. Any non‑zero count indicates orphaned items.

3. Validate item meta linkage

   SELECT COUNT(*) FROM wp_woocommerce_order_itemmeta WHERE order_item_id NOT IN (
       SELECT order_item_id FROM wp_woocommerce_order_items
   );
   

   Result should be 0. Non‑zero values mean orphaned meta rows.

4. Validate stats linkage

   SELECT COUNT(*) FROM wp_woocommerce_order_stats WHERE order_id NOT IN (
       SELECT ID FROM wp_posts WHERE post_type='shop_order'
   );
   

   Result should be 0. Any non‑zero count indicates missing statistical records.

5. Run the built‑in reconciliation endpoint

   curl -X GET https://example.com/wp-json/wc/v3/reconcile/orders
   

   The JSON response contains a mismatches array. It should be empty; otherwise review the listed items.

6. Optional: Cross‑check legacy meta

   SELECT COUNT(*) FROM wp_postmeta WHERE meta_key LIKE 'order_id' AND meta_value NOT IN (
       SELECT ID FROM wp_posts WHERE post_type='shop_order'
   );
   

   Non‑zero indicates legacy meta pointing to missing orders.

Can the Reconciliation Endpoint Be Extended?

The current endpoint only returns a list of mismatches; it does not auto‑flag them in the admin UI. Extending it would require custom plugin code that hooks into the REST API response and writes back a flag or triggers a scheduled task. Until such a plugin exists, administrators should rely on the SQL checks above or write a custom query to surface the same data in the admin area.

Preserving the Migration Flag During Import

When restoring the dump, ensure the woocommerce_use_hpos option is set to 1 in wp_options. If the dump omitted this row, manually insert it:

INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('woocommerce_use_hpos', '1', 'yes');

Alternatively, use a SQL script that first checks for existence and updates or inserts accordingly:

REPLACE INTO wp_options (option_name, option_value, autoload) VALUES ('woocommerce_use_hpos', '1', 'yes');

Doing this prevents the site from falling back to legacy storage and avoids split‑storage scenarios.

Summary

To guarantee internal consistency after a restore, run the five SQL checks above, confirm the migration flag, and verify the reconciliation endpoint reports no mismatches. If any of these checks fail, address the orphaned rows before relying on admin reports.

Missing Diagnostic Detail

Do you know whether the target database contains the wp_woocommerce_order_stats table? (It exists from WooCommerce 8.0 onward.) If it is absent, the stats check is not applicable.