Database – Post Revisions – Orphan Records

Update

I just ran LS Purge Post Revisions and now have 1295 orphan records in postmeta.

Cache was operational. Could that be the problem?

This is actually a material issue – I’ve run up as many as 100K orphan recs that push the DB size to well over 1GB.

Thx

Hi,

I have not received any similar report

purge reivision is just run

$sql = "DELETE FROM$wpdb->postsWHERE post_type = 'revision' AND post_parent = %d ORDER BY ID LIMIT %d";

to delete the row with post_type = revision

best regards,

Sorry – not clear what your comment/SQL means.

Just to clarify, when I run the LS Post Revisions purge:
– the post record(s) associated with revisions are correctly purged.
– postmeta records associated with the revision post record are not purged.

Assuming the SQL in your comment is the code executed by the Post Revisions purge, pls note:

$sql = “DELETE FROM$wpdb->posts is only deleting records from the posts table. But the postmeta records associated with the revision post records are not referenced in your SQL statement. And I doubt there is a cascading delete in place.

I’m sure I’m stating the obvious, but:

posts table – contains the original post header record
– posts table – contains the revisions header record (these are correctly deleted)
— postmeta table – contains the detail records associated with the revision header in the posts table (these are not getting deleted)

Could you please confirm that the LS Posts Revision purge is, in fact, deleting the revisions’ postmeta records. Every time I run this function, I get orphan postmeta records.

the query is

DELETE FROM $wpdb->posts WHERE post_type = 'revision' $sql_add

it deletes the rows in wp_posts table where the post_type is revision , if that is what you want to know

You clearly do NOT understand the issue.

There are two tables involved in purging revisions: posts and postmeta.

– the posts table contains the revision header. For the third time, the post record is being correctly deleted.

– the postmeta table contains all the revision detail records. And for the third time, your plugin is NOT properly deleting these records.

The complete sql should include sth like:

DELETE FROM xxx_postmeta postmeta

LEFT JOIN xxx_posts posts ON posts.ID = postmeta.post_id

WHERE posts.post_type = 'revision'

• This reply was modified 2 years, 3 months ago by yinxingmaiming.
• This reply was modified 2 years, 3 months ago by yinxingmaiming.

@yinxingmaiming , please feel free to test this patch and let us know if it resolves your issue. Thank you!
https://github.com/litespeedtech/lscache_wp/pull/515

• This reply was modified 2 years, 3 months ago by Tynan Beatty.

Actually, I’ll you test it and put a tested fix into your GA product. The pull request title Database optimizer now handles postmeta when cleaning revisions is a little concerning, as it suggests that your team had not even considered postmeta records in your original design. The posts-postmeta relationship is a fundamental structure in WP – I’m now concerned about what else may be missing.

But thanks for updating the status.