Customers list “No data to display”

  • Resolved swinggraphics

    (@swinggraphics)


    11 months, 2 weeks ago

    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_data

    If necessary, I can post specific info from the WC status report, but nothing looks relevant; everything is green or regular gray.

    View post on imgur.com
Viewing 13 replies - 16 through 28 (of 28 total)
  • Thread Starter swinggraphics

    (@swinggraphics)

    wp_wc_customer_lookup has 2784 rows.

    Saif

    (@babylon1999)

    Hello @swinggraphics,

    Thanks for confirming!

    I see you’ve reached out to us via email about this issue so I’ll mark this thread as solved.

    In the meantime, I suggest increasing your PHP time limit and deleting/re-importing the customer data, as mentioned in my previous reply. If you have any other new findings, please let us know via email. :?)

    Happy holidays! :?)

    Thread Starter swinggraphics

    (@swinggraphics)

    I see you’ve reached out to us via email about this issue so I’ll mark this thread as solved.

    I will mark this resolved when the issue is resolved. Yes, I opened an email ticket 4 days ago, but so far they have not taken any direct action, and they asked me to follow up to this thread.

    In the meantime, I suggest increasing your PHP time limit

    Changing the the max execution time has no effect. It still shows the “No data to display” message after about 2 seconds.

    deleting/re-importing the customer data, as mentioned in my previous reply

    As mentioned in a previous reply of mine, I did this already. Truth be told, I did it three times.

    Saif

    (@babylon1999)

    I will mark this resolved when the issue is resolved. Yes, I opened an email ticket 4 days ago, but so far they have not taken any direct action, and they asked me to follow up to this thread.

    I understand your frustration, however, we’re handling a backlog and can’t prioritize specific tickets. I hope you understand.

    Changing the the max execution time has no effect. It still shows the “No data to display” message after about 2 seconds.

    I’m sorry that didn’t work out. Just to clarify, I meant to recommend increasing it and then re-importing, nevertheless, the team is already investigating your case, so hopefully, you’ll hear from us soon. :?)

    If you have more questions, please respond to the email ticket.

    Thread Starter swinggraphics

    (@swinggraphics)

    Yes, I understand. But this thread is actually active with suggestions while the paid support is stagnant, so it makes sense from my standpoint to continue here. ???♂? If paid support will take over, that is fine. I will return when/if we fix the problem and mark this resolved. Thank you all for trying to figure this out.

    Please note that “Delete Historical Data” does not exist. What I did do was: set max_execution_time set to 500, click “Delete Previously Imported Data”, uncheck “Skip previously imported customers and orders”, click “Start” with “All” selected under “Import historical data”. I get “Status: All historical data imported”. There are no errors in JS console, and only the familiar “Unknown column ‘0000-00-00 00:00:00′” in debug.log.

    Thread Starter swinggraphics

    (@swinggraphics)

    Support from woo.com has indicated that particular server settings are required. Those should be listed in the system requirements for WC. They also stated that “Adding `SET SQL_MODE=’ALLOW_INVALID_DATES’; to the queries when they are run on your site produces expected results”. It seems to me that the query as run by the WC plugin should include that addition if it produces the intended results.

    Despite changing the db server setting as instructed, our Customers table remains empty.

    No reply is needed to this thread at this point, but I want to stop it from being marked “resolved” and closed to further replies; because when we do find the solution, I want to be able post it here.

    Followed everything in this topic, using standard WC testing practices (storefront, only WC active), altered the SQL mode as well.

    No luck! The table is updated when I import the data manually via Analytics > Settings

    Found a solution, it was due to other conflicting Global SQL mode options

    Try setting only the options below, my customer list appeared after regenerating the lookup table

    • This reply was modified 9 months, 1 week ago by smgdarien.
    Thread Starter swinggraphics

    (@swinggraphics)

    Thanks @smgdarien! We will give that a try. What do you mean by “regenerating the lookup table”?

    After you update your SQL mode, clear your analytics cache (WooCommerce > Status > Tools > Clear analytics cache), head to Analytics > Settings, Delete Historical Import, Reimport

    In my case I had to uncheck the skip option to see the correct data

    Plugin Support Zubair Zahid (woo-hc)

    (@doublezed2)

    Hello smgdarien

    Thank you for sharing a solution with the community. ??
    I hope this solution is helpful for swinggraphics and other members who face a similar problem.

    Have a great day!

    Thread Starter swinggraphics

    (@swinggraphics)

    Our server admin finally got around to trying your suggestion @smgdarien, and success! Thank you so much!

    @woo-hc folks, please update the system requirements for the plugin.

    Plugin Support omarfpg a11n

    (@omarfpg)

    Hi @swinggraphics, thank you so much for confirming the solution proposed has worked for you. And special thanks to @smgdarien for providing it!

    I’ll pass this information along to the docs team for this to be added to the system requirements for the plugin.

    Cheers!
    Omar

Viewing 13 replies - 16 through 28 (of 28 total)
  • The topic ‘Customers list “No data to display”’ is closed to new replies.