CODEWITHSUNDEEP
sqlmediawiki
ozz
ozz

Reputation: 183

How to delete old MediaWiki revisions when deleteOldRevisions run out of memory

is there a possibilty to delete revisions , when the maintenance skript deleteOldRevisions runs out of memory (tried to allocate more than 2 GB under 32bit). I not even get the list of the ids to delete it step by step.

Maybe directly using SQL?

I tried/read already https://www.mediawiki.org/wiki/Manual:Reduce_size_of_the_database and also Use SQL to delete old MediaWiki revisions without shell access?

Upvotes: 2

Views: 1030

Answers (1)

Ilmari Karonen
Ilmari Karonen

Reputation: 50328

Mmm, yeah... after taking a peek at that script, I'm not surprised it fails. It really should be fixed to iterate over the pages instead of just building a huge list in memory. I suppose the only reason it hasn't been is that, for wikis like Wikipedia, deleting revisions isn't really something one normally ever does.

Anyway, besides fixing the script, I see a bunch of possible workarounds:

  • The deleteOldRevisions.php script can take a list of page IDs. You could just try running for each page on your wiki, one at a time. You could even write a simple shell script to run it in a loop, starting at page ID 1 and counting up to whatever the current maximum page ID on your wiki is.

  • Alternatively, you could make a dump of your wiki containing only the lastest revision of each page, then delete all pages on your wiki (i.e. everything in the page, revision and text tables) and import them back from the dump.

  • In principle, if you know what you're doing, you can also delete old revisions directly using SQL.

    The first things you always want to do before messing with the database are to set your wiki to read-only mode and make a full backup. If you haven't done this before, it's also a good idea to practice restoring the backup onto a local test wiki before doing anything irrevocable to your real wiki.

    Then, to delete all but the latest revision of each page, run the following SQL command:

    DELETE FROM revision WHERE NOT EXISTS
  ( SELECT * FROM page WHERE page_id = rev_page AND page_latest = rev_id )

    Note that the command above will only delete the old revision metadata, not the actual text of those revisions. The easiest way to get rid of the old text records is to run the purgeOldText.php maintenance script, although you should be able to do it using SQL too, something like:

    DELETE FROM text WHERE NOT EXISTS
  ( SELECT * FROM revision WHERE rev_text_id = old_id )

    Finally, if everything went well, I recommend running the rebuildall.php maintenance script to fix things like recent changes that will otherwise point to deleted revisions. Then make sure that everything looks as it should and turn read-only mode off again.

  • Finally, if you're doing this to try and save space, consider compressing your old revisions instead of just outright deleting them. This will save a significant amount of space while still keeping all the revisions available on your wiki.

Upvotes: 4

Related Questions