Database table doesn’t exist

Resolved

(@cagsmith)

Howdy, we have a customer whose site is filling up the error log fast – 30GB and counting. All the errors are the same and it’s being written to non-stop.

It appears a database table doesn’t exist for this plugin – what is the best way to create this, or have the plugin create it? Example error is below – have replaced the actual database name with “database-name”:

[27-Mar-2023 16:44:45 UTC] WordPress database error Table 'database-name.edh_icwp_wpsf_ip_rules' doesn't exist for query SELECT ips.ip as ip, ir.id, ir.ip_ref, ir.cidr, ir.is_range, ir.offenses, ir.type, ir.label, ir.last_access_at, ir.blocked_at, ir.unblocked_at, ir.last_unblock_attempt_at, ir.expires_at, ir.updated_at, ir.created_at, ir.deleted_at
                                        FROM edh_icwp_wpsf_ip_rules as ir
                                        INNER JOIN edh_icwp_wpsf_ips as ips
                                                ON ir.ip_ref = ips.id
                                        WHERE (ips.ip=INET6_ATON('15.235.23.65') AND ir.is_range='0') OR (ir.is_range='1')
                                        ORDER BY ir.created_at DESC

                                        ; made 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->boot, FernleafSystems\Wordpress\Plugin\Shield\Rules\RulesController->processRules, FernleafSystems\Wordpress\Plugin\Shield\Rules\RulesController->processRule, FernleafSystems\Wordpress\Plugin\Shield\Rules\Processors\ConditionsProcessor->runAllRuleConditions, FernleafSystems\Wordpress\Plugin\Shield\Rules\Processors\ConditionsProcessor->processConditionGroup, FernleafSystems\Wordpress\Plugin\Shield\Rules\Conditions\Base->run, FernleafSystems\Wordpress\Plugin\Shield\Rules\Conditions\IsIpWhitelisted->execConditionCheck, FernleafSystems\Wordpress\Plugin\Shield\Modules\IPs\Lib\IpRules\IpRuleStatus->isBypass, FernleafSystems\Wordpress\Plugin\Shield\Modules\IPs\Lib\IpRules\IpRuleStatus->getRulesForBypass, FernleafSystems\Wordpress\Plugin\Shield\Modules\IPs\Lib\IpRules\IpRuleStatus->getRules, FernleafSystems\Wordpress\Plugin\Shield\Modules\IPs\Lib\IpRules\IpRuleStatus->loadRecordsForIP, FernleafSystems\Wordpress\Plugin\Shield\Modules\IPs\DB\IpRules\LoadIpRules->select, FernleafSystems\Wordpress\Plugin\Shield\Modules\Data\DB\Common\BaseLoadRecordsForIPJoins->selectRaw, FernleafSystems\Wordpress\Services\Core\Db->selectCustom

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

Plugin Author Paul
(@paultgoodchild)

1 year ago

Hi,

Sorry to hear about the trouble. This is a strange one since Shield would normally automatically create any tables you need. Can you check whether the website has enough MySQL disk space so that it can create any new tables? I can’t think why it wouldn’t be able to.

The logs might point to other mysql errors that are occurring – can you dig a little more through the logs and look for errors pertaining to mysql CREATE commands?

Thread Starter cagsmith
(@cagsmith)

1 year ago

Hi there,

Thanks for the reply! It was necessary to remove the error log unfortunately because the log was causing the account to max out their available disk space so we’re unable to look for any historic CREATE errors.

Disk space is presently normal – including for MySQL and there’s no reason why a new table wouldn’t have been able to be created.

It is conceivable that this table might have been created (or tried to have been created) at a time when the account had previously exhausted their available disk space, thus resulting in this error.

Plugin Author Paul
(@paultgoodchild)

1 year ago

That might explain it… If you can keep an eye out for the create errors, that’d be a great help of it happens again. Cheers
Thread Starter cagsmith
(@cagsmith)

1 year ago
Hey there,

Right… but this doesn’t actually address the issue at hand, where the missing table is causing hundreds and hundreds of errors to spew into error_log.

So, is there a way to trigger the plugin to check for and create (if not existing) the tables that it needs?

I did install this plugin on an external staging site (not related to the specific site with this problem) and figured I could get around the issue by exporting and importing the specific missing table, but I get this when importing the table:
```
Foreign key constraint is incorrectly formed
```
Plugin Author Paul
(@paultgoodchild)

1 year ago

The plugin does it all automatically itself. It’ll attempt to create the table, and any other connected tables itself, if they doesn’t exist – which why I’ve asked you to look for mysql error logs for create. You don’t need, and we haven’t provided the ability, to manually create the table(s).

Doing the import as you describe won’t work, since, as the error indicates, there are foreign key restraints.

As to the error logs, that’s configurable by PHP itself. To configure that, you could start here, for example.
https://stackify.com/php-error-logs-guide/

My suggestion would be to disable Shield, then completely delete all database tables that start with icwp_wpsf_ and on the mysql server itself, look for the db table files and ensure they’re all deleted. The mysql server is clearly having an issue recreating the table, so you may need to do some cleanup there.
Thread Starter cagsmith
(@cagsmith)

1 year ago
Hi there, I see this error now – you wanted to be notified in the event there were any create errors:
```
[29-Mar-2023 13:47:04 UTC] WordPress database error Can't create table database_wor1.edh_icwp_wpsf_scanresults (errno: 150 "Foreign key constraint is incorrectly formed") for query CREATE TABLE edh_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 edh_icwp_wpsf_scans(id) ON DELETE CASCADE ON UPDATE CASCADE, 
FOREIGN KEY (resultitem_ref) REFERENCES edh_icwp_wpsf_resultitems(id) ON DELETE CASCADE ON UPDATE CASCADE
			) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; made 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/plugin_init.php'), ICWP_WPSF_Shield_Security->start, FernleafSystems\Wordpress\Plugin\Shield\Controller\Controller->boot, FernleafSystems\Wordpress\Plugin\Shield\Controller\Controller->loadModules, FernleafSystems\Wordpress\Plugin\Shield\Controller\Checks\PreModulesBootCheck->run, FernleafSystems\Wordpress\Plugin\Shield\Modules\Base\Databases->loadAllDbHandlers, 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\Common\AlignTableWithSchema->align, FernleafSystems\Wordpress\Services\Core\Db->doSql
```
Plugin Author Paul
(@paultgoodchild)

1 year ago

This error doesn’t actually even relate to the original. Is this error reoccurring, or was it a once-off?

Did you try deleting all tables starting with icwp_wpsf_?

Thread Starter cagsmith
(@cagsmith)

1 year ago

Our customer deactivated and reactivated this plugin and then this error (most recent one) showed up. Apparently they were requested to do… something… by the plugin (they didn’t enlarge upon what exactly that was) but that error then stopped. Deactivating and reactivating also appears to have created the originally missing table, wpsf_ip_rules, and no more errors are being logged, so this appears fine now! Thank you for your time 🙂

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

The topic ‘Database table doesn’t exist’ is closed to new replies.

Tags