Call for early testing – custom order table migrations

As we are progressing with implementing the custom order table project, we would like to open up a call for testing to test our migration processes.

Please note that this is only the first milestone from a much larger effort, but we would still appreciate some early feedback. The main goal of this call for testing is to test the migration process across various hosts and server combinations. More specifically, we’re interested in:

  1. Finding out and resolving any major issues/bugs in our migration routines.
  2. Finding out how much time and server resources migrations will take.

This call for testing is intended for developers, agencies, and hosting partners only as it will need you to set up a staging environment and database and run custom code.

Please use a staging database for testing, don’t try out the testing steps on your production database.

Migration Details

To recap, we intend to migrate orders from wp_posts and wp_postmeta table to four custom order tables that we added to our design scheme:

  1. wp_wc_orders
  2. wp_wc_order_addresses
  3. wp_wc_order_operational_data
  4. wp_wc_orders_meta

You can read in detail about the structure of these tables in our initial planning proposal.

Testing guide

Since the project is incomplete, any features and code related to the project don’t load by default. Follow these steps to get the feature enabled for testing:

Testing environment and WP CLI

For this testing, you will need a staging environment and a staging database as it will put a lot of load on your database (and your wp_posts and wp_postmeta tables). Additionally, the testing environment needs to be configured with WP-CLI.

Make sure that there are some orders already in your staging database. You can duplicate the production database into your staging/test database; this way, you will accurately know how much time migrations will take when we enable the feature eventually. You can also use the smooth generator to generate dummy orders.

The steps to testing are as follows:

  1. Getting the latest WooCommerce build which has the custom order table feature code.
  2. Enabling the custom order table feature.
  3. Running migrations via the WP-CLI (and optionally, via Action Scheduler)
  4. Reporting back the results

Get the latest WooCommerce build.

The code for testing is currently not included in any official release. You can download this build package with the required migration code and upload it to your staging site.

Enable the feature

Enabling the custom order table feature currently requires a lot of manual steps. This is intentional so that merchants may not accidentally enable the feature before it’s complete and stable.

To enable the feature:

  1. Add this code snippet to your staging site:
function enable_cot(){
	$order_controller = wc_get_container()
		->get('Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController');
	if( isset( $order_controller ) ) {
		$order_controller->show_feature();
	}
}
add_action( 'init', 'enable_cot', 99 );

2. Go to WooCommerce > Status > Tools and find the tool with the title Create the custom orders tables. Click on the Create button

3. You should see a message that custom tables were created like so:

4. Now, the custom order table feature should be enabled. You can verify by going to WooCommerce > Settings > Advanced > Custom Data Stores.

If you have orders yet to be migrated, which will likely be the case, you will see the option to switch to the custom order table disabled with pending orders count like so:

If the shop didn’t have any orders, the UI would look like this:

Make sure that the checkbox Keep the posts table and the orders tables synchronized is unchecked for now. Ticking this checkbox and saving changes will trigger the migration process in the background, which we don’t want at this time in our testing.

Running migrations

Now that we are all set up, it’s time to test out by running migrations. There are two ways to run the migration, either via CLI, which we recommend for particularly large shops or via Action Scheduler, which is the default way and would be useful for regular merchants.

Running migration via WP CLI

You would need WP-CLI installed and configured in your staging environment for this method. Follow the installation heading on WP-CLI page for information about how to set it up.

We have added commands for migration under the wp wc cot namespace. Let’s test them out.

Run the command to count the number of orders to be migrated – wp wc cot count_unmigrated:

> wp wc cot count_unmigrated
There are 22872 orders to be migrated.
>

If you don’t have any orders or all the orders are already migrated, this will be reported as 0.

Next, run the command to actually migrate all orders – wp wc cot migrate. Note that this command will take a while to complete and will put a load on your database.

> wp wc cot migrate
This feature is not production ready yet. Make sure you are not running these commands in your production environment.
There are 22872 orders to be migrated.
Order Data Migration  100% [===========================================================================================================] 1:34 / 1:12
Migration completed.
Success: 22872 orders were migrated in 90.777226 seconds

Now that all our orders are migrated let’s verify that all data was migrated as it should. To do this, run the verify command – wp wc cot verify_cot_data

> wp wc cot verify_cot_data
This feature is not production ready yet. Make sure you are not running these commands in your production environment.
There are 22872 orders to be verified.
Order Data Verification  100% [========================================================================================================] 5:27 / 4:54
Verification completed.
Success: 22872 orders were verified in 333.464455 seconds

Starting over by dropping tables

If you’d like to run the migration again, you can drop the tables and start the process all over again. To drop custom order tables, go to WooCommerce > Status > Tools and run the Delete the custom orders tables tool.

After deleting the custom order tables, the tool for creating the table will come back, and you will be able to start the process from scratch.

Running migrations via Action Scheduler

You can also run the migrations from the background by scheduling them via actions. To start that process, go to WooCommerce > Settings > Advanced > Custom Data Stores, enable the checkbox with the label Keep the posts table and the orders tables synchronized and save changes.

Settings page to start migration progress in the background

After starting the process, you can check the progress by refreshing the page. We process 250 orders in a single batch in the background migration process by default.

Clicking on Save changes button will trigger the migration again, which may be useful in case the process is stuck for some reason. Although, it may take a while to update the order count.

Request for feedback

If you are testing, we request that you share the following information in the comments of this post:

  1. How much time did it take to run the migration? How many orders were migrated? What were the server memory size and DB version (you can get these details from the system status report under the heading WordPress memory limit and MySQL version respectively)?
  2. You can change the batch size if the memory configured on the PHP server is low by providing the --batch-size option in the CLI command. Did you need to use this option, or was the default batch size of 500 orders good enough?
  3. What all issues did you face during the migration process?
  4. Any other details you would like to share or any comments on the process.

26 responses to “Call for early testing – custom order table migrations”

  1. It isn’t clear how will works for plugins that use post_meta in orders how this will handle those cases.

    1. vedjain Avatar
      vedjain

      We migrate all the post_meta associated with orders to the orders_meta table, and the plugins would be able to use the orders_meta table just like post_meta earlier. This project is still incomplete, as we are still working on implementing data stores that will make interacting with orders_meta as seamless as it is with post_meta.

      1. A huge amount of plugins (and tons of code snippets online) use get/update_post_meta to amend meta data in orders, I understand there is a means of this data being synced to the new tables, but what will happen after this sync has occurred, is this a one off sync? If a plugin still uses get/update_post_meta after these tables exist what will happen?

        Are we going to need a conditional for each instance, 1 for old tables using get/update_post_meta and 1 for the new tables? I appreciate there is update_meta_data on the order object, but concerned about the overhead of calling in the WC_Order class just to update some meta data.

        1. In addition to the above there all the other functions related to the posts table itself too to consider e.g. plugins using get_post_type for differentiating between shop_order, shop_order_refund, shop_subscription, etc, get_post_status(), etc, etc.

          1. vedjain Avatar
            vedjain

            Hi, we have actually few mitigations planned to handle direct updates, but we do want people to upgrade to using WooCommerce’s data store APIs instead of updating meta directly. Note that the project is far from complete, and we plan to publish upgrade guides to do this migration in their code safely.

            what will happen after this sync has occurred, is this a one-off sync? If a plugin still uses get/update_post_meta after these tables exist what will happen?

            This will be an ongoing sync, which means we will continue updating data in new tables if older tables are updated, although that won’t happen in real-time to prevent performance issues. Eventually, we plan to disable this sync completely.

            Are we going to need a conditional for each instance, 1 for old tables using get/update_post_meta and 1 for the new tables?

            So for simple operations, switching to data stores will be sufficient and they will take care of executing the query on the correct table. However, if you execute a custom SQL on the wp_posts and wp_postmeta table, you would have to write another query for custom tables here and execute one of them conditionally.

            consider e.g. plugins using get_post_type for differentiating between shop_order, shop_order_refund, shop_subscription, etc, get_post_status(), etc, etc

            Currently, we are only moving shop_order and shop_order_refund post types, and they will anyway have a corresponding post entry. So other post types shouldn’t be affected. We are also working on a document to make these design decisions clearer (https://github.com/woocommerce/woocommerce/issues/32846, and also get more feedback).

            You can follow the project’s progress on this project board as well, although it might not be simple to consume: https://github.com/woocommerce/woocommerce/projects/8/.

  2. Sachin Palewar Avatar
    Sachin Palewar

    WordPress Memory Limit – 512 MB
    MY SQL Version – 5.7.37-40-log
    Created Custom Tables
    182172 Orders Pending Synch
    wp wc cot migrate timed out after 26 seconds (apparently the command keeps running in background, I won’t be able to see the output or when it finished though)
    I check WP-Admin immeditely, assuming the migration is in-progress, but now it doesn’t show any Pending Orders. Even if no orders were migrated I believe.
    I see Woocommerce Database Updated Required notice in admin. I click on update database.
    Delete the custom tables
    Again create the tables
    182172 Orders Pending Synch
    run wp wc cot migrate again
    While the WP-CLI (accessing on WP-Engine web dashboard) times out again.
    Now when I come back on WP-Admin. I can see nos of Orders going down continuously.
    I kept refreshing the page and started stopwatch to roughly measure the time taken for migration. While not accurate, it took 11 Mins and 50 seconds for all the Orders to be migrated via CLI
    Next I tried migrating via Action Scheduler
    It’s still running but a lot more slowly as expected. I will let it run and will report again if encounter any issues.

    Good luck with this feature and let me know if you need more details.

    1. vedjain Avatar
      vedjain

      Thanks for the testing, let me see if we can replicate the CLI hanging.

      1. Sachin Palewar Avatar
        Sachin Palewar

        I think CLI hanging is a non-issue as I was using a web interface of CLI on host so probably it works like that.

        I am not sure if database update notices in the admin is an issue or not as it didn’t work for me first time as I mentioned.

        Also Action Scheduler migration is getting stuck for me and hasn’t finished even after trying a few times.

        1. vedjain Avatar
          vedjain

          There is probably an issue AS migration where it gets stuck when it times out, I have details in https://github.com/woocommerce/woocommerce/issues/32922 and we will pick it up soon. Thanks for testing this out, much appreciated!

  3. 64588 orders migrated by CLI in 116.068194 seconds
    No need to use –batch-size
    2021 error(s) found with verify_cot_data
    I’m happy to send a text file with the error array if that can help. The errors seem to affect core woo postmeta values and our own custom postmeta values as well.

    1. vedjain Avatar
      vedjain

      Error log would be really useful, you can send them to vedanshu[dot]jain[at]automattic[dot]com. If there is any sensitive info in the error log, then just a few examples of failing meta entries would be good enough; maybe we are missing converting some data types properly. Thanks so much for testing!

  4. thermesmarins Avatar
    thermesmarins

    I participated to the test, sent your the complete result by email. There is a big log file of 4.5M, hope your receive it if not please notify me here.

    wp wc cot count_unmigrated
    There are 46212 orders to be migrated.

    wp wc cot migrate
    There are 46212 orders to be migrated.
    Order Data Migration 100% [=======================] 1:12 / 1:21
    Migration completed.
    Success: 46212 orders were migrated in 58.267055 seconds

    wp wc cot verify_cot_data
    There are 46212 orders to be verified.
    Verification completed.
    Error: 46212 orders were verified in 193.733808 seconds Please review above errors.
    20029 error(s) found

    1. vedjain Avatar
      vedjain

      Thanks for your report, much appreciated. Based on the logs that you sent, I have opened following issues as bugs to be fixed:
      1. Handle decimal insertion when source is null. (https://github.com/woocommerce/woocommerce/issues/33147)
      2. Handle accented characters in meta migration. (https://github.com/woocommerce/woocommerce/issues/33146)

      I am still looking into logs more and will file more issues as needed. Thanks.

  5. Sascha Avatar

    I got stuck at using the WP CLI command “wp wc cot count_unmigrated”, which would return the source code of the WP login page. The only command that works is “wp –info”, all others return the login page as result, so I am probably missing something fundamental here. I am not too familiar with using the WP CLI, obviously. 🙁
    So, instead, I tried migrating the order tables via WooCommerce settings by activating the checkbox to synchronize wp_posts and the new tables. I had 42 sample orders, generated by Smooth Generator, and those were migrated without any problem within a minute or so. Could have been faster, but that was when I checked back.
    So, is the WP CLI not necessary, after all? Or, if it is, what am I doing wrong about getting the HTML source of the login page as return value to all of my commands?
    Cheers, Sascha

    1. vedjain Avatar
      vedjain

      This sounds like an issue with WP CLI setup in your environment. Maybe it’s not connected to the correct WP instance?
      In any case, while you don’t need the WP CLI for this migration, it’s recommended if you have a shop with large number of orders (perhaps more than 10,000). That said, ActionScheduler will also migrate any number of orders, it just that will be bit slower than WP CLI based migration.

  6. There are 52365 orders to be migrated.
    Order Data Migration 100% [============================================] 2:32 / 2:24
    Migration completed.
    Success: 52365 orders were migrated in 135.861406 seconds

    1. dhilditch Avatar
      dhilditch

      Wow that’s actually quite decent, 2 minutes to migrate 50,000. That’s a lot faster than the recent product attributes lookup table.

  7. ruditrip Avatar
    ruditrip

    Thanks for this exciting development. A related question: why is the same thing not happening with products and moving to custom post table or is this planned in future? This has always been a concern with wordpress/ woocom when compared to Magento and the like, that the architecture is not product focused enough. Would appreciate your feedback on this.

    1. Hi Rudi — we will likely consider doing something similar with Products in the future. We opted to start with Orders because that’s where we more consistently see/hear about problems, and we didn’t want to bite off too much change in one hit. This is already a very large undertaking. Once this change has landed, and is well-adopted, we’ll have a better sense of how important it really is to make changes to Products, or if the Orders change actually covers most use-cases.

  8. Phil C Avatar

    @vedjainsays I have completed a test migration and emailed you my results.

  9. dimitrisv Avatar
    dimitrisv

    Great feature,

    I was using this:

    https://github.com/liquidweb/woocommerce-custom-orders-table

    But had to abandon it because it was incompatible with the WPAllexport.

    These were my stats.

    There are 19999 orders to be migrated.
    Order Data Migration 100% [=======================================] 0:30 / 0:23
    Η μεταφορά ολοκληρώθηκε.
    Success: 19999 orders were migrated in 27.209294 seconds

    Verification was completed in 2/3 of the time
    Success: 19999 orders were verified in 19.942693 seconds

    Sadly the WP All Export, Orders Export is not working too with the new approach…

    I guess you have to talk with the soflly guys to sort it out. I guess many of us are using their package to export our orders to ERP systems etc.

    Keep up the good work!

    1. dimitrisv Avatar
      dimitrisv

      Also it will be great to know what does happen in multisite wordpress installations. I didnt see any parameter for site id in your commands.

    2. dimitrisv Avatar
      dimitrisv

      I did try to use in in WP Multisite context on 6.7. As soon as I did the migration, even when I opted to also keep the data synchronised all past orders disappeared from view. I had to revert back to the post table to be able to view.

      1. vedjain Avatar

        Hey, it will be because the order view is not released yet and is still in progress. I am looking into the multisite context and will reply soon.

        1. dimitrisv Avatar
          dimitrisv

          Thank you so much for the immediate response. Here is a video response about the task at hand from the clients perspective:
          https://vimeo.com/729647267/ce3835756c

          1. dimitrisv Avatar
            dimitrisv

            Please try:
            vimeo.com/729647267/ce3835756c

            vimeo.com
            /729647267/ce3835756c

Leave a Reply

Your email address will not be published. Required fields are marked *