Back to Reports
Home / Bug Reports / Plugin → Woocommerce

WooCommerce Customer Table Fails to Load with ANSI_QUOTES SQL Mode Enabled

open Sep 26, 2025 Plugin → Woocommerce

Summary:

When the MySQL database server has the ANSI_QUOTES SQL mode enabled, the WooCommerce Customers admin page fails to display any customer data, showing only 'No data to display'. This occurs because a SQL query in the code uses double quotes around a date string, which is interpreted as an identifier (column name) when ANSI_QUOTES is active, leading to a syntax error.

The root cause is a specific line in the customer report query within the assign_report_columns() method of the AutomatticWooCommerceAdminAPIReportsCustomersDataStore class. The problematic code is: IF(date_last_active <= "0000-00-00 00:00:00", NULL, date_last_active). In standard MySQL, double quotes can be used for string literals, but with ANSI_QUOTES enabled, they are treated as identifier quotes, causing the database to look for a column named 0000-00-00 00:00:00 and resulting in the error: WordPress database error Unknown column '0000-00-00 00:00:00' in 'field list'.

This bug prevents administrators from viewing or managing their customer list in the WooCommerce backend when using a database configuration that includes the ANSI_QUOTES mode.

How to Replicate:

  1. Configure the MySQL server to use sql_mode=ANSI_QUOTES (e.g., by editing the my.cnf file).
  2. Ensure WooCommerce has at least one customer with an order.
  3. In the WordPress admin, navigate to WooCommerce → Customers.
  4. Observe that the customer table is empty and displays the message 'No data to display'.