Orphaned post left-overs in aioseo_posts table

[PrinceOfAbyss]

While trying to debug another issue (which I reported in a separate bug report), I noticed that in aioseo_posts table there was a huge** amount of orphaned records/left-overs from posts that weren't anymore in the DB.

To be honest, I then created enough test posts, which I deleted, and indeed the respective aioseo_posts records were also deleted. But evidently (see image below), there are cases where this is not happening, and orphaned records do remain in the table...

** In a posts table of ~360K records, there existed 20K+ orphaned records in the aioseo_posts table when I first found out, which I deleted yesterday. And until ten minutes ago, when I took the screenshot below, there are currently 221 new orphaned ones... So, it's not happening so rarely... The only thing left is to find out the circumstances under which this is happening...

[arnaudbroes]

@PrinceOfAbyss do those posts still exist in some way or another in the regular posts table? The only thing I can think off is that the data for these posts is being migrated over from the old version and their records somehow still exist in the posts table.

[PrinceOfAbyss]

@arnaudbroes the website didn't always use Wordpress. It was migrated by me and my partner web developer a couple of months ago from another platform. So, those 20K orphaned records happened during this 2-3 months period under some unknown circumstances!

Also, the site didn't have the chance to use another, old version of AIOSEO, as it wasn't, like I said before, using Wordpress. We purchased the Pro Basic license, and tested/used your plugin for the first time on 15 of this February!

Something else is actively producing these left-overs. Yesterday I deleted the 20K ones, and until now, another 221 were born... And TBH, I ran the SELECT * FROM aioseo_posts WHERE post_id NOT IN (SELECT ID FROM posts); at around 21 o'clock local time (now it's 1:54), so that is about 5hrs ago, and there were no orphaned records. So during these last 5 hours 221 records somehow got created...

Edit: Feel free to use the same admin credentials I created for you for the other ticket, to attempt debugging this one as well if you want!

[PrinceOfAbyss]

@PrinceOfAbyss do those posts still exist in some way or another in the regular posts table? The only thing I can think off is that the data for these posts is being migrated over from the old version and their records somehow still exist in the posts table.

To reply to your exact question, no those post_id's are totally non-existent in the regular posts table. The SQL query I ran (I mention it in my previous reply) is perfectly describing the situation.

An educated guess would be that the post_id's may be coming from revisions?!? But is it possible that you haven't taken care of this in your code?

I've noticed the DOING_AUTOSAVE check in your code, in app/Common/Admin/PostSettings.php

        // Ignore auto saving
        if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
            return;
        }

But is it possible that you've not taken any measures for the revisions? I doubt that, for such a well-thought and built plugin!

[PrinceOfAbyss]

@arnaudbroes I think I just found out the reason this is happening! In my site I'm using WP-Optimize to clean, among others, the revisions of posts... As I said in my previous post, the orphaned post records were 221 some minutes ago. I just ran WP-Optimize however, and that number jumped to 238, obviously after WP-Optimize removed some post revisions...

Please elaborate on this, as I've not searched, so I don't know if in your code you're taking care of post revisions. Is this the root of the problem?

[PrinceOfAbyss]

@arnaudbroes I just noticed the following, while searching for the term revision in the source code of your plugin:

In app/Common/Utils/Helpers.php you have this function isValid() that takes care of revisions, etc...

    public function isValidPost( $post ) {
        if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
            return false;
        }

        if ( ! is_object( $post ) ) {
            $post = get_post( $post );
        }

        // In order to prevent recursion, we are skipping scheduled-action posts.
        if (
            empty( $post ) ||
            'scheduled-action' === $post->post_type ||
            'revision' === $post->post_type ||
            'publish' !== $post->post_status
        ) {
            return false;
        }

        return true;
    }

On the other hand, in app/Common/Admin/PostSettings.php function saveSettingsMetabox() only checks for DOING_AUTOSAVE like this:

    public function saveSettingsMetabox( $postId ) {
        // Ignore auto saving
        if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
            return;
        }

Shouldn't this function also make use of isValid() instead?

Basically, when a post changes from publish to revision, IMO, the relevant aioseo_posts record should be deleted. In case the user rolls back to a revision, the aioseo_posts record, along with its score etc, should be recalculated and re-written in the DB...

[arnaudbroes]

@PrinceOfAbyss when you restore a revision, the ID of the post should stay the same and AIOSEO should only create a record in the aioseo_posts table for that original ID. For instance, when I create a post with ID 601, then make some changes and create revisions with ID 602, 603 and 604, and then restore revision with ID 603, the only record I have in the aioseo_posts table is for 601, the original post. Are you saying this is not the case on your site?

[PrinceOfAbyss]

Hello again! Sorry for taking so long to respond. Actually, I've not tracked what is happening step-by-step behind the scenes when you create a post revision. My scenario was only an educated guess, based on the fact that I have tons of orphaned leftovers in my aioseo_posts table. Since 3 days ago that I first reportedthis issue, another 282 orphaned records have been created...

[arnaudbroes]

@PrinceOfAbyss I was not able to reproduce this previously. When I have some time I'll try to check whether this is caused by WP-Optimize like you said. Are you still using that plugin?

[PrinceOfAbyss]

@PrinceOfAbyss I was not able to reproduce this previously. When I have some time I'll try to check whether this is caused by WP-Optimize like you said. Are you still using that plugin?

Yup, I'm actively using it everyday to tidy up a bit my heavy website. As it's a busy news site, there are more than a dozen Editors who write numerous posts, edit them, etc everyday.

[PrinceOfAbyss]

Take a look at this video I recorded for you... As soon as I run WP-Optimize, orphaned records jump from 282 to 474!

https://www.youtube.com/watch?v=J4cIFmeThrM

[arnaudbroes]

@PrinceOfAbyss thanks, I've been able to reproduce this now. We'll have to take a look at what WP-Optimize exactly does and see what we can do in response. We'll most likely add a CRON job when WP-Optimize is active to periodically clean up orphaned posts in our table. I've put it in our roadmap for version 4.1.3. The next update, 4.1.2, is already in QA so we can't include it in that one anymore.

[PrinceOfAbyss]

I'm so happy to hear that you've been able to spot that! Great work!

[arnaudbroes]

Hey @PrinceOfAbyss,

It actually took us until today to figure out why we were storing data for revisions (because we eventually realized that is the root cause of this issue - we shouldn't be storing data for them).

We couldn't reproduce the issue ourselves originally but now we know it only happens when you use the Classic Editor. I've developed a fix for it and it should be included in the 4.1.8 release which will be going out late February.