I have a site that was upgraded from Drupal 6 to Drupal 7. This site is quite large and has over 22,000 nodes.

When we try to run a bulk update of aliases, we get the following error:

An AJAX HTTP error occurred. HTTP Result Code: 500 Debugging information follows. Path: /batch?id=1000170&op=do StatusText: Internal Server Error ResponseText: 500 Internal Server Error Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request.

In addition to the previous error every time we found with phpMyAdmin the following SQL query hanging on the server for long time:

SELECT COUNT( * ) AS expression
SELECT 1 AS expression
FROM users u

...and this other query:

Copying to tmp table
SELECT n.nid AS nid
node n
LEFT OUTER JOIN url_alias ua ON CONCAT('node/', n.nid) = ua.s

It seems like these queries take too long and AJAX gives up after a while.

When running the bulk update for Taxonomy terms only (about 250 terms), it works very well.

Thanks for this great module. One of my favorites.

#13 pathauto-bulk-update-1073266.patch2.15 KBgaele
FAILED: [[SimpleTest]]: [MySQL] 303 pass(es), 9 fail(s), and 2 exception(s). View
Members fund testing for the Drupal project. Drupal Association Learn more


Dave Reid’s picture

Status: Active » Closed (works as designed)

There's not anything we can really do to make that query better. If you've got any ideas, let us know, otherwise this is kinda how this has to work.

Corwin’s picture

SELECT n.nid AS nid FROM node n LEFT OUTER JOIN url_alias ua ON CONCAT('node/', n.nid) = ua.source;
28013 rows in set (4 min 58.56 sec)

SELECT nid FROM node;
28008 rows in set (0.02 sec)

Why are we doing a left join when we are only taking data from the left table?

$ diff /tmp/2011-10-11_LEFT_OUTER_JOIN.txt /tmp/2011-10-11_SELECT_nid.txt
< 1
< 1
< 1
< 1
< 3

The only difference on my site between the queries is:
1. The second query is 14,928 times faster
2. nid 1 comes up once instead of 5, and nid 3 comes up once instead of twice. Why would we even want the same nid coming up multiple times? The query only returns the nid.

Dave Reid’s picture

You're missing the important part of needing WHERE ua.alias IS NULL which was missing from the original report. And I'm not sure why this query would be performing an outer join. It's now how the query is coded and it's not how its executed on my version of MySQL.

micropony’s picture

I've hit the same wall (and same error) with bulk updating a site with ~9,000 nodes that need some nice new aliases.

I've tried accomplishing the task with drush, but hit another snag with that as well (looks like pathauto function names have shuffled a little). Described it here.

gaele’s picture

Title: An AJAX HTTP error occurred while builk updating URL aliases » An AJAX HTTP error occurred while bulk updating URL aliases
Category: bug » support
Status: Closed (works as designed) » Active

Same problem here. No time-out, but the bulk update is extremely slow.

70,000 nodes
MySQL 5.0.86
And an outer join:

SELECT n.nid AS nid
FROM node n
LEFT OUTER JOIN url_alias ua ON CONCAT( 'node/', n.nid ) = ua.source
ua.source IS NULL
n.nid > '0'
phreadom’s picture

Same here... same AJAX error after running through around 3,000 of our 50,000 users. :(

It's also running incredibly slow, but I'll post that on the other ticket.

gaele’s picture

I've changed the query, and this seems to do the trick:

  • Don't do a CONCAT( 'node/', n.nid ). Instead cut the 'node/' form ua.source using SUBSTRING. This will give you the nid.
  • Create a temporary table with these nids, so they can be indexed.
function node_pathauto_bulk_update_batch_process(&$context) {
  if (!isset($context['sandbox']['current'])) {
    $context['sandbox']['count'] = 0;
    $context['sandbox']['current'] = 0;

  // Create a temporary table with an index on nid.
  $result = db_query('CREATE TEMPORARY TABLE tmp_alias (nid int PRIMARY KEY)');
  // Cut the nid from the source field.
  $result = db_query("INSERT INTO tmp_alias (nid) SELECT SUBSTRING(source, 6) FROM url_alias WHERE (SUBSTRING(source, 1, 5) ='node/')");
  // The next four lines are adjusted to use the temporary table.
  $query = db_select('node', 'n');
  $query->leftJoin('tmp_alias', 'ta', "n.nid = ta.nid");
  $query->addField('n', 'nid');
  $query->condition('n.nid', $context['sandbox']['current'], '>');
  $query->addMetaData('entity', 'node');

  // Get the total amount of items to process.
  if (!isset($context['sandbox']['total'])) {
    $context['sandbox']['total'] = $query->countQuery()->execute()->fetchField();

    // If there are no nodes to update, the stop immediately.
    if (!$context['sandbox']['total']) {
      $context['finished'] = 1;

  $query->range(0, 25);
  $nids = $query->execute()->fetchCol();

  pathauto_node_update_alias_multiple($nids, 'bulkupdate');

  // Drop the temporary table so it can be recreated in the next run.
  $result = db_query('DROP TABLE tmp_alias');

  $context['sandbox']['count'] += count($nids);
  $context['sandbox']['current'] = max($nids);
  $context['message'] = t('Updated alias for node @nid.', array('@nid' => end($nids)));

  if ($context['sandbox']['count'] != $context['sandbox']['total']) {
    $context['finished'] = $context['sandbox']['count'] / $context['sandbox']['total'];

I didn't find out how to implement this using the database API.

gaele’s picture

Wow! While typing this the bulk update finished: "Generated 59841 URL aliases."
In less than 30 minutes!

I'll check the results.

phreadom’s picture

Do I need to delete any existing aliases first? I have about half the aliases generated for content, users, etc... and when I try to finish aliasing them with this patch, I get the exact same behavior as before... incredibly high load on the CPU and very slow progress... it would take me 3 or 4 days at this speed to alias just my articles. A few more days for my users, etc. :(

gaele’s picture

Hi phreadom, the adjusted version of node_pathauto_bulk_update_batch_process() worked for me. I didn't delete the existing aliases.

phreadom’s picture

I commented out function node_pathauto_bulk_update_batch_process(&$context) in pathauto.pathauto.inc, and pasted in your new version, and tried running the bulk update for content, and later for users, and both behaved the same way as before, running high load for hours. :(

Am I doing something wrong? I'd really love to get this working.

phreadom’s picture

I cleared my existing content aliases, and deleted any leftover batches from my batches table and then ran the content bulk alias update again and it's working!

It's gone through around 7,000 already in ~20 minutes and there's almost no load on the CPU either! Fantastic! Thank you!


Status message
Generated 16555 URL aliases.

4 hours total, but that's a hell of a lot better than the 4 days it would have taken previously!

Now I'm going to try porting the same changes to the other functions for bulk updating my user aliases, as I have around 50,000 users, so the same problems definitely apply there as well. I'll update with the code if I get it to work.

gaele’s picture

Title: An AJAX HTTP error occurred while bulk updating URL aliases » Queries take too long while bulk updating huge amount of URL aliases
Category: support » feature
2.15 KB
FAILED: [[SimpleTest]]: [MySQL] 303 pass(es), 9 fail(s), and 2 exception(s). View

Let's see what Dave thinks of this (#1).

I've put the temporary table creation in a separate, generic function, so it can be used for nodes, users, terms etc.

@phreadom: updating your 50,000 users should be fairly easy using this patch.

gaele’s picture

Status: Active » Needs review

Status: Needs review » Needs work

The last submitted patch, pathauto-bulk-update-1073266.patch, failed testing.

phreadom’s picture

@gaele: "Status message Generated 54934 URL aliases." took a few hours, but it made it through all of the user aliases as well. :D Thanks again!

Since it "works" for me at the moment, since I patched the other functions I needed as well, I'll just leave it as is for the moment and hope the maintainer adds these changes into the module itself in some form so that I won't have to maintain a set of custom patches for it.

gaele’s picture

Status: Needs work » Needs review

I just ran the bulk update again. Generated 70970 URL aliases in 34 minutes.

gaele’s picture

Status: Needs review » Needs work
gaele’s picture

Sorry, this is my first real encounter with the D7 database API. I couldn't find out how to create a temporary table that has an index on the (n)id in a different way.

sarhugo’s picture

Why not adding the fields entity_type and entity_id to the url_alias table?

bago’s picture

Are you sure this is not the same of http://drupal.org/node/1415930 ?
Are you on mysql 5.0 ? I experienced the same issue you describe here, and it worked very fast after I applied http://drupal.org/node/1415930.

hop’s picture

Version: 7.x-1.x-dev » 7.x-1.2
Assigned: Unassigned » hop
Category: feature » bug

When I run Bulk Update, I see error message: "An AJAX HTTP error occurred. HTTP Result Code: 500 Debugging information follows. Path: /batch?id=306&op=do StatusText: Internal Server Error ResponseText:".

When I generate URL aliases with module Views Bulk Operations (VBO) it was all OK :)

I recommend patch http://drupal.org/node/1415930#comment-5511014

nfollmer’s picture

+1 for the patch in #22... I have 13,000+ users and over 29,000 nodes that needed updated and that patch made it happen!