Woocommerce product categories are missing (not displaying) when adding product

Question

I have recently come across a problem where i can't assign products to categories as the categories aren't displaying in the meta box when adding products. Here is the example:

Once i add the new category it seems to disappear when the page is reloaded. You can see that it says 6 items but it doesn't show any categories. Here is an example:

Answer 1

OP created a Wisdom of the Ancients post for me with this one.

I turned on debugging and saw an error discussed in this question: How to resolve "ORDER BY clause is not in SELECT list" caused MySQL 5.7 with SELECT DISTINCT and ORDER BY. I was using a Managed MySQL service from Digital Ocean and couldn't modify the global settings or my.cnf files.

For my future self and other wanderers. My issue was that MySQL's 'ANSI' mode includes 'ONLY_FULL_GROUP_BY'.

WordPress filters out 'ONLY_FULL_GROUP_BY' by default in /wp-includes/wp-db.php but my Managed SQL server had ANSI set by default as well.

My solution was to make a crappy little WordPress plugin that would make sure they both got removed every session. https://fishy.getgit.co/fishy/remove-ansi-sql-mode

Or just Copy/Pasta:

<?php
/*
Plugin Name: Remove ANSI SQL_MODE
Version: 1.0
Description: Removes the 'ANSI' SQL MODE if it exists as it contains 'ONLY_FULL_GROUP_BY' since MySQL 5.7.5. See https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_ansi
*/
class Remove_Ansi_Sql_Mode {
    static function init(){
        add_action('init', array( __CLASS__, 'strip_ansi_mode' ) );
    }

    static function strip_ansi_mode(){
        global $wpdb;
        // Copied from /wp-includes/wp-db.php
        $incompatible_modes = array(
          'NO_ZERO_DATE',
          'ONLY_FULL_GROUP_BY',
          'STRICT_TRANS_TABLES',
          'STRICT_ALL_TABLES',
          'TRADITIONAL',
          'ANSI' // Adding ANSI
        );
        $sql_modes = explode(',', $wpdb->get_col( "SELECT @@SESSION.sql_mode" )[0]);
        foreach ($sql_modes as $key => $value) {
          if(in_array($value, $incompatible_modes)){
            unset($sql_modes[$key]);
          }
        }
        $wpdb->set_sql_mode($sql_modes);
    }   

}
Remove_Ansi_Sql_Mode::init();

Answer 2

I had this same issue after updating to WooCommerce 3.6. Solved it by deactivating the WP Term Images plugin.