Deleting a site from a multisite network leaves orphaned database tables and files

[bluep92877]

Looking at the table structure of a typical multisite install, I see that tables for individual sites within the network are prefixed with wp_{siteid}_ tablename. When I delete a site from the from the network, all standard WP tables for that particular site ID are deleted; however, several plugin tables are not.

As well, looking at the uploads/sites directory, the directory for that site (represented by its ID) is also still there along with all its files (media and other uploads).

an earlier ticket:30673 suggested that this should be caught when uninstalling a plugin from the master site. But, I'd have to disagree. Most uninstall scripts use a type of {TABLEPREFIX} before its table names. This would delete the tables for every site in the network, wouldn't it? and that's not what the goal would be.

Aside from the plugin issue are the media files that are also orphaned. Media file uploads is part of core, and that's not something a plugin author could approach.

When deleting a site from the network, this is irrevocable. Wordpress even create an extra confirmation page upon delete saying so just to make sure. If that's the case, and this is by design, what's the point of keeping these extra files and tables if they are never going to be used again?

Shouldn't all site specific db tables and all files in the uploads directory be deleted when a site is deleted? Or, perhaps as an enhancement, on the Delete Site confirmation page, could we have two checkboxes: "1) delete all tables associated with this site" and "2) delete all files associated with this site"

Currently I have a script setup to alert me as to when a site is deleted. I then open up a cli to manually delete the db tables for that install, and then its files from the uploads directory. This doesn't feel like the intended workflow to handle the orphaned elements of the site.

[mdifelice]

Hello. I do think that when you uninstall a plugin it will only delete the tables that it created for that specific site. Of course it depends on the plugin, and whether the plugin was network activated or only activated on the specific site.

Either way, I think that it would be a good addition what you propose. About the tables, it would be nice if WP listed all those orphan tables before deleting the site and ask the user what to do with them: delete or keep them.

About the media files, I think that each site has a particular folder assigned to them, and simply deleting it it would remove all the associated media.

Another doubt that I have is whether WP really deletes a site or it keeps like in a hidden status. Because if that is the case the idea is that the site could be recover at any time and in those cases files and extra tables should be kept.

Regards.

[johnjamesjacoby]

Hey @bluep92877, thanks for opening this issue up, and congrats on your first ticket here on WordPress Trac.

I think you've brought up good points, and these are all areas where clarification and improvements are necessary.

Re: #30673, I agree that "uninstall" in the context of a multisite installation implies that all relevant data is removed from all sites, which seems heavy handed under most circumstances, though I can imagine why that might be necessary.

Plugins can (and should) use the wpmu_drop_tables filter to add their custom database tables to the array of tables that are dropped when a site is deleted.

Database tables and Uploads are both pretty sacred, IMO. If we are deleting one, we can safely delete the other. Dropping large database tables can be costly on the database server in a similar fashion to unlinking a directory. It looks like wpmu_delete_blog() does try to delete both the database tables and the uploads directory. Maybe something is wrong with the $drop logic, or something else is in the way?

That said, permanent deletion is so scary; I'm always hesitant to give anyone that power (even super admins.) 1 click to ruin it all is a lot of responsibility, even 2 or 3 clicks with warning signs and notices.

---

I think erring on the side of caution here is OK. Only delete stuff if we are 100% certain it's OK to do so, and leave it untouched if there is any doubt. This might result in orphaned tables or files, but I think that's better than blindly deleting them without explicit say-so.

I guess, now that I type this, we could (should) do a quick check on that delete-site confirmation screen that says "Here are all of the database tables this site has, and it contains 2GB of uploads. Are you sure you want to delete all of this stuff?"

[scamartist26]

For what it's worth, we have this same issue on our multisite install. I do not see any reason to keep tables around at all. We created this little guy as a workaround:

<?php

/**
 * Cleanup orphaned tables during site deletion
 *
 * @param $blog_id
 * @param $drop
 */
add_action( 'delete_blog', 'delete_blog_43162', 10, 2 );
function delete_blog_43162( $blog_id, $drop ) {

    if ( true == $drop ) {

        /**
         * SELECT all tables relating to a specific blog id and add them to wpmu_drop_tables
         */
        global $wpdb;
        $prep_query = $wpdb->prepare( "SELECT table_name FROM information_schema.TABLES WHERE table_name LIKE %s;", $wpdb->esc_like( "{$wpdb->base_prefix}{$blog_id}_" ) . '%' );
        $table_list = $wpdb->get_results( $prep_query, ARRAY_A );

        add_filter( 'wpmu_drop_tables', function ( $filter_list ) use ( $table_list ) {

            foreach( $table_list as $index => $data ) {
                $filter_list[] = $data['table_name'];
            }

            return array_unique( $filter_list );

        });
    }
}

[waynmeyer]

Replying to scamartist26:

For what it's worth, we have this same issue on our multisite install. I do not see any reason to keep tables around at all. We created this little guy as a workaround:

<?php

/**
 * Cleanup orphaned tables during site deletion
 *
 * @param $blog_id
 * @param $drop
 */
add_action( 'delete_blog', 'delete_blog_43162', 10, 2 );
function delete_blog_43162( $blog_id, $drop ) {

    if ( true == $drop ) {

        /**
         * SELECT all tables relating to a specific blog id and add them to wpmu_drop_tables
         */
        global $wpdb;
        $prep_query = $wpdb->prepare( "SELECT table_name FROM information_schema.TABLES WHERE table_name LIKE %s;", $wpdb->esc_like( "{$wpdb->base_prefix}{$blog_id}_" ) . '%' );
        $table_list = $wpdb->get_results( $prep_query, ARRAY_A );

        add_filter( 'wpmu_drop_tables', function ( $filter_list ) use ( $table_list ) {

            foreach( $table_list as $index => $data ) {
                $filter_list[] = $data['table_name'];
            }

            return array_unique( $filter_list );

        });
    }
}

This worked like a charm, please could we have this added to core its vital especially for multisite. Alternative would be as @mdifelice stated, simply ask an admin when they deleting the subsite if they would like to delete orphaned tables with a checkbox next to each table for custom selection, when an admin wishes to retain data then the choice should be available as to what tables are chosen by the admin.

Bigup to @scamartist26 for assisting with the snippet.

[carl-alberto]

To add on as a temporary solution in deleting the corresponding subsite's uploads folder

<?php
add_action( 'wp_delete_site', 'wpmu_delete_blog_uploads_folder' );

/**
 * Deletes the corresponding uploads folder of a subsite.
 *
 * @return boolean Return true if successful.
 */
function wpmu_delete_blog_uploads_folder() {

                require_once ABSPATH . 'wp-admin/includes/class-wp-filesystem-base.php';
                require_once ABSPATH . 'wp-admin/includes/class-wp-filesystem-direct.php';

                $upload_dir = wp_upload_dir(); // This function already calls the subsite's upload folder path automatically.

                $fileSystemDirect = new WP_Filesystem_Direct( false );

                return $fileSystemDirect->rmdir( $upload_dir['basedir'], true );

}

It seems @johnjamesjacoby is right that there is a ready function wpmu_delete_blog which also calls the wp_delete_site which also calls wp_uninitialize_site and supposedly deletes the corresponding uploads folder. Those functions might need to be revisited as it seems to be logical to delete those along when a subsite is deleted, otherwise a busy multisite can easily be bloated with unnecessary files or possibly have something like a checkbox for confirmation like what @mdifelice proposed, something like the attached image and might contain warnings that it may take awhile for large db/uploads:

Update the wp_uninitialize_site function to delete the uploads folder and custom db tables created by plugins

Trac ticket: https://core.trac.wordpress.org/ticket/43162

[carl-alberto]

Found the function that is supposed to delete the database table as well as the uploads folder which seems not to be working anymore ​https://github.com/WordPress/wordpress-develop/blob/186abb264d6c97dc955ecc787f29c12be3ffb33c/src/wp-includes/ms-site.php#L828-L866

to get this issue moving, opened up a PR to use the built in WP FS system functions

​https://github.com/WordPress/wordpress-develop/pull/3951

Update the wp_uninitialize_site function to delete the uploads folder and custom db tables created by plugins

Trac ticket: https://core.trac.wordpress.org/ticket/43162

[SergeyBiryukov]

Found this ticket after a coding session with @aristath and @poena, where we noticed that wp_uninitialize_site() calls unlink() and rmdir() directly, instead of using the Filesystem API. Fixing that seems like a good first step here.

This particular code appears to be introduced in ​mu:1069.

[carl-alberto]

Trying to add a patch

[carl-alberto]

Patch would be using the Filesystem API to delete the associated folder related to the subsite being deleted, deletes all the related tables for that subsite and it also contains the unit test. Hopefully I did it correctly :)

Patch would be using the Filesystem API to delete the associated folder related to the subsite being deleted, deletes all the related tables for that subsite and it also contains the unit test

Trac ticket: https://core.trac.wordpress.org/ticket/43162

fixing the orphaned uploads folder

https://core.trac.wordpress.org/ticket/43162

fixing the orphaned uploads folder

https://core.trac.wordpress.org/ticket/43162

fixing the failing php unit test for multisite

https://core.trac.wordpress.org/ticket/43162

[spacedmonkey]

As it is unclear which PR we should be reviewing here, I am going to punt to 6.4, when we have more time to look through code submitted.

[oglekler]

Hi @carl-alberto, this ticket was discussed during the bug scrub, and we noticed many PRs and most are closed and it is confusing, please, if you can, make a new PR with all changes including Unit tests. And if you don't have a capacity to work on it now, please, tell like it is, possibly someone else will be able to pick up the work. Thank you!

Add props to @mukesh27

When you delete a WP mutisite subsite, it orphans the DB and relative uploads folder. Becomes problematic on huge multisites leaving a lot of db tables and wasted space

Trac ticket: https://core.trac.wordpress.org/ticket/43162

[carl-alberto]

Thanks for the follow-up @oglekler, I have added another attempt on this patch in this PR ​https://github.com/WordPress/wordpress-develop/pull/5066

[carl-alberto]

Not sure if I made it correctly but I replaced the deletion method to use the FilesystemAPI, when I tested the deletion of WP multisite, it now deletes the orphaned folders but unit tests fails so it might need to be updated too?

[oglekler]

@carl-alberto yes, it looks like it, tests are failing to find directories what are searching for.

[oglekler]

Because the patch needs a bit of work, and we are in two days before the Beta 1, I am moving this ticket into 6.5. It can go in trunk as soon as the patch will be ready and trunk open again after 6.4 will be branched out.

[carl-alberto]

ok thanks! I may be needing help with the unit test for this one if that is the case for the 6.5 release

[janthiel]

Hey there, just came along this ticket by chance.

I would like to add one suggestion for the sake of current work on the sqlite front: ​https://make.wordpress.org/core/2022/09/12/lets-make-wordpress-officially-support-sqlite/

Although I am not affiliated with the sqlite work or Plugin in any way ;-).

Could you please make the table selection query filterable? There is no information_scheme table in sqlite. So the hard coded MySQL based query would break WP on any db where the information_scheme table does not exist for whatever reasons.

Luckily easy to fix with a filter.

Unrelated to that: Thank you very much for picking up this issue and providing a solution. Highly appreciated!