When updating the modules database tables through the tools page, Drupal output the following error messages:

  • user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE xn.nid IN (SELECT xnn.nid FROM dr6__xmlsitemap_node xnn ' at line 2 query: DELETE FROM dr6__xmlsitemap_node xn WHERE xn.nid IN (SELECT xnn.nid FROM dr6__xmlsitemap_node xnn LEFT JOIN dr6__node n ON n.nid = xnn.nid WHERE n.nid IS NULL) in Applications/MAMP/htdocs/dr6/sites/all/modules/xmlsitemap/xmlsitemap_node/xmlsitemap_node.module on line 420.
  • user warning: Unknown column 'xn.last_changed' in 'where clause' query: SELECT COUNT(n.nid) FROM dr6__node n INNER JOIN dr6__xmlsitemap_node xn ON xn.nid = n.nid WHERE n.changed > xn.last_changed in /Applications/MAMP/htdocs/dr6/sites/all/modules/xmlsitemap/xmlsitemap_node/xmlsitemap_node.module on line 435.

Comments

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented
Status: Active » Fixed

The errors were caused from the use of a sub-query in the DELETE operation, and from an inconsistent use of the name of a database field.

I have fixed this in CVS.

jolaa’s picture

jolaa commented

i have the same error on line 420.
could you tell me how did you fix it :] i don't understand what you've done...

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented

Simply wait until Drupal creates the new tarball, and you will get the fixed code.
For development snapshots, Drupal creates a tarball archive everyday at 12:00 AM, and 12:00 PM GMT (if the code has been modified).

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented

The error was the WHERE xn.nid in (...).
I simply split the query in two: the first part looks for the next nid to delete, and the second one deletes the row with that value. The delete query becomes then DELETE FROM {xmlsitemap_node} WHERE nid = %d.

jolaa’s picture

jolaa commented

now (after upgrading XML sitemap) I am getting different error:

when using xml tool:
user warning: Unknown column 'xn.changed' in 'where clause' query: SELECT COUNT(n.nid) FROM node n INNER JOIN xmlsitemap_node xn ON xn.nid = n.nid WHERE n.changed > xn.changed in /home/content/j/o/l/jolaa/html/sites/all/modules/xmlsitemap/xmlsitemap_node/xmlsitemap_node.module on line 453.

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented
Status: Fixed » Postponed (maintainer needs more info)

Do you have access to the database tables? Can you verify the database fields for the xmlsitemap table, and report them here?

jolaa’s picture

jolaa commented

Yes I have an access to DB tables. But I have never changed anything there. So probably I will need some more info about what you require. I have 3 entries for xmlsitemap in my DB:

1. xmlsitemap 8 MyISAM utf8_general_ci 7.4 KiB -

structure:
Field Type Collation Attributes Null Default Extra Action
loc varchar(255) utf8_general_ci No
module varchar(255) utf8_general_ci No
type varchar(32) utf8_general_ci No
id int(10) UNSIGNED No 0
sid int(10) UNSIGNED No 0
lastmod int(10) UNSIGNED No 0
changefreq int(10) UNSIGNED No 0
priority float No 0.5

2. xmlsitemap_file 0 MyISAM utf8_general_ci 1.0 KiB -

3. xmlsitemap_node 8 MyISAM utf8_general_ci 2.2 KiB -

structure:
Field Type Collation Attributes Null Default Extra Action
nid int(10) UNSIGNED No 0
last_changed int(11) Yes NULL
previously_changed int(11) Yes NULL
last_comment int(11) Yes NULL
previous_comment int(11) Yes NULL
priority_override float No -2

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented
Status: Postponed (maintainer needs more info) » Active

The reason for me to asking is that the code I changed rename the last_changed field to changed but still your database contains the old name.

I now added a check more so the updating code execute the following lines:

    if (db_column_exists('xmlsitemap_node', 'last_changed')) {
      db_change_field($ret, 'xmlsitemap_node', 'last_change', 'changed',
        array(
          'description' => 'The Unix timestamp of the last change.',
          'type' => 'int',
        )
      );
    }
    if (!db_column_exists('xmlsitemap_node', 'changed')) {
      db_add_field($ret, 'xmlsitemap_node', 'changed',
        array(
          'description' => 'The Unix timestamp of the last change.',
          'type' => 'int',
        )
      );
    }

If your database still contains the old field, then I would suggest you to:

  • Delete the files of the project modules from your server, and copy the new files.
  • Execute the update of your Drupal site (through update.php).

If that doesn't resolve the problem, then I don't know what else suggest you. The code to update the database definition is present, and if your database doesn't get updated then it's not a bug of the project code.

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented
Status: Active » Fixed

The error was caused by a misspelled field name used by the update code.

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented
Component: xmlsitemap_node.module » Code
jolaa’s picture

jolaa commented

now I am having 520 error :)

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 query: ALTER TABLE xmlsitemap_file CHANGE fid `fid` INT unsigned NOT NULL DEFAULT 0, ADD in /home/content/j/o/l/jolaa/html/includes/database.mysql-common.inc on line 520.

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented

That is a completely different issue that must be reported in a different report.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.