Yoast SEO canonical URL gets pointed to the homepage

I noticed that all problematic posts had their wp_yoast_indexable row updated a few months ago which exactly matched with when I ran a script to update all existing posts. It was something like this:

$posts = get_posts($args);

foreach ($posts as $i => $post) {
    $post_data = array(
        'ID' => $post->ID,
        // other stuff
    );

    wp_update_post($post_data);
}

The problem was that I ran the script from the CLI, where $_SERVER['HTTP_HOST'] is not set.

Apparently, when you run wp_update_post(), Yoast SEO runs and updates its own tables. If HTTP_HOST is missing, the updated permalink turns to something like http:///the-post-slug/. That’s what I saw in the debugger. My guess is that it gets sanitized at some point and finally turns to /.

To fix this, all I had to do was to run an empty wp_update_post() on all problematic posts while HTTP_HOST is set:

<?php

$_SERVER['HTTP_HOST'] = 'example.com';

require '/opt/bitnami/wordpress/wp-load.php';

global $wpdb;

$query = <<<SQL
SELECT object_id
FROM wp_yoast_indexable
WHERE permalink="https://wordpress.stackexchange.com/";
SQL;

$result = $wpdb->get_results($query, ARRAY_A);
$ids = array_column($result, 'object_id');

foreach ($ids as $id) {
    wp_update_post(['ID' => $id]);
}

Related Posts: