[BUG] CLI Sync Issue: Historical Orders Not Syncing Correctly

Describe the bug I connected my account and followed the steps here to perform a quick CLI sync. After it synced the coupon codes, the script moved to sync the orders. I see the following output:

Completed processing action 3424879 with hook: MailChimp_WooCommerce_Single_Product
Running 26 actions  96 % [====================================================================================================================================================================>      ] 0:25 / 0:20Started processing action 3424880
Completed processing action 3424880 with hook: MailChimp_WooCommerce_Process_Full_Sync_Manager
Running 26 actions  100% [===========================================================================================================================================================================] 0:25 / 0:20
Found 1 scheduled task
Started processing action 3424884
Completed processing action 3424884 with hook: MailChimp_WooCommerce_Process_Orders
Running 1 action  100% [=============================================================================================================================================================================] 0:00 / 0:00
14 batches executed.
Success: 1191 scheduled tasks completed.
wp --url=https://thepostmansknock.com --path=/home/wpe-user/sites/thepostmanskck/ action-scheduler run
Found 5 scheduled tasks
Started processing action 3424885
{"success":false,"data":{"message":"You're not allowed to do this","from":"mailchimp-for-woocommerce"}}wpe-user@thepostmanskck.ssh.wpengine.net:~$ wp --url=https://thepostmansknock.com --path=/home/wpe-user/sites/thepostmanskck/ action-scheduler run
Found 6 scheduled tasks
Started processing action 3424881
Completed processing action 3424881 with hook: pinterest-for-woocommerce-handle-feed-registration
Running 6 actions  16 % [============================>                                                                                                                                               ] 0:00 / 0:00Started processing action 3424882
Completed processing action 3424882 with hook: pinterest-for-woocommerce-handle-feed-registration
Running 6 actions  33 % [=========================================================>                                                                                                                  ] 0:01 / 0:03Started processing action 3424888
Completed processing action 3424888 with hook: MailChimp_WooCommerce_Cart_Update
Running 6 actions  50 % [======================================================================================>                                                                                     ] 0:04 / 0:14Started processing action 3424889
PHP Warning:  Attempt to read property "ID" on null in /nas/content/live/thepostmanskck/wp-content/mu-plugins/courier/includes/class-courier-comments.php on line 325
Warning: Attempt to read property "ID" on null in /nas/content/live/thepostmanskck/wp-content/mu-plugins/courier/includes/class-courier-comments.php on line 325
Completed processing action 3424889 with hook: action_scheduler/migration_hook
Started processing action 3423227
Completed processing action 3423227 with hook: pinterest_for_woocommerce_hourly_heartbeat
Running 6 actions  83 % [===============================================================================================================================================>                            ] 0:04 / 0:14Started processing action 3424890
Completed processing action 3424890 with hook: MailChimp_WooCommerce_Process_Products
Running 6 actions  100% [============================================================================================================================================================================] 0:07 / 0:08
Found 100 scheduled tasks
Started processing action 3424894
Completed processing action 3424894 with hook: MailChimp_WooCommerce_Single_Product
Running 100 actions  1  % [=>                                                                                                                                                                        ] 0:00 / 0:00Started processing action 3424895
...
Warning: Attempting to reduce used memory...
Running 100 actions  100% [==========================================================================================================================================================================] 1:36 / 1:07
2 batches executed.
Success: 106 scheduled tasks completed.
wpe-user@thepostmanskck.ssh.wpengine.net:~$ wp --url=https://thepostmansknock.com --path=/home/wpe-user/sites/thepostmanskck/ action-scheduler run
0 batches executed.
Success: 0 scheduled tasks completed.

However, only 2 orders synced to Mailchimp:

Screenshot 2024-08-12 at 8 59 22 PM

Object cache is disabled.

To Reproduce
Steps to reproduce the behavior:

Connect a new website to your Mailchimp account using the WooCommerce integration.
Follow the steps on the Wiki to perform a CLI sync.
See how historical orders fail to sync.

Expected Behavior
Historical orders should sync.

Operating environment (please complete the following information):

Plugin version: 4.2.1
WooCommerce version: 8.7.0
WordPress version: 6.4.5
PHP version: 8.2.19

I just realized it's not syncing historical contacts either.

Hi @chvillanuevap,

If your WooCommerce orders are not syncing to Mailchimp, there are several potential issues and steps you can take to diagnose and resolve the problem:

Order Status: Mailchimp only syncs orders from WooCommerce that are marked as Processing, Completed, or Paid. Orders with statuses like On Hold, Draft, Pending, or Failed will not be synced.
Product Status: Check the status of the products involved in the orders. Products marked as Backorder, Out of Stock, Draft, or Published will sync, but those marked as Trashed or Pending Review will not.
Customer Status: The syncing process can be affected if the WooCommerce customer record is marked as unsubscribed, or if a customer unsubscribes from a Mailchimp campaign, or is Archived in Mailchimp. Additionally, the user roles in WordPress can impact syncing; typically, orders for users with the roles of subscriber or customer are synced.
User Roles: By default, the plugin syncs orders for users assigned the WordPress roles of either subscriber or customer. If orders are placed by users with other roles, such as administrator, these might not sync unless custom modifications are made.

If after checking these points your orders still aren't syncing, consider the following additional steps:

Check for Plugin Conflicts: Disable other plugins temporarily to see if there is a conflict preventing the syncing.
Update Plugins and Themes: Ensure that all your plugins and themes are up to date.
Enable Remote Support: If available, enable remote support in the Mailchimp for WooCommerce settings to allow for more detailed troubleshooting.

Thank you for the reply, but none of those scenarios apply to me. I have ~400,000 completed orders:

~135,000 registered customers:

~500 products:

and ~1,000 coupons

The products do seem to sync, and the coupons sync, but only partially. For some reason, it already thinks it's 100% complete:

I can't just deactivate plugins, as the website is a live store (thepostmansknock.com). I could set up a staging site, but I don't think Mailchimp offers developer testing accounts. That said, I don't see anything in the error logs that would indicate a plugin conflict.

I have two clues as to what's going on. First, I see this during the CLI syncing process:

Found 1 scheduled task
Started processing action 3427877
Completed processing action 3427877 with hook: MailChimp_WooCommerce_Process_Orders
Running 1 action  100% [=============================================================================================================================================================================] 0:00 / 0:00
Found 1 scheduled task
Started processing action 3427878
{"success":false,"data":{"message":"You're not allowed to do this","from":"mailchimp-for-woocommerce"}}

Second, I see this debug trace:

I dug a little deeper into this and found the following function:

function mailchimp_count_posts($type) {
    global $wpdb;
    if ($type === 'shop_order') {
        $query = "SELECT post_status, COUNT( * ) AS num_posts FROM {$wpdb->posts} WHERE post_type = %s AND post_status = %s";
        $posts = $wpdb->get_results( $wpdb->prepare($query, $type, 'wc-completed'));
    } else if ($type === 'product') {
        $query = "SELECT post_status, COUNT( * ) AS num_posts FROM {$wpdb->posts} WHERE post_type = %s AND post_status IN (%s, %s, %s) group BY post_status";
        $posts = $wpdb->get_results( $wpdb->prepare($query, $type, 'private', 'publish', 'draft'));
    } else {
        $query = "SELECT post_status, COUNT( * ) AS num_posts FROM {$wpdb->posts} WHERE post_type = %s AND post_status = %s";
        $posts = $wpdb->get_results( $wpdb->prepare($query, $type, 'publish'));
    }

    $response = array();
    foreach ($posts as $post) {
        $response[$post->post_status] = $post->num_posts;
    }
    return $response;
}

This function is used to count the number of orders, products, and coupons that still need syncing as part of the MailChimp_WooCommerce_Process_Orders class. However, this function does not support HPOS, and querying wp_posts is no longer the proper way to count orders. This is confusing because HPOS support was declared here, and WooCommerce really pushed plugin developers to upgrade to the new system last year.

The original query returns zero on my database:

However, when I query the orders using the HPOS method, like this:

use Automattic\WooCommerce\Utilities\OrderUtil;
function mailchimp_count_posts($type) {
    global $wpdb;
    if ($type === 'shop_order') {

        // Check if HPOS is enabled.
        if ( OrderUtil::custom_orders_table_usage_is_enabled() ) {
            // HPOS is enabled, use the wp_wc_orders table.
            $query = "SELECT status AS post_status, COUNT(*) AS num_posts FROM {$wpdb->prefix}wc_orders WHERE status = %s";
            $posts = $wpdb->get_results( $wpdb->prepare( $query, 'wc-completed' ) );
        } else {
            // HPOS is not enabled, use the wp_posts table.
            $query = "SELECT post_status, COUNT(*) AS num_posts FROM {$wpdb->posts} WHERE post_type = %s AND post_status = %s";
            $posts = $wpdb->get_results( $wpdb->prepare( $query, $type, 'wc-completed' ) );
        }
        mailchimp_debug('admin', 'mailchimp_count_posts', count($posts));
    } else if ($type === 'product') {
        $query = "SELECT post_status, COUNT( * ) AS num_posts FROM {$wpdb->posts} WHERE post_type = %s AND post_status IN (%s, %s, %s) group BY post_status";
        $posts = $wpdb->get_results( $wpdb->prepare($query, $type, 'private', 'publish', 'draft'));
    } else {
        $query = "SELECT post_status, COUNT( * ) AS num_posts FROM {$wpdb->posts} WHERE post_type = %s AND post_status = %s";
        $posts = $wpdb->get_results( $wpdb->prepare($query, $type, 'publish'));
    }

    $response = array();
    foreach ($posts as $post) {
        $response[$post->post_status] = $post->num_posts;
    }
    return $response;
}

It seems the query does return the correct count, and the orders are being processed:

Started processing action 3445261
Completed processing action 3445261 with hook: MailChimp_WooCommerce_Process_Orders
Running 100 actions  98 % [======================================================================================================================================================================>   ] 3:18 / 3:22Started processing action 3445262
Completed processing action 3445262 with hook: MailChimp_WooCommerce_Process_Full_Sync_Manager
Started processing action 3445263
Completed processing action 3445263 with hook: MailChimp_WooCommerce_Process_Full_Sync_Manager
Warning: Attempting to reduce used memory...
Running 100 actions  100% [==========================================================================================================================================================================] 3:18 / 3:22

However, the task still fails:

Started processing action 3445293
Completed processing action 3445293 with hook: MailChimp_WooCommerce_Single_Order
Running 35 actions  94 % [=================================================================================================================================================================>         ] 1:05 / 1:20Started processing action 3445297
Completed processing action 3445297 with hook: wc-admin_import_orders
Started processing action 3445296
Completed processing action 3445296 with hook: MailChimp_WooCommerce_Single_Order
Running 35 actions  100% [===========================================================================================================================================================================] 1:10 / 1:24
Found 2 scheduled tasks
Started processing action 3445298
{"success":false,"data":{"message":"You're not allowed to do this","from":"mailchimp-for-woocommerce"}}

I wonder if it's also related to HPOS.

@chvillanuevap thank you for this detailed issue info. This is addressing a couple issues at the same time - and actually we've been working on both in our next release 4.3. This version should be addressing the issues you've pointed out here. Stay tuned - we'll be in touch on it after testing. 👍

@chvillanuevap just as a quick attempt to help you through that You're not allowed to do this error, you can add a filter for the detection of managing woocommerce by doing this:

function detect_mailchimp_cli_mode( $capability ) {
        if (defined( 'WP_CLI' ) && WP_CLI && class_exists('WP_CLI')) {
            return 'manage_woocommerce';
        }
    return $capability;
}
add_filter( 'mailchimp_allowed_capability', 'detect_mailchimp_cli_mode' );

We have an update coming out in the next version which should automatically detect this for you but for now this could get your CLI to work properly.

That being said, you do still have the issue about the post counts which ARE being addressed in the next release of 4.3.

We'll be happy to continue this back and forth with you while we finish up QA on our end to get you to the finish line quicker if at all possible :)

@ryanhungate Thank you for the suggestion! I added that function to my website, but I still encountered the same You're not allowed to do this error. But running the CLI with --user=1 resolved the error. Now the sync appears to complete:

Completed processing action 3470214 with hook: MailChimp_WooCommerce_Single_Order
Running 83 actions  100% [===========================================================================================================================================================================] 2:59 / 4:25
1 batch executed.
Success: 83 scheduled tasks completed.

But in actuality, the sync is far from finished since I have 350,000 orders:

Is the plugin capable of syncing historical data, or does it only sync orders from the point of installation?

@chvillanuevap hmm this is an interesting one to say the least - but it's entirely possible that you might benefit from our next release which is coming out shortly. We're working on the HPOS for orders and the initial sync should be good after we push this one up. I'm hesitant to say grab that now because we're in QA and addressing a few things that popped up currently.

I recall you saying that all of your orders are in the "completed" state, which SHOULD technically be synced as you would expect, but I'm wondering if this has something to do with a migration that might have went wrong or skipped from the woocommerce admin?

I'm not trying to say you did anything wrong on purpose but I would just question the settings used for the initial migration. Seems like this is an older mature store that went from the old into the new woo setup.

The one change that might have the biggest impact on your syncing is actually a very tiny change to the bootstrap.php file for getting the order posts count...

/**
 * @return int
 */
function mailchimp_get_order_count() {
    return wc_orders_count('completed');
}

Since you're clearly comfortable working with php and making changes, I would suggest that to hurry this up, maybe this is the one change you need to make for syncing orders. It should give you a proper count of the "completed" orders and this is what our syncing engine uses to create the pagination required for syncing orders.

Could it be that your "old" version of posts only had those 526 order records, but everything else is in your HPOS tables?

Just thinking out loud here - maybe we try something like this and re-sync.

I'm here to get you squared away - sorry for the confusion :)

@ryanhungate Thank you for the new suggestions! I deleted all of the Mailchimp settings from the wp_options table and cleared all of the rows in wp_mailchimp_jobs to start fresh. I then reconnected the account to Mailchimp and started the CLI process again, but it's still not working, and I'm not sure why.

I see that wp_mailchimp_jobs gets populated with many MailChimp_WooCommerce_ProcessOrders entries, and in the logs, I see:

2024-08-18T02:53:43+00:00 NOTICE action_scheduler.queue_job :: MailChimp_WooCommerce_Process_Orders queued :: page 1
...
2024-08-18T02:55:54+00:00 NOTICE action_scheduler.queue_job :: MailChimp_WooCommerce_Process_Orders queued :: page 3793
2024-08-18T02:56:10+00:00 NOTICE order_sync.completed :: Done with the order queueing.
...

In wp_mailchimp_jobs, I see that each MailChimp_WooCommerce_Process_Orders entry in the serialized array accounts for 100 orders, so 3793 pages makes sense given the size of my database.

The function you mentioned, mailchimp_get_order_count, should work similarly to the mailchimp_count_posts I wrote above with HPOS support.

The script processes a few Mailchimp_WooCommerce_SingleOrder calls and then declares the process complete.

I still don't fully understand the entire process, so I'm not sure where the problem lies. In the meantime, which function or method handles the syncing for an order exactly? Is there a "sync_this_order_to_mailchimp($order_id)" function or something similar that I could use to write my own syncing script?

@chvillanuevap ok - so we're most certainly making progress here. The fact you see those logs tell me that you SHOULD have had a lot of jobs in the pending queue which will take a while to process. You're correct the process doesn't run forever, it needs to be kept alive in the CLI. Can you please confirm that if you continue to run that script again it processes another chunk of items?

The issue here is that the database was never really meant to be a queue but the Action Scheduler uses it, and we're kind of stuck in a situation where we need to make the best of it given this is what was recommended by the wordpress ecosystem for managing queues.

That being said, you have a fairly large store with lots of data to sync so we're finding ourselves in a tough spot to get you through the first hump of "historical data syncing".

I think that you might need to just validate that when you run that script a few times that it does indeed process another chunk of orders. Once you know it's working, I would set that up on a cron schedule to run every minute or 2. That should take care of things correctly.

Would you mind reporting back on this one again for us after trying that? :) thanks so much!

@chvillanuevap just wanted to follow up again on this issue to make sure we're able to provide some help if possible before the new release comes out. Were you able to make any progress on this?

I was debugging this issue last night and found that the current iteration of the plugin won't be able to sync legacy orders with HPOS enabled in WooCommerce, which is the setup for my store, no matter how many times I call the script.

When we modified how mailchimp_get_order_count operates (either by using my HPOS check and function above or wc_orders_count), MailChimp_WooCommerce_Process_Orders jobs do get added to the Action Scheduler queue. However, these jobs call MailChimp_WooCommerce_Transform_Orders::getOrderPosts, and unfortunately, this function doesn't support HPOS because it still relies on get_posts instead of wc_get_orders or WC_Order_Query and the MailChimp_WooCommerce_Single_Order jobs never get queued.

Given this, I’ve been working on rewriting parts of the plugin to make it compatible with HPOS. I’m curious about your timeline for releasing a new version. I can likely have my version up and running in a day or two.

@chvillanuevap thanks for the heads up on that. We are in final stages of QA over here. We don't ever give absolute dates for releases until the day of but it's right around the corner for sure after we finalize a few things. We're working on branch 4.3 here for now if you wanted to take a peek at what's being done.

https://github.com/mailchimp/mc-woocommerce/tree/4.3

Thanks, @ryanhungate! I just took a look at the 4.3 branch. I noticed that MailChimp_WooCommerce_Transform_Orders doesn't yet support HPOS (see the relevant line here). Do you have plans to add HPOS support for the order sync before the release? Thanks!

Hey @chvillanuevap thanks so much for noticing that - you're right that wasn't pushed to 4.3 yet but it is now. If you wanted to take a peek at the changes, the real update was that it's now using wc_get_orders instead of get_posts...

Hi @ryanhungate, no problem! I made some changes to MailChimp_WooCommerce_Transform_Orders and mailchimp_get_order_count in my version of the plugin, and it looks like my sync is working now. It will take a few days to complete due to the large number of orders, but at least it's running. Thanks for your help! I'll go ahead and close this ticket.

@chvillanuevap cool thanks for the heads up. I'm curious to how you went about that vs. what we were doing... but keep in mind when the plugin comes out it will update yours to the new code too. :) Should be good to go of course but just to be careful with all your custom edits.

@ryanhungate I was able to sync my orders after making a few adjustments. First, I changed the mailchimp_get_order_count function. I would recommend using wc_orders_count('completed', 'shop_order') instead of just wc_orders_count('completed') in your update. If shop_order is not specified, the order count also includes shop_order_refund types, which can cause this error during CLI sync: Error: Error processing action XXXXXX: Call to undefined method Automattic\WooCommerce\Admin\Overrides\OrderRefund::get_order_number().

I also modified the method MailChimp_WooCommerce_Transform_Orders::getOrderPosts. The query I used is:

$params = array(
    'status'         => 'completed',
    'limit'          => $posts,
    'offset'         => $offset,
    'orderby'        => 'date',
    'order'          => 'DESC',
    'return'         => 'ids', // Returns order IDs
);

$orders = wc_get_orders( $params );

return empty( $orders ) ? false : $orders;

Although again, I should have specified shop_order because I ended up trying to sync refunds. What you have right now in that function in 4.3 should work I think.

mailchimp / mc-woocommerce

[BUG] CLI Sync Issue: Historical Orders Not Syncing Correctly #1187