Make WordPress Core

Opened 18 months ago

Last modified 5 months ago

#57385 accepted defect (bug)

Disable foreign key checks when dropping tables inside wp_uninitialize_site() function

Reported by: naveen17797's profile naveen17797 Owned by: johnjamesjacoby's profile johnjamesjacoby
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Database Keywords: dev-feedback has-patch changes-requested
Focuses: multisite Cc:

Description

By default when removing the tables from a subsite, if the table has foreign key constraint it wont drop the table resulting in tables not being removed.

so this line should be changed from this to

	foreach ( (array) $drop_tables as $table ) {
		$wpdb->query( "DROP TABLE IF EXISTS `$table`" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}
<?php

    $wpdb->query('SET FOREIGN_KEY_CHECKS=0;');
        foreach ( (array) $drop_tables as $table ) {
                $wpdb->query( "DROP TABLE IF EXISTS `$table`" ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        }
    $wpdb->query('SET FOREIGN_KEY_CHECKS=1;');

or any other better alternative.

Change History (5)

This ticket was mentioned in PR #3801 on WordPress/wordpress-develop by @naveen17797.


18 months ago
#1

  • Keywords has-patch added; needs-patch removed

This ticket was mentioned in Slack in #core by naveen17797. View the logs.


5 months ago

This ticket was mentioned in Slack in #core-multisite by naveen17797. View the logs.


5 months ago

#4 @johnjamesjacoby
5 months ago

  • Keywords changes-requested added
  • Owner set to johnjamesjacoby
  • Status changed from new to accepted

Hey @naveen17797 👋 and thank you for this pull request.

First, I like it 👍 and support merging it (or something like it) for a reason I'll tack onto the end.

It is a small code change that will improve the chances that the un'initialization/clean-up routine finishes completely, as intended by the documentation for the wp_uninitialize_site() function.

Worth noting that WordPress core database tables do not implement FOREIGN KEY constraints (and have not since early-early-early days) which implies to me that this code change largely benefits a somewhat limited audience of:

  • modified core tables
  • plugins adding custom tables to the blog scope of $wpdb->tables

At a cursory, only one other DROP TABLE usage appears to me like it would benefit from the same treatment, and that is in /tests/phpunit/includes/utils.php:Mock_Class::drop_tables(), which is a utility that is used with a similar intention but during unit testing instead of in production.

(Other DROP TABLE usages are for specific core tables from previous WordPress versions that did not have foreign key constraints.)


Off the top of my head, this change introduces a few possible consequential scenarios:

  • Anyone in the past 25 years who has been leaning on the fact that WordPress does not delete database tables with foreign key constraints would have a bad time. Some forensic analysis across the web would be necessary to determine if this approach has been published or popularized.
  • It adds to existing slippery slopes where code exists in WordPress to support things that WordPress itself does not use. I believe, personally, sometimes this makes sense and sometimes it doesn't, and our freedom to assess each decision independently has pros & cons (debate vs. paralysis).

Regarding the code changes in the PR, before merging, I would:

  • make foreign_key_checks uppercase
  • run the whitespace fixer
  • add some inline documentation before toggling the SET each time, to add helpful context to future contributors who may not immediately understand why that toggle is necessary
  • instead of hard-coding it to 1 and then 0, it would be more developer & globally environmentally friendly to not assume that 0 is the default, and instead do a query to GET the current value and only toggle it if necessary.

The main reason(s) I am +1 to add support for this, is because wp_uninitialize_site() specifically was coded with the intention of obliterating all site data, and subsequently similar considerations have been made to accommodate deleting all uploads – even in directories that are not limited to WordPress core ones.

#5 @spacedmonkey
5 months ago

@naveen17797 As work around could you use the wpmu_drop_tables and wpmu_delete_blog_upload_dir filters.

add_filter( 'wpmu_drop_tables', function( $tables ){
  global $wpdb;
  $wpdb->query( 'SET foreign_key_checks = 0' );
  return $tables;
});
add_filter('wpmu_delete_blog_upload_dir', function( $basedir ){
  global $wpdb;
  $wpdb->query( 'SET foreign_key_checks = 1' );
  return $basedir;
});

If this code was added to an mu-plugin or network activated plugin, then it would mean you don't want to wait for this code patch.

Note: See TracTickets for help on using tickets.