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   3161 Views

Avatar
Berteh

Community Member, 11 Posts

24 December 2008 at 5:11am

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

Community Member, 97 Posts

19 February 2009 at 4:12pm

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

Community Member, 15 Posts

20 February 2009 at 4:07am

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

Community Member, 11 Posts

2 March 2009 at 6:52am

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

Forum Moderator, 213 Posts

16 May 2012 at 3:26pm

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

Community Member, 11 Posts

19 June 2013 at 1:38pm

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

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