Hi Tom
I think the datetime columns could be bigints - which aren't handled as such by
default in TS. There has been a patch which could help this though - it's not
in 2.0.5, and I haven't released a new TS just yet... so, can you use the
rails3 branch of the github repo as your source?
git://github.com/freelancing-god/thinking-sphinx.git
If so, try setting use_64_bit to true in your sphinx.yml file - this will
ensure all datetime values are casted to bigints instead of ints.
Cheers
--
Pat
On 10/08/2011, at 2:49 AM, Tom Cocca wrote:
> Hello,
>
> I am running thinking-sphinx 2.0.5, Sphinx 0.9.9-id64-release (r2117)
> and PG 9.0.2 and I am getting the following error when I run rake
> ts:rebuild
>
>
> Generating Configuration to /home/deploy/rails/generic_updater/
> releases/20110809141657/config/production.sphinx.conf
> Sphinx 0.9.9-id64-release (r2117)
> Copyright (c) 2001-2009, Andrew Aksyonoff
>
> using config file '/home/deploy/rails/generic_updater/releases/
> 20110809141657/config/production.sphinx.conf'...
> indexing index 'mls_property_core'...
> ERROR: index 'mls_property_core': sql_range_query: ERROR: integer out
> of range
> (DSN=pgsql://db_user:***@postgres.mydomain.com:6432/mls_properties).
> total 423927 docs, 187383324 bytes
> total 757.711 sec, 247301 bytes/sec, 559.48 docs/sec
> distributed index 'mls_property' can not be directly indexed;
> skipping.
> total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
> total 638 writes, 1.737 sec, 953.1 kb/call avg, 2.7 msec/call avg
> Started successfully (pid 3592).
>
>
> The Index looks as follows:
>
> define_index do
> indexes description, street_number, street_name, mls_id
> indexes state.abbreviation, :as => :state
> indexes town.name, :as => :town
> indexes list_office.mls_id, :as => :list_office_mls
> indexes list_office.name, :as => :list_office_name
> indexes list_agent.mls_id, :as => :list_agent_mls
> indexes "agents.first_name || ' ' || agents.last_name", :as
> => :list_agent_name
> indexes sale_office.mls_id, :as => :sale_office_mls
> indexes sale_office.name, :as => :sale_office_name
> indexes sale_agent.mls_id, :as => :sale_agent_mls
> indexes "properties.town_id || 'x' ||
> properties.neighborhood_id", :as => :town_neighborhood
> indexes "(properties.full_bathrooms + properties.half_bathrooms +
> properties.quarter_bathrooms +
> properties.three_quarter_bathrooms)", :as => :total_bathrooms
> indexes zip, :as => :zip_code
> group_by 'list_agent_name', 'town_neighborhood', 'total_bathrooms'
>
> set_property :enable_star => true
> set_property :min_prefix_len => 3
>
> has neighborhood_id, state_id, town_id, property_type_id,
> status_id, service_id
> has bedrooms, full_bathrooms, half_bathrooms, price,
> square_footage, acres, year_built, parking_spaces, taxes
> has parking_available, waterfront, beachfront, basement,
> bank_owned, opt_out
> has mls_created_at, mls_updated_at
> has open_houses.start_time, :as => :open_house_start
> has (:id), :as => :property_id
> end
>
> With a generated production.sphinx.conf file like so:
>
>
> indexer
> {
> }
>
> searchd
> {
> listen = 127.0.0.1:9312
> log = /home/deploy/rails/generic_updater/releases/20110809141657/log/
> searchd.log
> query_log = /home/deploy/rails/generic_updater/releases/
> 20110809141657/log/searchd.query.log
> pid_file = /home/deploy/rails/generic_updater/releases/
> 20110809141657/log/searchd.production.pid
> }
>
> source mls_property_core_0
> {
> type = pgsql
> sql_host = postgres.mydomain.com
> sql_user = db_user
> sql_pass = db_pass
> sql_db = mls_properties
> sql_port = 6432
> sql_query_pre = SET TIME ZONE 'UTC'
> sql_query = SELECT "properties"."id" * 1::INT8 + 0 AS "id" ,
> "properties"."description" AS "description",
> "properties"."street_number" AS "street_number",
> "properties"."street_name" AS "street_name", "properties"."mls_id" AS
> "mls_id", "states"."abbreviation" AS "state", "towns"."name" AS
> "town", "offices"."mls_id" AS "list_office_mls", "offices"."name" AS
> "list_office_name", "agents"."mls_id" AS "list_agent_mls",
> agents.first_name || ' ' || agents.last_name AS "list_agent_name",
> "sale_offices_properties"."mls_id" AS "sale_office_mls",
> "sale_offices_properties"."name" AS "sale_office_name",
> "sale_agents_properties"."mls_id" AS "sale_agent_mls",
> properties.town_id || 'x' || properties.neighborhood_id AS
> "town_neighborhood", (properties.full_bathrooms +
> properties.half_bathrooms + properties.quarter_bathrooms +
> properties.three_quarter_bathrooms) AS "total_bathrooms",
> "properties"."zip" AS "zip_code", "properties"."id" AS
> "sphinx_internal_id", 0 AS "sphinx_deleted", 1814387568 AS
> "class_crc", "properties"."neighborhood_id" AS "neighborhood_id",
> "properties"."state_id" AS "state_id", "properties"."town_id" AS
> "town_id", "properties"."property_type_id" AS "property_type_id",
> "properties"."status_id" AS "status_id", "properties"."service_id" AS
> "service_id", "properties"."bedrooms" AS "bedrooms",
> "properties"."full_bathrooms" AS "full_bathrooms",
> "properties"."half_bathrooms" AS "half_bathrooms",
> "properties"."price" AS "price", "properties"."square_footage" AS
> "square_footage", "properties"."acres" AS "acres",
> "properties"."year_built" AS "year_built",
> "properties"."parking_spaces" AS "parking_spaces",
> "properties"."taxes" AS "taxes", "properties"."parking_available" AS
> "parking_available", "properties"."waterfront" AS "waterfront",
> "properties"."beachfront" AS "beachfront", "properties"."basement" AS
> "basement", "properties"."bank_owned" AS "bank_owned",
> "properties"."opt_out" AS "opt_out", cast(extract(epoch from
> "properties"."mls_created_at") as int) AS "mls_created_at",
> cast(extract(epoch from "properties"."mls_updated_at") as int) AS
> "mls_updated_at",
> array_to_string(array_accum(COALESCE(cast(extract(epoch from
> "open_houses"."start_time") as int), '0')), ',') AS
> "open_house_start", "properties"."id" AS "property_id" FROM
> "properties" LEFT OUTER JOIN "states" ON "states"."id" =
> "properties"."state_id" LEFT OUTER JOIN "towns" ON "towns"."id" =
> "properties"."town_id" LEFT OUTER JOIN "offices" ON "offices"."id" =
> "properties"."list_office_id" LEFT OUTER JOIN "agents" ON
> "agents"."id" = "properties"."list_agent_id" LEFT OUTER JOIN "offices"
> "sale_offices_properties" ON "sale_offices_properties"."id" =
> "properties"."sale_office_id" LEFT OUTER JOIN "agents"
> "sale_agents_properties" ON "sale_agents_properties"."id" =
> "properties"."sale_agent_id" LEFT OUTER JOIN "open_houses" ON
> "open_houses"."property_id" = "properties"."id" WHERE
> ("properties"."id" >= $start AND "properties"."id" <= $end) GROUP BY
> "properties"."id", "properties"."description",
> "properties"."street_number", "properties"."street_name",
> "properties"."mls_id", "states"."abbreviation", "towns"."name",
> "offices"."mls_id", "offices"."name", "agents"."mls_id",
> "sale_offices_properties"."mls_id", "sale_offices_properties"."name",
> "sale_agents_properties"."mls_id", "properties"."zip",
> "properties"."id", "properties"."neighborhood_id",
> "properties"."state_id", "properties"."town_id",
> "properties"."property_type_id", "properties"."status_id",
> "properties"."service_id", "properties"."bedrooms",
> "properties"."full_bathrooms", "properties"."half_bathrooms",
> "properties"."price", "properties"."square_footage",
> "properties"."acres", "properties"."year_built",
> "properties"."parking_spaces", "properties"."taxes",
> "properties"."parking_available", "properties"."waterfront",
> "properties"."beachfront", "properties"."basement",
> "properties"."bank_owned", "properties"."opt_out",
> "properties"."mls_created_at", "properties"."mls_updated_at",
> "properties"."id", list_agent_name, town_neighborhood, total_bathrooms
> sql_query_range = SELECT COALESCE(MIN("id"), 1::bigint),
> COALESCE(MAX("id"), 1::bigint) FROM "properties"
> sql_attr_uint = sphinx_internal_id
> sql_attr_uint = sphinx_deleted
> sql_attr_uint = class_crc
> sql_attr_uint = neighborhood_id
> sql_attr_uint = state_id
> sql_attr_uint = town_id
> sql_attr_uint = property_type_id
> sql_attr_uint = status_id
> sql_attr_uint = service_id
> sql_attr_uint = bedrooms
> sql_attr_uint = full_bathrooms
> sql_attr_uint = half_bathrooms
> sql_attr_uint = price
> sql_attr_uint = square_footage
> sql_attr_uint = year_built
> sql_attr_uint = parking_spaces
> sql_attr_uint = property_id
> sql_attr_bool = parking_available
> sql_attr_bool = waterfront
> sql_attr_bool = beachfront
> sql_attr_bool = basement
> sql_attr_bool = bank_owned
> sql_attr_bool = opt_out
> sql_attr_timestamp = mls_created_at
> sql_attr_timestamp = mls_updated_at
> sql_attr_float = acres
> sql_attr_float = taxes
> sql_attr_multi = uint open_house_start from field
> sql_query_info = SELECT * FROM "properties" WHERE "id" = (($id -
> 0) / 1)
> }
>
> index mls_property_core
> {
> source = mls_property_core_0
> path = /home/deploy/rails/generic_updater/releases/20110809141657/db/
> sphinx/production/mls_property_core
> charset_type = utf-8
> min_prefix_len = 3
> enable_star = 1
> }
>
> index mls_property
> {
> type = distributed
> local = mls_property_core
> }
>
>
> I have narrowed it down to 1 of these pieces in the index (I believe).
>
> has mls_created_at, mls_updated_at
> has open_houses.start_time, :as => :open_house_start
>
> Any idea what is causing this issue or how to fix it? I have the id64
> version of sphinx and the latest released gem of TS which is what
> issue #97 (https://github.com/freelancing-god/thinking-sphinx/issues/
> 97) said I needed?
>
> Any ideas would be incredibly appreciated.
>
> Thanks,
> ~ Tom
>
> --
> You received this message because you are subscribed to the Google Groups
> "Thinking Sphinx" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/thinking-sphinx?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Thinking Sphinx" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/thinking-sphinx?hl=en.
