Posted by Vc Developer on July 5, 2011 at 10:27pm
3 followers
| Project: | Search configuration |
| Version: | 7.x-1.0-beta1 |
| Component: | Code |
| Category: | bug report |
| Priority: | major |
| Assigned: | Unassigned |
| Status: | closed (fixed) |
Issue Summary
I get this exception only after I created my own content type other than the default ones:
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'ESCAPE '\\') ) GROUP BY i.type, i.sid HAVING (COUNT(*) >= '2') ) subquery' at line 4: SELECT COUNT(*) AS expression FROM (SELECT i.sid AS sid FROM {search_index} i INNER JOIN {node} n ON n.nid = i.sid INNER JOIN {search_total} t ON i.word = t.word INNER JOIN {search_dataset} d ON i.sid = d.sid AND i.type = d.type WHERE (n.status = :db_condition_placeholder_0) AND( (i.word = :db_condition_placeholder_1) OR (i.word = :db_condition_placeholder_2) )AND (i.type = :db_condition_placeholder_3) AND (n.type NOT IN (:db_condition_placeholder_4_0, :db_condition_placeholder_4_1)) AND( (d.data LIKE :db_condition_placeholder_4_0, :db_condition_placeholder_4_1 ESCAPE '\\') ) GROUP BY i.type, i.sid HAVING (COUNT(*) >= :matches) ) subquery; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => comis [:db_condition_placeholder_2] => conventio [:db_condition_placeholder_3] => node [:matches] => 2 [:db_condition_placeholder_4_0] => page [:db_condition_placeholder_4_1] => did_you_know ) in PagerDefault->execute() (line 74 of /media/webserverdocs/7/production/drupal-7.4/includes/pager.inc).....I re-stored a backup of a fresh install and it worked fine until I created a new content type. I tested with and without the the mod and I get the PDOException with the mod.
Comments
#1
This is how my settings look.
#2
I did another test giving anonymous users full content search and it works as expected, but I want to be able to give full search to registered user only.
#3
It looks like this is a bug in core DB, there is this bit in the query that that is wrong. However, it would be good to see what triggered this to fix the trigger here and then pass it along into the core queue to fix that too!
What was the machine name of the new content type? This should be the only thing that differs between the core content types and the new content types.
#4
Machine name: did_you_know
Should this be a one word name?
#5
I tried with "did_you_know_" and I couldn't replicate the error.
What version of Drupal are you using? Any other access or search related modules installed?
#6
And trying a machine name of didyouknow would be an interesting test if you have time.
Cheers
#7
D7.4
Ooops! I didn't mention additional details of how I was searching! In the Advance Search Form I did a "Containing the phrase" search and that's when I got the PDOException.
#8
Here's my mysql test data and modules folder. Used phpmyadmin to compress the data. And the password is 123admin
#9
Cool, I've got these, so you can remove the above files if you can / want. I'll look at setting up a test site tonight.
#10
Installed, tested and I can not replicate this issue!
Can you write down step by step how to get the error?
I ran tests as user = 0, user = 1 and another user (uid = ?), searches.
Also, what version of MySQL / PHP are you using?
#11
MySql: 5.1.41-3ubuntu12.10
PHP: 5.3.2-1ubuntu4.9
Steps:
1) Did a normal search from one of the words from a generated post.
2) After the results I did a 'word phrase' search using the same word + the word next to it forming a phrase from the content.
For example:
A Post:
Autem ille paratus plaga suscipit. Autem mauris metuo minim quidne quis. Aliquip consectetuer laoreet occuro quadrum. Cogo dolore fere imputo jugis magna similis tincidunt. Ad autem diam hendrerit meus rusticus. Autem dignissim feugiat gilvus quidne.
--->Then search all post with 'autem'
Results:
Autem ille paratus plaga suscipit. Autem mauris metuo minim quidne quis. Aliquip consectetuer laoreet occuro quadrum. Cogo dolore fere imputo jugis magna similis tincidunt. Ad autem diam hendrerit meus rusticus. Autem dignissim feugiat gilvus quidne.
Autem ille paratus plaga suscipit. Autem mauris metuo minim quidne quis. Aliquip consectetuer laoreet occuro quadrum. Cogo dolore fere imputo jugis magna similis tincidunt. Ad autem .......
blah...
blah..
.....
--->now do a search to trim down hits with 'autem diam' (now I only want every post like the first hit) and this is when I get the PDOException on the anonymous...
#12
This was a strange core bug, from memory there is a preg_replace() or something similar in the core DB code, so there is a error there!
Right, I'll push through a new release soon, but changing the code from:
<?php
$excluded_content_types = array();
foreach (search_config_content_types() as $type => $label) {
if (!user_access("search $type content")) {
$excluded_content_types[] = $type;
}
}
// This is the valid DB code that breaks the query
if (!empty($excluded_content_types)) {
$query->condition($node . '.type', array($excluded_content_types), 'NOT IN');
}
?>
to this resolved it!
<?php
$excluded_content_types = array();
foreach (search_config_content_types() as $type => $label) {
if (!user_access("search $type content")) {
$excluded_content_types[] = $type;
}
}
// A core bug results in a DB error if we use the following: Ref: #1210072
// $query->condition($node . '.type', array($excluded_content_types), 'NOT IN');
if (!empty($excluded_content_types)) {
$db_and = db_and();
foreach ($excluded_content_types as $type) {
$db_and->condition($node . '.type', $type, '!=');
}
$query->condition($db_and);
}
?>
Stranger still, the rewrite happens a number of times successfully before failing! Thanks for the time you spent helping track this bug!
cheers
Alan
#13
The new release has been published. Reopen if you have any more issues
#14
Your welcome! It's a pleasure and want to help make Drupal the best CMS! I'll download the new release and if anything comes up I'll let you know! ;)
#15
Automatically closed -- issue fixed for 2 weeks with no activity.