CatalogSearch API fails with SQL error


Description

When using Catalog Search API to search Service Catalog, the script fails with error in the instance :

FAILED TRYING TO EXECUTE ON CONNECTION glide.ts.22...

The script works fine in a developer instance.

Cause

the sc_cat_item table was not indexed

Resolution

Catalog search api is not working because catalog item table is not indexed on the instance.

Compare it with any OOB instance, it will be indexed and the field Indexed Rows shows number of fields indexed.

When sc_cat_item table is indexed, there will tables with prefix ts_v4_19 in the instance which will be used when performing search. As those table don't exist yet, search throws error.

So to resolve this issue you will have to index the sc_cat_item table.