Jump to:
| Project: | Fuzzy Search |
| Version: | 6.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | closed (fixed) |
Issue Summary
Hi, this module works great when i am logged in as admin, but when i enable it for anonymous users i get the following 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 ') GROUP BY n.nid ORDER BY completeness DESC, score DESC LIMIT 0, 10' at line 1 query: SELECT DISTINCT(n.nid), SUM(percent) AS completeness, SUM(score) AS score FROM (SELECT n.nid, SUM(completeness) percent, SUM(score) score FROM search_fuzzy_index AS s LEFT JOIN node AS n ON (n.nid = s.nid) INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'content_access_rid'))) AND ( (( (ngram = 'dag' AND completeness <50.1) OR (ngram = 'ago' AND completeness <50.1)) AND n.status = 1) ) GROUP BY word_id HAVING percent > 40) AS n ) GROUP BY n.nid ORDER BY completeness DESC, score DESC LIMIT 0, 10 in /var/www/iz3/20070915_getcars/includes/database.mysql.inc on line 172.
(searched for 'dago')
I dont have the time now to check it thoroughly, but i thought i may as well let u know. I thought it may have something to do with node permission module...
Comments
#1
I just recently checked this out on a new project I'm working on and yes I do receive the same errors when I have my users doing searches. This definitely has to do with the url re-writing that takes place when the node access information is inserted when db_rewrite_sql is called. This is a high priority for me and will work hard to get this fixed asap.
#2
Hiya - just wondering if there was any movement on this - I'm launching a site on monday so at the moment I've just got the block switched off for anonymous users. If you have any pointers as to where to look, I can attempt to dive into the code.
#3
OK, ignore that, I've uninstalled it - didn't realise it errored for authenticated users too, only works for webmaster. Plus I'm trying to hide searching of particular content types, specifically issues as I'm using the case tracker module for internal bug reports. So I'm using the search block module, but I am getting the feeling that fuzzy search doesn't take blocked types into account because I'm still getting the result I don't want to see.
#4
I've been having the same problem, it seems that this is only a problem when users do not have node administrative access. Don't have a fix for this but hopefully this helps.
#5
Hi,
i also have this problem.
Yesterday i played around a little with the problem, and i found out the following:
A part of the SQL-Query looks like this:
... GROUP BY word_id HAVING percent > 40) AS n ) GROUP BY n.nid ...
and the ")" between "AS n" and "GROUP BY" ist wrong and results in an SQL-Error.
This ")" and some other things are made by the "db_rewrite_sql".
Here is an very dirty workaround i made to get it working. I am not shure of any security vulnerability!
File: fuzzysearch.module
Line: 580
Original:
$query = pager_query(db_rewrite_sql("SELECT n.nid, SUM(percent) AS completeness, SUM(score) AS score FROM (SELECT n.nid, SUM(completeness) percent, SUM(score) score FROM {search_fuzzy_index} AS s LEFT JOIN {node} AS n ON (n.nid = s.nid) WHERE (($clause) AND n.status = 1) GROUP BY word_id HAVING percent > $min_completeness) AS n GROUP BY n.nid ORDER BY completeness DESC, score DESC"), 10, 0, "SELECT COUNT(DISTINCT(n.nid)) FROM (SELECT n.nid, CEILING(SUM(completeness)) completeness, SUM(score) score FROM {search_fuzzy_index} AS n WHERE $clause GROUP BY word_id HAVING SUM(completeness) > $min_completeness ORDER BY completeness DESC, score DESC) AS n");Workaround:
$q = db_rewrite_sql("SELECT n.nid, SUM(percent) AS completeness, SUM(score) AS score FROM (SELECT n.nid, SUM(completeness) percent, SUM(score) score FROM {search_fuzzy_index} AS s LEFT JOIN {node} AS n ON (n.nid = s.nid) WHERE (($clause) AND n.status = 1) GROUP BY word_id HAVING percent > $min_completeness) AS n GROUP BY n.nid ORDER BY completeness DESC, score DESC");$q = str_replace("AS n ) GROUP BY", "AS n GROUP BY", $q);
$query = pager_query($q, 10, 0, "SELECT COUNT(DISTINCT(n.nid)) FROM (SELECT n.nid, CEILING(SUM(completeness)) completeness, SUM(score) score FROM {search_fuzzy_index} AS n WHERE $clause GROUP BY word_id HAVING SUM(completeness) > $min_completeness ORDER BY completeness DESC, score DESC) AS n");
Mabey this ends up in an issue for the drupal-core-team.
Best regards.
#6
I know this is being worked on from the note on the module page. I'll be prepared to test any fixes asap when a potential fix becomes available.
#7
I raised this as a issue (which i've since flagged as a duplicate) here: http://drupal.org/node/279884, and can offer the following detail: if you give a user the modr8 permission "moderate content" then they don't get this error.
#8
i've just replaced the SQL (as per richi - November 7, 2007 - 14:43) and it seems to be ok. haven't done any thorough testing but will post again if i see a problem with it.
#9
I recently took over this module. I'm going through all the old issues and closing them if possible. I'm not making any changes other than security fixes to the Drupal 5 version.
If you would still like to see this in the Drupal 6 version, please create a new issue or reopen this one, changing the version number.
Thanks,
awolfey
#10
I really hate to be the guy who re-opens this, but it looks as though the drupal 6 version still conflicts with the modr8 module, is there a fix for this? Thanks!
#11
whoops, i guess this is still "critical"
#12
I'll look into this soon.
#13
If I were wanting to take a shot at trying to fix a copy, would it be possible for you to point me in the right direction, like a starting point or something (what exactly is broken in D6)
Absolutely don't want to sound pushy but I really like the way this module works and it is a shame it isn't D6 yet so I would love to help in any way I can.
#14
DollarSign: The problem is that the query breaks when a module rewrites it. It's usually node access modules that rewrite node queries. See db_rewrite_sql() and hook_db_rewrite_sql() on api.drupal.org.
Thanks for looking.
#15
I figured out the problem. in the query, the table being selected is given the clause "AS n" in order to make it easier to refer to. this conflicts with Modr8 because in their re-write query they also use "n" as the table alias.
In order to fix this problem, I actually edited the Modr8 module, not the fuzzysearch module and changed every instance of the queries from "n" to a different alias (i used "yn" for no particular reason) and the search now works for everybody with no errors.
#16
But is it actually filtering out the moderated nodes?
In my testing I got an error because fuzzysearch did not select uid and moderated from the node table. When I added those I did not get any errors.
Could you post the error you saw before you change modr8?
Thanks
#17
In my case I am restricting the content type to search only one content type and that particular type will not be moderated.
However, I set fuzzysearch to include my moderated content type and searched for a node that hasn't been published yet and it returned no results. Which is what we want I would think.
I then published the queued node and researched for it and fuzzy did find it. So it appears to not have broken any moderating functionality.
my original error was (again it includes my content type exclusions):
user warning: Unknown column 'n.moderate' in 'where clause' query: SELECT *, n.nid, n.type, SUM(percent) AS completeness, SUM(score) AS score FROM (SELECT n.nid, n.type, s.ngram, SUM(completeness) percent, SUM(score) score FROM fuzzysearch_index AS s LEFT JOIN node AS n ON (n.nid = s.nid) WHERE (( (ngram = 'app' AND completeness <50.1) OR (ngram = 'ppl' AND completeness <50.1) OR (ngram = 'ple' AND completeness <50.1)) AND n.status = 1 AND n.type NOT IN ('booth','simplenews','event','group','group_post','image','offer','story','page','things_to_do','webform')) GROUP BY word_id HAVING percent > 40) AS n INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'private_author') OR (na.gid = 0 AND na.realm = 'og_public'))) AND ((n.moderate != 1)) GROUP BY n.nid ORDER BY completeness DESC, score DESC LIMIT 0, 10 in sites/all/modules/fuzzysearch/fuzzysearch.module on line 809.#18
Right. You don't have to hack modr8. It's a problem in fuzzysearch.
If you change the Main query in fuzzysearch.module to the following it will also work without a modr8 hack.
// Main query
$sql = "
SELECT *, n.nid, n.type, SUM(percent) AS completeness,
SUM(score) AS score FROM (SELECT n.nid, n.moderate, n.uid, n.type, s.ngram, SUM(completeness) percent, SUM(score) score
FROM {fuzzysearch_index} AS s
LEFT JOIN {node} AS n ON (n.nid = s.nid)
WHERE (($clause) AND n.status = 1
AND n.type NOT IN ($placeholders))
GROUP BY word_id HAVING percent > %d) AS n
GROUP BY n.nid ORDER BY completeness DESC, score DESC";
I also need to fix the count query so that the pager is in sync.
#19
Yeah, I had noticed a bug on some of the search results with the pager being off. If I get a chance I may try and look into that, if I do I will let you know.
also, you may want to think about releasing an updated version of the module with the new "Main Query" so that people can install it and search without errors ;)
#20
I'll get this in when I have a change to fix the pager query. If you want to work on it, essentially you need to JOIN to the node table and also SELECT the uid and moderated columns fron node. You'll also have to fix the table aliases. The node table should then be n.
#21
OK, this is committed, but I still need to work on the pager query. Changing title for future searchers.
#22
Pager query is now fixed in dev. Closing the issue.