Search index stuck at 0%, mysql error on public search, can't clear error

Question

I have just discovered that the Drupal 7 search on my website is giving 'website encountered an unexpected error. Please try again later.' to users who are not logged in. (it's fine for logged in users.)

Checking the logs, I can see that when a non-logged in user runs a search it causes a mySQL error, which looked like there was a problem because I'd excluded content type admin_only_help from public search.

OK, I'll include that content type in search. Nope, still the same error. OK, re-index the search: nope, won't re-index the search, stalls at 0%.

I tried:

• setting content type admin_only_help to be indexed.

• unpublishing all nodes of content type admin_only_help

• re-indexing the search from the admin panel (it stalls)

• running drush search-index from the command line (it stalls)

• turning off the Custom Search module

• clearing cache (after all of the above).

I'd be very grateful for any further suggestions.

The exact error from my logs is:

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 'WHERE (c.nid = '873') AND 
(c.status = '1') AND (n.type <> 'admin_only_help')' at line 2: SELECT COUNT(*) 
AS expression FROM {comment} c INNER JOIN {node} n ON n.nid = .nid WHERE 
(c.nid = :db_condition_placeholder_0) AND (c.status = 
:db_condition_placeholder_1) AND (n.type <> :db_condition_placeholder_2) ; 
Array ( [:db_condition_placeholder_0] => 873 [:db_condition_placeholder_1] => 
1 [:db_condition_placeholder_2] => admin_only_help ) in PagerDefault-
>execute() (line 74 of /home/mysite/public_html/includes/pager.inc).

Edit with update: I tried completely removing the problem content-type admin_only_help and completely removing the two nodes that belonged to that content type.

Now I have this error:

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 
'WHERE (c.nid = '421') AND (c.status = '1')' at line 2: SELECT COUNT(*) AS expression 
FROM {comment} c INNER JOIN {node} n ON n.nid = .nid WHERE (c.nid = :db_condition_placeholder_0) 
AND (c.status = :db_condition_placeholder_1) ; 
Array ( [:db_condition_placeholder_0] => 421 [:db_condition_placeholder_1] => 1 ) in PagerDefault->execute() 
(line 74 of /home/mysite/public_html/includes/pager.inc).

And I still can't re-index my database.

Answer 1

I have traced this particular problem to the Node View Permissions Module. Disabling this module allowed my search to re-index and be seen by users who are not logged in.

There is a patch available here : https://www.drupal.org/node/2564273