During its occasional and automatic core version updates (for example from version 5.9.2 to 5.9.3) WordPress inspects its tables in its database server (MariaDB or MySQL) and tries to restore them to WordPress’s standard schema. Sometimes a site owner or plugin customizes those tables. For example my Index WP MySQL For Speed plugin reorganizes some tables with new primary keys. In that case the WordPress version update workflow tries to restore the standard keys. The workflow inspects each table in turn looking for differences between the actual table and its “standard” definition, and then issues ALTER TABLE statements where it finds differences.
The dbDelta() function inspects tables and tries to restore them, with source code here. The wp_get_db_schema() function’s source code contains the standard table definitions.
So, when the tables have been customized by a plugin it would be helpful for the plugin to filter the database changes during the version update process. This can preserve customizations. There’s a WordPress filter for this purpose, called dbdelta_queries
. WordPress passes it a list of the SQL statements, like CREATE TABLE
statements, to create the tables from scratch. After the filter fires, the dbDelta() function compares those statements with the actual tables in the database. Therefore, a filter can change the queries to reflect the current state of each table. Then dbDelta() won’t find differences and try to change the tables back to standard.
Here is a do-nothing version of a dbdelta_queries
filter.
add_filter( 'dbdelta_queries', 'my_query_filter', 10, 1 );
/** Filters the dbDelta SQL queries.
*
* @param string[] $queries An array of dbDelta SQL queries.
* @since 3.3.0
*
*/
function my_query_filter( $queries ) {
return $queries;
}
Each element of the $queries
array contains a SQL statement. Filter code can inspect those statements and change them as needed. The filter code should not remove statements from the array if they’re unchanged, however.
There are a couple of complications to consider, though.
- WordPress does not load ordinary plugins during the version update workflow. So, the filter must be in a must-use plugin.
- Some published plugins use the dbDelta() function to create plugin tables. Statements for those tables are presented to the filter just like statements for version updates. So, the filter must ignore statements that do not relate to the tables it care about and pass them back unchanged.
A developer of an ordinary plugin must do some extra work.
- Write the filter in a separate file.
- Store that file in the must-use directory during plugin activation.
- Remove it from the must-use directory during deactivation.
There doesn’t seem to be any WordPress action for “version update begins” or “version update ends” that can be hooked from an ordinary plugin, so the must-use plugin must remain in place during normal operations. The filter is called very rarely and only during the creation or update of database tables, so the efficiency of the code in it is not important. But the efficiency of the main part of the must-use plugin is critical.
At the beginning of the version update workflow, WordPress calls WP_Upgrader::create_lock( 'core_updater' );
The source code is here for that function. It works by creating an option named core_updater.lock
containing the time() value when the lock was created. At the end of the update workflow it calls WP_Upgrader::release_lock( 'core_updater' );
to delete the option.
So, a dbdelta_queries
filter can avoid doing anything unless the version update workflow is in progress with code like this.
add_filter( 'dbdelta_queries', 'my_query_filter', 10, 1 );
/** Filters the dbDelta SQL queries.
*
* @param string[] $queries An array of dbDelta SQL queries.
* @since 3.3.0
*
*/
function my_query_filter( $queries ) {
// Look for the core update lock
$lock_option = 'core_updater.lock';
$lock_result = get_option( $lock_option );
/* No lock found? We'e not doing a core update, so bail */
if ( ! $lock_result ) {
return $queries;
}
// Check to see if the lock is still valid. If it is, bail.
if ( $lock_result > ( time() - ( 15 * MINUTE_IN_SECONDS ) ) ) {
return $queries;
}
// Filter the queries as needed
return $queries;
}
Hi Ollie!
Very nice post – Please does it mean we (users of Index WP MySQL For Speed plugin) need to do something after Woocommerce updates?
No. This plugin doesn’t touch any WooCommerce specific tables or keys.
As of version 1.4.10 of the plugin it will also use the code in this post to avoid changes to indexes during WordPress version updates.