sql error

Hey

We’re getting very similar errors in our logs:

[Mon Feb 27 15:25:25 2017] [warn] [client REDACTED] mod_fcgid: stderr: WordPress database error Table 'wp_9_wfNotifications' doesn't exist for query SELECT * FROM wp_9_wfNotifications WHEREnew= 1 ANDcategory= 'wfplugin_keyconflict' ORDER BYpriorityASC,ctimeDESC LIMIT 1 made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('/var/www/vhosts/REDACTED/httpdocs/wp-config.php'), require_once('wp-settings.php'), do_action('plugins_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, wordfence::veryFirstAction, wfCrawl::isVerifiedGoogleCrawler, wfCrawl::verifyGooglebotViaNOC1, wfAPI->call, wfNotification::getNotificationForCategory

Many errors similar to this but they reference different multisites.

If you need any further information please let me know.

Many thanks,
James

Hi. Thanks for providing a great tool.

I have a multisite WordPress installation. NB: This installation has been running since WordPress MU days. I have also been using Wordfence for a couple of years.

I too am experiencing these errors in my logs for poor referencing of the wfNotifications table. First error log entry recorded Jan 31. Still seen currently using:

WordPress 4.7.3
Wordfence 6.3.6

For me, I see error notifications for DB-TABLE-PREFIX_SITE-ID_wfNotifications references, e.g. wp_0_wfNotifications. Whilst, in fact, I only have a single DB table called: DB-TABLE-PREFIX_wfNotifications.

Please note re-installing the plugin, with the option to recreate database tables enabled, did not resolve this issue for me.

Example errors:

WordPress database error Table ‘wordpress.wp_0_wfNotifications’ doesn’t exist for query SELECT * FROM wp_0_wfNotifications WHERE new = 1 AND category = ‘wfplugin_updates’ ORDER BY priority ASC, ctime DESC LIMIT 1

WordPress database error Table ‘wordpress.wp_1_wfNotifications’ doesn’t exist for query INSERT IGNORE INTO wp_1_wfNotifications (id, category, priority, ctime, html, links) VALUES …

I also still see some errors for referencing _wfHits:

WordPress database error Table ‘wordpress.wp_1_wfHits’ doesn’t exist for query SELECT attackLogTime, IP, URL, UA, actionDescription, actionData FROM wp_1_wfHits WHERE action = ‘blocked:waf’ AND attackLogTime > (UNIX_TIMESTAMP() – 604800) ORDER BY attackLogTime DESC LIMIT 2000 made by do_action_ref_array, WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, wfActivityReport::executeCronJob, wfActivityReport->sendReportViaEmail, wfActivityReport->toEmailView, wfActivityReport->getFullReport, wfActivityReport->getRecentFirewallActivity, wfRecentFirewallActivity->run, referer

NB: I did need to re-install the plugin and recreate database tables already to get rid of errors that related to the ?? table. Which eliminated the following errors, e.g.

WordPress database error Unknown column ‘blockType’ in ‘field list’ for query INSERT INTO wp_wfBlockedIPLog (IP, countryCode, blockCount, unixday, blockType) VALUES …

Do you expect each subsite to have it;s own wfNotifications and wfHits tables? Or just one per installation?

Thanks.

Hi All,
Sorry for my late reply, it could be that you have installed Wordfence on every single site on the WordPress network you have? as you know Wordfence needs to be installed on your Network Admin plugin installation page, and not on each individual site. (reference: Set up Wordfence on Multisite).

Keep me updated,
Thanks.

Thanks for the suggestion. But the plugin is network activated.

Wordfence Security – Anti-virus, Firewall and Malware Scan
Version 6.3.6 | By Wordfence | View details
Network Activated
Network Only

@ta5ae Please go to (Wordfence > Tools => Diagnostics) and scroll down the page till “Send Report by Email” and send the report to “alaa [at] wordfence [dot] com”, make sure to include your forum username, I will take a look at this report and let you know my findings.

Thanks.

Thanks @wfalaa. Report email sent.

Tessa

@ta5ae Upon checking this issue, it seems that we fixed a bug similar to this one before, I know you mentioned that you have reinstalled the plugin, but I just want to double check on that, especially by enabling this option “Delete Wordfence tables and data on deactivation” on the plugin’s options page, and deactivate the plugin then re-install it again through WordPress Dashboard.

If you are still getting this error messages in the server error log file, I would appreciate if you can disable other installed plugins -temporarily- and re-check the error log file again, and make sure to include the full error messages “with dates” when replying to this thread.

P.S. We recommend PHP version 5.6 or newer, also WordPress recommends PHP version 7 or greater, some hosts might have older PHP versions installed but those are backported with security fixes and you will experience lower performance in general compared with the recent versions.

Thanks.

Please note I have already re-installed the plugin – with the option to delete the tables selected.

We are using PHP 5.3 with backported security fixes which WordFence claims to support.

I will trial disabling all other plugins on my demo site and see if this has any effect on the WordFence code writing to its database tables.

Can you confirm that WordFence expects to create multiple wfNotifications and wfHits tables, ie one per subsite? Or whether there should only be one wfNotifications and wfHits table WordPress installation should be created. Is this not an issue with the database table prefix in use in the query?

Thanks

Hi @wfalaa

I have sent you a report email from my demo site.

I have tried deactivating all plugins, including must use/drop in like plugin on my demo installation. Unfortunately, this does not stop the error from occurring.

I have verified that the database tables are actually removed from the database.

I have re-installed the WordFence plugin with and without deleting the tables. Unfortunately, the error remains.

Also upon activation, I see the following errors:

[Wed Apr 12 17:05:34 2017] [error] [client nnn.nnn.nnn.nnn] WordPress database error Table ‘wordpress.wp_0_wfNotifications’ doesn’t exist for query SELECT * FROM wp_0_wfNotifications WHERE new = 1 AND category = ‘wfplugin_keyconflict’ ORDER BY priority ASC, ctime DESC LIMIT 1 made by require(‘wp-admin/plugins.php’), activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::runInstall, wfAPI->call, wfNotification::getNotificationForCategory, referer: https://example.com/wp-admin/network/plugins.php?plugin_status=active&paged=1&s
[Wed Apr 12 17:05:35 2017] [error] [client nnn.nnn.nnn.nnn] WordPress database error Table ‘wordpress.wp_0_wfFileMods’ doesn’t exist for query ALTER TABLE wp_0_wfFileMods ADD COLUMN stoppedOnSignature VARCHAR(255) NOT NULL DEFAULT ” made by require(‘wp-admin/plugins.php’), activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::runInstall, referer: https://example.com/wp-admin/network/plugins.php?plugin_status=active&paged=1&s
[Wed Apr 12 17:05:35 2017] [error] [client nnn.nnn.nnn.nnn] WordPress database error Table ‘wordpress.wp_0_wfFileMods’ doesn’t exist for query ALTER TABLE wp_0_wfFileMods ADD COLUMN stoppedOnPosition INT UNSIGNED NOT NULL DEFAULT ‘0’ made by require(‘wp-admin/plugins.php’), activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::runInstall, referer: https://example.com/wp-admin/network/plugins.php?plugin_status=active&paged=1&s
[Wed Apr 12 17:05:35 2017] [error] [client nnn.nnn.nnn.nnn] WordPress database error Table ‘wordpress.wp_0_wfBlockedIPLog’ doesn’t exist for query ALTER TABLE wp_0_wfBlockedIPLog ADD blockType VARCHAR(50) NOT NULL DEFAULT ‘generic’ made by require(‘wp-admin/plugins.php’), activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::runInstall, referer: https://example.com/wp-admin/network/plugins.php?plugin_status=active&paged=1&s
[Wed Apr 12 17:05:35 2017] [error] [client nnn.nnn.nnn.nnn] WordPress database error Table ‘wordpress.wp_0_wfBlockedIPLog’ doesn’t exist for query ALTER TABLE wp_0_wfBlockedIPLog DROP PRIMARY KEY made by require(‘wp-admin/plugins.php’), activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::runInstall, referer: https://example.com/wp-admin/network/plugins.php?plugin_status=active&paged=1&s
[Wed Apr 12 17:05:35 2017] [error] [client nnn.nnn.nnn.nnn] WordPress database error Table ‘wordpress.wp_0_wfBlockedIPLog’ doesn’t exist for query ALTER TABLE wp_0_wfBlockedIPLog ADD PRIMARY KEY (IP, unixday, blockType) made by require(‘wp-admin/plugins.php’), activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::runInstall, referer: https://example.com/wp-admin/network/plugins.php?plugin_status=active&paged=1&s
[Wed Apr 12 17:05:35 2017] [error] [client nnn.nnn.nnn.nnn] WordPress database error Table ‘wordpress.wp_0_wfFileMods’ doesn’t exist for query ALTER TABLE wp_0_wfFileMods ADD COLUMN SHAC BINARY(32) NOT NULL DEFAULT ” AFTER newMD5 made by require(‘wp-admin/plugins.php’), activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::runInstall, referer: https://example.com/wp-admin/network/plugins.php?plugin_status=active&paged=1&s
[Wed Apr 12 17:05:35 2017] [error] [client nnn.nnn.nnn.nnn] WordPress database error Table ‘wordpress.wp_0_wfFileMods’ doesn’t exist for query ALTER TABLE wp_0_wfFileMods ADD COLUMN isSafeFile VARCHAR(1) NOT NULL DEFAULT ‘?’ AFTER stoppedOnPosition made by require(‘wp-admin/plugins.php’), activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::runInstall, referer: https://example.com/wp-admin/network/plugins.php?plugin_status=active&paged=1&s

I hope this helps.

Tessa

Hi Tessa,
Regarding PHP version, the recommended version is 5.6 or newer, I’m not saying this issue could be related to that old PHP version you have, but it was just something worth to mention while checking your website diagnostic report, as I mentioned using backported versions will guarantee fixing the security issues but without the performance upgrade in newer versions.

It’s not expected to have multiple databases tables (wfNotifications, wfHits and others) for every site you have on the network, only one set of tables should be there.

After investigating the diagnostic report, it turned out that “WP_ALLOW_MULTISITE” constant isn’t defined on your website, didn’t you follow this step to allow Multisite on your WordPress installation?

This definitely can cause all these troubles.

Thanks.

Hi @wfalaa

I inherited the WordPress installation where multi-sites were already/always enabled. AS far as I am aware it came from a WordPress MU install.

Reading around, I see that WP_ALLOW_MULTISITE allows the creation of new networks but that MULTISITE constant is expected to continue to exist and is checked in the is_multisite function and other places.

I will investigate defining the MULTISITE and or WP_ALLOW_MULTISITE constants on our demo site. I will report back on the forum, if this was the problem with my installation.

Thanks for your assistance.

FYI: We have SUNRISE defined as on, so the is_multisite function returns True in our case still.

While I will investigate my installation’s usage of the MULTISITE, this would explain why some plugins and WordPress see the site a multisite.

• This reply was modified 7 years, 7 months ago by ta5ae.

I can confirm that the reported errors do not appear if the MULTISITE constant is defined. As this affects the value returned by the call to the WordFence function wfDB::networkPrefix. Which tries to find out the DB table prefix for blog site 0 with $wpdb->get_blog_prefix(0).

Unfortunately, once MULTISITE is defined this brings up other issues regarding the wp_options table. Due to the way WordPress has developed over the years, it will take some digging to resolve what are the ramifications of defining the constant MULTISITE on a multisite installation which has developed from WordPress MU days with SUNRISE defined and in use.

NB: The is_multisite() function.

Is there a reason the code does not reference the base prefix every time if only one table should ever exist for wfNotifications, wfHits etc. i.e. wp_wfNotifications vs wp_0_wfNotifications in the first place?

Hi @ta5ae
I have logged a case for this issue on our issue tracking system (internal reference number: #5044) this should enhance the way we detect the table prefix on websites with similar installations as yours (which is -honestly- the first I’ve seen), I can’t tell for sure when this will be implemented in a future release, however, I’m sure you can get WordPress Network websites working properly with both “SUNRISE” and “MULTISITE” defined, I’m copying the following lines from one of my testing network sites:

define( 'WP_ALLOW_MULTISITE', true );
define('MULTISITE', true);
define( 'SUNRISE', 'on' );

“SUNRISE” should handle the domain mapping feature in such a case.

Thanks.

@ta5ae said:

…it will take some digging to resolve what are the ramifications of defining the constant MULTISITE on a multisite installation which has developed from WordPress MU days with SUNRISE defined and in use.

Thank you for providing such great detail. I have a similar issue, numerous wp_wfNotofications errors clogging up our error logs every day, on an otherwise perfectly functioning multisite install migrated from WPMU years ago.

If I define(‘MULTISITE’, true); however, we get an error establishing database connection across the whole network.

Did you ever resolve the issues you encountered when defining MULTISITE in wp-config? If so, how?

@wfalaa said:

#5044…this should enhance the way we detect the table prefix on websites with similar installations as yours (which is -honestly- the first I’ve seen)…

Did this ever get implemented?

We are clearly not the only two legacy WPMU (multisite) installations out there hoping to run Wordfence. And something is clearly still going on with the way Wordfence detects multisite and adds primary site tables on original WPMU installs.

I have a Premium Wordfence API key and have been communicating with your support team who has been very helpful…until the recent response stating our “multisite configuration is broken.”

Our configuration is not “broken” as the multisite network has been operating fine since we created it years ago. The wp_0_wfNotifications errors we now encounter in our logs are the first indication of anything mis-configured, there are no other symptoms.

The recommended solution to define(‘MULTISITE’, true); will not work for us since doing so results in the error establishing database connection. When testing output on the main blog, we get base_prefix: wp_ get_blog_prefix: wp_0_

Any additional assistance or feedback for further troubleshooting is greatly appreciated. Thank you!