Support

Account

Home Forums General Issues Delete postmeta after delete revisions

Solving

Delete postmeta after delete revisions

  • I’ve 2 custom fields from advanced custom fields.
    I normally delete with phpmyadmin the revisions of the post to clean my database.

    DELETE FROM wp_posts WHERE post_type = 'revision';

    Now I have a very big wp_postmeta table and I’ve made question to see how many postmeta fields do not have relation with any post

    SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL

    and the result is total: 256927 fields
    And a lot of the are from my custom fields form acf

    Some of the adf are emty and other are with content.

    1- Are these postmeta from deleted post or from deleted revisions?
    2- Is a problem to delete them from my database?
    3- Is it possible to delete the post meta fields from acf when I delete the revisions?
    4- Is there a way from advanced custom field to delete all the fields that the plugin do not need?
    5- Do you have a query to delete all the postmeta that is not needed?

    For each item on tthe wp_postmeta table I have 2 fields

    For example:

    Meta ID Post ID Meta_key Meta_value
    28218 8796 my-acf-name
    28219 8796 _my-acf-name field_517ea3ab3737
    28220 8796 my-acf-name2 this is content
    28221 8796 _my-acf-name2 field_517ea3ab3737

    6- is correct to delete the 2 fields from each acf items?

  • Have anyone deleted the post_meta files from my custom fields that are without post_id?
    Is dangerous?
    SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL

    is there any query to make that?

  • Could any admin form advanced custom fields comment this post if i’s possible to clean the post_meta table with not problems?
    On my first message I comment what is the problem?
    I deleted with php my admin the post revisions and now there are a lot of custom fields files on the post_meta table. some of them without post_id relation, other with no content…

  • The problem is that you deleted the posts in phpMyAdmin. When you do this it becomes extremely difficult to delete the orphaned post meta values. The link I posted is to a plugin that’s supposed to be able to clean this up.

    The only way short of this is to build your own SQL queries and run them through php. The steps involved are

    1. Query the database and get a list of all active post IDs
    2. Run a delete query on the _postmeta table WHERE post_id NOT IN (post id list)

    The developer of ACF does not visit here much, this is a users helping users forum. If you want to get input from the developer then the best chance you have of that is submitting a new support ticket here https://support.advancedcustomfields.com/new-ticket/

  • thanks a lot John.
    I have this query to view the postmeta files that do not have any post relation

    SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL

    But the query do not show only the advanced custom fields files.
    On the next query I have added the acf field to select only these fields

    SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL AND pm.meta_key LIKE '%my-custom-field-name%'

    And now I would like to know if the query is correct and i can make the delete query without problems and errors on my wordpress after

    DELETE FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL AND pm.meta_key LIKE '%my-custom-field-name%'

  • Honestly, I can’t say if your query is right or wrong by looking at it. For me, I would back up the database and run it and see what happens. I’m not really an expert on SQL.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.