While we’re comfortable declaring our expertise in WordPress, it’s not without challenges. One issue that’s quite frustrating is the database architecture utilized for WooCommerce. Specifically, various records are stored in the wp_posts table in WordPress, and their post type categorizes them. Here’s a list of some common post types used along with a brief description of each:

1. Product: Post type product is used to store information about individual products in your WooCommerce store. This includes product name, price, description, and more details.
2. Product Variation: Post type product_variation represents different product variations, such as size or color options. These are linked to the main product.
3. Order: Post type shop_order stores information about customer orders, including order status, customer details, and items ordered.
4. Order Refund: Post type shop_order_refund tracks refunds associated with specific orders.
5. Coupon: Post type shop_coupon stores details about coupons and discounts that can be applied to orders.
6. Shop Webhook: Post type shop_webhook is used for storing information related to webhooks, which can be used to trigger actions in response to events in your WooCommerce store.
7. Shop Subscription: Post type shop_subscription It is relevant if your store has subscription-based products and stores information about customer subscriptions.
8. Shop Subscription Renewal: Post type shop_subscription_renewal is used to record subscription renewals.
9. Shop Subscription Switch: Post type shop_subscription_switch tracks changes or switches in subscription products.
10. Shop Subscription Pending Payment: Post type shop_subscription_pending_payment represents subscription orders with pending payments.
11. Shop Subscription Failed: Post type shop_subscription_failed is used to record failed subscription payments.
12. Product Review: Post type product_review is used to store customer reviews for products. Each review is treated as a separate post, including reviewer information, review text, and ratings for the associated product.

If you’re designing or implementing a new theme for WordPress, you typically push a copy of the site and database to a staging or local development environment. Meanwhile, the site continues to collect orders and other ecommerce applicable events.

Database Conflicts in wp_posts

In other words, records are being created in production that will conflict with them. Example: You add a new page on staging and the next incremental ID is 6702. However, there’s an order on your production environment that is using the same incremental ID of 6702. There are a couple of issues with this:

• Order IDs are not sequential. If you have one order that’s 5 and then build 3 pages, your next order ID is 9. Viewing your order ID gives you no insight whatsoever into the number of orders you’ve fulfilled on your site.
• Order IDs can not be changed! WooCommerce utilizes that ID and communicates it directly to your customer in all subsequent invoices and order references.

It’s quite troubling that WooCommerce engineers didn’t utilize an additional field for orders that is both sequential and unique, but differed from their internal ID. In other words, ID 6702 could have been invoice 4322… and easily added between databases with a different ID in wp_posts. Products do this with an optional SKU field, but it’s also not fully integrated with the platform to utilize that as a primary key.

I admire the simplicity of this approach to expanding the platform into commerce. That said, I’m also shocked they didn’t go a step further to resolve this issue. This means there’s no simple way to take a staging environment and synchronize it with production to go live with a new theme.

How To Resolve This

There is a solution to this, but it’s not a simple one. Import Export Suite for WooCommerce is the solution I’ve used and it makes this a much more manageable process.

Step 1: Export Current Order Data from your Production Environment

Within your production environment, you can export each of the critical post types. You can also utilize advanced filtering… like utilizing the last order date in your staging area to only include orders after your data went out of sync.

Step 2: Import Current Order Data to your Staging Environment

And then you can import that data in the default file format into your staging environment, ensuring that you don’t write over any current data in the database.

Step 3: Resolve Conflicting IDs

As the plugin iterates through records to import, it will report whether or not there are any conflicts on specific IDs. This is when it gets a bit more difficult.

Connecting directly to the MySQL database, I had to search for those IDs in the wp_posts table to figure out what kind of record it was. If it was a page or post, I just copied those to ensure they utilized a new ID. If it was something else, I had to determine how to deal with it.

Once I eliminated all conflicts, I re-imported the data and all records were successfully imported. Once all of the data conflicts were resolved, I was able to push staging to production. A nice feature of the plugin was that I didn’t have to re-upload the import, I could just rerun the import in the history tab.