How to delete node revisions

Question

I need to delete all node revisions before upgrading to Drupal 7 to save time in migration. Is there any way to do that?

Answer 1

You can use the following code.

db_query('DELETE FROM {node_revisions} nr WHERE nr.vid NOT IN (SELECT n.vid FROM {node} n)');

You don't want to delete all the revisions: You want to delete all but the current revision.

Executing only that query is going to leave data about deleted revisions in other database tables, as modules that save information about a revisions are not notified a revision is going to be deleted. I am thinking of the CCK module, for example, which saves information about the node fields in its own database tables. The CCK module executes the following code, when a revision is deleted.

/**
 * Implementation of hook_nodeapi 'delete_revision' op.
 *
 * Delete node type fields for a revision.
 */
function content_delete_revision(&$node) {
  _content_field_invoke('delete revision', $node);
  _content_field_invoke_default('delete revision', $node);
  cache_clear_all('content:'. $node->nid .':'. $node->vid, content_cache_tablename());
}

A more complete code would be similar to the following one.

$query = db_query('SELECT nr.nid, nr.vid FROM {node_revisions} nr WHERE nr.vid NOT IN (SELECT n.vid FROM {node} n)');

while ($revision = db_fetch_object($query)) {
  if ($node = node_load($revision->nid, $revision->vid)) {
    node_invoke_nodeapi($node, 'delete revision');
  }
}

db_query('DELETE FROM {node_revisions} nr WHERE nr.vid NOT IN (SELECT n.vid FROM {node} n)');

Looking at the code of node_revision_delete_confirm_submit(), I don't see anything else that needs to be done.

The new code I wrote could take much time to be executed, depending on the number of nodes, and revisions. This means the code should be executed:

1. In an implementation of hook_cron()
2. As a batch operation
3. In a PHP file that bootstraps Drupal, and increases the execution time with @set_time_limit(240)
4. With Drush

Considering that the code is only necessary for the migration, I would avoid the first two options, which would require you to write a module you need just in this very specific case. If you have more sites, and all of them need to execute the same code, option 3 is always possible, and that is what is done from Drupal with cron.php.
About option 4, I would first check Drush doesn't have already commands that could be helpful in this case. I would not reinvent the wheel, when it is not strictly necessary. I would also check how to write Drush Shell Scripts.

Answer 2

We had a website with >500,000 old revisions in their database. There is a nice module for knocking out small numbers of revisions called http://drupal.org/project/revision_deletion. But, to quickly remove a large set we wrote this script.

The general idea is to remove all revisions older than a certain date and then do the same for each CCK table. No matter what date limit you pick the current revision is not deleted.

WARNING: it makes assumptions about your table naming conventions -- use only on a backup

<?php
// Note - this is for Drupal 6.x
function remove_revisions() {

  // Set limit for revision age
  $days_ago = 30;

  // delete all revisions from node_revisions
  $limit = time() - (60*60*24*$days_ago);
  $sql = "DELETE FROM node_revisions WHERE timestamp < $limit AND vid NOT IN (SELECT vid FROM node)";
  db_query($sql);

  $rows = db_affected_rows();
  drupal_set_message("$rows revisions deleted -- node_revisions");

  $table_types = array('content_field_%%', 'content_type_%%');

  // remove orphaned data from CCK content tables
  foreach($table_types as $table_type) {
    $result= db_query("SHOW TABLES LIKE '$table_type'");
    while ($row = db_fetch_array($result)) {
      $table = current($row);
      $sql = "DELETE FROM $table WHERE vid NOT IN (SELECT vid FROM node_revisions)";
      db_query($sql);
      $rows = db_affected_rows();
      drupal_set_message("$rows revisions deleted -- $table");
    }
  }
}
?>

Source: http://fivepaths.com/drupal-revision-removal-and-database-cleanup-by-brute-force

Answer 3

in phpmyadmin

just do :

DELETE FROM node_revisions WHERE node_revisions.vid NOT IN (SELECT node.vid FROM {node} );

Ajay singh rathore. Bangalore

Answer 4

This piece of code takes care of node revisions and all the CCK revisions. It doesn't know about any other possible modules saving revisions but it's fast and you avoid thinking of cron, batch, execution time limit or Drush. It also has a day limit setting as in how many days from today backwards do you not want to remove revisions. You have to put the code inside some module and add a hook_menu. Worked well for me.

http://fivepaths.com/drupal-revision-removal-and-database-cleanup-by-brute-force

Edit Aug 3, 2015: Oops, the page is gone. I can dig this up if someone wants it.