Automate WooCommerce Database Upgrade Process For Single and Multisite

If you run WooCommerce on a multisite, you have probably gone through the tedious process of running the WooCommerce data upgrader for each subsite. It is not so painful if you only have 5 subsites but imagine if you have 50 or 500! I had a client recently ask ‘I have a WooCommerce multisite with 30 subsites, can you help me automate upgrading these so I don’t have to do it manually?’. Yes I can and did with WP-CLI, this was a fun project with an amazing client on Codeable.

First you will learn how to automatically update the WooCommerce database for single site and then for multisite.

Automate WooCommerce Database Update Single Site

The WooCommerce WP-CLI command wp wc requires an additional package or you will see an error messageError: 'wc' is not a registered wp command.

wp package install wp-cli/restful --allow-root 

Here is the script that will run the WooCommerce database update command for a single site.

Check that you have an administrative user id as you will need that to run the wc update command.

If your WooCommerce admin ID is not 1 then be sure to update the ADMINID variable below.

#!/usr/bin/env bash
# update WooCommerce database for single site

WPPATH=""
WPFLAGS="--path=${WPPATH} --skip-themes --allow-root"

#admin user ID
ADMINID="1"

# make sure woocommerce command is available
RESTFULTEST=$( wp package list --fields=name ${WPFLAGS} | grep restful)
if [[ -z "${RESTFULTEST}" ]]; then
    wp package install wp-cli/restful ${WPFLAGS}
fi

wp wc update --url=${SITE} --user=${ADMINID} ${WPFLAGS}

You could see some output from the update like this

Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version

Automate WooCommerce Database Update for Multisite

This is an extended version of the script above that will cycle through all of your subsites and update the WooCommerce database programmatically for you!

Please note that the command requires an admin user id, here it is set to ADMINID=1 so please be sure to change this if necessary.

#!/usr/bin/env bash
# update WooCommerce database for multisite

WPPATH=""
WPFLAGS="--path=${WPPATH} --skip-themes --allow-root"

#admin user ID
ADMINID="1"

# make sure woocommerce command is available
RESTFULTEST=$( wp package list --fields=name ${WPFLAGS} | grep restful)
if [[ -z "${RESTFULTEST}" ]]; then
    wp package install wp-cli/restful ${WPFLAGS}
fi

SITEIDLIST=($(wp site list --field=blog_id ${WPFLAGS} | tr -d ' '))
# need to get siteurl to replace with
for SITEID in ${SITEIDLIST[@]};
do
    echo ${SITEID}
	SITE=$(wp site list --field=url --blog_id=${SITEID} ${WPFLAGS})
	echo ${SITE}
    # check if WooCommerce is installed
	wp plugin is-active woocommerce --url=${SITE} ${WPFLAGS}
	# check exit status code from the is-installed command
	WOOINSTALL=$(echo $?)
	# run the updater if woo is installed, 0 means installed
	if [[ ${WOOINSTALL} = 0 ]]; then
	    echo "WooCommerce active, running update!"
	    # updating woocommerce database via CLI https://support.pagely.com/hc/en-us/articles/115003313251-Update-WooCommerce-DB-via-Command-Line
	    wp wc update --url=${SITE} --user=${ADMINID} ${WPFLAGS}
	fi
done

Here is a sample of the output I got from running this on a client’s site before I added the check for WooCommerce installation

https://www.testsite.dev/
Success: 0 updates complete. Database version is 3.5.4
7
https://biz.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
5
https://test3.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
11
https://affiliate.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
12
https://ammo.testsite.dev/
Calling update function: wc_update_330_image_options
Calling update function: wc_update_330_webhooks
Calling update function: wc_update_330_product_stock_status
Calling update function: wc_update_330_set_default_product_cat
Calling update function: wc_update_330_clear_transients
Calling update function: wc_update_330_set_paypal_sandbox_credentials
Calling update function: wc_update_330_db_version
Calling update function: wc_update_340_states
Calling update function: wc_update_340_state
Calling update function: wc_update_340_last_active
Calling update function: wc_update_340_db_version
Calling update function: wc_update_343_cleanup_foreign_keys
Calling update function: wc_update_343_db_version
Calling update function: wc_update_344_recreate_roles
Calling update function: wc_update_344_db_version
Calling update function: wc_update_350_reviews_comment_type
Calling update function: wc_update_350_db_version
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 20 updates complete. Database version is 3.5.4
13
https://idaho.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
14
https://northcarolina.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
15
https://guardian.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
16
https://southcarolina.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
17
https://washington.testsite.dev/
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 3 updates complete. Database version is 3.5.4
18
https://illinois.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
19
https://michigan.testsite.dev/
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 3 updates complete. Database version is 3.5.4
20
https://maryland.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
21
https://montana.testsite.dev/
Calling update function: wc_update_300_grouped_products
Calling update function: wc_update_300_settings
Calling update function: wc_update_300_product_visibility
Calling update function: wc_update_300_db_version
Calling update function: wc_update_310_downloadable_products
Calling update function: wc_update_310_old_comments
Calling update function: wc_update_310_db_version
Calling update function: wc_update_312_shop_manager_capabilities
Calling update function: wc_update_312_db_version
Calling update function: wc_update_320_mexican_states
Calling update function: wc_update_320_db_version
Calling update function: wc_update_330_image_options
Calling update function: wc_update_330_webhooks
PHP Fatal error:  Call to a member function reset_query() on null in /var/www/html/wp-content/plugins/event-calendar-3-for-php-53/eventcalendar3.php on line 1438
Fatal error: Call to a member function reset_query() on null in /var/www/html/wp-content/plugins/event-calendar-3-for-php-53/eventcalendar3.php on line 1438
22
https://pennsylvania.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
23
https://minnesota.testsite.dev/
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 3 updates complete. Database version is 3.5.4
24
https://arizona.testsite.dev/
Success: 0 updates complete. Database version is 3.5.4
25
https://alabama.testsite.dev/
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 3 updates complete. Database version is 3.5.4
26
https://alaska.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
27
https://arkansas.testsite.dev/
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 3 updates complete. Database version is 3.5.4
28
https://california.testsite.dev/
Success: 0 updates complete. Database version is 3.5.4
29
https://colorado.testsite.dev/
Success: 0 updates complete. Database version is 3.5.4
30
https://connecticut.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
31
https://delaware.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
32
https://florida.testsite.dev/
PHP Fatal error:  Call to a member function reset_query() on null in /var/www/html/wp-content/plugins/event-calendar-3-for-php-53/eventcalendar3.php on line 1438
Fatal error: Call to a member function reset_query() on null in /var/www/html/wp-content/plugins/event-calendar-3-for-php-53/eventcalendar3.php on line 1438
33
https://georgia.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
34
https://hawaii.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
35
https://indiana.testsite.dev/
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 3 updates complete. Database version is 3.5.4
36
https://iowa.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
37
https://kansas.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
38
https://kentucky.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
39
https://louisiana.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
40
https://maine.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
41
https://massachusetts.testsite.dev/
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 3 updates complete. Database version is 3.5.4
42
https://mississippi.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
43
https://missouri.testsite.dev/
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 3 updates complete. Database version is 3.5.4
44
https://nebraska.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
45
https://nevada.testsite.dev/
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 3 updates complete. Database version is 3.5.4
46
https://newhampshire.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
47
https://newjersey.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
48
https://newmexico.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
49
https://newyork.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
50
https://northdakota.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
51
https://oklahoma.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
52
https://oregon.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
53
https://rhodeisland.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
54
https://southdakota.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
55
https://tennessee.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
56
https://texas.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
57
https://utah.testsite.dev/
Success: 0 updates complete. Database version is 3.5.4
58
https://vermont.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
59
https://virginia.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
60
https://westvirginia.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
61
https://wisconsin.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
62
https://wyoming.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
63
https://podcast.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?
64
https://ohio.testsite.dev/
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 2 updates complete. Database version is 3.5.4
65
http://notyouraveragegungirls.testsite.dev/
Calling update function: wc_update_330_image_options
Calling update function: wc_update_330_webhooks
Calling update function: wc_update_330_product_stock_status
Calling update function: wc_update_330_set_default_product_cat
Calling update function: wc_update_330_clear_transients
Calling update function: wc_update_330_set_paypal_sandbox_credentials
Calling update function: wc_update_330_db_version
Calling update function: wc_update_340_states
Calling update function: wc_update_340_state
Calling update function: wc_update_340_last_active
Calling update function: wc_update_340_db_version
Calling update function: wc_update_343_cleanup_foreign_keys
Calling update function: wc_update_343_db_version
Calling update function: wc_update_344_recreate_roles
Calling update function: wc_update_344_db_version
Calling update function: wc_update_350_reviews_comment_type
Calling update function: wc_update_350_db_version
Calling update function: wc_update_352_drop_download_log_fk
Calling update function: wc_update_354_modify_shop_manager_caps
Calling update function: wc_update_354_db_version
Success: 20 updates complete. Database version is 3.5.4
66
http://dc.testsite.dev/
Error: 'wc' is not a registered wp command. See 'wp help' for available commands.
Did you mean 'db'?

That is a long list of output! Be sure to log this in case there are any errors 🙂

Sources

Update WooCommerce Database via Command Line
WP-CLI plugin is-active command