i18n (Internationalization) module and user warning: Unknown column 'n.nid'
I've seen many threads discussing "user warning: Unknown column 'n.nid' " errors stemming from problems in the SQL queries made by some modules, apparently under MySQL 5.0. Some existing threads:
http://drupal.org/node/58502 http://drupal.org/node/47741 http://drupal.org/node/69108 http://drupal.org/node/43735 http://drupal.org/node/40623
I'm getting the same type of error after installing the i18n package for internationalization, which includes modules called i18n.module and translation.module.
http://drupal.org/project/i18n
Disabling those modules eliminates the error.
My question: Has anyone else experienced the "Unknown column 'n.nid' " problem specifically following installation of i18n, and if so, did you find a fix?
More background, starting with my error message:
user warning: Unknown column 'n.nid' in 'on clause' query: SELECT n.nid, n.title, n.uid, n.changed, l.last_comment_timestamp, GREATEST(n.changed, l.last_comment_timestamp) AS last_change, l.comment_count FROM node n, node_comment_statistics l LEFT JOIN i18n_node i18n ON n.nid = i18n.nid WHERE (i18n.language ='en' OR i18n.language ='' OR i18n.language IS NULL) AND n.nid = l.nid AND n.status = 1 AND n.type IN ('blog', 'book', 'amazon', 'amazon_node', 'tasks', 'forum', 'page', 'poll', 'story') AND 1=1 ORDER BY last_change DESC LIMIT 0, 3 in /Library/WebServer/Documents/includes/database.mysql.inc on line 120.
Oddly enough, I have another site running the same i18n package, and it works beautifully there. Same module versions, same module settings, same Drupal setup all around (though there could be differing versions of other modules lurking within the two setups...) The only key difference that I can see is... ah, a big one: the site with the error is on MySQL 5.0.22, the site with no error is on MySQL 4.1.2. According to the forums, trouble with 5.x is likely the source of the problem.
I don't see i18n specifically discussed in other threads dealing with the n.nid error. From the forum discussions, the problem looks very complex, requiring custom fixes that still don't seem to work for everyone. Before I start mucking about in this (to me) scary field, I'd like to ask whether anyone else has wrestled with this error and i18n already.
Thank you!!
More strict interpetation of SQL in Mysql 5.x
I had the same problem in a standalon script earlier this week and I found the solution in the bottom of this MySql bug report:
http://bugs.mysql.com/bug.php?id=13832
It seems like Mysql 5 have a more strict interpetation of SQL queries than 4.x, and thus require more attention to the use of parantheses to ensure the SQL query is parsed in the right order.
-Olegu
http://www.zbz5.net/
That's helpful in understanding...
... though unfortunatately, my low skills leave me struggling to figure out what to do with the above, and with the copious amount of discussion in other threads.
From many comments, I make the educated (?) guess that what I'll want to change will be a query inside a .module file, whose text includes "ON n.nid". However, of all the files inside the i18n package, only translation.module contains that string, in three locations:
$result = pager_query(db_rewrite_sql("SELECT n.nid, n.title FROM {node} n INNER JOIN {i18n_node} i ON n.nid = i.nid WHERE i.language = '%s' ORDER BY i.trid"), 40, 0, NULL, $lang);
$sql = 'SELECT n.nid, n.title, n.status, a.language FROM {node} n INNER JOIN {i18n_node} a ON n.nid = a.nid INNER JOIN {i18n_node} b ON a.trid = b.trid WHERE '. implode(' AND ', $conds);
$sql = 'SELECT n.nid FROM {i18n_node} n INNER JOIN {i18n_node} a ON n.nid = a.nid INNER JOIN {i18n_node} b ON a.trid = b.trid AND b.nid =%d WHERE n.nid != %d AND n.language = \'%s\' AND a.trid != 0';
How, if at all, one or more of those should be modified to fix the problem, is something I'm playing with a little... but I have my doubts that the cause lies within translation.module, because disabling translation.module completely still leaves the problem! In fact, disabling translation.module, i18nblocks.module, and i18nmenu.module (all parts of the i18n package, and dependent upon the core i18n.module), and leaving only i18n.module enabled, returns the error. This suggests to me that the problem query must lie within the i18n.module file... That makes sense, right? Yet i18n.module doesn't contain that string; in fact, it only contains "n.nid" in one place: $result['join'] = "LEFT JOIN {i18n_node} i18n ON $primary_table.nid = i18n.nid";
The solution may be as simple as spicing one line in one module with the right parentheses... or otherwise rearranging one line a bit... but I'll need plenty more study to figure it out. In the meantime, I think I'll try to hit the i18n developer for suggestions.
Thanks again for the reply; every little piece is a great help!
Issue
The best you can do to get this worked out is to file an issue against the i18n project (http://drupal.org/project/issues/i18n), The module developers will then most likely respond to the bug and provide a fix, or otherwise explain how to work this out.
-Olegu
http://www.zbz5.net/
simple solution to "unknown column 'n.nid'
all this happened long ago and i assume the issues got solved one way or the other, but this topic keeps popping up and today it happened to me. i didn't see any real solution anywhere, but after following the way the faulty SQL query takes through different modules, i found an easy solution that works for me, and most probably will for most other cases, too.
it's not the i18n module that's at fault, as has been suggested, but other modules sending their queries to hook_db_rewrite_sql, that gets intercepted by i18n.
i found the key to the whole thing in the post More strict interpetation of SQL in Mysql from olegu. he points to a discussion of this topic in a MySQL bug discussion where a similar but in MySQL 5 is described, and also cases of sloppy queries that cause the same error.
version 5 applies some rules more strictly than before, and the code sent, in my case by aggregator2-module, to db_rewrite_sql() needed just two brackets to stop causing this error.
i changed line 759 of aggregtor2_module
$result = db_query(db_rewrite_sql('SELECT n.nid, n.title FROM {node} n, {aggregator2_feed} af WHERE af.nid = n.nid'));to
$result = db_query(db_rewrite_sql('SELECT n.nid, n.title FROM ({node} n, {aggregator2_feed} af) WHERE af.nid = n.nid'));one additional pair of brackets (after FROM), and everything works again. i18n - module itself couldn't possibly do anything about it - it's the module originating the query where the code needs to be straightened up to comply with MySQL 5.
phani.