Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

General Questions

General questions about getting started with SilverStripe that don't fit in any of the categories above.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

trim database: remove all history revisions but current


Reply

6 Posts   3148 Views

Avatar
Berteh

24 December 2008 at 5:11am Community Member, 11 Posts

The database of my SilverStripe website gets too big. When I have a look at the mysql tables it turns out SiteTree_versions and PageView are the culprits.

I could remove all content for PageViews, but I'd suggest a nice button in the admin interface saying: "clean up database from old visits ~ +3 months ago", for instance.

I could as well drop SiteTree_versions content manually but this might prevent me from reverting to the current revision with further editing, as pointed out here: http://www.silverstripe.org/archive/show/166007

Could you please give me the mysql code to get rid of all history versions BUT THE MOST RECENT, for every page of the current site tree? Would be awesome... but I just can't figure the mysql command... and besides: linking it to a button in admin interface would be so coool ;o)

Thanks for any input,
Berteh.

Avatar
tobych

19 February 2009 at 4:12pm Community Member, 97 Posts

I imagine you'd be best of learning how to do this using the object-relational layer SS provides rather than a direct MySQL queries. You'll get to understand better what SS is doing with all these version files, I reckon. Have a look at the "Versioned" class in saphire/core/model.

Avatar
weberho

20 February 2009 at 4:07am Community Member, 15 Posts

You could try this script, but be _careful_ and make a database backup before:

DELETE FROM sitetree_versions
WHERE CONCAT_WS('_', RecordID, Version) NOT IN
(SELECT CONCAT_WS('_', id, Version) AS combined_id FROM sitetree);

Avatar
Berteh

2 March 2009 at 6:52am Community Member, 11 Posts

thanks weberho, your sql code made it after quick case correction.

To clean pageview I executed a stupid
DELETE FROM `PageView` WHERE ID < 225745
after having spotted a record with a date that would match the time slot I wanted to keep.

These 2 queries allowed me to save ~25Mb out of a 55Mb database.

Tobych: I might have a look into it someday, thanks for the pointer.

Avatar
Nicolaas

16 May 2012 at 3:26pm Forum Moderator, 213 Posts

has anyone got a fix for this? I am keen to remove pages that are more than six month old or something like that.

Also see:
http://groups.google.com/group/silverstripe-dev/browse_thread/thread/6b66facdc7ccc3f0

http://www.silverstripe.org/customising-the-cms/show/5190

https://github.com/silverstripe-labs/silverstripe-recipes/blob/master/model/tools/SanitiseTasks.php
(this last one seems to do the trick)

Avatar
Jellygnite

19 June 2013 at 1:38pm Community Member, 11 Posts

This is what I used - hope someone else finds this useful.

https://github.com/axllent/silverstripe-version-truncator