ziadeid

Following up on our progress with the 276k order migration on staging. While the sync has technically reached 100%, we have encountered several critical issues upon enabling HPOS as the primary datastore that make the system currently unusable.1. Final Sync Hurdle & Workaround

Even after backfilling missing address fields, the sync continued to be hard-blocked by:

WordPress database error: Duplicate entry ” for key ‘wp_wc_orders.order_key’

The Root Cause: We discovered the migrator was failing to map the existing _order_key from wp_postmeta to the new wp_wc_orders table. Because it was attempting to insert multiple empty strings into a UNIQUE column, the process crashed repeatedly.

The Action Taken: To allow the sync to complete, we manually altered the schema of wp_wc_orders to drop the UNIQUE index on order_key and modified the column to allow NULL. This permitted the sync to hit 100%, but it has left thousands of orders with empty order_key values in the new table.2. Current Display & Data Integrity Issues

Once HPOS was enabled, we observed the following mapping failures:

• Total Amounts: The Order List view shows 0 for all migrated orders, although the “Order Preview” and individual order screens correctly display the totals. It appears the total_amount column in wp_wc_orders was not populated during sync.
• Order Keys: As noted above, the order_key column is largely NULL or empty.
• Custom Metadata: Essential legacy fields (e.g., Billing Phone and custom status columns) are missing from the dashboard list view.

3. Critical Performance Degradation

Performance has dropped significantly since switching to HPOS:

• Load Times: The Orders dashboard takes ~10.65s to load (Compatibility Mode OFF).
• Query Bloat: Query Monitor shows one specific query from CustomMetadataStore fetching 95,075 rows from wp_wc_orders_meta just to render a single page of 100 orders.
• Compatibility Mode: When enabled, load times increase to 15s+ with over 10,900 queries (including 5,777 duplicates).

Optimization Attempted: We have manually re-added indexes for (status, type, date_created_gmt) on wp_wc_orders and (order_id, meta_key) on wp_wc_orders_meta, followed by ANALYZE TABLE, but the 10-second lag persists.Questions for the Team:

1. Mapping Failures: Why would the official wp wc hpos sync fail to map standard fields like order_key and total_amount despite them existing in postmeta?
2. Metadata Fetching: Why is HPOS triggering a 95k-row metadata fetch for a standard list view? Is there a specific way to re-trigger a “repair” of these metadata mappings?
3. Official Repair: Is there a recommended “official” way to force a re-sync of these specific missing columns without resetting the entire 276k-order migration?

We want to ensure we have a clean, performant path before we attempt this logic on the Production site. Looking forward to your guidance.

And also all that will be help or we should just delete the old orders and start with just the last year to make the dashboard lighter

Hi, thanks for the explanation.

We downgraded WP-CLI PHP to 8.2.29 on staging and confirmed it via:

wp --info

(PHP binary now /usr/bin/php8.2).

Notes:

• wp wc hpos reset is not available in our environment (reset is not a registered subcommand).
• HPOS status still shows ~276,749 unmigrated orders.

We then ran:

wp wc hpos sync --batch-size=50

Result:

• The sync starts and shows progress (0% → 1% → 5%).
• It repeatedly throws this error:

WordPress database error: Duplicate entry '' for key 'wp_wc_orders.order_key'

• Despite the progress output, no rows are written to wp_wc_orders, and count_unmigrated does not decrease.

Additional checks:

• _order_key in wp_postmeta has no NULL or empty values.
• wp_wc_orders has order_key as NOT NULL with a UNIQUE index.

So after downgrading to PHP 8.2, the issue changed from “silent no-write” to a blocking duplicate empty order_key error during sync.

Please advise on how to safely handle/reset this state so HPOS sync can proceed (e.g. recommended cleanup, backfill, or alternative reset approach).

Thanks for the guidance.

• I checked WooCommerce → Settings → Advanced → Features → View and manage, and no plugins or extensions are listed as blocking HPOS.

• The staging environment has 1 GB PHP memory, MySQL 8, and sufficient CPU resources(We have a VPS host, and we just use 50% of his power.).

What should I do next?