No XMLSitemap_node table in database

Probably some extra-picky setting in your database is generating the error. What MySQL version are you using?

Hi,

I have the same problem. I use Drupal 5 and have MySQL 3.23.58 running, PHP version , PHP 4.4.2 .

Hereunder the errors

* user warning: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead query: CREATE TABLE xmlsitemap_node ( nid int, pid int, last_changed int(11), previously_changed int(11), last_comment int(11), previous_comment int(11), priority_override float, PRIMARY KEY (nid) ) /*!40100 DEFAULT CHARACTER SET utf8 */; in /var/www/html/Drupal_5_5/includes/database.mysql.inc on line 172.

* user warning: You have an error in your SQL syntax near 'xn, url_alias ua SET xn.pid = ua.pid WHERE xn.pid IS NULL AND ua.src =' at line 2 query: UPDATE xmlsitemap_node xn, url_alias ua SET xn.pid = ua.pid WHERE xn.pid IS NULL AND ua.src = CONCAT('node/', xn.nid) in /var/www/html/Drupal_5_5/includes/database.mysql.inc on line 172.

* user warning: Table 'drupal_5_5.xmlsitemap_node' doesn't exist query: INSERT INTO xmlsitemap_node (nid, pid, last_changed, previously_changed, last_comment, previous_comment, priority_override) SELECT nid, pid, last_changed, previously_changed, last_comment, previous_comment, priority_override FROM gsitemap in /var/www/html/Drupal_5_5/includes/database.mysql.inc on line 172.

* user warning: You have an error in your SQL syntax near 'xn INNER JOIN node n ON xn.nid = n.nid LEFT JOIN node_comment_statistics s ON s.' at line 2 query: UPDATE xmlsitemap_node xn INNER JOIN node n ON xn.nid = n.nid LEFT JOIN node_comment_statistics s ON s.nid = xn.nid SET xn.previously_changed = xn.last_changed, xn.last_changed = n.changed, xn.last_comment = s.last_comment_timestamp WHERE xn.nid = n.nid AND (xn.last_changed <> n.changed OR xn.last_comment <> s.last_comment_timestamp) in /var/www/html/Drupal_5_5/includes/database.mysql.inc on line 172.
* 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/html/Drupal_5_5/includes/database.mysql.inc on line 172.

3.23.58

Got a similar error not sure this is the same problem, I had to disable the node option:

Table 'db225858978.motivatedsellers_xmlsitemap_node' doesn't exist query: INSERT INTO motivatedsellers_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 motivatedsellers_node n LEFT JOIN motivatedsellers_node_comment_statistics s ON s.nid = n.nid LEFT OUTER JOIN motivatedsellers_comments c ON c.nid = n.nid AND c.timestamp < s.last_comment_timestamp LEFT JOIN motivatedsellers_xmlsitemap_node xn ON xn.nid = n.nid LEFT JOIN motivatedsellers_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 /homepages/25/d135573405/htdocs/drupal/includes/database.mysql.inc on line 172.

I am using Drupal 5.7 latest version of xml sitemap (installed 1 month ago) with MySQL 5.0.51

Same error as up above:

Table 'database.xmlsitemap_node' doesn't exist query: SELECT n.nid, n.type, n.promote, n.changed, xn.previously_changed, xn.priority_override, ua.dst AS alias FROM node n LEFT JOIN xmlsitemap_node xn ON xn.nid = n.nid LEFT JOIN url_alias ua ON ua.pid = xn.pid WHERE n.status > 0 AND (n.type NOT IN ('') AND xn.priority_override IS NULL OR xn.priority_override >= 0) AND n.nid <> 2 in mysite.com/includes/database.mysql.inc on line 172.

Drupal 5.7, installed sitemap today, php 4.4.2, and mysql 3.23.49

I've never built a patch before, but what I've discovered was if you go into xmlsitemap/xmlsitemap_node and edit xmlsitemap_node.install - change the following:

    case 'mysql':
    case 'mysqli':
      db_query("CREATE TABLE {xmlsitemap_node} (
        nid int,
        pid int,
        last_changed int(11),

to:

    case 'mysql':
    case 'mysqli':
      db_query("CREATE TABLE {xmlsitemap_node} (
        nid int NOT NULL,
        pid int,
        last_changed int(11),

Just add the NOT NULL after nid int to resolve the issue. If this is not the correct way to fix the problem, please let us know.

Seems to be an issue with old versions of MySQL.

OK, it appears that my fix did NOT fix the problem. The table xmlsitemap_node is created, but existing nodes are not placed into the database. Any nodes created after the fact are added and work correctly, but something apparently needs fixed in the insert statement. I'm working on it.

I have no idea if anyone else will run into this, but I'm going to guess the problem is with the insert statement in the xmlsitemap_node.install. In the create table, as I mentioned above, it doesn't allow a primary key to be NULL, so to create the table, you have to make nid NOT NULL. Well, a few lines down when the table is being populated (I'm pretty sure that's what function _xmlsitemap_node_insert_query() does) there is a portion of $query that says, "WHERE xn.nid IS NULL".

I just got really frustrated with the whole process and didn't want to guess anymore, so since I didn't have any comments to worry about in my database, I just went to shell and inserted all nodes, populating nid from node, pid from url_alias, and last_changed from node, leaving everything else NULL. If you're interested in the command I used, it was:

INSERT INTO xmlsitemap_node (nid, pid, last_changed) SELECT nid, ua.pid, changed FROM node LEFT JOIN url_alias ua ON ua.src = CONCAT('node/', nid);

Everything was populated and my sitemap works great, and my weighting updates and all is right with the world (as far as I can tell).

Please let me know if something I've done is going to create problems for me down the road. Will there be a problem with xmlsitemap_node.nid being set to NOT NULL? So far all looks fine and adding nodes adds to the table correctly, so I'm assuming my personal issue is resolved unless someone lets me know otherwise. I agree with you, Darren Oh, this appears to be a problem with old versions of MySQL. I can't wait for our company to update things. :) Do you know if MySQL versions under 4 allow for NULL primary keys? It appears from the code that it's acceptable in more recent MySQL databases. Also, when I was trying to manually run the entire sql insert statement from shell (the one found in _xmlsitemap_node_insert_query()) this is what happened:

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) GROUP BY n.nid, ua.pid, n.changed, s.last_comment_timestamp;

ERROR 1093: INSERT TABLE 'xmlsitemap_node' isn't allowed in FROM table list

You'll notice I left out the part about WHERE xn.nid IS NULL. Hopefully this helps someone debug the problem better than I could.

You won't have any problems down the road. There will only be a problem for those whose servers are unable to insert all the nodes in one query. The query was written so that it could be run multiple times until it was complete.

I just realized that there will be a problem for those who disable the module temporarily and re-enable it. Both problems are MySQL bugs which have been fixed:

4.0.13

PRIMARY KEY now implies NOT NULL.

4.0.14

Enabled INSERT from SELECT when the table into which the records are inserted is also a table listed in the SELECT. Prior to that, a temporary table must be used.

So for now XML Sitemap is incompatible with MySQL versions below 4.0.14. See CVS commit 115962. If it’s important to be compatible with older versions and someone provides a patch, please re-open this issue.