Support » Fixing WordPress » Database with repeating tables preceded by 6 letter prefix keeps growing in size

  • I have two websites with excessively large databases, each >2GB but they should both only be 100mb or so tops. Looking at them with phpMyAdmin I discovered the original WordPress tables all had a 6 letter prefix and they repeat again and again with each repeating set of tables preceded by a different 6 letter prefix.

    Instead of just wp_posts for example, I have caoyqp_wp_posts, cojqff_wp_posts, dlbvwa_wp_posts, zjdqhe_wp_posts, etc. Like bunnies they seem to keep growing as the database keeps growing even though nothing has been added to the site to warrant such growth.

    I created a website with all the same plugins being used, but it is not happening with the new site. I am thinking an old plugin I may have tried in the past on the sites started this process and removing it did not restore the original tables and the ghost of it is still active.

    Maybe it is something else. I am clueless at this point of how to fix it and get back to the original WordPress database tables and stop the database from growing.

    I tried some plugins that claim to clean the WordPress database but none cleared these tables or restored them to what they should be.

    Any insights on what is going on or who I might turn to to help figure this out is greatly appreciated.

    Thank you.

Viewing 12 replies - 1 through 12 (of 12 total)
  • Moderator Jose Castaneda

    (@jcastaneda)

    THEME COFFEE MONKEY

    Howdy!

    That is super bizarre to see. My first question actually is how did you create the extra site? Was it a clone/duplicate or from a fresh install? By that I mean you only copied over the plugins/themes and ran it like a new install?

    Thread Starter steverino2

    (@steverino2)

    I created the new site fresh through Softaculous and then installed the plugins it did not port over in the install process.

    When I create a staging site or a clone of the ones in question, the same issue exists.

    Now this is really weird, when I cloned one of the problematical sites, I looked at the size of the mysql database and it was 87mb. I thought wow, cloning it must have fixed it. I went to lunch and to do some other things, looked at the database size again and it was at 1.8GB.

    Maybe the initial size of 87MB was wrong and it took some time for cPanel to catch up and 1.8GB is what it always was. I am clueless.

    These are the plugins on one site:
    Astra Bulk Edit, Astra Pro, Autoptimize, Beaver Builder, Cache Enabler, Ecwid Shopping Cart, Optimole, Rank Math SEO, Ultimate Addons for Beaver Builder, WP Crontrol, WP Reset Pro

    The other site has the above sans Ecwid shopping cart and Rank Math and Optimole, but has Gumlet (a different photo opimizer), Embed Any Document, SmartVideo, Yoast Duplicate Post, and 5 eLearnCommerce plugins.

    Between the two I have found no common denominator.

    I am baffled for sure.

    Moderator Jose Castaneda

    (@jcastaneda)

    THEME COFFEE MONKEY

    How the fuzzy? O_o

    https://media.giphy.com/media/n5NtisNKVzXWg/giphy.gif

    Okay. What happens if you deactivate all the plugins on the staging site, wait a bit and see if those tables keep being added. That will at the very least rule out any plugins if those tables are still being added.

    Thread Starter steverino2

    (@steverino2)

    Fuzzy was funny.

    Trial and error and yep it was definitely a plug in. WP Reset Pro. It was taking a snapshot of each table of the database and adding the prefix as a copy every time a WP plugin was updated. If the update screwed up something it could easily be reset, which is a good thing.

    But since plugin updates happen all the time, the files kept growing. I thought the plugin was set to store snapshots on the plugin’s cloud server. Turns out they were in the mysql database and that is what I was seeing and I had no idea that is what it was. Argh.

    That will teach me to dig deeper into plugins before I install and use them.

    Moderator Jose Castaneda

    (@jcastaneda)

    THEME COFFEE MONKEY

    That is super odd it behaved that way. You might want to at least reach out to the their devs and see if they’ve had similar issues.

    Thread Starter steverino2

    (@steverino2)

    It actually did what it was supposed to do flawlessly. Problem was me – not well enough versed in tech stuff, know just enough to be dangerous, and I keep learning things about WordPress as I stumble along.

    If I read the pertinent documentation (I didn’t) I’d have seen this:

    snapshots are stored as custom database tables; one snapshot table for each original table
    naming template for snapshot tables is {6_char_random_hex}_{table_prefix_for_your_site}_{original_table_name}

    With so many plugin updates happening all the time, and each snapshot capturing the state of the database the moment any plugin gets updated, and storing that snap in the database for easy rollback if needed, well, those MB turn into GB pretty fast. I should have had them go directly to the cloud for storage and kept up on it as the snapshots accumulated. I was oblivious and not paying attention.

    I have to say WP Reset Pro is nice security to have if an update screws something up. Working through this I can at least say I know a lot more about how it works and a whole lot more about WP database tables too.

    Thanks Jose for responding to my query here.

    downtime

    (@downtime)

    @steverino2 @jcastaneda – we have just gone through the exact same issue, resulting in our hosting limiting the database due to the size. Can you let me know what you did to clean up the database? We are currently not able to log in to the backend of wp due to the limited database until the size has been reduced. curious as to the process you went through to clean this up. Thanks in advance!

    Moderator Jose Castaneda

    (@jcastaneda)

    THEME COFFEE MONKEY

    Howdy!

    Sorry to hear you are having a similar issue. It may be best to open a new topic as one this is a pretty old topic and two it may sound fairly similar but could be something entirely different.

    As for how to trimming the database, you may want to reach out to your hosting company about how to connect to the database using something like Sequel Pro or MySQL Workbench or even phpMyAdmin to see what tables are on there.

    downtime

    (@downtime)

    thanks will try that – it is the same issue we’re having though, with the same plugin (wp reset pro)…just thought i’d see if i could get some insight from the original poster 😉 . thx

    Thread Starter steverino2

    (@steverino2)

    I got Advanced Database Cleaner PRO which allowed me to see what was going on as I could see all the database tables, etc. You will see all kinds of duplicate wp files with weird prefixes, these are the files created by wp reset pro. I don’t remember if deleting the snapshot in wp reset pro clears them or not, but if not, you will definitely see them with the Database Cleaner Pro and then you can clear them out. My database shrunk enormously after I did this.

    downtime

    (@downtime)

    awesome! – thanks for the tip.

    prwordpressorg

    (@prwordpressorg)

    I had a similar problem and just to confirm, it was the issue of WP Reset PRO creating these duplicates of the database tables. This is a function of the plugin, so it means it is working as it should… it´s just that sometime, when people (like me 🙂 delete things on their site thought FTP and thus don´t allow WP Reset Pro to know about it, these duplicates remain in the database and need to be cleared with additional steps.

    This is the advice that I got from WP Reset PRO support:

    “When creating a snapshot, a copy of the database tables is created so they can be restored if you wish to revert to that snapshot. If you no longer need the snapshot, you can just delete it, and the tables associated with it will be removed as well.

    The reason they are stored in the database is so that they can be created and restored very quickly. If you with to keep your snapshot for longer but don’t want it to take up space in your database you can:
    1. download it to your computer
    2. upload it to a cloud service (WP Reset Cloud, Dropbox, Google Drive, or pCloud)
    and then delete it from your website’s storage.

    Getting back to your current issue, you can identify WP Reset snapshot tables by their random 6 letter prefix. For example “dsadsd_”, “uyueou_” etc. followed by the normal WordPress prefix. If your table names are very long, WP Reset could also use 4 letter prefixes followed by the normal WordPress prefix.
    So you can delete the tables that have prefixes like that to free up your database 🙂

    If WP Reset is active, you can enable Debug mode at the bottom of the Support tab, then in the Debug tab, under the Snapshots section you can delete them (See attached screenshots)`

    I did what they suggested, cleared the databse tables with the prefixes and all is fine now 🙂

    (And I do keep using and am happy with WP Reset PRO)

Viewing 12 replies - 1 through 12 (of 12 total)
  • The topic ‘Database with repeating tables preceded by 6 letter prefix keeps growing in size’ is closed to new replies.