Cannot add foreign key constraint for the query CREATE TABLE `wp_icwp_wpsf_scanresults`

[ghost]

Today I got overflowed by errors:

[23-Mar-2022 08:07:29 UTC] WordPress database error Cannot add foreign key constraint for the query CREATE TABLE `wp_icwp_wpsf_scanresults` (
                id int(11) UNSIGNED NOT NULL AUTO_INCREMENT  COMMENT 'Primary ID', 
scan_ref int(11) UNSIGNED NOT NULL  COMMENT 'Foreign Key For Primary ID', 
resultitem_ref int(11) UNSIGNED NOT NULL  COMMENT 'Foreign Key For Primary ID', 
created_at int(15) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Created', 
PRIMARY KEY  (id), 
FOREIGN KEY (scan_ref) REFERENCES wp_icwp_wpsf_scans(id) ON DELETE CASCADE ON UPDATE CASCADE, 
FOREIGN KEY (resultitem_ref) REFERENCES wp_icwp_wpsf_resultitems(id) ON DELETE CASCADE ON UPDATE CASCADE
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci; done by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('plugins_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, icwp_wpsf_init, require_once('/plugins/wp-simple-firewall/init.php'), ICWP_WPSF_Shield_Security->start, FernleafSystems\Wordpress\Plugin\Shield\Controller\Controller->loadAllFeatures, do_action('icwp-wpsf-run_processors'), WP_Hook->do_action, WP_Hook->apply_filters, FernleafSystems\Wordpress\Plugin\Shield\Modules\Base\ModCon->onRunProcessors, FernleafSystems\Wordpress\Plugin\Shield\Modules\HackGuard\ModCon->isReadyToExecute, FernleafSystems\Wordpress\Plugin\Shield\Modules\HackGuard\ModCon->getDbH_ScanResults, FernleafSystems\Wordpress\Plugin\Shield\Modules\Base\Databases->loadDbH, FernleafSystems\Wordpress\Plugin\Core\Databases\Base\Handler->execute, FernleafSystems\Wordpress\Plugin\Core\Databases\Base\Handler->run, FernleafSystems\Wordpress\Plugin\Core\Databases\Base\Handler->tableInit, FernleafSystems\Wordpress\Plugin\Core\Databases\Base\Handler->isReady, FernleafSystems\Wordpress\Plugin\Core\Databases\Common\AlignTableWithSchema->align, FernleafSystems\Wordpress\Services\Core\Db->doSql

...

23-Mar-2022 10:54:41 UTC] WordPress database error Table 'Sql_1.wp_icwp_wpsf_scanresults' doesn't exist for query SELECT COUNT(*)
                        FROM `wp_icwp_wpsf_scanresults` as sr
                        INNER JOIN `wp_icwp_wpsf_scans` as `scans`
                            ON `sr`.scan_ref = `scans`.id
                        INNER JOIN `wp_icwp_wpsf_resultitems` as `ri`
                            ON `sr`.resultitem_ref = `ri`.id
                        INNER JOIN `wp_icwp_wpsf_resultitem_meta` as `rim`
                            ON `rim`.`ri_ref` = `ri`.id
                        WHERE `sr`.`scan_ref`=253 AND `ri`.`auto_filtered_at`=0 AND `ri`.`ignored_at` = 0 AND `ri`.`item_repaired_at`=0 AND `ri`.`item_deleted_at`=0 AND `ri`.`deleted_at`=0 AND `ri`.notified_at=0; made by do_action_ref_array('icwp-wpsf-hourly'), WP_Hook->do_action, WP_Hook->apply_filters, FernleafSystems\Wordpress\Plugin\Shield\Crons\HourlyCron->runCron, do_action('icwp-wpsf-hourly_cron'), WP_Hook->do_action, WP_Hook->apply_filters, FernleafSystems\Wordpress\Plugin\Shield\Modules\Reporting\Lib\ReportingController->runHourlyCron, FernleafSystems\Wordpress\Plugin\Shield\Modules\Reporting\Lib\ReportingController->buildAndSendReport, FernleafSystems\Wordpress\Plugin\Shield\Modules\Reporting\Lib\ReportingController->buildReportAlerts, FernleafSystems\Wordpress\Plugin\Shield\Modules\Reporting\Lib\Reports\Build\BaseBuilder->build, FernleafSystems\Wordpress\Plugin\Shield\Modules\Reporting\Lib\Reports\Build\BuilderAlerts->gather, FernleafSystems\Wordpress\Plugin\Shield\Modules\HackGuard\Lib\Reports\ScanAlerts->build, FernleafSystems\Wordpress\Plugin\Shield\Modules\HackGuard\Lib\Reports\Query\ScanCounts->standard, FernleafSystems\Wordpress\Plugin\Shield\Modules\HackGuard\Scan\Results\Retrieve->count, FernleafSystems\Wordpress\Services\Core\Db->getVar23-Mar-2022 10:54:41 UTC] WordPress database error Table 'Sql_1.wp_icwp_wpsf_scanresults' doesn't exist for query SELECT COUNT(*)
                        FROM `wp_icwp_wpsf_scanresults` as sr
                        INNER JOIN `wp_icwp_wpsf_scans` as `scans`
                            ON `sr`.scan_ref = `scans`.id
                        INNER JOIN `wp_icwp_wpsf_resultitems` as `ri`
                            ON `sr`.resultitem_ref = `ri`.id
                        INNER JOIN `wp_icwp_wpsf_resultitem_meta` as `rim`
                            ON `rim`.`ri_ref` = `ri`.id
                        WHERE `sr`.`scan_ref`=253 AND `ri`.`auto_filtered_at`=0 AND `ri`.`ignored_at` = 0 AND `ri`.`item_repaired_at`=0 AND `ri`.`item_deleted_at`=0 AND `ri`.`deleted_at`=0 AND `ri`.notified_at=0; made by do_action_ref_array('icwp-wpsf-hourly'), WP_Hook->do_action, WP_Hook->apply_filters, FernleafSystems\Wordpress\Plugin\Shield\Crons\HourlyCron->runCron, do_action('icwp-wpsf-hourly_cron'), WP_Hook->do_action, WP_Hook->apply_filters, FernleafSystems\Wordpress\Plugin\Shield\Modules\Reporting\Lib\ReportingController->runHourlyCron, FernleafSystems\Wordpress\Plugin\Shield\Modules\Reporting\Lib\ReportingController->buildAndSendReport, FernleafSystems\Wordpress\Plugin\Shield\Modules\Reporting\Lib\ReportingController->buildReportAlerts, FernleafSystems\Wordpress\Plugin\Shield\Modules\Reporting\Lib\Reports\Build\BaseBuilder->build, FernleafSystems\Wordpress\Plugin\Shield\Modules\Reporting\Lib\Reports\Build\BuilderAlerts->gather, FernleafSystems\Wordpress\Plugin\Shield\Modules\HackGuard\Lib\Reports\ScanAlerts->build, FernleafSystems\Wordpress\Plugin\Shield\Modules\HackGuard\Lib\Reports\Query\ScanCounts->standard, FernleafSystems\Wordpress\Plugin\Shield\Modules\HackGuard\Scan\Results\Retrieve->count, FernleafSystems\Wordpress\Services\Core\Db->getVar

Full log: debug.log

[paulgoodchild]

It looks like either tables were deleted, or this is a new installation and there is a problem creating new tables.

Are these errors still going? If so, please delete the database tables:

• icwp_wpsf_resultitem_meta
• icwp_wpsf_resultitems
• icwp_wpsf_scanitems
• icwp_wpsf_scanresults
• icwp_wpsf_scans

You may see some errors in the logs after doing this until Shield picks up that the tables don't exist.

[ghost]

It is an old install and these errors are still going. I have updated the plug-in the other day, so the errors of today has appeared without know cause.

Apparently these table exist:

• icwp_wpsf_resultitem_meta
• icwp_wpsf_resultitems
• icwp_wpsf_scans

But these doesn't exist:

• icwp_wpsf_scanitems
• icwp_wpsf_scanresults

I will try to delete all of them but something isn't working properly.

[ghost]

Deleting the table fixed the issue, thanks. Do you need some specific info for discovering how it is happened?

[paulgoodchild]

There are 2 possible causes:

• one of the tables got corrupted and it prevented 1 of the tables that links to it from being created
• the WP prefix was altered and the DB tables were renamed - this broke the foreign key link. I've only seen this once before now.

[ghost]

The only thing done was moving from a server with MySQL 5.6 to a server with 5.7 but it happened days before the plug-in update, so if it was having a problem would have appeared earlier.

[ghost]

I'm closing it because I'm not sure if the problem is reproducible.