There is a strange problem in Drupal taxonomy system related to getting term node counts, if database is pgsql. The problem causes both site_map.module and sitemenu.module to fail: see this and this issue for examples. Even though both of those issues are against 4.5, and at least the actual SQL syntax used by sitemenu has since changed a bit, the problem still exists in 4.6. I've very limited understanding of PHP, but I do understand SQL, so I'll explain my observations -- what Drupal attempts to do, and what it should do.

What practically happens is, that site_map or sitemenu attempts to get taxonomy node count, and this results in executing an SQL query like this (sitemenu for Drupal 4.6):

SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM term_node t INNER JOIN node_access na ON na.nid = n.nid INNER JOIN node n ON t.nid = n.nid WHERE (n.language ='fi' or n.language = '') AND (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access2','term_access6','term_access5')) AND n.status = 1 GROUP BY t.tid

It is pretty obvious that this cannot work, as a selection ( COUNT(DISTINCT(n.nid)) ) is attempted to do from a relation, which is not included in FROM clause. If this is fixed ('node n' added to the from clause, then the next problem is, that the query attempts to join a table that is already part of the selection. Thus, what the query actually attempts to do would be achieved by the following query:

SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM liitto_term_node t, liitto_node n INNER JOIN liitto_node_access na ON na.nid = n.nid WHERE t.nid = n.nid AND (n.language ='fi' or n.language = '') AND (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access2','term_access6','term_access5')) AND n.status = 1 GROUP BY t.tid;

(As you can see from the queries above, the installation I'm using here is rather heavily patched, as it has both Taxonomy Access Control and i18n in use. While these both require patching of taxonomy.module -- see here for a combined patch, eliminating these two modules had no effect to this bug.)

The problem is, however, that I have no idea where the bug actually lies. Could be in taxonomy system, but could even be in pgsql's database handler. The query above as such is not included in any module: taxonomy module comes closest, as it has the following function:

function taxonomy_term_count_nodes($tid, $type = 0) {
  static $count;

  if (!isset($count[$type])) {
    // $type == 0 always evaluates true is $type is a string
    if (is_numeric($type)) {
      $result = db_query(db_rewrite_sql('SELECT t.tid, COUNT(n.nid) AS c FROM {term_node} t INNER JOIN {node} n ON t.nid = n.nid WHERE n.status = 1 GROUP BY t.tid'));
    }
    else {
      $result = db_query(db_rewrite_sql("SELECT t.tid, COUNT(n.nid) AS c FROM {term_node} t, {node} n WHERE t.nid = n.nid AND n.status = 1 AND n.type = '%s' GROUP BY t.tid"), $type);
    }
    while ($term = db_fetch_object($result)) {
      $count[$type][$term->tid] = $term->c;
    }
  }

  foreach (_taxonomy_term_children($tid) as $c) {
    $children_count += taxonomy_term_count_nodes($c, $type);
  }
  return $count[$type][$tid] + $children_count;
}

I've attempted to fix the 'if' clause above 'else' to be like this:

    if (is_numeric($type)) {
      $result = db_query(db_rewrite_sql('SELECT t.tid, COUNT(n.nid) AS c FROM {term_node} t, {node} n WHERE t.nid = n.nid AND n.status = 1 GROUP BY t.tid'));
    }

But no help. Besides, the count here does not use DISTINCT, so that already gives a hint that the actual problems is somewhere else. I've however hit the limits of my PHP reading skills, and do not really understand how Drupal constructs the actual queries it sends to DB engine, so I'd be glad for any help.

Note: If uid=1, something is done differently, as the problem does not occur.

Comments

plj’s picture

plj commented
Component: taxonomy.module » postgresql database

I'm changing component from taxonomy.module to postgresql database, as this is most likely problem with db_rewrite_sql. This seems to occur with event.module too (CVS version needed to run with PostgreSQL), if event calendar block is turned on. When user with UID other than 1, including anonymous, requests the front page under this conditions, the following query fails:

SELECT DISTINCT(n.nid), e.event_start FROM liitto_event e INNER JOIN liitto_node_access na ON na.nid = n.nid INNER JOIN liitto_node n USING (nid) WHERE (n.language ='fi' or n.language = '') AND (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access1')) AND n.status = 1 AND ((e.event_start > 1117573200 AND e.event_start < 1120165199) OR (e.event_end > 1117573200 AND e.event_end < 1120165199) OR (e.event_start < 1117573200 AND e.event_end > 1120165199)) ORDER BY e.event_start

igrcic’s picture

igrcic commented

had same problems! When counting nodes and TA.module was on it would fail for non admin user. But today i tried it again and everthings working!!!

Don't know where the problem was, I know i didnt install any of taxonomy patches...

I reported it in this this issue.

igrcic’s picture

igrcic commented

problem is happening again :(

I installed i18 taxonomy patch

plj’s picture

plj commented
Version: 4.6.0 » 4.6.1

Hello igrcic,

I've been lazy with this problem lately, but I recall that I once tested this with 4.5, before reporting it as that site_map bug you referred to, and then it occured even after disabling taxonomy_access and reverting to unpatched module, as I told there.

I've thought that the problem would be in Drupal's SQL rewriting (db_rewrite_sql) function, because as I stated above, fixing the bad syntax in taxonomy.module had no effect at all. The fix you proposed in that site_map bug is not sufficient either, as in case where one has both i18n and taxonomy_access the code produces two adjacent INNER JOINs (this sort of syntax alone is enough to cause me headache), of which another refers to the node table – that should be part of the FROM clause – and dropping it without adding the condition defined by it to WHERE does not necessarily give the desired result.

So, in SQL terms, the fix is clear. But where in the code this all happens is a totally different thing… thanks for testing and reporting it too, though. May be someday someone even figures this out…

I'm now running 4.6.1, and this still occurs.

plj’s picture

plj commented
Title: Odd problem getting term node counts (with PostgreSQL) » Getting term node counts fails without "Administer Nodes" permission
Project: Drupal core » Taxonomy Access Control
Version: 4.6.1 » master
Component: postgresql database » Code
Priority: Normal » Critical

OK,

I've now done some more extensive research regarding to this problem, and can now undeniably say that taxonomy_access.module is to blame.

Database: Postgres 8

Test setup 1: Drupal HEAD and sitemenu.module HEAD (due to changes in HEAD's taxonomy.module current versions of taxonomy_access cannot be installed under HEAD).

Result: SiteMenu works – no errors.

Test setup 2: Drupal 4.6.2 and sitemenu.module 4.6.0. Taxonomy_access NOT installed.

Result: SiteMenu works – no errors.

Test setup 3: Drupal 4.6.2, sitemenu.module 4.6.0 and taxonomy_access.module HEAD. Taxonomy_access is enabled and in use, taxonomy.module is patched.

Result: All users without "Administer Nodes" permission attempting to access SiteMenu will receive the following errors for all terms that have any nodes in them and that users have granted permission to access:

pg_query(): Query failed: ERROR: relation "n" does not exist in /var/www/drupal_vanilla/includes/database.pgsql.inc on line 45.

query: SELECT t.tid, COUNT(DISTINCT(n.nid)) AS c FROM term_node t INNER JOIN node_access na ON na.nid = n.nid INNER JOIN node n ON t.nid = n.nid WHERE (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access1')) AND n.status = 1 GROUP BY t.tid in /var/www/drupal_vanilla/includes/database.pgsql.inc on line 62.

I will try to search this further, but any suggestions are helpful. I have to say that I'm quite lost with this one. The failed function call in sitemenu.module seems to be call of function taxonomy_term_count_nodes, defined in taxonomy.module.

Zed Pobre’s picture

Zed Pobre commented

This also breaks image.module and article.module, by the way, and I made a very dangerous discovery just now:

If you try to work around this by enabling Administer Node access to anonymous users, and then block them from doing anything harmful by not granting anonymous users any term_access permissions for change/delete/create on any keywords, they *STILL* get edit/delete access to random nodes!

I have no idea why some nodes are secured and not others, but I'm exceedingly nervous at the moment as I don't know how to fix my site. If I turn off Administer Node access, I break images and articles (which comprise the majority of my content), and if I don't, eventually a random stranger is going to come by and destroy my site.

I'm going to try disabling taxonomy_access completely again (I tried this once under 4.5 and it caused extreme site breakage, but it may work out better under 4.6), and see if I can regain control.

Zed Pobre’s picture

Zed Pobre commented

It's not just taxonomy_access -- I think it may be anything that makes use of node_access at all. The node_privacy_byrole module is generating exactly the same behaviour, and I can't even shut it off! ARGH!

Zed Pobre’s picture

Zed Pobre commented

I tracked down where the INNER JOIN {node_access} part is coming from: that's in _node_access_join_sql in node.module.

Zed Pobre’s picture

Zed Pobre commented
Project: Taxonomy Access Control » Drupal core
Version: master » 4.6.0
Component: Code » node.module

I'm fairly confident at this point that this problem isn't specific to taxonomy_access, so I'm reassigning it to Drupal. It *does* have to do with anything that triggers a node_access join outside of realm 'all', though. At the moment, my best guess is that this is happening inside of node.module, so that's where I'm sticking it.

killes@www.drop.org’s picture

killes@www.drop.org commented
Component: node.module » postgresql database

moving to pgsql database

niteman’s picture

niteman commented
Title: Getting term node counts fails without "Administer Nodes" permission » Getting term node counts fails without "Administer Nodes" permission also fails witch MySQL
Component: postgresql database » database system

Hi all, i'm unsure of where to post this.
First of all my english is not very nice. I'll try to point the issue however.
I've read this thread completely and i have been searching google and dupral for this issue with very little luck.
There is my system's resume:
OS: Windows 2003 SP1
HTTP Server: IIS 6.0
PHP: version 5.03 loaded as ISAPI module
DB Server: MySQL Server 5.0
Drupal version: 4.6.3
The issue is that with with taxonomy_access enabled every time any block calls taxonomy_term_count_noves (in the patched taxonomy module; by the way i wonder if it cuold be caused by the patch) the user gets an error like this:

user error: Unknown column 'n.nid' in 'on clause'
query: SELECT DISTINCT(n.nid), n.title, n.type, u.uid, u.name FROM term_node r  INNER JOIN node_access na ON na.nid = n.nid LEFT JOIN node n ON r.nid = n.nid LEFT JOIN users u ON n.uid = u.uid WHERE  (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','term_access2','term_access4')) AND  n.status = 1 AND 1 AND r.tid = '11' ORDER BY n.changed DESC LIMIT 0, 50 in *:\*********\********\includes\database.mysql.inc on line 66.

Any insights?
Note that i'm using MySQL

pyzahl’s picture

pyzahl commented

I found the same error, but only after migrating a 100% copy of the drupal php core and modules including the database to a new server. So this looks like some trouble related to the php or mysql interface to me then!

I still run drupal 4.6.2 on both systems. I can verify the problem exactly as described!
I hope this hint helps the experts.

On the new system I have (Debian packages):

i AU php4 4:4.4.0-4 4:4.4.0-4
i AU php4-cli 4:4.4.0-4 4:4.4.0-4
i AU php4-common 4:4.4.0-4 4:4.4.0-4
i U php4-gd 4:4.4.0-4 4:4.4.0-4
i U php4-imagick 0.9.11-4 0.9.11-4
i AU php4-mcrypt 3:4.3.10-2 3:4.3.10-2
i AU php4-mysql 4:4.4.0-4 4:4.4.0-4
i U phpmyadmin 4:2.6.4-pl 4:2.6.4-pl

i U libapache2-mod-auth-mysql 4.3.9-2 4.3.9-2
i U libapache2-mod-php4 4:4.4.0-4 4:4.4.0-4

on the old system, not problems! I do have:

i libapache2-mod-php4 4:4.3.10-1 4:4.3.10-1
i php4 4:4.3.10-1 4:4.3.10-1
i php4-cli 4:4.3.10-1 4:4.3.10-1
i A php4-common 4:4.3.10-1 4:4.3.10-1
i php4-gd 4:4.3.10-1 4:4.3.10-1
i php4-imap 4:4.3.10-1 4:4.3.10-1
i php4-ldap 4:4.3.10-1 4:4.3.10-1
i php4-mysql 4:4.3.10-1 4:4.3.10-1

-PyZahl

killes@www.drop.org’s picture

killes@www.drop.org commented
Status: Active » Closed (duplicate)

http://drupal.org/node/43128