xmlsitemap_node causes database timeouts

XML Sitemap doesn't execute the INSERT INTO watchdog query like shown in the error message; actually, it doesn't even call watchdog() passing such arguments.

It seems that the reported error is caused by another module, and by a coincidence the error happened when Drupal was executing a XML Sitemap query.

I noticed the query that is being executed, but I also noticed that the second query (leaving out the part starting with INSERT INTO watchdog) it's not a query executed from XML Sitemap, and it's executed in a different time that the first query.

It could be another module causing the problem, and you see the last queries executed before the database engine shuts down.

This bug related not to xmlsitemap but to weak design of url_alias table.
The workaround for this bug is change a query variable in function xmlsitemap_node_xmlsitemap_links() to:

  $query = 
    "select a.*, $coalesce(ua.dst) AS alias 
    from (
    SELECT $columns FROM {node} n
    LEFT JOIN {xmlsitemap_node} xn ON n.nid = xn.nid
    $left_join
    WHERE n.status > 0
      AND n.type NOT IN (". db_placeholders($excludes, 'varchar') .")
      AND (xn.priority_override = -2 OR xn.priority_override >= 0)
      AND n.nid <> %d
    GROUP BY $columns
    )a, {url_alias} ua
    WHERE ua.src = CONCAT( 'node/', a.nid )";

this fix "MySQL server has gone away query" on my site with 7k aliases

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	 	ALL 	NULL 	NULL 	NULL 	NULL 	6549 	 
1 	PRIMARY 	ua 	ALL 	src 	NULL 	NULL 	NULL 	6548 	Range checked for each record (index map: 0x4)
2 	DERIVED 	xn 	ALL 	PRIMARY 	NULL 	NULL 	NULL 	6549 	Using where; Using temporary; Using filesort
2 	DERIVED 	n 	eq_ref 	PRIMARY,node_status_type,node_type 	PRIMARY 	4 	xxxx.xn.nid 	1 	Using where
2 	DERIVED 	s 	eq_ref 	PRIMARY 	PRIMARY 	4 	xxxx.n.nid 	1 	 

old explain is:

1  	SIMPLE  	xn  	ALL  	PRIMARY  	NULL  	NULL  	NULL  	6549  	Using where; Using temporary; Using filesort
1 	SIMPLE 	n 	eq_ref 	PRIMARY,node_status_type,node_type 	PRIMARY 	4 	xxxx.xn.nid 	1 	Using where
1 	SIMPLE 	s 	eq_ref 	PRIMARY 	PRIMARY 	4 	xxxx.n.nid 	1 	 
1 	SIMPLE 	ua 	ALL 	src 	NULL 	NULL 	NULL 	6548 	 

origin sample sql:
EXPLAIN SELECT n.nid, n.type, n.promote, xn.changed, xn.previously_changed, xn.priority_override, s.comment_count, s.last_comment_timestamp, xn.previous_comment, COALESCE(ua.dst) AS alias

FROM node n
LEFT JOIN xmlsitemap_node xn ON n.nid = xn.nid
LEFT JOIN node_comment_statistics s ON n.nid = s.nid
LEFT JOIN url_alias ua ON ua.src = CONCAT('node/', n.nid)

WHERE n.status > 0 AND n.type NOT IN (". .") AND (xn.priority_override = -2 OR xn.priority_override >= 0) AND
n.nid <> 0
GROUP BY n.nid, n.type, n.promote, xn.changed, xn.previously_changed, xn.priority_override, s.comment_count, s.last_comment_timestamp, xn.previous_comment

patched sample sql:
EXPLAIN SELECT a . * , COALESCE( ua.dst ) AS alias
FROM (

SELECT n.nid, n.type, n.promote, xn.changed, xn.previously_changed, xn.priority_override, s.comment_count, s.last_comment_timestamp, xn.previous_comment
FROM node n
LEFT JOIN xmlsitemap_node xn ON n.nid = xn.nid
LEFT JOIN node_comment_statistics s ON n.nid = s.nid
WHERE n.status >0
AND n.type NOT
IN (
". ."
)
AND (
xn.priority_override = -2
OR xn.priority_override >=0
)
AND n.nid <>0
GROUP BY n.nid, n.type, n.promote, xn.changed, xn.previously_changed, xn.priority_override, s.comment_count, s.last_comment_timestamp, xn.previous_comment
)a, url_alias ua
WHERE ua.src = CONCAT( 'node/', a.nid )