Crashed WordFence database tables keeping backups from running

  • Hi there –

    I provide support for another WP plugin and we’ve received a number of support requests after backups fail that look similar to the following: 

    - mysqldump: Got error: 145: Table './xxxxxxxxx/wp_wfHits' is marked as crashed and should be repaired when using LOCK TABLES
SQLSTATE[HY000]: General error: 145 Table './xxxxxxxxxx/wp_wfHits' is marked as crashed and should be repaired

    Some additional troubleshooting and research showed me that the first error is because the user might not have proper permissions (generally permissions to LOCK TABLES are assigned but some hosts disallow it for security reasons). So they have to go in and repair the database within phpmyadmin.

    My concern is that this particular table is crashing and being reported when our plugin tries to run a backup – otherwise a user might not realize it crashed. I’ve probably gotten a couple support requests a week related to this error, and most of the time it is due to this table.

    I wasn’t sure if you guys were already aware of issues with saving data to that table or not, whether the tables normally crash when too many hits are recorded to the site? I wanted to ensure there wasn’t additional information I should tell users when they have issues specifically like this, where the table impacted is one of yours.

    Thanks so much and just let me know if you need further info,
    Kat

    https://www.ads-software.com/plugins/wordfence/

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

  • Hi Kat!
    Thanks for contacting us. AFAIK we haven’t had any reports of this previously.

    I’m fairly sure we require full DB access on the user and I believe this is the recommendations when running WordPress in general? I’ll definitely check this with our devs though to see if there is anything we can do about it.

    I’ll get back to you as soon as I know something.

    Hi again Kat,
    The user would notice if this table has crashed as they would no longer be able to see live traffic i Wordfence. So maybe it is crashing during the backup process.

    I checked with one of our developers and he suggests the people affected recreate the table as InnoDB instead of MyISAM. Wordfence inherits choice of storage engine from the default but InnoDB is much more stable than MyISAM so we recommend it.

    The same thing just happened to me. The tables wp_wfHits and wp_wfLeechers had this error “Table is marked as crashed and should be repaired”, I was able to repair the tables but I am concerned that this will continue to happen.

    We just checked the DB tables again this morning and it happened again, but it seems like our MySQL service died in the middle of the write.

    We just tried changing the wp_wfLeechers table to use InnoDB to help improve write stability but get an error message “Default empty string is not supported for the datatype binary”.

    • This reply was modified 7 years, 6 months ago by Jamie Edwards.

    Yeah, I’m getting this as well. The issue here appears to be that when a lot of spammy traffic is coming in, these tables are in an almost constant write state, so if you get an out of memory condition and MySQL dies they get corrupted and need to be cleared out.

    I’ve seen corruption in:
    wp_wfHits, wp_wfHoover, wp_wfVulnScanners, wp_wfNet404s, wp_wfBadLeechers

    Would be nice if Wordfence could check for such corrupted tables and repair automatically or use an alternative storage engine.

    Hi Stinky!
    We do not choose the storage engine, we end up with whatever is set as default in MySQL. You can change the default, or you can do a fresh install and then change the individual tables to InnoDB right after install. That should work as well.

    Yeah, we already use InnoDB 90% of the time, and end up having to drop these tables when they get corrupted since there’s no way to repair them. I meant a totally alternative storage engine, file based, memcached etc. Unfortunately I think it just boils down to these tables commonly being in a write state when the server gets overloaded, and OOM killers these days on Debian/Ubuntu just love to kick MySQL before anything else.

    To be honest just checking these tables for corruption occasionally and repairing/rebuilding if necessary would be helpful.

    Hi stinky!
    I completely agree. I have added a feature request for this in our system. We should be able to check integrity of Wordfence tables every time a scan runs.

    Thanks for the feedback, and hope you have a great week.

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘Crashed WordFence database tables keeping backups from running’ is closed to new replies.

Tags