TS3.0.3 - query error: no field 'sphinx_internal_class_name' found in schema

[bkieser]

I am getting this error: SELECT * FROM site_core WHERE MATCH('( test ) | ( test ) @sphinx_internal_class_name (Site)') AND sphinx_deleted = 0 ORDER BY @weight DESC, load_count DESC LIMIT 0, 8 OPTION max_matches=1000

However, @sphinx_internal_class_name is a Thinking Sphinx generation!

This was after an upgrade from TS 2.

app/indices/site_index.rb:

ThinkingSphinx::Index.define :site, :with => :active_record do set_property :enable_star => 1 set_property :min_infix_len => 3 indexes protected_areas.english_name, :sortable => true, :as => :english_name indexes protected_areas.local_name, :sortable => true, :as => :local_name indexes protected_areas.countries.name, :sortable => true, :as => :country_name indexes protected_areas.countries.iso, :as => :iso indexes protected_areas.countries.iso_3, :as => :iso_3 indexes protected_areas.countries.region.name, :as => :region_name indexes protected_areas.iucn_category.name, :sortable => true, :as => :iucn_cat indexes protected_areas.designation.english_name, :sortable => true, :as => :designation indexes protected_areas.designation.name, :sortable => true, :as => :designation_local has protected_areas.countries(:region_id), :as => :region_id, :multi => true, :type => :integer, :facet => true has protected_areas.countries(:id), :as => :country_id, :facet => true, :type => :integer has protected_areas.iucn_category_id, :facet => true has protected_areas.designation_id, :facet => true has protected_areas.sphinx_marine, :facet => :true, :as => :marine, :multi => true, :type => :integer has protected_areas.is_official, :type => :integer, :facet => true has load_count, :as => :load_count

 has 'radians(ST_X("protected_areas"."centre_geom"))',  :as => :longitude, :type => :float
 has 'radians(ST_Y("protected_areas"."centre_geom"))', :as => :latitude, :type => :float

 group_by :longitude
 group_by :latitude
 group_by :marine

end

[pat]

What's the code you've written that calls this search? And have you run rake ts:rebuild since upgrading?

[bkieser]

Hi Pat,

I had run ts:rebuild, but doing it again produces an error: ERROR: index 'site_core': sql_range_query: ERROR: function array_agg() does not exist LINE 1: ...al_class", 0 AS "sphinx_deleted", array_to_string(array_agg(... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. (DSN=pgsql://postgres:***@localhost:5432/ppe_development).

which is a bit odd because it's a perfectly valid postgres function. Could this be to do with the fact that it's interfacing to searchd as mysql?

[bkieser]

Pat, thanks to your suggestion of running the rebuild, I was able to determine that it's these lines that are causing the aray_agg error reported above:

 has protected_areas.countries(:region_id), :as => :region_id, :multi => true, :type => :integer, :facet => true
 has protected_areas.countries(:id), :as => :country_id, :facet => true, :type => :integer

[bkieser]

Converting to: has protected_areas.countries.region_id, :as => :region_id, :multi => true, :type => :integer, :facet => true has protected_areas.countries.id, :as => :country_id, :facet => true, :type => :integer

solves the problem. Once the rebuild has finished, I will retest the search.

[bkieser]

Pat, the rebuild completed successfully but the problem still persists.

[bkieser]

Hi Pat, can I check versions? I am running TS 3.0.3 and Sphinx 2.0.7. Are these compatible?

[pat]

I'm running Sphinx 2.0.6 with TS 3.0.3, but there's almost no difference with 2.0.7, so what you're running with should be fine. What's the search query you're running when this error occurs, and what's the sql_query value for the site_core source in the generated config/development.sphinx.conf?

[bkieser]

The config/development.sphinx.conf query is below, I included the range as well.

The SQL that's being run is: SELECT * FROM site_core WHERE MATCH('( test ) | ( test ) @sphinx_internal_class_name (Site)') AND sphinx_deleted = 0 ORDER BY @weight DESC, load_count DESC LIMIT 0, 8 OPTION max_matches=1000

---

sql_query = SELECT "sites"."id" * 2 + 0 AS "id", 'Site' AS "sphinx_internal_class_name", array_to_string(array_agg(protected_areas."english_name"), ' ') AS "english_name", array_to_string(array_agg(protected_areas."local_name"), ' ') AS "local_name", array_to_string(array_agg(countries."name"), ' ') AS "country_name", array_to_string(array_agg(countries."iso"), ' ') AS "iso", array_to_string(array_agg(countries."iso_3"), ' ') AS "iso_3", array_to_string(array_agg(regions."name"), ' ') AS "region_name", array_to_string(array_agg(iucn_categories."name"), ' ') AS "iucn_cat", array_to_string(array_agg(designations."english_name"), ' ') AS "designation", array_to_string(array_agg(designations."name"), ' ') AS "designation_local", "sites"."id" AS "sphinx_internal_id", 'Site' AS "sphinx_internal_class", 0 AS "sphinx_deleted", array_to_string(array_agg(countries."region_id"), ',') AS "region_id", array_to_string(array_agg(countries."id"), ',') AS "country_id", array_to_string(array_agg(protected_areas."iucn_category_id"), ',') AS "iucn_category_id", array_to_string(array_agg(protected_areas."designation_id"), ',') AS "designation_id", array_to_string(array_agg(protected_areas."sphinx_marine"), ',') AS "marine", array_to_string(array_agg(protected_areas."is_official"), ',') AS "is_official", "sites"."load_count" AS "load_count", radians(ST_X("protected_areas"."centre_geom")) AS "longitude", radians(ST_Y("protected_areas"."centre_geom")) AS "latitude" FROM "sites" LEFT OUTER JOIN "protected_areas" ON "protected_areas"."site_id" = "sites"."id" LEFT OUTER JOIN "countries_protected_areas" ON "countries_protected_areas"."protected_area_id" = "protected_areas"."id" LEFT OUTER JOIN "countries" ON "countries"."id" = "countries_protected_areas"."country_id" LEFT OUTER JOIN "regions" ON "regions"."id" = "countries"."region_id" LEFT OUTER JOIN "iucn_categories" ON "iucn_categories"."id" = "protected_areas"."iucn_category_id" LEFT OUTER JOIN "designations" ON "designations"."id" = "protected_areas"."designation_id" WHERE ("sites"."id" >= $start AND "sites"."id" <= $end) GROUP BY "sites"."id", "sites"."id", "sites"."load_count", longitude, latitude, marine sql_query_range = SELECT COALESCE(MIN("sites"."id"), 1), COALESCE(MAX("sites"."id"), 1) FROM "sites"

[pat]

The field's definitely there - it's the second column in the sql_query value. What's the output when you run ts:rebuild?

[glaszig]

same thing here. @bkieser did you get to any solution?

[pat]

@glaszig picking up from where @bkieser and I left off - what's the output when you run the ts:rebuild rake task?