Customers list “No data to display”
-
This is happening in 8.3.0 on production, and after updating to 8.4.0 on staging. Below the table it says “Page 1 of 88” and “2,194 customers” but the table itself is empty.
- Storefront is the active theme.
- WooCommerce is the only active plugin.
- WP_DEBUG is enabled, and there are no errors generated in WC’s Tools > Logs, nor WP’s debug.log, nor in Apache’s error.log.
- There are no errors in JavaScript console or Network tab.
- I ran the “Verify base database tables” tool and got “Database verified successfully.”
If I do a search for a customer, it does generate an error in debug.log:
[13-Dec-2023 20:01:14 UTC] WordPress database error Unknown column '0000-00-00 00:00:00' in 'field list' for query SELECT wpjk_wc_customer_lookup.customer_id as id, user_id, username, CONCAT_WS( ' ', first_name, last_name ) as name, email, country, city, state, postcode, date_registered, IF( date_last_active <= "0000-00-00 00:00:00", NULL, date_last_active ) AS date_last_active, MAX( wpjk_wc_order_stats.date_created ) as date_last_order, SUM( CASE WHEN parent_id = 0 THEN 1 ELSE 0 END ) as orders_count, SUM( total_sales ) as total_spend, CASE WHEN SUM( CASE WHEN parent_id = 0 THEN 1 ELSE 0 END ) = 0 THEN NULL ELSE SUM( total_sales ) / SUM( CASE WHEN parent_id = 0 THEN 1 ELSE 0 END ) END AS avg_order_value FROM wpjk_wc_customer_lookup LEFT JOIN wpjk_wc_order_stats ON wpjk_wc_customer_lookup.customer_id = wpjk_wc_order_stats.customer_id AND ( wpjk_wc_order_stats.status NOT IN ( 'wc-auto-draft','wc-trash','wc-pending','wc-failed','wc-cancelled','wc-checkout-draft' ) ) WHERE 1=1 AND CONCAT_WS( ' ', first_name, last_name ) LIKE '%greg%' GROUP BY wpjk_wc_customer_lookup.customer_id ORDER BY date_registered desc LIMIT 0, 10 made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, Automattic\WooCommerce\Admin\API\Reports\Customers\Controller->get_items, Automattic\WooCommerce\Admin\API\Reports\Customers\Query->get_data, WC_Data_Store->__call, Automattic\WooCommerce\Admin\API\Reports\Customers\DataStore->get_data [13-Dec-2023 20:01:14 UTC] WordPress database error Unknown column '0000-00-00 00:00:00' in 'field list' for query SELECT wpjk_wc_customer_lookup.customer_id as id, user_id, username, CONCAT_WS( ' ', first_name, last_name ) as name, email, country, city, state, postcode, date_registered, IF( date_last_active <= "0000-00-00 00:00:00", NULL, date_last_active ) AS date_last_active, MAX( wpjk_wc_order_stats.date_created ) as date_last_order, SUM( CASE WHEN parent_id = 0 THEN 1 ELSE 0 END ) as orders_count, SUM( total_sales ) as total_spend, CASE WHEN SUM( CASE WHEN parent_id = 0 THEN 1 ELSE 0 END ) = 0 THEN NULL ELSE SUM( total_sales ) / SUM( CASE WHEN parent_id = 0 THEN 1 ELSE 0 END ) END AS avg_order_value FROM wpjk_wc_customer_lookup LEFT JOIN wpjk_wc_order_stats ON wpjk_wc_customer_lookup.customer_id = wpjk_wc_order_stats.customer_id AND ( wpjk_wc_order_stats.status NOT IN ( 'wc-auto-draft','wc-trash','wc-pending','wc-failed','wc-cancelled','wc-checkout-draft' ) ) WHERE 1=1 AND CONCAT_WS( ' ', first_name, last_name ) LIKE '%greg%' GROUP BY wpjk_wc_customer_lookup.customer_id ORDER BY date_registered desc LIMIT 0, 10 made by require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, Automattic\WooCommerce\Admin\API\Reports\Customers\Controller->get_items, Automattic\WooCommerce\Admin\API\Reports\Customers\Query->get_data, WC_Data_Store->__call, Automattic\WooCommerce\Admin\API\Reports\Customers\DataStore->get_dataIf necessary, I can post specific info from the WC status report, but nothing looks relevant; everything is green or regular gray.
-
Hey @swinggraphics,
Thanks for sharing that!
Can you please do the following, in this same order, and see if this perhaps resolves the issue:
- Navigate to?Analytics?→?Settings, scroll down to?Date type, and make sure that?Date Created?is selected on the dropdown:
- Navigate to WooCommerce → Status → Tools, and click on Clear Analytics Cache
- Import Historical Data
Let us know how this goes!
Hi there @swinggraphics ??
Thank you for reaching back, with further details on this.
Also to note: this staging copy of the website was created some months ago and is used to preflight updates before applying to production. This issue with the customers list exists on both staging and production. It was suggested that there’s a problem with the database itself. If that is the case, it has been an unnoticed problem for a long time, or a specific update caused it on staging and production independently.
Reading the last sentence, above, along with the following one, from the opening message here:
This is happening in 8.3.0 on production, and after updating to 8.4.0 on staging.
We’d like to clarify the following:
- Staging was not showcasing the same issue before 8.4.0. Correct?
- HPOS is enabled, or otherwise? Currently, or at some other point in time. (for reference, I’m linking to a screenshot of that bit, found in a store’s System Status Report)
Looking forward to hearing from you!
- The topic ‘Customers list “No data to display”’ is closed to new replies.
