PHP Fatal error: Uncaught mysqli_sql_exception: Table doesn't exist in wordpress/wp-includes/wp-db.php

Question

I am writing a plugin and want to create a database table when the user installs / activates it I have written the following into an install.php

global $wpdb; 
$fm_db_version = '1.0.0';
$db_table_name = $wpdb->prefix . 'food_menu';  // table name
$charset_collate = $wpdb->get_charset_collate();

//Check to see if the table exists already, if not, then create it
if($wpdb->get_var( "show tables like '$db_table_name'" ) != $db_table_name ) 
{
    $sql = "CREATE TABLE $db_table_name (
        id int(11) unsigned NOT NULL AUTO_INCREMENT,
        item_title varchar(150) NOT NULL,
        item_description text NOT NULL,
        item_price decimal(10,2) NOT NULL,
        item_section enum('brunch','lunch','dinner','drinks','nye','easter') DEFAULT 'brunch',
        item_tags text DEFAULT NULL,
        item_image int(11) DEFAULT NULL,
        is_vegan bigint DEFAULT '0',
        is_vegetarian bigint DEFAULT '0',
        is_gluten_free bigint DEFAULT '0',
        is_dairy_free bigint DEFAULT '0',
        has_nuts bigint DEFAULT '0',
        item_date datetime DEFAULT NULL,
        PRIMARY KEY  (id)
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
    add_option( 'food_menu_version', $fm_db_version );
}

And in my plugin file I have added this

// Install the Plugin's Data Table
function install() { include dirname(__FILE__) . '/install.php'; }
register_activation_hook(__file__, 'install');

// unInstall the Plugin's Data Table
function uninstall() { include dirname(__FILE__) . '/uninstall.php'; }
register_uninstall_hook( __FILE__, 'uninstall' );

require_once(plugin_dir_path( __FILE__ ).'includes/fm-scripts.php');

add_action( 'init', function() {

    include dirname(__FILE__) . '/includes/class-foodmenuadminmenu.php';
    include dirname(__FILE__) . '/includes/class-foodmenu-list-table.php';
    include dirname(__FILE__) . '/includes/class-form-handler.php';
    include dirname(__FILE__) . '/includes/shortcode-functions-fm-display.php';
    include dirname(__FILE__) . '/includes/item-functions.php';

    new foodMenuAdminMenu();

});

When I try and activate I get the following error (I have removed full file paths for security)

Fatal error: Uncaught mysqli_sql_exception: Table 'wordpress.wp_food_menu' doesn't exist in /wordpress/wp-includes/wp-db.php:2056 
Stack trace:  
    #0 /wordpress/wp-includes/wp-db.php(2056): mysqli_query(Object(mysqli), 'DESCRIBE wp_foo...') 
    #1 /wordpress/wp-includes/wp-db.php(1945): wpdb->_do_query('DESCRIBE wp_foo...') 
    #2 /wordpress/wp-includes/wp-db.php(2695): wpdb->query('DESCRIBE wp_foo...') 
    #3 /wordpress/wp-admin/includes/upgrade.php(2749): wpdb->get_results('DESCRIBE wp_foo...') 
    #4 /wordpress/wp-content/plugins/food-menu/install.php(28): dbDelta(Array) 
    #5 /wordpress/wp-content/plugins/food-menu/food-menu.php(14): include('/FilePath/User/P...') 
    #6 /wordpress/wp-includes/class-wp-hook.php(303): install('') 
    #7 /wordpress/wp-includes/class-wp-hook.php(327): WP_Hook->apply_filters('', Array) 
    #8 /wordpress/wp-includes/plugin.php(470): WP_Hook->do_action(Array) 
    #9 /wordpress/wp-admin/plugins.php(193): do_action('activate_food-m...') 
    #10 {main} thrown in /wordpress/wp-includes/wp-db.php on line 2056

I am conscious that there may be similar questions to the above but those I have read do not provide an explanation / solution to this specific issue that I can see.

To be clear there isn't the table in the DB at the time of activating / installing the plugin as this is what I want to happen.

UPDATED to show full plugin file

Answer 1

I faced the same issue with PHP 8. In older PHP versions the MySQLi extension default error mode was set not to report the errors.

dbDelta uses Describe table_name; sql statement to determine if the table needs to created or updated. This throws error if the table doesn't exist. It was working perfectly because MySQLi default error mode was set to: MYSQLI_REPORT_OFF. So the errors were suppressed.

Now in PHP 8, MySQLi error mode is set to: MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT. So the error is not suppressed. Most of the plugins are facing the same issue.

The temporary work around is to disable error reporting before calling dbDelta:

mysqli_report(MYSQLI_REPORT_OFF);

Refer: https://php.watch/versions/8.1/mysqli-error-mode

Note from the above reference:

Note that mysqli_driver class has a mono-state pattern, which means once the error reporting value is set, it affects all MySQLi connections throughout the request unless the value is later overwritten in another call. The same applies for the mysqli_report function as well.