Why Some WordPress Plugins Leave Orphaned Tables in the Database

Reviews of WordPress plugins on the directory come in all shapes and sizes. Some are really helpful while others are short and to the point. For example, this review from a recent WP eCommerce user, “I uninstalled and it left orphan tables I now have to clean up manually. Poorly done.”

This type of review can be easy to shrug off but Justin Sainton, co-founder of WP eCommerce, responded in the best way possible. He acknowledges the user’s complaint and admits that they bring up an important point that the team could document in a better way.

“We make a very intentional decision to NOT remove tables from the database when people uninstall or deactivate WP eCommerce,” Sainton said.

“Any number of reasons we’ve encountered over the years result in someone deactivating and uninstalling their plugin. Even though WordPress makes it clear (if you have an uninstallation routine) that you’re removing data – this is a notice that is very easy to ignore when you’re on a mission to do something.

“For those reasons, we’ve made the intentional decision to not remove any of the data (much of which would be MASSIVELY detrimental to a business if it were removed unintentionally) upon uninstallation.”

What are Orphaned Tables?

Orphaned tables are those left by plugins that don’t perform a table cleanup when uninstalled. These tables often contain data, including forms, entries, settings, etc. For example, when I installed GravityForms 2.0 on the Tavern’s test site, I discovered that the forms I created six years ago were still in the database. Not only was I surprised, but thankful that I didn’t tell GravityForms to remove the data all those years ago.

Deactivating a Plugin Shouldn’t Remove Data

There is a distinct difference between deactivating a plugin and uninstalling it. The process of deactivating a plugin happens more frequently than uninstalling mostly due to the troubleshooting process. Deactivating a plugin should not remove any data from the database. Think about how frustrating it would be to deactivate a plugin and then have to reconfigure its settings?

Removing Data Should be Explicit

Instead, the removal of data should be an explicit action from the user. Plugin developers who want to give users the ability to remove data when uninstalling can register an uninstall hook and add a Uninstall.php file. From the WordPress developer reference guide regarding Uninstall.php.

If the plugin can not be written without running code within the plugin, then the plugin should create a file named ‘uninstall.php’ in the base plugin folder. This file will be called, if it exists, during the uninstall process bypassing the uninstall hook. The plugin, when using the ‘uninstall.php’ should always check for the ‘WP_UNINSTALL_PLUGIN’ constant, before executing.

Developers who use this method should provide a clear and concise confirmation prompt to users that warns data will be removed. A great example of a plugin that does this well is GravityForms. The uninstall routine is separate from the deactivation process and the warning clearly illustrates to the user that data will be lost.

GravityForms Uninstall Process
GravityForms Uninstall Process

In a comment published to the Tavern in 2013, Carl Hancock, Founder of RocketGenius, explained why they use their own uninstall process over the suggested use of Uninstall.php.

For instance it’s common for us to deactivate and delete versions of GF and Add-Ons on our own test machines via the plugin manager and then upload and install different versions. That doesn’t mean we want to lose all our data. So we certainly don’t want that to occur when we delete the plugin in the plugin manager.

This is why we chose to implement our own version of a full uninstall within the Gravity Forms Settings area that makes it very clear to the end user that it will delete ALL data and remove ALL database tables.

Orphaned Tables Typically Don’t Impact Site Performance

Some people believe that too many orphaned tables can negatively impact a site’s performance. I asked Sainton if this is true, “Generally speaking, no,” Sainton replied.

“There are certainly instances where the number of tables that exist in your database could potentially impact the performance of certain queries – but those types of queries should never really be run in most contexts – certainly not in most contexts that WordPress developers would be interested in.”

Orphaned tables and the data within them are contained within the WordPress database, something most average users rarely dive into. Most are probably unaware of the amount of data and tables sitting in their database taking up bytes of space. It’s good to know that in most instances, orphaned tables don’t degrade a site’s performance and isn’t something to be too concerned about.

Cleaning Up Orphaned Tables

There are a number of plugins that optimize and clean orphaned tables from the database. A cursory search of the plugin directory provides the following results.

There are more plugins than what I’ve listed here but each one interacts and manipulates the WordPress database. I learned from experience that before using any database cleaning and optimization plugin, you should create a full backup of your site.

I once used a database cleaning plugin and although it did the job, it removed things I didn’t expect like custom post statuses generated by Edit Flow.

Should The Uninstall Process Be a Plugin Directory Guideline?

Like the settings link on the Plugin management page, a number of plugins in the directory don’t use Uninstall.php or register an uninstall hook. I firmly believe that any plugin that adds data to the database should provide an easy way to remove it. It’s a guideline that if enforced would benefit users by helping to maintain cleaner databases.

Developers, let us know in the comments what you do to enable users to remove data from the database after it is added by your plugin.


23 responses to “Why Some WordPress Plugins Leave Orphaned Tables in the Database”

  1. Removing data on plugin deletion is a difficult decision for developers, Jeff. If we are talking about a massive amount of data, removing it makes a difference to performance. If we are talking about a small amount of data, particularly complex settings which you would likely want back, removing data is extremely user unfriendly.

    There should probably be an extra option next to the Delete button on the plugins page or as an interstitial checkbox option when you use the Delete button “Would you like to keep or remove Happy Forms data?” Perhaps even a second checkbox “Would you like to keep or remove Happy Forms settings?”

    I know Core WordPress team argue options complicate WordPress but hey we’re talking about professional software. There have to be options as there are decisions to be made which vary from plugin to plugin and user to user. If a decision must be made, the data and settings should be left in place for the user to remove at his/her own leisure/discretion.

  2. For me, uninstall means uninstall everything. If I want to stop using a plugin but keep the data for possible later use, I’ll deactivate.

    I came across a situation where I used version 1.x of some plugin, then uninstalled. Then later, I installed version 2.x of that plugin, and you guessed it, it didn’t work properly, because the orphan 1.x data threw a wrench. You can see where a table cleanup after uninstall would have been helpful here.

    Ideally, there should be 3 levels of uninstalling/deactivating:
    1. plugin not active, but files + data are kept
    2. files are removed, data is kept
    3. files + data are all removed

    However that might confuse the “common” user, so I personally think the best alternative is to have 1 + 3 only (and those are the options that my plugins have). Additional confirmation screens or options will just confuse people more, so I do think there should be a standard.

    • I agree to Senff. For me, uninstall means cleaning up. So the plugin should not leave anything after completely removing it.

      If you are troubleshooting or testing something, just deactivate it. Why uninstall? Unless it is a caching plugin like W3TC, deactivating is enough, if you have a plan of using it later.

    • Only reason I can think where uninstalling but keeping tables has been good is for when updating plugins that don’t come with an automated process so you wouldn’t lose your data but seems a lot more plugins have automated updates that aren’t hosted on WordPress.org.

      I have also noticed plugin developers can create custom links next to deactivate and uninstall so maybe ones that don’t have an automated process should create a custom button here for uninstall to update or something that retains tables.

    • Senff, for security reasons, we are told not to even keep inactive plugins on our live sites. Hence the need for the option to keep either data or settings for the future, in the case we wish to use that plugin again. Jeff himself was happy that his Gravity Forms forms were still in the database.

      A final (and probably the most sensible) option would be to easily allow import and export of a plugin’s settings and data (not custom for each plugin, but registered tables and settings with a standard routine which works across all plugins).

      • I understand the security reasons point, but shouldn’t that policy also not allow orphaned tables (of inactive/uninstalled plugins) on your live sites? Why would it not allow files of an inactive plugin, but it would allow data of an inactive plugin?

        I also recognize why Jeff was happy that the data of a deleted plugin was still available, but I consider that a lucky accident. The fact that the data was still there undid his mistake of deleting the plugin, but I believe that most of the time, deleting a plugin is not a mistake. I think the data shouldn’t be kept just in case the plugin removal is not actually what the user intended, or made it final.

        I do agree with presenting the user with the option to keep a plugin’s data (import/export), but it would probably a little too advanced for the average user.

        Another thought: have a “Trash Can” for old plugin’s settings. Just like deleted Posts and Pages have their separate space from which you can restore them, maybe a part of the database where all the “trash data” goes could be helpful. It could be restored when needed, but it would be in a separate part of the database.

  3. uninstall is uninstall is uninstall. It is not partial uninstall, it is not a maybe uninstall. User wanted an uninstall, and even if the user is an idiot you should respect his wishes.

    If external tables are not deleted why should anyone assume that options and such do get deleted? Unless it is a bug or oversight, it is a good sign to stay away from the plugins that do it.

    Some things are hard to delete, like media that was specifically associated with the plugin, but that should be a very small exception to the rule of “delete everything on uninstall”

    Actually it should double apply to storage of sensitive information like financial transactions.

    • We don’t agree all the time Mark but I agree with you 100% on this one.

      If I choose to delete a plugin then get ALL of your crap out of MY WordPress. If I made a mistake then it’s on me but no plugin dev has the right to leave anything behind when I delete.

  4. There are many times that I haven’t deleted a plugin through the WP plugins manager because I get the warning that it will also delete the data, and I think that I want to keep the data as I might re-install that particular plugin later. There are also times when I’ve had to reinstall plugins due to a corruption, and again I don’t want to delete the data, so I have to revert to FTP to delete the files. Conversely, there have also been many times when I look at a database and groan at how much junk there is there.

    So, like most things in life, the decision really should be mine. When I click delete in the WP plugins manager I should be given the choice to delete data. In fact, it would be nice if there were 3 options: Delete Files Only; Delete Files & Data; Delete Data Only.

    This should be a mandated part of the core WP plugins system which all plugins in the WP repository that have their own tables have to implement.

  5. When I uninstall programs on my desktop, there almost always is an option to retain settings & data. That makes upgrading or a reinstall much easier. Why should WP be different? It’s another option that can save loads of time.

  6. I feel like all these concerns can be addressed if developers just add a simple checkbox to the plugin settings that says, “Yes, delete all data on uninstall.” A lot of plugins do that and I feel like it solves every possible issue.

  7. I can see all sides of this one. You want to prevent users from accidentally screwing themselves over. Noble and valid.

    That said, there should always be some way to say “yes, burn it all down”. This is mainly a UI problem. But, I think it is well within the realm of solvable, given the existing hooks. If not, then perhaps a more detailed use case would be valuable to core to fix it in the best way.

    • sounds like what people want is “hard deactivation” or “soft uninstall” added to the plugin action links.

      I don’t see the point as when a plugin is deleted there is no more any indication that it was there so how will you know which plugin you need? So there will have to be a list of formerly deleted plugins stored somewhere as well

  8. After reading this it makes sense why some people don’t want to remove all the data after uninstall, I always delete data on uninstall. I wonder if it’s a good idea to have a checkbox in the plugin settings (unchecked by default) that asks the user if they want to remove the data on uninstall. An extra verification might make cleaner databases easier and data loss less common. Will be implementing this in my own plugins.

  9. What about plugins that don’t create their own tables, but leave their data behind in wp_options? I’ve been surprised a few times to see options in there for plugins that had been deleted years prior to me poking around.

    Data cleanup upon uninstall should apply to stuff in the main WP tables as well.

    • Good point Summer about orphaned data within the existing tables left by deleted plugins. I agree treatment of data should be equal, whether in separate tables or in the main tables.

      Getting uninstall write is not as simple as it looks. That simple question about data on delete suggested by Ashley and Lewis is looking awfully good.


Subscribe Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.