How can I safely delete data related to wp_post table manually from the mysql database?

Question

Recently I downloaded a plugin that had a bug that orphaned its custom post type items in the wp_post table rather than deleting them. Being new to wordpress, it wasn't apparent to me that the wp_postmeta table was related to that table. Now, I have entries in the latter with NULL values for the $post_id. (I simply deleted everything in wp_post with that content type.)

I'm assuming that it would be easy enough to do a left join query to find all those wp_postmeta items and delete everything with $post_id NULL. But, I'm not exactly sure how the wordpress tables relate to each other. If I were to do this manually, are there any other default tables that need to be taken into consideration? Does anyone have a script in their library to handle something like this?

If you can suggest a plugin, I'm open to that as well.

Answer 1

As you already deleted all the posts in the table, this is going to be quite a bit of work manually.

For the next time you do something like that, use wp_delete_post( $postid, $force_delete ); (Codex) while looping through all the posts with that posttype, as this function deletes all the data associated with that post.

$args = array(
    'posttype' => 'yourposttype',
    'numberposts' => -1
);
$todelete = get_posts( $args );

foreach( $todelete as $deletethis ) {

    wp_delete_post( $deletethis->ID, true );

}

In your case, you will have to check the following tables for data (I skip the tableprefix here):

• posts (which you already did)
• postmeta
• comments/commentmeta
• term_relationships

In all cases you will have to search for entries with the post_id/object_id NULL or with no entry in the posts table, and delete them. The SQL should be pretty straight forward.