Multisite and SQL Syntax Errors

samedwards
(@samedwards)

2 years, 7 months ago

Hello,

While trying your plugin for a large multi-site network, I came across two errors relating to the database upgrade/installation process.

The first issue is with the check for the database version not working correctly on a multisite installation. This was reported once before without a resolution: https://wordpress.org/support/topic/activation-issue-on-multisite/. The issue remains, where the hfcm_options_install() function uses add_option() to set the installed version, while hfcm_db_update_check() uses get_site_option() to check it. When the plugin is activated on a single site on a network, get_site_option() checks at the network level (the terminology for multisite is a bit muddled, “site” means “network”), which is not set by add_option().

The related issue here is in the database create query done in hfcm_options_install(). This has also been reported more than once https://wordpress.org/support/topic/sql-syntax-error-18/ and https://wordpress.org/support/topic/you-have-an-error-in-your-sql-syntax-26/. You’re using dbDelta(), which is good, but as the Codex notes, dbDelta is quite picky (it’s essentially using regexes to parse the query). There are two issues with your query. One is the inclusion of IF NOT EXISTS, which dbDelta is trying to parse as part of the table name. dbDelta already has code to check for the table existing, so this is not needed. The other issue is the lack of any whitespace between the table name and the opening parenthesis $table_name(, which causes the same issue where dbDelta tries to include the parenthesis as part of the table name.

Correcting the first line of the opening SQL statement to
"CREATE TABLE $table_name (
should solve both of these issues.

A further note: dbDelta can also do schema upgrades transparently, so you could get rid of all of your checks and ALTERs if you wanted to.

Viewing 7 replies - 1 through 7 (of 7 total)

Thread Starter samedwards
(@samedwards)

2 years, 7 months ago
And since these issues have both been reported before without resolution, I’ve gone ahead and made some workarounds that I’ve installed in an mu-plugin if anyone else comes across this and wants and immediate fix.
```
add_filter('dbdelta_queries', function ($queries) {
    global $wpdb;

    if (class_exists('NNR_HFCM')) {
        $table_name = $wpdb->prefix . NNR_HFCM::$nnr_hfcm_table;
        $badsql  = "CREATE TABLE IF NOT EXISTS $table_name(";
        $goodsql = "CREATE TABLE $table_name (";

        foreach ($queries as $id => &$query) {
            if (strpos($query, $badsql) === 0) {
                $query = str_replace($badsql, $goodsql, $query);
            }
        }
    }

    return $queries;
});

add_filter('pre_site_option_hfcm_db_version', function () {
    return get_option('hfcm_db_version');
});
```
Plugin Support 99robotsteam
(@99robotsteam)

2 years, 7 months ago

@samedwards

Can you please send us a note to – support@draftpress.com – as we’d like to provide you with a small gift for identifying this issue and solution?

Thread Starter samedwards
(@samedwards)

2 years, 7 months ago

@99robotsteam Do you have plans to fix these bugs in the plugin? I see there have been two new releases recently, but that these were not fixed. I’ve submitted a GitHub pull request with the proper fixes for these issues at https://github.com/99robots/99robots-header-footer-code-manager/pull/6

Plugin Author 99robots
(@99robots)

2 years, 7 months ago

Hey @samedwards – Yup. In fact, developer reviewing and working on it as we speak. You’ll see it come through in the next release.

Plugin Contributor DraftPress
(@draftpress)

2 years, 3 months ago

Hey @samedwards, this was reviewed and all the necessary changes were implemented in the last published version. Please update to the latest version. Thank you.

Thread Starter samedwards
(@samedwards)

2 years, 3 months ago

Hello,

The issue still remains with checking the hfcm_db_version option in the current version of the plugin. Please refer to my original post for more details about how using get_site_option() is the wrong function to use.

Plugin Support 99robotsteam
(@99robotsteam)

2 years ago

Hi @samedwards – We are still making updates to our plugin, but you can try the latest version of HFCM.

Thank you very much.