Issues with HPOS

Hey, it seems like there was some error in creating custom order tables, and the weirdness you are seeing is because of this. Although, the tool to create the table should still be presented (I will log a report and investigate and fix this).

(Note that this is not for the production site, I am assuming you are testing on a staging site with a cloned database. Please let me know if that’s not the case).

Meanwhile, can you please try the following:

1. Switch back to post as the choice of data storage from WooCommerce > Settings > Advanced > Custom Data Stores. This will enable the option to disable the feature.
2. Disable the HPOS feature by going to WooCommerce > Settings > Advanced > Features.

Above steps will enable the tool to delete custom order tables, which you can find in the WooCommerce > Status > Tools actions.

Please delete the custom order tables, and then follow the steps again to enable the feature and see if that fixes it. Please also see if you can find any error logs about what might be happening. If you can reproduce the issue again, please share the WooCommerce status report, so that we can try to reproduce the error.

Hi @vedjain,

Thanks for your response.

I checked the PHP error log, and found that it was due to the error “WordPress database error Specified key was too long; max key length is 1000 bytes for query“. Below are the details:

[31-Jan-2023 23:36:18 UTC] WordPress database error Specified key was too long; max key length is 1000 bytes for query 
CREATE TABLE wp_4_wc_orders (
	id bigint(20) unsigned,
	status varchar(20) null,
	currency varchar(10) null,
	type varchar(20) null,
	tax_amount decimal(26,8) null,
	total_amount decimal(26,8) null,
	customer_id bigint(20) unsigned null,
	billing_email varchar(320) null,
	date_created_gmt datetime null,
	date_updated_gmt datetime null,
	parent_order_id bigint(20) unsigned null,
	payment_method varchar(100) null,
	payment_method_title text null,
	transaction_id varchar(100) null,
	ip_address varchar(100) null,
	user_agent text null,
	customer_note text null,
	PRIMARY KEY (id),
	KEY status (status),
	KEY date_created (date_created_gmt),
	KEY customer_id_billing_email (customer_id, billing_email),
	KEY billing_email (billing_email),
	KEY type_status (type, status),
	KEY parent_order_id (parent_order_id),
	KEY date_updated (date_updated_gmt)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('wp_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Admin_Menus->save_settings, WC_Admin_Settings::save, do_action('woocommerce_settings_save_advanced'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Settings_Advanced->save, WC_Settings_Page->save_settings_for_current_section, WC_Admin_Settings::save_fields, update_option, do_action('updated_option'), WP_Hook->do_action, WP_Hook->apply_filters, Automattic\WooCommerce\Internal\Features\FeaturesController->__call, call_user_func_array, Automattic\WooCommerce\Internal\Features\FeaturesController->process_updated_option, do_action('woocommerce_feature_enabled_changed'), WP_Hook->do_action, WP_Hook->apply_filters, Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController->__call, call_user_func_array, Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController->handle_feature_enabled_changed, Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController->create_custom_orders_tables, Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer->create_database_tables, Automattic\WooCommerce\Internal\Utilities\DatabaseUtil->dbdelta, dbDelta
[31-Jan-2023 23:36:18 UTC] WordPress database error Specified key was too long; max key length is 1000 bytes for query 
CREATE TABLE wp_4_wc_orders_meta (
	id bigint(20) unsigned auto_increment primary key,
	order_id bigint(20) unsigned null,
	meta_key varchar(255),
	meta_value text null,
	KEY meta_key_value (meta_key, meta_value(100)),
	KEY order_id_meta_key_meta_value (order_id, meta_key, meta_value(100))
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci made by require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('wp_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Admin_Menus->save_settings, WC_Admin_Settings::save, do_action('woocommerce_settings_save_advanced'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Settings_Advanced->save, WC_Settings_Page->save_settings_for_current_section, WC_Admin_Settings::save_fields, update_option, do_action('updated_option'), WP_Hook->do_action, WP_Hook->apply_filters, Automattic\WooCommerce\Internal\Features\FeaturesController->__call, call_user_func_array, Automattic\WooCommerce\Internal\Features\FeaturesController->process_updated_option, do_action('woocommerce_feature_enabled_changed'), WP_Hook->do_action, WP_Hook->apply_filters, Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController->__call, call_user_func_array, Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController->handle_feature_enabled_changed, Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController->create_custom_orders_tables, Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer->create_database_tables, Automattic\WooCommerce\Internal\Utilities\DatabaseUtil->dbdelta, dbDelta
[31-Jan-2023 23:36:18 UTC] WordPress database error Table 'woocommerce.wp_4_wc_orders' doesn't exist for query 
SELECT(
	(
SELECT COUNT(1) FROM wp_4_posts posts
LEFT JOIN wp_4_wc_orders orders ON posts.id=orders.id
WHERE
  posts.post_type in ('shop_order', 'shop_order_refund')
  AND posts.post_status != 'auto-draft'
  AND orders.id IS NULL)
	+
	(SELECT COUNT(1) FROM (
		SELECT orders.id FROM wp_4_wc_orders orders
		JOIN wp_4_posts posts on posts.ID = orders.id
		WHERE
		  posts.post_type IN ('shop_order', 'shop_order_refund')
		  AND orders.date_updated_gmt < posts.post_modified_gmt
	) x)
) count made by do_action('woocommerce_page_wc-settings'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Admin_Menus->settings_page, WC_Admin_Settings::output, include('/plugins/woocommerce/includes/admin/views/html-admin-settings.php'), do_action('woocommerce_settings_advanced'), WP_Hook->do_action, WP_Hook->apply_filters, WC_Settings_Advanced->output, WC_Settings_Page->output, WC_Settings_Page->get_settings, WC_Settings_Page->get_settings_for_section, apply_filters('woocommerce_get_settings_advanced'), WP_Hook->apply_filters, Automattic\WooCommerce\Internal\Features\FeaturesController->__call, call_user_func_array, Automattic\WooCommerce\Internal\Features\FeaturesController->add_feature_settings, Automattic\WooCommerce\Internal\Features\FeaturesController->get_setting_for_feature, apply_filters('woocommerce_feature_description_tip'), WP_Hook->apply_filters, Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer->__call, call_user_func_array, Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer->handle_feature_description_tip, Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer->get_current_orders_pending_sync_count

Is this something you can improve in the WooCommerce migration script? Or do I have to change my database configurations?

Thanks.

• This reply was modified 2 years, 1 month ago by Chester from Afterpay.

Hi,

Any update on this?

Thanks.

Hello @chesterz4afterpay,

Thanks for your patience. I did find a similar error in the GitHub repository. As a quick test, can you please this GitHub issue to see if the user’s configuration is of any help in your situation?

https://github.com/woocommerce/woocommerce/issues/34849

In particular, the innodb-large-prefix=ON setting seems like it addresses your particular error.

We look forward to hearing back from you, and if you have any further details please include those in your reply. Thanks!

Hi @lioneldaniel ,

Thank you. That configuration has addressed the issue.

Hello @chesterz4afterpay,

Thanks for getting back and letting us and the community know! Feel free to start a new topic if you have any more questions.

Cheers!

Hi there,

We are facing an issue in creating an order when HPOS is active. New orderID is new incrementID when Use the WordPress posts table is used, but its 0 when Use the WooCommerce orders tables is active.

Admin -> WooCommerce -> Orders -> Add new order

When we proceed with adding product.

https://drive.google.com/file/d/1lo98qaBOd7zTRpY8fnsRrTCCXAzqR4OF/view

https://drive.google.com/file/d/139dbIWTXgUl-Flx2GXGZKGqfbf-YmRsz/view

It stops with this error in image given below due to new OrderID 0.

Can you please guide me what is the issue and how it can be solved?

• This reply was modified 1 year, 9 months ago by irfanfarooq6261.

Hello @irfanfarooq6261,

Assuming you’re having the exact same issue, then please check the steps here: https://github.com/woocommerce/woocommerce/issues/34849

If that didn’t help, please open your own thread so we can investigate further.

Cheers!