Upgrading gives search_api table not found error

What version were you updating from? Can you keep trying to run updates to see if you can get past that error? Sometimes it takes multiple attempts.

OK, so the problem is that 2.30 had a bug that caused the tables to get the _1 appended to their names, and that error is fixed in 2.31.

This could be tricky to fix, but here is what I recommend:

1) Try disabling and then uninstalling the search-related modules: oa_search, panopoly_search, search_api:

drush dis -y oa_search
drush dis -y panopoly_search
drush dis -y search_api
drush pm-uninstall oa_search
drush pm-uninstall panopoly_search
drush pm-uninstall search_api

That should delete your existing search index tables.

2) Then, try re-enabling the modules in the reverse order:

drush en -y search_api
drush en -y panopoly_search
drush en -y oa_search

3) Now see if you can revert features and then run updates and clear cache.

Might take some fiddling and there might be some other dependent modules to handle. But uninstalling a module is the only clean way to remove a bad table like this.

I didn't run into this during our 2.30 site upgrades here but will look into this to see if it's a more general problem that we need to fix. Others should post here if they are having a similar problem upgrading from 2.30.

After step (1) in comment #7 when search_api has been uninstalled, clear cache and then you can manually remove those _1 (and _2??) tables. Not sure where _2 tables would have come from though. But that should give you a clean site with no indexes before you start re-enabling things.

You can try reverting to your database backup if you wish, but I don't think it will really help. All of this just related to annoyances in how search_api handles its index tables so all we are trying to do here is get rid of those old indexes in a way that allows you to move forward with clean indexes.

I have also tables numbered _1 and _2 from search_api_db

I also realized that the urls in search_api_db_user_index_search_api_url have my http://localhost/user/0. Would I have problems when I use in the production server?

candelas: If you didn't get any update errors you can safely remove the old _1 and _2 tables. Your production server will create it's own index and will update the url fields accordingly. But please try not to confuse this thread. Keep this thread on search_api errors when upgrading from 2.30.

The search works on content but not in users. It gives the error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_name' in 'where clause'

It works before update 3.1. I have reverted all Features. Now I am going to try with #7

To be able to uninstall search_api, I had to uninstall search_api_db, search_api_facetapi, search_api_solr,
search_api_views. I went to the database and I had still tables with _1. So I disable and uninstall search and delete all the tables.

The I enabled and all got broken :)

I take a look to a fresh install OA 7.x-2.26 and already I have duplicated tables with _
search_api_db_database_node_index_field_oa_related_1
search_api_db_database_node_index_field_oa_related_field_par_1
search_api_db_database_node_index_field_oa_related_snippet_b_1
search_api_db_user_index_field_user_display_name_1
search_api_db_user_index_mail_1
search_api_db_user_index_name_1
search_api_db_user_index_og_user_node_1
search_api_db_user_index_search_api_language_1
search_api_db_user_index_search_api_url_1

So now I am going to make a fresh install 7.x-2.31 to see and report.

In a fresh OA 7.x-2.31 install I get also the same tables duplicated.

In the fresh install the user search works.

In the fresh install, Panopoly Search is already overriden.
Strongarm LockOverridden
facetapi:block_cache:search_api@database_node_index facetapi:block_cache:search_api@node_index search_active_modules search_api_facets_search_ids

I reverted and the user search and the content too.

Not sure how you got those dup tables on a fresh install. Are you sure you removed your previous database. None of our fresh installs here have those _1 and _2 tables.

The overridden panopoly_search is already mentioned in the release notes here: https://www.drupal.org/node/2433041 so that is not a problem.

mysql> show tables like '%search%';
+----------------------------------------------------------+
| Tables_in_test_install_oa (%search%) |
+----------------------------------------------------------+
| cache_search_api_solr |
| search_api_db_database_node_index |
| search_api_db_database_node_index_field_oa_related |
| search_api_db_database_node_index_oa_section_ref_title |
| search_api_db_database_node_index_og_group_ref_title |
| search_api_db_database_node_index_search_api_access_node |
| search_api_db_database_node_index_text |
| search_api_db_user_index |
| search_api_db_user_index_name |
| search_api_db_user_index_og_user_node |
| search_api_db_user_index_search_api_language |
| search_api_db_user_index_search_api_url |
| search_api_db_user_index_text |
| search_api_index |
| search_api_item |
| search_api_server |
| search_api_task |
| search_dataset |
| search_index |
| search_node_links |
| search_total |
+----------------------------------------------------------+
21 rows in set (0.00 sec)

Happened also to me (a OA2 updated from 2.30 to 2.31 today following all steps as per upgrade webinar). I too noticed that (All spaces/This space/Users) searches don't work anymore just after the upgrade.
The OA2 status reports all green (no errors): if I look for any type of content (example if I look for a user) I hit a blank page with the URL ending with the word I searched (if I lookg for the word "test" the URL is in my case "http://192.168.0.252/turbosol/search/node/test").

Below my tables:

Database changed
MariaDB [turbosol]> show tables like '%search%';
+----------------------------------------------------------------+
| Tables_in_turbosol (%search%)                                  |
+----------------------------------------------------------------+
| cache_search_api_solr                                          |
| search_api_db_database_node_index_author                       |
| search_api_db_database_node_index_created                      |
| search_api_db_database_node_index_field_oa_related_1           |
| search_api_db_database_node_index_field_oa_related_field_parag |
| search_api_db_database_node_index_field_oa_related_snippet_bod |
| search_api_db_database_node_index_nid                          |
| search_api_db_database_node_index_search_api_language          |
| search_api_db_database_node_index_search_api_url               |
| search_api_db_database_node_index_status                       |
| search_api_db_database_node_index_type                         |
| search_api_db_database_node_index_url                          |
| search_api_db_user_index_field_user_display_name               |
| search_api_db_user_index_mail                                  |
| search_api_db_user_index_og_user_node_1                        |
| search_api_db_user_index_search_api_language_1                 |
| search_api_db_user_index_search_api_url_1                      |
| search_api_db_user_index_text                                  |
| search_api_index                                               |
| search_api_item                                                |
| search_api_server                                              |
| search_api_task                                                |
| search_dataset                                                 |
| search_index                                                   |
| search_node_links                                              |
| search_total                                                   |
+----------------------------------------------------------------+
26 rows in set (0.00 sec)

Greets.

I use the browser to install and I choose Quick installation. I deleted all the tables on the database. I am reinstalling again 2.31. I removed all modules and libraries that I had in sites (from tests from before, thought they were not enabled). Now I only got one duplicated table.

search_api_db_user_index_text_1

If you already have the old _1 tables, I wonder if you can simply go to the admin/config/search/search_api page and either delete and reindex your content, or disable the indexes and then clear cache and revert features, and then re-enable them?

OK, I just ran into a site that has this same problem with the search_api tables. We are digging into this to try and figure out what's causing this. It's something in the search_api module update that was part of the newest Panopoly.

Hello @mpotter

Thanks for your time and patience :)
I followed your advice and delete all index in admin/config/search/settings and delete the duplicate tables. I wait for it to reindex and searching in content works, but in users I keep getting

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_name' in 'where clause'

I don't know where to search for that column. In a fresh install it works.

I'm assuming this is related.

See attached image of Drush updb output.

Try the patches in #2436351: Patch search_api_db to be a bit less brittle

Should at least fix the update error, not sure if there's more than one error being discussed now.

The patches referenced in #25 allowed my "drush updb" to work, getting around the problem with search_api_db update 7104 and then the table not found errors.

If you are having trouble with the 2.31 update and getting search_api errors during the drush updb or the feature revert, please try the patches and let us know if it helps so we can determine if we should release 2.32 to address these issues.

I received the same error yesterday upgrading from v2.25 to v2.31. Will try the patches.

I did have to run update.php several times to capture most of the updates. However, each time I run it, the following 4 updates keep appearing and don't seem to process correctly:

panopoly_search module
• 7005 - Make sure search is configured to index Panelizer and reindex all nodes.

search_api_db module
• 7104 - Use a single full text table per index.
• 7105 - Add a (word, field_name) covering index to fulltext tables.
• 7106 - Change full text score from float to int.

When I upgraded the OA distribuition, I used update.php and from 12 changes, I didn't get any error.
Now, I downloaded Panopoly dev and apply the patch #25 and put the Panopoly Search module in substitution of the old folder. This time with drush, drush updatedb and it says 'No database updates required'.

Do I have to change all Panopoly? Any tips... My user search keeps not working.

To test this issue you either need to roll back to your OA 2.30 database, then apply the patches to Panopoly given in #25 and then try re-running the "drush updb" and feature revert process. If you already did the drush updb then nothing new is going to happen.

This was mainly for people who were stuck during the update process and couldn't get past the missing tables when doing drush updb or features revert.

@mpotter should I start a different issue with the problem that searching users doesn't work? Thanks :)

Perhaps, as I cannot reproduce any issue with searching users and this thread is more about search_api errors that are blocking people from running updates or reverting features. But all I'll likely say in a different issue is the same procedure in #7 to reset all of your search indexes.

The patches for fixing these issues were committed to Panopoly 1.18 and included in Open Atrium 2.32. This should help people upgrading from v2.30 and people who are stuck unable to run updates because of the search_api tables.

I've just upgraded straight to 2.32 and I've received the same error. So going from a pre 2.30 site to 2.32 still breaks search in the same way.

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'sitedb.search_api_db_database_node_index' doesn't exist

Marked as duplicate: #2444599: User search and Node search both broken after upgrade to 7.x-2.32

Related? #2443595: Search function for users or nodes not working for 7.x-2.32

Possibly also related: #1347438: Reverting Features Drops Search API DB tables

I have been able to fix the issue by disabling the 'Database server' and 'index' in the search api settings.

I then 'revert'ed the server and the index. It now seems to work OK.

The solution in #37 seems to be what is needed on some sites. Panopoly added that same instructions to their 1.16 release notes here: https://www.drupal.org/node/2425263

I had hoped the search_api patches we added in v2.32 would help with this. They seemed to have helped with some issues but not others.

Since this only affects some people and some upgrades I'm just going to copy those instructions into the OA Release Notes and direct people there. Ultimately still looks like a search_api issue but since it only affects some updates in some distros I'll be surprised if it gets much attention.

This is close to being fixed. I ran through the update with the special instructions for this release as shown on https://www.drupal.org/node/2443025. I also manually deleted extra tables in my database with _1, _2 etc. I got everything working fine and then ran drush updb again to be sure all updates finished, but they didnt. I got errors and search was broken again. I was able to fix search by going through the steps again and manually deleting the extra tables, but I was not able to get through the drush updb without errors and without breaking search. Here are the errors:

Table search_api_task already exists.                              [error]
Performed update: search_api_update_7116                           [ok]

So, I manually deleted the search_api_task table from the database and ran the drush updb again and this time it finished clean. Just letting you know in case anyone else can't get through that last one either.

A very similar error occurs in the latest OA version (openatrium-7.x-2.617).
In fact I was using the earlier version and then when I saw the bug I reinstalled from new.
First the cron jobs does't start

Then I see an error with this error in the log :

PDOException while trying to index item 0 on index User Index: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'site_drupal.search_api_db_user_index_name' doesn't exist: DELETE FROM {search_api_db_user_index_name} 
WHERE  (item_id = :db_condition_placeholder_0) ; Array
(
    [:db_condition_placeholder_0] => 0
)
 in SearchApiDbService->indexItem() (line 875 of /var/data/sites/mysite/public_html/profiles/openatrium/modules/contrib/search_api_db/service.inc).
PDOException while trying to index item 1 on index User Index: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'site_drupal.search_api_db_user_index_name' doesn't exist: DELETE FROM {search_api_db_user_index_name} 
WHERE  (item_id = :db_condition_placeholder_0) ; Array
(
    [:db_condition_placeholder_0] => 1
)
 in SearchApiDbService->indexItem() (line 875 of /var/data/sites/mysite/public_html/profiles/openatrium/modules/contrib/search_api_db/service.inc).

I try on a second site with the same error.

Thanks for help ;-)

This also looks like related to this: #1347438: Reverting Features Drops Search API DB tables