Support

Account

Register Login

Home › Forums › General Issues › Flush Unused Custom Fields

Solved

Flush Unused Custom Fields

  • Is there no way to:
    1. Have ACF log the data in creates in DB, and then remove based off of log?
    2. Have ACF prefix fields / data it creates with something like acf_and then the data or key, etc.
    3. Create it’s own table to read / write to?

    I think it’s understood that ACF is one of WPs top most used addons / plugins.. those that use it, know this.. I preface my comment saying that because I would gladly create another DB table OR entire DB just for ACF to utilize if it meant the unused data could then be safely removed.

  • I’d like to know too. Thanks. I was looking into a plugin called wp-optomize ( https://wordpress.org/plugins/wp-optimize/ ) and didn’t know if it was something anyone here has used.

  • Automatic cleanup will be difficult no matter what information is stored.

    When would the cleanup happen?
    Each of the following conditions would leave behind orphaned data and would have its own problems and hurdles to jump. Some of them are more complicated to achieve than others.

    • When a field is deleted
    • When a field group is deleted
    • When the location of a field group is changed
    • When a field is hidden by conditional logic
    • When the sub fields of a repeater/flex field/clone change
    • When a field is moved from one field group to another – should data be deleted or moved?
    • When the parent of a field is changed (When a field is dragged into a different location, for example one repeater to another) – should data be deleted or moved?
    • When the field name is changed – should data be deleted or moved?
    • I’m sure I’m missing others

    What data do you think ACF could store to help make the clean up of data in each of the above conditions easier?

    How could ACF overcome the limits of WP when it comes to the issue of performing all of the checking and deletion of data from the five locations where it can be stored (_postmeta, _termmeta, _usermeta’, _commentmeta and options) when each check/deletion will require calling a WP function to delete the specific data? If the answer to some of the conditions is that data would be move then this will also require calling the WP function to insert the data in the new location. How can this be performed on a large site on every post without causing the admin to time out due to the number of db queries that must be performed?

  • First of all, thanks for even spending the time and effort with the issue, I’m sure I speak for all above.

    In reference to the latter part of your last post, and keeping in mind I’m not even remotely close to having the dev knowledge you have.. but maybe sometimes someone without the knowledge could introduce something so elementary that it may have gotten overlooked 🙂

    You said, “What data do you think ACF could store to help make the clean up of data in each of the above conditions easier?”

    Once again, coming from an elementary view.. If and when ACF creates a (_postmeta, _termmeta, _usermeta’, _commentmeta and options) it would also log that creation. Maybe chain it (create a relationship) of this specific creation and attach a unique serial number to it. So when it is “clean up time”, that specific and unique action was taken can then be read by ACF, and then go back and erase / clean up no longer used data.

    “How could ACF overcome the limits of WP when it comes to the issue of performing all of the checking and deletion of data from the five locations where it can be stored (_postmeta, _termmeta, _usermeta’, _commentmeta and options) when each check/deletion will require calling a WP function to delete the specific data?”

    Maybe not an ongoing cleaning process, but one initiated by admin when it’s an optimal time for that site. A tab / setting in admin to click “clean” button, accept warning and then clean. Obviously warning one to backup DB first.

  • Hi all,
    if you look at my try to achieve this:
    https://github.com/filippozanardo/redvolver-acfcleaner

    You see that ,expect some field that i didn’t found a way yet, can be made in 2 ways, one is as john say delete all the meta related to ACF on acf/save_post hook at high priority.
    The other a more difficcult way is to match the field in the database/json and then to delete the field that is not present anymore in the current saved data.
    I found a trick also to check if the field is a flexible field and delete the field.
    As far as i test works remember to backup db first.

    For now works on post and custom post type when i have time i want also to include a “Clean all ACF” tool that use this method, and i’m trying also to find a way to “clean” all the fields for taxonomy, user etc etc.

    Suggestions, ideas and pull requests are welcome.

  • In my list, I’ve basically listed different things in order of difficulty. For example, it is actually pretty easy to delete all of the existing data for a field if a field is deleted.

    When you start getting into the other things, quite honestly, I don’t really have the time to build an application that will root through all the fields in every group and try to find what matches up for something like changing the location of a field group.

    And when it comes to conditional logic, of altering sub fields of repeaters, or even renaming a field, if you want to accomplish this, even if you save other data to help in the process, it means that you need to inspect all of the data that was saved and compare it to all of the data that is saved and I think that this is something that is reasonable to achieve. The amount of work that would be needed to achieve it would far outweigh the benefit of it. To do this you would need to loop through every field group and every field, figure out what should be there and then delete anything that shouldn’t be there and the looping would need to actually look at the conditional logic associated with every field…

    … and then to complicate things even further. Let’s say that you have a tab field and this tab field is controlled with conditional logic. All the fields in the tab are conditional, but they do not need to have their own conditions since the tab controls it. Now, with every field you need to look and see… is this field in a tab? does the tab have conditional logic? should all fields in the tab be removed?

    Like @redvolver says, and I mentioned before, it could be done easily if you just flush everything when a post is saved and make ACF update with new values. The main issue with this method would be clone fields. The reason for this issue is that clone fields causes duplicated field keys. There are some other conditions that could also create duplicate field keys, for example, registering field groups using PHP and using the same field in several places without using a unique key.

    Like I said, I started building something that would do this automatic cleanup, but clone fields stopped me, since, in the end the only thing you really have to go by is the field key. Even a process that must be initiated by an admin would have these problems.

    I’ll continue to think about the issue and if I think of some way to accomplish it without writing a million lines of code to make it safe I will.

  • Thank you, John. So if you have to inspect data, then you wouldn’t recommend wp-optimize because it does not offer enough choices or offers too many for a novice to understand? Does it have perils regarding that conditional logic? I understand it to a point, but have difficulty where there are situations of intersecting data but my need is to focus on what the two named groups don’t have in common, etc.

    What about for the easy-to-delete unused data from a non-existing field? I’m a little leery of going into my database itself exactly because I’m so new.

  • I don’t think that wp-optimize will remove meta values and options, at least I don’t get this from reading the description and features, so I don’t think that it will hurt to use it. Maybe I’m missing something. It appears to delete orphaned data that could possibly be left behind when a post or some other object is deleted but not all of the meta values for that object are deleted. Honestly, I’ve never seen this happen except by going into the database and deleting posts from there rather than doing so through the admin.

    Deleting content when an ACF field is deleted is easy, relatively. Please note that the use of this function cannot be undone and it will erase all traces of content for any ACF field that is deleted. Also, I don’t use prepare in this, some people will probably find issue with that. It could probably be done with the first query, but I don’t think it can be done with the second query. Anyway, the object here is to wholesale delete all of the content with the least number of queries possible.

    Please, please be sure that this is something that you want to do before implementing this and I would strongly suggest that this is only enabled during development and not on a live site. Should a client go into ACF for some reason and delete a field, there is nothing that you’d be able to do to recover form it.

    
<?php 
  
  // this action is run by ACF whenever a field is deleted
  // and is called for every field in a field group when a field group is deleted
  add_action('acf/delete_field', 'delete_acf_content_on_delete_field');

  function delete_acf_content_on_delete_field($field) {
    // runs when acf deletes a field
    // find all occurences of the field key in all tables and delete them
    // and the custom field associated with them
    global $wpdb;
    // remove any tables from this array that you don't want to check
    $tables = array('options', 'postmeta', 'termmeta', 'usermeta', 'commentmeta');
    foreach ($tables as $table) {
      $key_field = 'meta_key';
      $value_field = 'meta_value';
      if ($table == 'options') {
        $key_field = 'option_name';
        $value_field = 'option_value';
      }
      $table = $wpdb->{$table};
      // this query gets all key fields matching the acf key reference field
      $query = 'SELECT DISTINCT('.$key_field.')
                FROM '.$table.'
                WHERE '.$value_field.' = "'.$field['key'].'"';
      $results = $wpdb->get_col($query);
      if (!count($results)) {
        // no content found in this table
        continue;
      }
      // loop through keys and construct list of meta_key/option_names to delete
      $keys = array();
      foreach ($results as $key) {
        $keys[] = $key; // delete acf field key reference
        $keys[] = substr($key, 1); // delete acf field value
      }
      // do escping of all values.... just in case
      $keys = $wpdb->_escape($keys);
      // delete all of the content
      $query = 'DELETE FROM '.$table.'
                WHERE '.$key_field.' IN ("'.implode('","', $keys).'")';
      $wpdb->query($query);
    } // end foreach table
  }

?>

  • Thank you very much, John. Yes, I’ll be very careful…and make absolutely certain to backup first.

  • Any updates here?

  • I think the importance of this is pretty obvious and has been discussed about through and through, so I’ll just leave my +1 here, hoping mass demand will impact the roadmap.

  • John Huebner, this is useful, but I noticed a problem that some people may have if they don’t read your code and paste it into their functions file. I didn’t have this problem myself, but I imagine others might…

    People: Please note that this function deletes field data by name. This can get you in trouble if you are not careful. If you have two custom post types, for example:

    – project
    – staff member

    And they both have a field with the same name but for different purposes, say “subtitle,” deleting a field from one field group will delete ALL data for all fields with that name. In this example, if you delete “subtitle” from staff member, than all project “subtitles” will be deleted.

    Hope this helps someone…

  • Yes it does. What my code does is this

    1) It queries every table for the value that matches the ACF field key
    2) It constructs a list of “name” values where one is the field name and the second is “_{$field_name}” which holds the field key
    3) It deletes all of the values for this field name

    This is only safe for field that will have unique names in the database. This would not be safe if you have fields that are named the same. There is really nothing that I can do about this.

    It is a crude hack to do something that is really impossible to do safely without a lot of time and work involved.

    This is why I added the warning and that any time you’re messing with the DB in this way you should have reliable backups of your DB.

  • Annoyingly i’ve already deleted all my fields and check this useful thread a bit too late to read the clean on delete possibilities.

    I’ve got around 4,000 posts with a lot of dead meta.

    Couldn’t this be achieved with a bit of SQL? Trying to piece it together but not my strongest field!

    post_type=’acf-field’, post_excerpt seems to be the name of the field in this case ‘post_startdatetime’

    post_meta.meta_key is then like this
    post_startdatetime
    _post_startdatetime

    So if the post_type=’acf-field’ are deleted could we loop through the post_meta.meta_key, check if there’s a matching posts.post_excerpt and if not delete the post_meta entry?

    But there’s there’s an entry per post with a name like ‘field_534fe013d9a0d’ though we would still get 75% of unused post_meta.

  • The topic is marked as solved but after reading through it I still con’t find a convincing way to clean/flush out old fields created and then changed or removed during development.. Please can someone help me, am I being dumb? Has this been solved or not?

  • No, there really is no solution and I doubt there ever will be. There really isn’t a safe way to delete all of the data for a field if a field is deleted. The solution that I posted will work if you are extremely careful about never creating 2 fields with the same name.

    There isn’t a safe way to generically remove unnecessary field values when a post is updated. This is something that you need to decide to do and create a custom solution that works for your needs and use the acf/save_post hook to create a filter.

  • +1

  • +1

  • +1

    One thing i noted by reading some of the possible solutions is that it seems to be a problem that custom fields doesn’t have a unique identifier. I don’t get why ACF doesn’t just implement that every field created gets it own unique id regardless of the field name and such. This would make all this much easier. Am I missing something here? 🙂

  • Every field does have a unique identifier, it’s called the field key.

    The problem is that ACF does not know that a field has been deleted or what data should be deleted. When you update a field group ACF has the submitted data to work with. Since the deleted field in the group is not submitted there is no immediate way to know that anything was removed. This might be possible to do by comparing the old values with the new values. This would be an extremely complex issues due to repeaters, flex fields, clones and the like and the complicated way these fields can be nested.

    The second problem is efficiency. Let say that you have a really large site of 10,000 posts. ACF works within the confines of WP, using nothing by WP functions and filters. It does nothing that we cannot do on our own using standard WP function. Taking this into account, ACF would need to do a query for every post on the site, loop though them and use the WP function to delete the meta value for each post 1 at a time, and it would need to do this for every field that was deleted. Doing this on more than a few posts would time out the process. This would also need to be done based on field nesting of things like repeaters, making it more complex to do that just calling the delete function. Could this be done faster, yes, by doing queries on the database directly, but this is not something the can be done with existing WP functions and filters.

    Let’s look at this second method of querying the database directly. You could do a query of the meta field values for all rows matching the field key. This would get a list of all of the field names matching that field key. You can then do a delete query to delete anything that matches that field name. The problem here is that you could have 2 fields with the same name. Even using queries directly on the database it would be impossible to discern the difference between these fields. If you have 2 fields of the same name you will delete the data for both of them. This is mainly an issue with the way that WP’s database is created. There isn’t a way to relate 1 row in the meta table with another.

    Anything that changes the way that ACF stores the value would change the way it works. One of the developers goals is also to make it possible for you to use standard WP functions, like get_post_meta() to get values and not to need to depend on get_field() if you want to remove ACF for some reason. Any complex way of storing data make you dependent on ACF rather that it being a big container for the things that can be done with WP without ACF.

    I’m not the developer, and I don’t know if he will ever be able to do these things, but the problem is far more complex than it may appear.

  • Hi John

    I know in general from reading this thread that it is a very complex issue that will probably not be solved any time soon. What I was referring to was the rather “simple” thing you mentioned of deleting all values in specific fields upon deleting the fields themselves in ACF. You made a useful action for this, but there was a the issue of fields with the same names getting deleted in different groups. That made me wonder why this was an issue if the fields had unique identifiers. 🙂

  • @hube2
    Totally understand what you are saying.. but the problem still persists. And we are not talking about something “cosmetic” that can just be overlooked. The amount of leftover unused data in the DB increases and increases and increases forever.

    There are some key words.. unused data and increases forever. That alone should make this a priority.

    I’m pretty sure that EARLY in development this was noticed, begs the question, “Why wasn’t it addressed then?”
    And regardless of the answer to that.. KNOWING this unused data remains and increases, while not being addressed with a fix, rather the whole plugin needs to be re-developed or not.. is mind-boggling!

    Like you said in your comment, “Let say that you have a really large site of 10,000 posts”… Imagine the DB bloat making edits to this over time will create! Imagine this same site being a client of yours, and that client asks you why all of this unused data is just sitting here?

  • Sorry about rehashing a bunch of old crap 🙂

    I can’t really say much when it comes to development of the plugin, only the developer can answer that question and that’s really not going to happen here. Not everyone may be aware of this, but the developer doesn’t look at these forums much. He depends on me and any other volunteers to help other users. Main reason is that with with the number of questions here, if he tried to keep up with it, there would never be any improvements in the plugin.

    For me personally, I don’t see the never ending increase in unused data. I build sites in a dev environment. If I want to test something out I do so in a testing environment before adding it to a site. I almost never delete a field once I’ve created it. The only “excess” data that I see on some sites happens when someone changes an option that triggers conditional fields. When posts/users/terms are deleted, WP handles the deletion of the meta data associated with it. If you do delete a field data will never be save to that field again so that shouldn’t cause any excess unused data. I’m not at all sure where or why others would have problems with all this excess unused data is in the database.

  • +1


    @john

    What if rather than creating a process that loops through every single post trying to cleanup old orphaned meta values, the plugin instead cleaned up values no longer in use when the post was edited/saved?

    This wouldn’t give you the 1 click solution but, it would cause the database to clean and improve itself over time.

    Has any new DB optimization plugin been created since the inception of this question that is capable of cleaning up these meta values? I assume not since the values aren’t orphaned… right?

Viewing 25 posts - 51 through 75 (of 100 total)
1 2 3 4

The topic ‘Flush Unused Custom Fields’ is closed to new replies.