Uncaught mysqli_sql_exception for WP_STATISTICS\Install::create_events_table() (Multisite installation)

[nowrap]

Hello,

i've got a php fatal error with wp-statistics 14.5.2 on multisite wordpress (version 5.8.9) under php 8.2.

[21-Mar-2024 08:18:27 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF' at line 1 in /var/www/wordpress/wp-includes/wp-db.php:2056 Stack trace: #0 /var/www/wordpress/wp-includes/wp-db.php(2056): mysqli_query() #1 /var/www/wordpress/wp-includes/wp-db.php(1945): wpdb->_do_query() #2 /var/www/wordpress/wp-includes/wp-db.php(2695): wpdb->query() #3 /var/www/wordpress/wp-admin/includes/upgrade.php(2749): wpdb->get_results() #4 /var/www/wordpress/wp-content/plugins/wp-statistics/includes/class-wp-statistics-install.php(254): dbDelta() #5 /var/www/wordpress/wp-content/plugins/wp-statistics/includes/class-wp-statistics-install.php(442): WP_STATISTICS\Install::create_events_table() #6 /var/www/wordpress/wp-includes/class-wp-hook.php(303): WP_STATISTICS\Install->plugin_upgrades() #7 /var/www/wordpress/wp-includes/class-wp-hook.php(327): WP_Hook->apply_filters() #8 /var/www/wordpress/wp-includes/plugin.php(470): WP_Hook->do_action() #9 /var/www/wordpress/wp-settings.php(578): do_action() #10 /var/www/wordpress/wp-config.php(118): require_once('...') #11 /var/www/wordpress/wp-load.php(50): require_once('...') #12 /var/www/wordpress/wp-admin/admin.php(34): require_once('...') #13 /var/www/wordpress/wp-admin/network/admin.php(13): require_once('...') #14 /var/www/wordpress/wp-admin/network/users.php(11): require_once('...') #15 {main} thrown in /var/www/wordpress/wp-includes/wp-db.php on line 2056

I logged the generated sql to the wordpress debug.log:

[21-Mar-2024 08:18:27 UTC] CREATE TABLE IF NOT EXISTS wp_statistics_events ( ID bigint(20) NOT NULL AUTO_INCREMENT, date datetime NOT NULL, page_id bigint(20) NULL, visitor_id bigint(20) NULL, event_name varchar(64) NOT NULL, event_data text NOT NULL, PRIMARY KEY (ID), KEY visitor_id (visitor_id), KEY page_id (page_id), KEY event_name (event_name) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci

Running the query manually works and the table exists.

It seems the the method create_events_table is run on every visit of wp-admin. For the moment i have commented out the call from dbDelta.

Regards nowrap

[mostafasoufi]

Hey,

Can you please tell me what is your MySQL and plugin table version?

SELECT `option_value` FROM `wp_options` WHERE `option_name` = 'wp_statistics_plugin_version';

[nowrap]

Hello,

the mysql version is: 8.0.36

The option value query returned: 14.5.2

Regards nowrap

[mostafasoufi]

Can you please double-check whether the code this code blocks is returning or not?

https://github.com/wp-statistics/wp-statistics/blob/master/includes/class-wp-statistics-install.php#L378-L382

[nowrap]

Sure,

i added:

        // Check installed plugin version
        $installed_version = get_option('wp_statistics_plugin_version');
error_log($installed_version);
error_log(WP_STATISTICS_VERSION);
error_log($installed_version == WP_STATISTICS_VERSION);
        if ($installed_version == WP_STATISTICS_VERSION) {
            return;
        }

The result is:

[21-Mar-2024 10:47:20 UTC] 14.5.2 [21-Mar-2024 10:47:20 UTC] 14.5.2 [21-Mar-2024 10:47:20 UTC] 1

So it should exit the method right away.

But i guess because of the error before the version in the DB wasn't adjusted. After commenting out the dbDelta call the plugin_upgrades finished it's run.

Regards nowrap

[mostafasoufi]

Thank you for debugging, it is weird indeed.

Is there any specific error mentioned in the MySQL log?

[nowrap]

You're welcome. Sorry, i've just the log from the wp debug.log. But the query looks good and can be executed manually.

I am not familiar with the dbDelta() function. Maybe this one is the source of the exception - it's mentioned in the stacktrace?

[mostafasoufi]

dbDelta() should maintain the integrity of the query structure and execute it without alterations.

I'll investigate more and will get back to you.

Best