how to delete runaway post from database

  • Today, my client notified me that they were unable to delete a post on their site. Clearly, this post has become corrupt. The error log on the server was > 2GB in size after all his attempts and he actually crashed his VPS.

    Perhaps I am over-complicating this manual deletion but I am aware that post data fragements are stored in the following tables:

    wp_posts
    wp_post_meta
    wp_term_relationships
    wp_term_taxonomy

    There are 29 rows associated with the offending post in wp_posts. Each has a unique post ID.

    Has anyone written a query that I can use to locate all these posts and their related fragements from the tables? Alternatively, can anyone offer guidance on how I would structure this SQL code?

Viewing 4 replies - 1 through 4 (of 4 total)

  • Before you start, backup your DB ??

    The extra rows in wp_posts are most likely revisions, child posts or attachments.

    SELECT ID, post_status WHERE post_parent = (ID of current published post)

    will give you the list of everything. I’d first kill the revisions with:

    DELETE FROM wp_post WHERE post_parent = (ID of current post) AND post_status = ‘revision’;

    If you’re OK ditching the attachments you can run:

    DELETE FROM wp_post WHERE post_parent = (ID of current post) AND post_status = ‘attachment’;

    If you’re OK ditching the child posts you can run:

    DELETE FROM wp_post WHERE post_parent = (ID of current post) AND post_status = ‘publish’ or post_status=’draft’;

    That should clean up the wp_post table. The entries in the other tables will just become orphans, they’ll do no harm, but to rid them run:

    DELETE FROM wp_postmeta WHERE post_id = (ID of current post);
    DELETE FROM wp_term_relationships WHERE object_id = (ID of current post);

    It’s probably best just to leave the orphans in wp_term_taxonomy be.

    Again, back up your DB before you start! (I haven’t tested any of the SQL above against a live DB, it’s just from my head – which sometimes contains dust and lint…)

    Thread Starter Marj Wyatt

    (@marjwyatt)

    Thanks for your reply, Roscius. My client kept trying to delete the post through the dashboard so I was forced to address it the same day I posted my inquiry. Receiving the notification reminded me that I had worked it out already.

    I did backup the site and database before I began mucking around. ??

    I actually took the easier and softer way though. Once I deduced that the post_parent ID was the simplest way to locate all the database content, I used phpMyAdmin’s “search database” function to locate all the tables and rows within those tables for the post parent ID. I then opened each of those reported tables in a separate tab and scrutinized the data to ensure that it was related to the runaway post.

    The wp_posts table had 2,741 rows related to the rogue post. Each of them had the same post title and time stamp. Amazing!

    Look for orphaned post_meta entries:

    SELECT wp_postmeta . *
    FROM wp_postmeta
    LEFT JOIN wp_posts ON wp_postmeta.post_id = wp_posts.ID
    WHERE wp_posts.ID IS NULL
    AND wp_postmeta.post_id IS NOT NULL

    Thread Starter Marj Wyatt

    (@marjwyatt)

    Thanks for the reply but I have resolved this issue using the approach that I posted just above your reply. All references to that parent post in the database were located using a full database search and subsequently deleted.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘how to delete runaway post from database’ is closed to new replies.