Skip to content

Instantly share code, notes, and snippets.

@pelmered
Last active October 13, 2021 14:55
Show Gist options
  • Save pelmered/e5633068658e75ba9a80 to your computer and use it in GitHub Desktop.
Save pelmered/e5633068658e75ba9a80 to your computer and use it in GitHub Desktop.
Cleanup orphaned ACF data
# This might ruin your database and I do not take any responsibility for that. Backup your database before continuing
!
# Check the results throughly
SELECT * FROM `wp_postmeta`
WHERE `meta_key` IN
( SELECT TRIM(LEADING '_' FROM `meta_key`) AS mk
FROM `wp_postmeta`
WHERE `meta_value` regexp '^field_[0-9a-f]+'
AND `meta_value` NOT IN
(SELECT `post_name` FROM `wp_posts` WHERE `post_type` = 'acf-field')
)
OR `meta_key` IN
( SELECT `meta_key` AS mk
FROM `wp_postmeta`
WHERE `meta_value` regexp '^field_[0-9a-f]+'
AND `meta_value` NOT IN
(SELECT `post_name` FROM `wp_posts` WHERE `post_type` = 'acf-field')
)
# Apply delete
DELETE FROM `wp_postmeta`
WHERE `meta_key` IN
( SELECT TRIM(LEADING '_' FROM `meta_key`) AS mk
FROM `wp_postmeta`
WHERE `meta_value` regexp '^field_[0-9a-f]+'
AND `meta_value` NOT IN
(SELECT `post_name` FROM `wp_posts` WHERE `post_type` = 'acf-field')
)
OR `meta_key` IN
( SELECT `meta_key` AS mk
FROM `wp_postmeta`
WHERE `meta_value` regexp '^field_[0-9a-f]+'
AND `meta_value` NOT IN
(SELECT `post_name` FROM `wp_posts` WHERE `post_type` = 'acf-field')
)
@pelmered
Copy link
Author

@pelmered any idea about @mikeStapes question?

No, not really. Sorry.
I haven't touched this for over five years. I guess this error is because of newer MySQL versions.

But try what ziembatron suggested. You probably have to add pm. before any column references to the wp_postmeta table as well.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment