SQL error when node has multiple aliases

Run this query and report your result:

SELECT n.nid, ua.pid, n.changed, s.last_comment_timestamp, MAX(c.timestamp) FROM node n
LEFT JOIN node_comment_statistics s ON s.nid = n.nid
LEFT OUTER JOIN comments c ON c.nid = n.nid AND c.timestamp < s.last_comment_timestamp
LEFT JOIN xmlsitemap_node xn ON xn.nid = n.nid
LEFT JOIN url_alias ua ON ua.src = 'node/' || n.nid
WHERE xn.nid IS NULL
GROUP BY n.nid, ua.pid, n.changed, s.last_comment_timestamp
HAVING COUNT(n.nid) > 1

Now let's see if we can find out why that node is being duplicated. Try this query:

SELECT n.nid, ua.pid, n.changed, s.last_comment_timestamp, MAX(c.timestamp) FROM node n
LEFT JOIN node_comment_statistics s ON s.nid = n.nid
LEFT OUTER JOIN comments c ON c.nid = n.nid AND c.timestamp < s.last_comment_timestamp
LEFT JOIN xmlsitemap_node xn ON xn.nid = n.nid
LEFT JOIN url_alias ua ON ua.src = 'node/' || n.nid
WHERE xn.nid IS NULL AND n.nid = 799
GROUP BY n.nid, ua.pid, n.changed, s.last_comment_timestamp

It didn't show any duplicate entries. I've seen a similar unexplained error in issue 192928.

Testing is needed to find a query that works. Contact me directly if you would like help with this.

Posting here is too slow. I can use PHP code to run queries from within Drupal, or we can chat and you can run the queries. Either way, it would be best for you to contact me directly.

I was having the same problem with Drupal 5.3 and mySQL. The problem is that the query does not account for multilpe rows in the url_alias with the same value in the src field. I did not fix the query (davidwhthomas and project administrators can do this) but I did find a work around for my scenario. In my case, I had multilpe rows in the url_alias with the same value in the src field because I have the pathauto module installed and I entered a value in the "URL path settings" form field on the create content (aka node) that is different from what pathauto would generate. When I did this two rows were inserted into the url_alias table with the same src field value, one created automatically by pathauto and one for the value I entered in the "URL path settings" form field. To work around this I just updated my nodes and cleared the "URL path settings" from each node. Then I used the link created by pathauto when reference this content throughout my site.

I an also having problems with the xml site map module;

When I run Cron i get the following error

user warning: INSERT TABLE 'xmlsitemap_node' isn't allowed in FROM table list query: INSERT INTO xmlsitemap_node (nid, pid, last_changed, last_comment, previous_comment) SELECT n.nid, ua.pid, n.changed, s.last_comment_timestamp, MAX(c.timestamp) FROM node n LEFT JOIN node_comment_statistics s ON s.nid = n.nid LEFT OUTER JOIN comments c ON c.nid = n.nid AND c.timestamp < s.last_comment_timestamp LEFT JOIN xmlsitemap_node xn ON xn.nid = n.nid LEFT JOIN url_alias ua ON ua.src = CONCAT('node/', n.nid) WHERE xn.nid IS NULL GROUP BY n.nid, ua.pid, n.changed, s.last_comment_timestamp in /var/www/vhosts/thehaguecentral.com/httpdocs/includes/database.mysql.inc on line 172.

I am running the latest drupal the dec. 18th xmlsitemap dev release and I have path auto and clean urls installed but I do not have clean urls in my sitemap

http://thehaguecentral.com/sitemap.xml

Can anyone help me?

I've attached a patch which seems to fix the issue for me. I'm using MySQL. This is against stable.

Of course, now I realize it's the same as the query above for pgsql :P

I haven't touched the pgsql code as I have no way to test it.

Thanks!
--Andrew

PostgreSQL will not select a single value from a group of values. Thus, when results are grouped, we must group by each column from which we wish to select a single value. If we do not wish to group by ua.pid, we must use an aggregate function to retrieve a value from each group of results.

MySQL, on the other hand, will select a single value from a group of results if asked to do so. However, the value it selects is random and unpredictable, so it's poor design to rely on this MySQL quirk.

We just need to decide on the right aggregate function:

MySQL 5.0 Reference Manual
PostgreSQL 8.2: Aggregate Functions

Ideally, we would get the same result as drupal_lookup_path(), which simply returns the first result.

Ideally, we would get the same result as drupal_lookup_path(), which simply returns the first result.

Just to clarify, was that tested on a node with multiple URL aliases?

Try this: SELECT * FROM url_alias GROUP BY src HAVING count(pid) > 1

If that doesn't show duplicates, we haven't identified the cause of this bug.

Sorry for giving you a bad query. Now the the question is, does

SELECT dst FROM {url_alias} WHERE src = 'node/1120' LIMIT 1

produce A1GP-competition or info/1120/competitions?

Fixed in CVS commit 94686. This whole issue will become irrelevant when issue 198173 is fixed.