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

  • I apologise: My first answer was incomplete. You should let other modules know you are deleting node revisions, or there would be revision data left on database tables handled by third-party modules. During the migration, it is possible the third-party modules remove outdated information, but I doubt they would check the existing revisions to remove not necessary data; in a Drupal site with many nodes, that would take much time.
    – apaderno
    Commented Aug 22, 2012 at 21:08

4 Answers 4


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.

  • Thanks Kiamlaluno...yeah I want to keep the current revision.
    – user8012
    Commented Aug 20, 2012 at 1:27
  • 1
    Does anybody know if this will prevent the revisions of the field values from transferring over as well? I'm guessing it would, but just curious. Commented Aug 22, 2012 at 18:54
  • @JohnathanElmore I have updated my answer.
    – apaderno
    Commented Aug 22, 2012 at 20:46
  • :) that's awesome. I'm thinking that would be a lot of load on the server, but some ini_set('memory_limit','128M'); and/or ini_set('max_execution_time', 1000); would mitigate those kinds of issues. Commented Aug 22, 2012 at 21:35
  • 3
    The proper subquery is (SELECT n.vid FROM {node} n). Otherwise, you'll get a SQL error about a bad column name.
    – mpdonadio
    Commented Aug 23, 2012 at 0:03

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

// 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)";

  $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)";
      $rows = db_affected_rows();
      drupal_set_message("$rows revisions deleted -- $table");

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


in phpmyadmin

just do :

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

Ajay singh rathore. Bangalore


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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.