I upgraded from the patched 4.7.x-2.1 version to 5.x-1.x-dev and found out that if "Hide terms with no content" is selected then the directory page is blank and there is a SQL error in the log. The error is as follows:
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 'DISTINCT(n.nid) FROM term_node t JOIN node n ON t.nid = n.nid WHERE n.status = 1' at line 1 query: SELECT t.tid, DISTINCT(n.nid) FROM term_node t JOIN node n ON t.nid = n.nid WHERE n.status = 1 /drupal/includes/database.mysql.inc -s on line172.

If I don't select "Hide terms with no content" then everything is ok and no errors in the logs when browsing the directory. I haven't had trouble with this feature in previous releases.

CommentFileSizeAuthor
#20 sql.error_.count_.nodes_.147063.3.patch1.95 KBbeginner
#13 sql.error_.count_.nodes_.147063.2.patch1.97 KBbeginner
#11 sql.error_.count_.nodes_.147063.patch1.49 KBbeginner

Comments

beginner’s picture

beginner commented

This is a development-only release. You didn't specify which version you are using.
Please make sure to update to the latest dev version and report back whether or not you still experience this problem.

martig’s picture

martig commented

The module version seems to be 1.11.2.18 2007/05/23 13:07:33(latest release), Drupal version is 5.1.

beginner’s picture

beginner commented

I cannot reproduce this.

Anybody else has the same problem?

Try to test by disabling all other contrib module, to see if you can reproduce with only core + directory.

The query is modified probably by another module.

Also, try installing devel.module, and set it so that it shows queries at the bottom of the page. Tell me which function is actually calling the query that is at fault.

martig’s picture

martig commented

The function is directory_taxonomy_term_count_nodes
and the query is
SELECT t.tid, DISTINCT(n.nid) FROM term_node t JOIN node n ON t.nid = n.nid WHERE n.status = 1

Hope it helps. I'll try to test it later with other modules disabled.

beginner’s picture

beginner commented

That helps (I was looking at the wrong query).

Another module must be involved.
db_rewrite_sql() is adding a DISTINCT() to my original query.

I don't know why they would do so, and I am not sure the solution is to remove db_rewrite_sql() .

beginner’s picture

beginner commented

http://api.drupal.org/api/5/function/_db_rewrite_sql :

Helper function for db_rewrite_sql.
Collects JOIN and WHERE statements via hook_db_rewrite_sql(). Decides whether to select primary_key or DISTINCT(primary_key)

?

martig’s picture

martig commented

So, the problem isn't caused by another module?

beginner’s picture

beginner commented

Yes, it is.
The query is being modified - and broken - by another module.
We need to figure out which of the two modules (this one or that other one) needs to be fixed.

Can you reproduce when disabling all contrib modules?

martig’s picture

martig commented

I tested the site under another configuration (php 5 and mysql 5, the main site is on php 4 and mysql 4) and disabled all modules I could and also tried different themes, but it didn't help. Also I noticed now that it doesn't show the category node count even though I have selected "Show count of nodes in categories" - it doesn't produce any errors, seems like it's ignored.

beginner’s picture

beginner commented

There must be a module, somewhere...
I am looking hard, trying to understand some of the code, in core.

Is your second setup a clean install? Or did you use your site's DB?

What does this query return?
SELECT COUNT(*) FROM node_access WHERE nid = 0 AND ((gid = 0 AND realm = 'all')) AND grant_view >= 1

Have a look at the content of the table node_access in phpmyadmin. What do you see there? (how many rows, content?)

beginner’s picture

beginner commented
StatusFileSize
new sql.error_.count_.nodes_.147063.patch1.49 KB

Please, first reply to my questions above: I still need/want to know.

Then, apply this patch. Tell me if it solves your problem.

beginner’s picture

beginner commented
Status: Active » Needs review
beginner’s picture

beginner commented
StatusFileSize
new sql.error_.count_.nodes_.147063.2.patch1.97 KB

The previous patch is wrong.
Try this one instead.

martig’s picture

martig commented

I used the same database in my second setup.

SELECT COUNT(*) FROM node_access WHERE nid = 0 AND ((gid = 0 AND realm = 'all')) AND grant_view >= 1
returns:

+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

There are 805 rows in node_access and the content seems to be 1's and 0's.

martig’s picture

martig commented

The patch worked. Now it's showing the node count and hiding the empty categories.
Btw, earlier when testing I disabled all modules besides directory, menu, taxonomy and the core modules of course.

Thanks for working on this issue. I hope it helped you, too.

martig’s picture

martig commented

Unfortunately I discovered a new bug. I have the site set up so that the 404 page is the directory page. When I'm logged in and enter an invalid address there's no problem, it just gets logged. But when a visitor requests a nonexistent page the directory page shows up empty (the category part of the page is missing) and there's an error in the log.
Unknown table 'term_node' in where clause query: SELECT t.tid, n.nid FROM term_node t JOIN node n ON t.nid = n.nid WHERE (term_node.tid IN ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59&# /drupal/includes/database.mysql.inc on line 172.

If I disable "Hide terms with no content" the categories show up, but there is no node count, like before. So the patch only worked partially.

martig’s picture

martig commented

Some extra information:
Although now, when I have disabled hiding of empty categories, the category listing shows up like before, but it produces the error mentioned with every unique invalid page request.

beginner’s picture

beginner commented

Is your second setup a clean install? Or did you use your site's DB?
You must be using some node access control module.

You didn't tell me what kind of values you have in the 'realm' column of the table node_access.

beginner’s picture

beginner commented

About your new problem, I don't think this would occur on a clean install. You are using a node access module (og, perhaps? Or more likely taxonomy_access!) that is changing the query.

About the list of values:
('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59
Have a good look at your data: do those numbers correspond to taxonomy term IDs (tid) or node IDs (nid)?

beginner’s picture

beginner commented
StatusFileSize
new sql.error_.count_.nodes_.147063.3.patch1.95 KB

Try this patch, to apply on a clean copy of the module (get a new copy of the dev snapshot).

Please, provide the information requested in my two previous comments.

Thanks for the feedback.

martig’s picture

martig commented

The last patch really seems to have done it - everything is working as it should and no errors in the log.

While testing I used the old database. I have taxonomy access installed, but I disabled it while testing.
In the 'realm' column there's only the value - term_access.
The list of values probably correspond to term IDs, cause I don't have nodes (maybe a few nodes), which have so low IDs.

beginner’s picture

beginner commented
Assigned: Unassigned » beginner
Status: Needs review » Fixed

@martig: Great. I'm glad I guessed correctly. You let me through some corners of Drupal core that I didn't know.
I have committed a revised version of the patch.

Important: make sure to grab a copy of the new official release (http://drupal.org/node/90450/release ) because the patched version you are using includes some debugging code you don't want to keep on your live site. The official release would be more performant.

martig’s picture

martig commented

Glad I could help.

Thank You

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)