Remove old database data

Is there a way to safely remove old data, say over a year odd, from the database?  I have a lot of data from old plugins and members who have been removed from my site.  The data is still in the metadata and metastrings tables and I am sure other places as well.

  • There are some plugins:

    Deleted User Content - Prevent accidental data loss when deleting a user

    URL fixer - This tool can be used to find and fix deprecated internal URLs from Elgg metastrings and object descriptions

    + Garbage Collector bundled plugin too

  • garbage collector will delete orphaned metadata and metastrings, but if they're still attached to content it won't help.  I don't think there's an existing plugin for mass-deleting old content.  I could be done though.

  • @Matt, how can it be done? 

    Would something like this work without deleting the wrong data?

    DELETE
      FROM metadata
     WHERE time_created < DATE_SUB(CURDATE(), INTERVAL 365 DAY) 

  • No, you can't delete it directly from the database unless you really know what you're doing.  The easiest way is to use a batch function

    $options = array(

        'type' => 'object',

        'subtype' => whatever you're cleaning out,

        'time_created_upper' => strtotime('-1 year'),

        'limit' => false

    );

    $batch = new \ElggBatch('elgg_get_entities', $options, null, 25, false);


    foreach ($batch as $e) {

        $e->delete();

    }

  • Of course you should make sure to back things up before you start testing out deletion scripts right? :)

  • What is the motivation for this cleanup?

  • Sorry for the delay in replying, been to busy with other things.

    #Matt, yes always backup before doing anything with the database, thanks for the reminder though.

    #Evan, the reason for the cleanup.  I was running 1.8.11 when I had a crash.  I tried many ways to revive the site after the crash and nothing worked.  I then went ahead and upgraded to 1.9 to 1.11 one step at a time.  I tried restoring the old database but none of the old users could use the site.  So, I just left it as is and started over.  But all the old data is still on the database.  That is slowing down the site.  That is why I am trying to cleanup the old data.