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:
- Product: Post type
productis used to store information about individual products in your WooCommerce store. This includes product name, price, description, and more details.
- Product Variation: Post type
product_variationrepresents different product variations, such as size or color options. These are linked to the main product.
- Order: Post type
shop_orderstores information about customer orders, including order status, customer details, and items ordered.
- Order Refund: Post type
shop_order_refundtracks refunds associated with specific orders.
- Coupon: Post type
shop_couponstores details about coupons and discounts that can be applied to orders.
- Shop Webhook: Post type
shop_webhookis used for storing information related to webhooks, which can be used to trigger actions in response to events in your WooCommerce store.
- Shop Subscription: Post type
shop_subscriptionIt is relevant if your store has subscription-based products and stores information about customer subscriptions.
- Shop Subscription Renewal: Post type
shop_subscription_renewalis used to record subscription renewals.
- Shop Subscription Switch: Post type
shop_subscription_switchtracks changes or switches in subscription products.
- Shop Subscription Pending Payment: Post type
shop_subscription_pending_paymentrepresents subscription orders with pending payments.
- Shop Subscription Failed: Post type
shop_subscription_failedis used to record failed subscription payments.
- Product Review: Post type
product_reviewis 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 e-commerce 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.
NOTE: There is an option with the plugin to update the conflicting Order ID to a new Order ID. If you’re not concerned with referencing older orders by ID, this option makes everything easier. However, if you’re looking to assist customers, you’ll need to search for their order using something other than the ID!
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.