taxonomy_update_7011 runs out of memory

To help with memory consumption, it should probably be rewritten as a count() query then loop over the results using db_query_range() to page through a thousand rows at a time.

Ugh, yeah. That's a lot of unpublished nodes. Hopefully most sites don't have anywhere near that many.

We could probably change the update function to run as a batch (with $sandbox), and also the initial query could be made to join against the {taxonomy_index} table (so it only pulls node IDs that actually need deleting; for many Drupal 7 sites this will be 0 anyway).

I added this to the "known issues" section of the release notes and release announcement for now.

@mikeytown2 - did you run the update via update.php or drush updb? Do you have a memory limit on drush?

For the record, and to save others from having to look the function up:

/**
 * Drop unpublished nodes from the index.
 */
function taxonomy_update_7011() {
  $nids = db_query('SELECT nid from {node} WHERE status = :status', array(':status' => NODE_NOT_PUBLISHED))->fetchCol();
  if (!empty($nids)) {
    db_delete('taxonomy_index')
      ->condition('nid', $nids)
      ->execute();
  }
}

So converting this to the regular ole' SQL syntax that MySQL would ultimately get, using @mikeytown2's 173,721 nodes, you end up with something like:

DELETE FROM taxonomy_index WHERE nid IN (###, ...)

And just keep adding to that ### until you have 173,721 values in your IN statement.

Two areas I can see that this could be greatly improved upon. The first is using a batch, as @David_Rothstein suggested.

The second is eliminating any nodes that aren't in taxonomy_index on the initial query:

SELECT n.nid FROM {node} n LEFT JOIN {taxonomy_index} t ON n.nid=t.nid WHERE n.status=0 AND t.nid IS NULL;

That would really help out sites with a lot of unpublished nodes that also don't have any taxonomy assigned.

The other option is a sub query, but I'm not sure about support in other databases. This should perform better though (If Mikey has a copy of that database around and could test, that would be great):

DELETE FROM taxonomy_index WHERE nid IN (select nid from node where status=0)

HAHA you're right. That's what I get for typing without the proper amounts of coffee in me.

We would want nodes where an entry exists on taxonomy_index. No need for the null check either.

SELECT n.nid
FROM node AS n
INNER JOIN taxonomy_index AS t
ON n.nid=t.nid
WHERE n.status=0;

That way if there isn't a corresponding taxonomy_index then it won't even get returned.

A quick fix that only execute 50 nids in each execute, via GIT 7.x:

/**
 * Drop unpublished nodes from the index.
 */
function taxonomy_update_7011() {
  $nids = db_query('SELECT nid from {node} WHERE status = :status', array(
    ':status' => NODE_NOT_PUBLISHED
  ))->fetchCol();

  // Prevent running out of memory, by execute 50 nids each time.
  // @see https://drupal.org/node/2174551
  while (!empty($nids)) {
    $slice = array_slice($nids, 0, 50);
    $nids = array_slice($nids, 50);
    db_delete('taxonomy_index')
      ->condition('nid', $slice)
      ->execute();
  }
}

I am questioning on performance of delete query: is it really such heavy that require for a background batch processing?

IMHO delete query shouldn't be such memory expensive, just because we put everything into a single IN () statement, and so result as a huge result matrix (i.e. running out of memory).

Shall #13 enough for fixing this issue?

13: drupal-fix_taxonomy_update_7011-2174551-13.patch queued for re-testing.

Rerolled to fix a few things; this should now be working correctly, I believe.

Cross-posted there, but I would say, since we don't know how many nodes the site has, batch processing is still a good idea.

If I understand mikeytown2's comment above correctly, this still takes 30 seconds to run in his situation (or rather, it would if all the unpublished nodes came from an upgraded Drupal 6 site with this issue), which is long enough to warrant a batch.

hii

Iwant to upgrade my drupal 6.30 to the updated version of drupal 7 . Please if somebody guide me through this migration.

prakharcse, you can learn more about upgrading Drupal core here.

Also ran into this issue (on a none d6->d7 site) with a lot of unpublished nodes(751,357). Patch here does the trick for me and prevents a nasty fatal error.

#20 is working for me.

Committed to 7.x - thanks! http://drupalcode.org/project/drupal.git/commit/5be1de3

I'll also update the 7.26 release notes and release announcement to mention this is now fixed.

20: drupal-2174551-18-taxonomy-update-7011-fix.patch queued for re-testing.

This is already committed

Drupal 7.27 was a security release, so this is now scheduled to go out in Drupal 7.28 instead.