Repeated UPDATE Query Bug with Yoast + Redis

We have both Object Cache Pro and Yoast SEO Premium installed. While diagnosing some load issues the other day, we noticed that there seemed to be an UPDATE query being triggered repeatedly, that looked as if it should not be. The issue appeared to be a combination of a particular value cached in redis not letting the UPDATE only happen once, as intended, since it was performing a direct DB write. The other dev working with me on this first had this to say:

$wpdb->update( $wpdb->options, [ 'autoload' => 'yes' ], [ 'option_name' => $option ] );
it’s updating the db directly, so not triggering alloptions cache to be refreshed maybe?
yep, re-created that scenario locally and it’s updating on every page load now
set them to autoload=no and flush redis

The options in question that we happened to observe this with are:
wpseo-premium-redirects-export-regex
wpseo-premium-redirects-export-plain

But it may not be limited to just them. To reproduce, I flushed the redis cluster on our stage environment, and flipped those two options’ autoload setting to ‘no’. Then on the next load, I saw in Query monitor that it performed the UPDATE. The database was changed to autoload=yes, but the cached value was stale, so followup refreshes also keep executing the UPDATE query that should not be happening anymore. Flushing redis fixed the problem, but this isn’t an ideal solution if there is a way to prevent that situation in the first place. We suggest something along these lines:

		// The code below is needed because we used to not autoload our redirect options. This fixes that.
		$all_options = wp_cache_get( 'alloptions', 'options' );
		foreach ( [ $this->normal_option_name, $this->regex_option_name ] as $option ) {
			$redirects[ $option ] = isset( $all_options[ $option ] ) ? maybe_unserialize( $all_options[ $option ] ) : false;
			if ( $redirects[ $option ] === false ) {
				$redirects[ $option ] = get_option( $option, false );
-				// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Normal methods only work if the option value has changed.
-				$wpdb->update( $wpdb->options, [ 'autoload' => 'yes' ], [ 'option_name' => $option ] );
+				delete_option($option);
+				add_option($option, $redirects[ $option ]);
			}
		}

I believe this is a preferred approach as delete/add go through the caching system when available, whereas a direct DB update seems to be bypassing that, causing this UPDATE loop.

• This topic was modified 3 years, 7 months ago by chief1983.