Fix definition of the default node index

I am also experiencing this error, and the log definitely says it is coming from search_api_db.

Versions:

I am currently running: Search API 7.x-1.3, Search Facets 7.x-1.3, and Database Search 7.x-1.0-beta3 [which I just downloaded today, so they are the latest releases but not the dev versions]

What I did:

a) Installed Drupal with standard install profile, and downloaded/enabled these modules (plus Entity API and CTools).

b) Created a second taxonomy and added it as an additional field to the core-provided Article content type. Created two simple Article content items.

c) Went to the Search API config page and enabled the Database back-end.

d) Set the default Nodes search type to use this back-end, and enabled it. As a note, I chose to have just the Title and Body fields indexed -- there was a blank line in the select box for fields but when I chose that, I got an error, so I set it to just index the title and body.

e) On the Nodes search type status page, it says nothing is indexed, so I clicked "Index all". It came back with an error that said "Couldn't index items. Check the logs for details." The status still says that neither of my 2 content items is indexed.

f) Went to the recent log messages (dblog) report. The error message says the "type" is "search_api_db", and the error message is:

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 (item_id = '2')' at line 1

(there's another one that says item_id = 1 -- I assume that's for the two nodes).

I will see if I can debug this... but for the moment I would say it is a critical error since nothing can be indexed.

Looks like the watchdog message is coming from line 318 in service.inc in method indexItems():

      catch (Exception $e) {
        // We just log the error, hoping we can index the other items.
        watchdog('search_api_db', check_plain($e->getMessage()), NULL, WATCHDOG_WARNING);
      }

And this is coming from the indexItem() method.

I put in some debugging statements and the query that is causing the problem is near the top of the method where it says:

        $table = $fields[$name]['table'];
        $boost = $fields[$name]['boost'];
        db_delete($table, $this->query_options)
            ->condition('item_id', $id)
            ->execute();

It is throwing this exception when trying to delete where the field is 'search_api_fulltext'.

The reason appears to be that $table is empty for this field.

This is apparently related to a bunch of other PHP warnings I have gotten from this module with 'search_api_fulltext' in them, which have been logged in other issues I have seen on the Search API module... anyway something is wrong with this field setup apparently?

As one final note. I went to the Fields tab of the Search API setup page for this index, and I changed the Title and Body field from being "fulltext" to being "string", and the indexing now works. So it seems to be a problem in indexing "fulltext" fields only.

I am not sure what "fulltext" really is supposed to be, but without the title/body fields set to "fulltext", I can't effectively search. Every keyword I enter gives me all the articles as matching, even something like "asdlfqsad;fksajdl;fj" which is not in either one of them.

So, I am guessing this module is totally broken...

Thanks for reporting!
Just took a look and it seems I forgot to keep the definition of the default node index up to date, causing your error. Re-saving the form with body and title having the proper type (“Fulltext”) should work, too, but please see if uninstalling the module and re-installing after applying the attached patch works, too!

Um... I'm confused about #5. Which form are you saying I should be able to re-save and make everything work (even without the patch perhaps)?

The Fields form of the index.

OK. So I went to admin/config/search/search_api/index/default_node_index/fields (the fields page for my index), changed both Title and Body to "Fulltext", and clicked Save.

Then I went to the Status tab (admin/config/search/search_api/index/default_node_index/status) and clicked Index Now. It reported Success, and said 2 of 2 items are indexed (it's a small site for testing).

And now I can go to the Page I set up and search for things in the title and body -- working!

So. I also tested the patch:
- I disabled and uninstalled all of the Search API-related modules, including Facets API, and cleared the cache just in case.
- I applied the patch to search_api.install
- I enabled Search API, Database Search, Facet API, Search Facets, Current Search Blocks, and Search Pages.
- Went to admin/config/search/search_api and it showed a red X next to the Default Node Index.
- Clicked "Add server", and created a Database Service with minimum word = 3 characters.
- Edited the settings for the default node index that had been created for me. Chose my new database server as the server. Clicked Save. Then enabled it.
- Visited the Fields tab, and noted that it had title/body as Fulltext.
- Went to the Status tab, and clicked "Index now". Success!
- Created a Search Page on this index. Chose both Title and Body as the search fields.
- Tested the search page -- seems to be working!

So, I think this patch fixes the problem. I am not seeing any more database errors and all the errors I was seeing before about the fulltext stuff seem to have gone away.

Thanks!

Thanks for testing, good to hear it works!
Committed.