Wordfence leaves a mess in the database

Trying to delete this comment. But can’t figure how. I added the content in the original post. Please delete this comment.

• This reply was modified 7 years, 3 months ago by mayankrungta. Reason: Wanting to delete this comment
• This reply was modified 7 years, 3 months ago by mayankrungta.

All the tables are created on WordFence install in a simple loop. One after the other. There is nothing special about the 3 tables you list as actually created, and in fact, the tables you end up with in this case are not even “in order”.

The Notifications and KnownFileList tables are the last tables created of all, and PendingIssues somewhere in the middle in the order.

That tells me something.

a) Your database filesystem likely was out of disk-space or iNodes at the time. Because of other things going on in your system something freed up space or inodes in the middle of creating tables. WF managed to create an initial table, then failed on a long list, and then managed to create the two last ones. ??
Just a bad situation.

b) WordFence does not check any errors returned from any table creation. Once in that “createAll” loop, it continues running even if something fails. Maybe they can improve that some. Creating the necessary tables should be a “create All, or create None” type thing. MySQL does not support DDL type transactions, but that is the principle that would have to be implemented manually.. If a table creation fails when in that loop, then roll-back/undo all the previously created. Start fresh.
But honestly, running out of space in the middle of table creation is such a rare occurrence, that I don’t see that as making much difference in the large picture.
WordPress itself is such a mismatch of code, that this “problem” can show up anywhere. Fixing one plugin out of thousands is of little consequence. ??

Yes, you can create the table by copying their definitions from another site. No problem.
BUT! The prefixes must be correct. You must adjust the table name prefix to match the site you are creating them in, or they twill never be found again.

I notice from your text that the system where they failed run with a WordPress base_prefix of “sbqte_” (a choice you made during the WordPress install). But the tables you list as trying to delete/create are using the prefix “wp_”, which is the DEFAULT WordPress table name prefix. You might be using that on another site?

So.. You can create the tables manually by copying their definitions from another site, but you must adjust the actual table names to match the site you are creating them for. Otherwise WordPress cannot find them. You cannot install “wp_” named tables on a site defined to use the “sbqte_” prefix on tables. The “wp_” named tables will appear from a WordPress perspective as if they were never created in the first place.

I would suggest you set up some simple automation to warn you when your DB filesystem run down on space or inodes.. Like by sending you an email warning when running under 20% or some number. WordFence, WordPress, or any plugin: when you run your Database filesystem out of resources, the results will frequently be indeterminate.

If that happened in the middle of for example saving a post in WordPress, I can guarantee that your options, posts and other tables would likely be out of sync as well. Parts of the post information would be saved, other parts not.

Hi,

The “wp_” prefix was appropriately replaced in all my attempts. I tried dumping WF tables from an empty schema from another site that works. When I tried with the tables with data it was giving errors. So tried without data, just creating the tables –

$ mysql -u root -p##### dbname < /tmp/wf.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1813 (HY000) at line 202: Tablespace ‘dbname.sbqte_wfKnownFileList‘ exists.

Before this a plain attempt to install led to the following errors in the database where I had cleared all traces of wf tables in the dbname database –

[Tue Aug 15 22:56:03.538136 2017] [:error] [pid 20412] [client XX.XX.XX.XX:4488] WordPress database error Table ‘dbname.sbqte_wfLockedOut’ doesn’t exist for query SHOW FULL COLUMNS FROM sbqte_wfLockedOut made by activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::isLockedOut, wfLog->isIPLockedOut, wfDB->querySingle, referer: https://dbname.com/wp-admin/plugin-install.php?s=wordfence&tab=search&type=term
[Tue Aug 15 22:56:03.541410 2017] [:error] [pid 20412] [client XX.XX.XX.XX:4488] WordPress database error Table ‘dbname.sbqte_wfLockedOut’ doesn’t exist for query SHOW FULL COLUMNS FROM sbqte_wfLockedOut made by activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::isLockedOut, wfLog->isIPLockedOut, wfDB->querySingle, referer: https://dbname.com/wp-admin/plugin-install.php?s=wordfence&tab=search&type=term
[Tue Aug 15 22:56:03.562689 2017] [:error] [pid 20412] [client XX.XX.XX.XX:4488] WordPress database error Tablespace ‘dbname.sbqte_wfBadLeechers‘ exists. for query create table IF NOT EXISTS sbqte_wfBadLeechers (\n\teMin int UNSIGNED NOT NULL,\n\tIP int UNSIGNED NOT NULL,\n\thits int UNSIGNED NOT NULL,\n\tPRIMARY KEY k1(eMin, IP)\n) default charset=latin1 made by activate_plugin, do_action(‘activate_wordfence/wordfence.php’), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, wordfence::installPlugin, wordfence::runInstall, wfSchema->createAll, wfDB->queryWrite, referer: https://dbname.com/wp-admin/plugin-install.php?s=wordfence&tab=search&type=term
[Tue Aug 15 22:56:03.569024 2017] [:error] [pid 20412] [client XX.XX.XX.XX:4488] WordPress database error Tablespace ‘dbname.sbqte_wfVulnScanners‘ exists. for query create table IF NOT EXISTS sbqte_wfVulnScanners (\n\tIP int UNSIGNED PRIMARY KEY,\n\tctime int UNSIGNED NOT NULL,\n\thits int UNSIGNED NOT NULL\n) made by activate_plugin, do_action(‘activate_wordfence/wordfence.php’), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, wordfence::installPlugin, wordfence::runInstall, wfSchema->createAll, wfDB->queryWrite, referer: https://dbname.com/wp-admin/plugin-install.php?s=wordfence&tab=search&type=term
[Tue Aug 15 22:56:03.575015 2017] [:error] [pid 20412] [client XX.XX.XX.XX:4488] WordPress database error Tablespace ‘dbname.sbqte_wfBlocks‘ exists. for query create table IF NOT EXISTS sbqte_wfBlocks (\n\tIP int UNSIGNED PRIMARY KEY,\n\tblockedTime int UNSIGNED NOT NULL,\n\treason varchar(255) NOT NULL,\n\tlastAttempt int UNSIGNED default 0,\n\tblockedHits int UNSIGNED default 0,\n\twfsn tinyint UNSIGNED default 0,\n\tpermanent tinyint UNSIGNED default 0,\n\tKEY k1(wfsn)\n) default charset=utf8 made by activate_plugin, do_action(‘activate_wordfence/wordfence.php’), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, wordfence::installPlugin, wordfence::runInstall, wfSchema->createAll, wfDB->queryWrite, referer: https://dbname.com/wp-admin/plugin-install.php?s=wordfence&tab=search&type=term
[Tue Aug 15 22:56:03.580199 2017] [:error] [pid 20412] [client XX.XX.XX.XX:4488] WordPress database error Tablespace ‘dbname.sbqte_wfConfig‘ exists. for query create table IF NOT EXISTS sbqte_wfConfig (\n name varchar(100) NOT NULL,\n val longblob,\n autoload enum(‘no’,’yes’) NOT NULL DEFAULT ‘yes’,\n PRIMARY KEY (name)\n) default charset=utf8 made by activate_plugin, do_action(‘activate_wordfence/wordfence.php’), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, wordfence::installPlugin, wordfence::runInstall, wfSchema->createAll, wfDB->queryWrite, referer: https://dbname.com/wp-admin/plugin-install.php?s=wordfence&tab=search&type=term
[Tue Aug 15 22:56:03.584330 2017] [:error] [pid 20412] [client XX.XX.XX.XX:4488] WordPress database error Tablespace ‘dbname.sbqte_wfCrawlers‘ exists. for query create table IF NOT EXISTS sbqte_wfCrawlers (\n\tIP INT UNSIGNED NOT NULL,\n\tpatternSig binary(16) NOT NULL,\n\tstatus char(8) NOT NULL,\n\tlastUpdate int UNSIGNED NOT NULL,\n\tPTR varchar(255) default ”,\n\tPRIMARY KEY k1(IP, patternSig)\n) default charset=latin1 made by activate_plugin, do_action(‘activate_wordfence/wordfence.php’), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, wordfence::installPlugin, wordfence::runInstall, wfSchema->createAll, wfDB->queryWrite, referer: https://dbname.com/wp-admin/plugin-install.php?s=wordfence&tab=search&type=term

What is an easy way to reset this plugin or my database or both?

Can you give me a list of commands that I can run on the db and it would do the needful? First cleansing of the database and then recreation of the tables?

Please note that at the current time there is no shortage of disk space on the server. I was referring to earlier issues I have had with Wordfence. Besides being able to deal with that problem what I was hoping for was that there is an easier mechanism to clean up any mess (first there should not be any mess left behind but if there is).

Please advise.

Thanks,
Mayank

Have you tried this:

1. On the Options page, down bottom, click Export WordFence settings.
That will save your configuration to the WordFence server(s). and give you a token-ID. (Save that Token)

2. On Options page, make sure to select “Delete Wordfence tables and data on deactivation” and then save the options.

3. Deactivate the WordFence plugin on the plugins page.. It should now delete all current WF tables from the database.

4. Re-enable WordFence on the plugin page. It should on activation detect the missing tables and re-create them.

5. If you want, then go on the options page and “Import” your configuration using the Token-ID you saved earlier.

Yes I have tried it. It did not help.

Please note that when the tables of WordFence not installed correctly it gives the error –

Wordfence could not get an API key from the Wordfence scanning servers when it activated. You can try to fix this by going to the Wordfence “options” page and hitting “Save Changes”. This will cause Wordfence to retry fetching an API key for you. If you keep seeing this error it usually means your WordPress server can’t connect to our scanning servers. You can try asking your WordPress host to allow your WordPress server to connect to noc1.wordfence.com.

and keeps opening the pop up for tour all the time. Does not allow me to save the options. I tried to insert tables from another database which only allows half the tables before some error shows up. This allows the options page to show up and be saved. Deactivate and activate does not help.

There is a problem with the tables. In my previous post –

https://www.ads-software.com/support/topic/wordfence-leaves-a-mess-in-the-database/#post-9412190

I detailed the problems with the WF tables. If there are commands table deletion/creation that I can run to set things right, it will help greatly. It is a lot of work to try inserting tables from another site and look into errors of tablespace which I don’t understand fully.

Thanks,
Mayank

OK.. Then you are down to deleting the tables manually.

Since per your earlier posting you seem to know how to use mysql directly on the command line, simply do

mysql -u root -p##### dbname

and use “DROP TABLE” commands to delete the ‘sbqte_wf*’ named tables one at a time from the mysql command line..

Or, since your hosting account I am pretty sure gives you phpMyAdmin access to the database (look in your hosting account), you could go in there in the web-interface where you can see the tables more directly, and simply DROP them in there.
The solutions are multiple.

Note. Disable WordFence first, before you start yanking out it’s foundation underneath it. ??

Then after having deleted all the tables manually, enable WordFence again.
At this point, the only thing that could even possibly be left to clean up are a couple of “left-behind” WordFence options in the options tables. Nothing else to disturb you.

I have tried these steps before but I repeated as you indicated.

This is what I did –

1. Deactivated the wordfence plugin
2. Deleted the tables as suggested –
DROP TABLE IF EXISTS sbqte_wfBadLeechers;
DROP TABLE IF EXISTS sbqte_wfBlockedIPLog;
DROP TABLE IF EXISTS sbqte_wfBlocks;
DROP TABLE IF EXISTS sbqte_wfBlocksAdv;
DROP TABLE IF EXISTS sbqte_wfConfig;
DROP TABLE IF EXISTS sbqte_wfCrawlers;
DROP TABLE IF EXISTS sbqte_wfFileMods;
DROP TABLE IF EXISTS sbqte_wfHits;
DROP TABLE IF EXISTS sbqte_wfHoover;
DROP TABLE IF EXISTS sbqte_wfIssues;
DROP TABLE IF EXISTS sbqte_wfKnownFileList;
DROP TABLE IF EXISTS sbqte_wfLeechers;
DROP TABLE IF EXISTS sbqte_wfLockedOut;
DROP TABLE IF EXISTS sbqte_wfLocs;
DROP TABLE IF EXISTS sbqte_wfLogins;
DROP TABLE IF EXISTS sbqte_wfNet404s;
DROP TABLE IF EXISTS sbqte_wfNotifications;
DROP TABLE IF EXISTS sbqte_wfPendingIssues;
DROP TABLE IF EXISTS sbqte_wfReverseCache;
DROP TABLE IF EXISTS sbqte_wfSNIPCache;
DROP TABLE IF EXISTS sbqte_wfScanners;
DROP TABLE IF EXISTS sbqte_wfStatus;
DROP TABLE IF EXISTS sbqte_wfThrottleLog;
DROP TABLE IF EXISTS sbqte_wfVulnScanners;

3. Count of tables reduced to 74 rows from 84 rows earlier.
4. The files under /var/lib/mysql/ were –
[root@neo:/var/lib/mysql/dbname]# ls -l sbqte_wf*
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfBadLeechers.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfBlockedIPLog.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfBlocksAdv.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfBlocks.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfConfig.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfCrawlers.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfFileMods.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfHits.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfHoover.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfIssues.ibd
-rw-r—– 1 mysql mysql 393216 Aug 12 23:50 sbqte_wfKnownFileList.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfLeechers.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfLockedOut.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfLocs.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfLogins.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfNet404s.ibd
-rw-r—– 1 mysql mysql 98304 Aug 12 23:50 sbqte_wfNotifications.ibd
-rw-r—– 1 mysql mysql 98304 Aug 12 23:50 sbqte_wfPendingIssues.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfReverseCache.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfScanners.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfSNIPCache.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfStatus.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfThrottleLog.ibd
-rw-r—– 1 mysql mysql 65536 Aug 12 23:49 sbqte_wfVulnScanners.ibd
[root@neo:/var/lib/mysql/dbname]# ls -l sbqte_wf* | wc -l
24
The other extensions vanished. I did not delete the .ibd files.

4. I reactivated the plugin
5. The number of rows in mysql remain 74 and /var/lib/mysql/dbname remains unchanged.

/var/log/apache/error.log has this error –
[Thu Aug 17 01:33:02.445101 2017] [:error] [pid 23691] [client XX.XX.XX.XX:1058] WordPress database error Table ‘dbname.sbqte_wfLockedOut’ doesn’t exist for query SHOW FULL COLUMNS FROM sbqte_wfLockedOut made by activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::isLockedOut, wfLog->isIPLockedOut, wfDB->querySingle, referer: https://dbname.com/wp-admin/plugins.php?plugin_status=all&paged=1&s
[Thu Aug 17 01:33:02.447551 2017] [:error] [pid 23691] [client XX.XX.XX.XX:1058] WordPress database error Table ‘dbname.sbqte_wfLockedOut’ doesn’t exist for query SHOW FULL COLUMNS FROM sbqte_wfLockedOut made by activate_plugin, include_once(‘/plugins/wordfence/wordfence.php’), wordfence::install_actions, wordfence::isLockedOut, wfLog->isIPLockedOut, wfDB->querySingle, referer: https://dbname.com/wp-admin/plugins.php?plugin_status=all&paged=1&s
[Thu Aug 17 01:33:02.457292 2017] [:error] [pid 23691] [client XX.XX.XX.XX:1058] WordPress database error Tablespace ‘kalavilasa.sbqte_wfBadLeechers‘ exists. for query create table IF NOT EXISTS sbqte_wfBadLeechers (\n\teMin int UNSIGNED NOT NULL,\n\tIP int UNSIGNED NOT NULL,\n\thits int UNSIGNED NOT NULL,\n\tPRIMARY KEY k1(eMin, IP)\n) default charset=latin1 made by activate_plugin, do_action(‘activate_wordfence/wordfence.php’), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, wordfence::installPlugin, wordfence::runInstall, wfSchema->createAll, wfDB->queryWrite, referer: https://dbname.com/wp-admin/plugins.php?plugin_status=all&paged=1&s

The error I mentioned earlier on a failed installation with the repeated popup for the tour continues to show up.

The behavior is the same if I delete *.ibd files too from under /var/lib/mysql/dbname. Kindly confirm if this needs to be done or the *.ibd files are required?

Also, trying to create tables using the other site failed –

$ mysql -u root -p##### dbname < /tmp/wf.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1813 (HY000) at line 202: Tablespace ‘dbname.sbqte_wfKnownFileList‘ exists.

This does the table creation halfway or so.

Isn’t the error in the logs giving a clue what’s going wrong? Why is the wordfence installation/activation failing? And if it fails why not make it easy to discard the changes/reset the system.

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

If I read your logs and failures correctly, you are having a problem that is entirely WordPress and WordFence unrelated.

Because of your disk-failures your MySQL data dictionary has gotten all messed up.
This problem must first be fixed with MySQL, before you can ever get your WordFence (and potentially other things) to be successful. But WordFence is innocent in this case. This is not a problem an “innocent” user like WordFence/WordPress can cause. It is caused by MySQL seeing failures (like your disks running full) in the middle of Data dictionary operations. For example ALTER/DROP/CREATE/… TABLE.

You are using InnoDB tables rather than MySQL’s default myISAM type. MyISAM is very simplistic and can be copied in strange ways, but not so for the InnoDB engine. MyISAM is frequently used as the default storage engine, because it fast and “simple”.

InnoDB is fantastic, mind you. Much more powerful and generally better than the default myISAM stuff. It stands up to system crashes much better, and keep much better data consistency, since it has the ability to rollback transactions for anything that did not commit correctly. Also supports Foreign keys to keep relational tables in sycn with each other. (Less important for WordPress, who is too dumb to use real Transactional operations. WordPress would almost guaranteed crash if anyone imposed Foreign Key relationships on it. ?? )
InnoDB has gotten even better over the past years from added features contributed by such as FaceBook and Google, who use InnoDB for their stuff. MyISAM is simply not reliable enough for big systems. But is still the default for small stuff like WordPress, where the default answer to data corruption is “Restore your last good backup”. ?? Something Big Iron systems could never allow.

But all that also makes InnoDB more tricky at times.

InnoDB uses multiple internal tables (“Data Dictionary”) to keep track of the individual user tables and their files. The .frm file is your table def, and the ibd file is what holds your data. InnoDB keeps track of them all in separate system tables, now messed up.

The fact that a DROP table leaves your .IBD file behind, tells you that the data dictionary is corrupt it stopped in the middle of an ALTER or DROP, and ended with an inconsistent dictionary (system table). With the inconsistent dictionary, innoDB end up in a variety of catch-22s.

It tries to create a table as instructed, creates a .FRM file, but then finds an existing .IBD file that it does not recognize and cannot use without help. So it leaves things behind in the dictionary.
When you issue a DROP TABLE, it deletes the .FRM file, but has to leave behind the unknown .IBD file.

I would imagine that your MySQL server logs fill up with errors every time the server process is restarted. Complaining that it cannot open or figure out these tables that sort of half-way exist.

Deleting the IBD file is unlikely to help. The internal Dictionary is still botched up.
What you have to do is to follow the instructions on this page:

https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html

Orphan tables can be a severe pain, but it is fully possible to fix.
You are lucky you have only about 10 tables. I once had to clean up a system were I had gotten hundreds of “versioned” tables had been orphaned. Had to create a script to filter out the nasty orphans and run each of them through the system procedures to fix. Or I could have sat there manually punching the keyboard for maybe weeks. ??

GOOD LUCK. ??

Hi,

I will try to wrap my head around that. I had a quick question:

Will repairing the database via tools not help such as –

mysqlrepair -u root -p##### –auto-repair –check –all-databases

or specific database –

mysqlrepair -o -u root -p##### dbname

Please let know if any such approach would work. It is just one site that is giving the problem. All others work fine and the cleanup happened with auto repair or no repair was necessary. Now I have moved the backups to cloud and there won’t be a problem with storage in future but I need to get across this corruption.

I had question, if I moved to another server with mysql or mariadb and recreated the setup, will a backup from this database work or will the problem be carried with the backup to the new server? Kindly confirm. That’s something I could try.

Thanks,
Mayank

No.. A simple mysqlrepair will not fix that. It needs manual intervention, as per the link I gave you.

It will not be fixed by a simple backup and restore/move thing.
The backup should include the bad Data dictionary content, and should move the corruption right over.

You will need to fix it.
It’s not too difficult. Especially with only a few tables to fix.