How to Delete Orphaned SBE Tables

I just discovered I have 1700+ tables with the table name "wp_x_subscriptions_log_table". Many of these are clearly orphans left behind after deleting blogs.

Can someone please help me write a query to safely delete these tables?

This will find them all:
SELECT * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '%subscriptions_log_table%';

How can I add variables to only find these tables that do NOT have a blog_id FROM wp_blogs? Or, any other way?

  • Ash

    Hello jcnjr

    IMPORTANT: First of all, take a backup of your entire database. Then use the following code:

    add_action( 'plugins_loaded', function() {
    	$sites = get_sites();
    	$blogs = array();
    	foreach( $sites as $site )
    	{
    		$blogs[] = $site->blog_id;
    	}
    
    	$blogs = array( 1 );
    
    	global $wpdb;
    	$query = "SELECT * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '%subscriptions_log_table%';";
    	$res = $wpdb->get_results( $query );
    
    	foreach( $res as $r )
    	{
    		if( DB_NAME != $r->TABLE_SCHEMA ) continue;
    		$table_name = $r->TABLE_NAME;
    		$table_name = explode( $wpdb->prefix, $table_name );
    		$table_name = $table_name[1];
    		$table_name = explode( '_subscriptions_log_table', $table_name );
    		$table_site_id = $table_name[0];
    
    		if( ! in_array( $table_site_id, $blogs ) )
    		{
    			if( 'subscriptions_log_table' == $table_site_id ) continue;
    
    			$deleted_table_name = $wpdb->prefix . $table_site_id . '_subscriptions_log_table';
    			$query = "DROP TABLE " . $deleted_table_name;
    			$res = $wpdb->query( $query );
    		}
    	}
    } );

    You can use this code in your child theme's functions.php if the theme is not changed. Otherwise mu-plugin is the best option. To create a mu-plugin, go to wp-content/mu-plugins folder. If there is no mu-plugins folder then, create one. Now, inside the mu-plugins folder create file with name anything.php (make sure file extension is .php). Now start with a <?php tag and then put the above code.

    Reload the site once. Check at phpmyadmin if the unused tables are deleted. If all tables are not deleted, reload one more time and so on. When all the tables are deleted, then remove the snippet.

    If it messes up, restore the db from the backup. Hope it helps! Please feel free to ask more questions if you have any.

    Have a nice day!

    Cheers,
    Ash

  • jcnjr

    Thanks, I'll give this a shot. Just curious...

    Does this have to be run as a plugin? Or is there a single query that can be run via phpMyAdmin?

    Also: I discovered these orphan tables when doing some database tuning, after finding numerous errors in our logs related to the SBE plugin. I've had to deactivate SBE after finding many entries like this generated daily:

    [01-Dec-2017 02:23:43 UTC] PHP Warning: Invalid argument supplied for foreach() in /home/tripawds/public_html/wp-content/plugins/subscribe-by-em
    ail/inc/settings.php on line 150

    We now have many member sites no longer sending out updates by email. These members are not happy about that. Can you help me eliminate this error?

  • Ash

    Hello jcnjr

    Apologies, I have left a debug code in the above snippet. And I knew that might happen, so I have suggested to keep a backup first.

    Please try the following code:

    add_action( 'plugins_loaded', function() {
    	$sites = get_sites();
    	$blogs = array();
    	foreach( $sites as $site )
    	{
    		$blogs[] = $site->blog_id;
    	}
    
    	global $wpdb;
    	$query = "SELECT * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '%subscriptions_log_table%';";
    	$res = $wpdb->get_results( $query );
    
    	foreach( $res as $r )
    	{
    		if( DB_NAME != $r->TABLE_SCHEMA ) continue;
    		$table_name = $r->TABLE_NAME;
    		$table_name = explode( $wpdb->prefix, $table_name );
    		$table_name = $table_name[1];
    		$table_name = explode( '_subscriptions_log_table', $table_name );
    		$table_site_id = $table_name[0];
    
    		if( ! in_array( $table_site_id, $blogs ) )
    		{
    			if( 'subscriptions_log_table' == $table_site_id ) continue;
    
    			$deleted_table_name = $wpdb->prefix . $table_site_id . '_subscriptions_log_table';
    			$query = "DROP TABLE " . $deleted_table_name;
    			$res = $wpdb->query( $query );
    		}
    	}
    } );

    And again, always take a backup of database before proceeding.

    Have a nice day!

    Cheers,
    Ash

  • jcnjr

    Great work Ash - thanks! I'm sure others may find this helpful.

    That worked as an mu-plugin to successfully delete orphaned sbe log tables

    Now...

    1. Could you please answer my original question? I would like to run a query via phpMyAdmin to SELECT * all TABLE_NAME like '%subscriptions_log_table%' AND with blog_id NOT IN wp_sites... I would like to review the selection before deleting the tables on our live site, and I obviously need help writing that query...

    2. Please ping the devs to have them consider adding this function to the plugin so any orphan sbe tables are deleted when a blog is deleted, if that is possible, and it can be done without risk of deleting live tables.

    3. At this point I am really more interested in eliminating the errors I mentioned above, as we still have SBE deactivated since it generates so many entries in the server error logs like this:

    [01-Dec-2017 02:23:43 UTC] PHP Warning: Invalid argument supplied for foreach() in /home/tripawds/public_html/wp-content/plugins/subscribe-by-em
    ail/inc/settings.php on line 150

  • Ash

    Hello there

    Glad to know that it worked :slight_smile:

    I would like to run a query via phpMyAdmin to SELECT * all TABLE_NAME like '%subscriptions_log_table%' AND with blog_id NOT IN wp_sites... I would like to review the selection before deleting the tables on our live site, and I obviously need help writing that query...

    This is not possible with a mysql query. Even if it is, you need to create stored procedure and that's quite complex. If you want, I can try to make another php script to show the email list from those tables. Please let me know if you want that.

    Please ping the devs to have them consider adding this function to the plugin so any orphan sbe tables are deleted when a blog is deleted, if that is possible, and it can be done without risk of deleting live tables.

    Actually, this is not a plugin bug. When a subsite is properly deleted from a multisite, wordpress itself deletes all corresponding subsite related tables. In your case, you need to check error log if there is anything that prevents deleting some subsite tables.

    At this point I am really more interested in eliminating the errors I mentioned above, as we still have SBE deactivated since it generates so many entries in the server error logs like this:

    If SBE is deleted, there is no chance errors generates from SBE plugin, this is not possible.

    I recommend, to clear the error log, do the same what you think generates those error. If you see the errors again in the log, go to /home/tripawds/public_html/wp-content/plugins/ and rename subscribe-by-email to something else, for example subscribe-by-email-bak.

    Then try to generate the errors again. Now if you still see new error from /home/tripawds/public_html/wp-content/plugins/subscribe-by-email/inc/settings.php instead of /home/tripawds/public_html/wp-content/plugins/subscribe-by-email-bak/inc/settings.php then you are checking wrong directory.

    Please let us know how it goes.

    Have a nice day!

    Cheers,
    Ash

  • jcnjr

    Ash said:

    If SBE is deleted, there is no chance errors generates from SBE plugin, this is not possible.

    I know that, the errors are from before I deactivated the plugin. Sorry for the confusion.

    When I re-activate SBE, the errors return. Suggestions for addressing this issue are greatly appreciated.

    No errors are generated when deleting sites, and the subscription_ tables are left behind.

    If I could have a script to see how many we have, that would be great! Thanks for clarifying the complexity of doing so via SQL query.

    -J

    Thanks!

  • Ash

    Hello jcnjr

    About the error, would you please go to Network Admin > Settings > Subscribe By Email and resave the settings? I have just tried the plugin in one of my test multisite and it generates no error. The suggestions I made based on the code in the line number you mentioned.

    No errors are generated when deleting sites, and the subscription_ tables are left behind.

    About this, I can confirm this in my test install. Though all other additional tables were deleted when I deleted a subsite but this one was left. I am marking this as a bug and reporting to the developer.

    If I could have a script to see how many we have, that would be great!

    First of all, delete/rename the debug.log file inside the wp-content folder. And then, please enable debug mode. To enable debug mode please go to wp-config.php and change

    define( 'WP_DEBUG', false );

    to

    define( 'WP_DEBUG', true );
    define( 'WP_DEBUG_DISPLAY', false );
    define( 'WP_DEBUG_LOG', true );

    Now, use the following code in mu-plugins:

    add_action( 'plugins_loaded', function() {
    	$sites = get_sites();
    	$blogs = array();
    	foreach( $sites as $site )
    	{
    		$blogs[] = $site->blog_id;
    	}
    
    	global $wpdb;
    	$query = "SELECT * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '%subscriptions_log_table%';";
    	$res = $wpdb->get_results( $query );
    
    	foreach( $res as $r )
    	{
    		if( DB_NAME != $r->TABLE_SCHEMA ) continue;
    		$table_name = $r->TABLE_NAME;
    		$table_name = explode( $wpdb->prefix, $table_name );
    		$table_name = $table_name[1];
    		$table_name = explode( '_subscriptions_log_table', $table_name );
    		$table_site_id = $table_name[0];
    
    		if( ! in_array( $table_site_id, $blogs ) )
    		{
    			if( 'subscriptions_log_table' == $table_site_id ) continue;
    
    			$deleted_table_name = $wpdb->base_prefix . $table_site_id . '_subscriptions_log_table';
    			$query = "SELECT * FROM " . $deleted_table_name;
    			$res = $wpdb->get_results( $query );
    
    			ob_start();
    			var_dump( $res );
    			error_log( ob_get_clean() );
    		}
    	}
    } );

    Run the code only ONCE. Then open the file called debug.log inside wp-content folder and you will all the data from the tables that are not associated with any subsite.

    Hope it helps! Please feel free to ask more questions if you have any.

    Have a nice day!

    Cheers,
    Ash

  • jcnjr

    Thank you for your continued help Ash .

    After following your debugging steps multiple times, the debug.log file does not generate on our dev install when setting 'WP_DEBUG_LOG', true. If I manually create the debug.log file, nothing gets written to it even though multiple errors do write to the screen if DISPLAY is set to true. (None of the sbe errors or data though.) I confirmed valid permissions on the file and parent directories.

    But honestly, I cannot focus on that any longer until we address the errors on our live site. I have to keep SBE deactivated until we figure that out. Re-saving the SBE network settings did not help.

    I cleared our error logs, reactivated the plugin, and within seconds there were hundreds of errors in the server log. Please see attached screenshot. I deactivate the plugin and no more errors appear.

    So think about this: The live site is a legacy WPMU installation that was later converted to a WPMS network when mu was merged into the WordPress core. I have been troubleshooting a separate similar issue with WordFence support where that plugin constantly generates errors due to the code improperly identifying site ID #1 on older WPMU installs. Perhaps unrelated, since the error is totally different, but I wanted to mention that in case it helps you identify the cause of the errors.

    FYI: Our dev install is a native multisite network created with WP 3.0+ and I'm no longer seeing the errors there after resaving the SBE settings.

    Once we address the errors, hopefully I can return to the orphan table debugging.

    Thanks again for your time!

  • jcnjr

    Ash said:

    Let me know if it works for you.

    Progress! I think...interesting behavior here, odd actually, but I think we are making progress nonetheless. Please review these notes and provide feedback at your convenience. (Be sure to see note #4.)

    1. SBE was installed but deactivated and we were still seeing the errors, consistently growing the log file. I know, that shouldn't be possible, but I confirmed this.

    2. I deleted the plugin and cleared the log file so there were no errors. I then downloaded the plugin, made the settings.php file edit and manually installed it.

    3. I activated the edited SBE, and... I am no longer seeing the errors! So, that is progress.

    4. However: I can no longer save Subscribe By Email Network Settings. When attempting to change the Mail batches setting or keep logs duration, the network admin page refreshes but the changes to not take effect, reverting to the defaults.

    5. I was able to save site-specific settings on the main blog (site ID 1) and another member blog.

    6. I'm watching PROCESSLIST the whole time while doing this and did notice to various things that indicate SBE seems to be doing its job.
    For Example:

    Query   | 1    | query end | DELETE FROM 'wp_3575_options' WHERE 'option_name' = '_transient_sbe_pending_mails_sent'
    Query   | 0    | checking query cache for query | SELECT option_value FROM wp_543_options WHERE option_name = '_transient_timeout_sbe_pending_mails_se |
    Query   | 1    | query end      | DELETE FROM 'wp_3123_options' WHERE 'option_name' = '_transient_timeout_incsub_sbe_check_logs'
    Query   | 0    | Opening tables | SELECT id FROM wp_3123_subscriptions_log_table WHERE mail_date < 1510690057
    Query   | 0    | statistics | SELECT option_value FROM wp_1_options WHERE option_name = '_transient_timeout_incsub_sbe_updating' L
    Query   | 1    | query end            | DELETE FROM 'wp_89_options' WHERE 'option_name' = '_transient_sbe_pending_mails_sent'

    Many more, but nothing stands out as peculiar...

    So aside from network settings no longer saving, I think we're getting there with the error.

    Thanks again for the good help!

  • jcnjr

    After updating the sbe settings.php file, I cannot replicate the issue of network settings not saving on our dev install. And I'm not setting up another clone for this specific issue.

    Please advise if there is any troubleshooting you can help me do on our live site without affecting anything except Subscribe By Email features. Otherwise, this may have to wait.

    I can live with the default SBE network settings, just thought that issue that arose might indicate other things not working...I am still waiting to see if emails are getting sent from sites I've subscribed to.

  • Ash

    Hello jcnjr

    Well, we can try troubleshooting on live site, but it is really uncertain that nothing will break. What we normally do is to take a backup and then restore, but still as a live site it may get some down time, so that's not good. So, we never really want to this type of testing on the live site.

    Let's see if emails are sent. If there is any issue with that, please let me know.

    Have a nice day!

    Cheers,
    Ash

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.