Sphinx fails to bootstrap

[Jacob Atzen]

Hi all,

I'm trying to get Sphinx working and running into a bit of a problem. So far I've installed Postgres 8.3.1, Sphinx 0.9.7 and run the sphinx configure rake task. Now when I try to run the bootstrap rake task I get the output listed below. Anyone know why this is happening?

Rebuilding configurations for production environment

Available models are MergeRequest, Comment and Project

Generating SQL

$ indexer --config '/usr/local/www/gitorious/config/ultrasphinx/production.conf' --all

Sphinx 0.9.7

Copyright (c) 2001-2007, Andrew Aksyonoff

using config file '/usr/local/www/gitorious/config/ultrasphinx/production.conf'...

indexing index 'main'...

ERROR: sql_range_query: ERROR:  syntax error at or near "."

LINE 1: ... category, 'Comment' AS class, 1 AS class_id, user.login AS ...

                                                             ^

 (DSN=pgsql://gitorious:***@localhost:5432/gitorious).

ERROR: index 'main': (no error message).

total 0 docs, 0 bytes

total 0.030 sec, 0.00 bytes/sec, 0.00 docs/sec

Sphinx 0.9.7

Copyright (c) 2001-2007, Andrew Aksyonoff

using config file '/usr/local/www/gitorious/config/ultrasphinx/production.conf'...

WARNING: index 'main': failed to preload schema and docinfos - NOT SERVING

FATAL: no valid indexes to serve

shutdown complete

Failed to start

Done

Please restart your application containers

--
Thanks

- Jacob Atzen

[Jacob Atzen]

Replying to myself in the hopes that someone might now the proper resolution for this. It seems PostgreSQL reserves the keyword user. If one must use it in SQL statements it must be quoted like "user". Doing this by hand in the ultrasphinx/production.conf file seems to do the job - alas, this will of course disappear the next time ultrasphinx:configure is run.

--
Med venlig hilsen
- Jacob Atzen

[teejayvanslyke]

I too am having this issue. After following your suggestion to
enclose instances of "user" in quotes, the indexing failed again
claiming that UNIX_TIMESTAMP wasn't defined. It seems like my version
of Ultrasphinx isn't properly configured for PostgreSQL and is using
MySQL syntax (UNIX_TIMESTAMP is part of MySQL, I think).

I have also tried running the ultrasphinx_migration generator and
migrating my database but that yields errors as well :-(.

> > Thanks- Jacob Atzen

[David]

On Jul 30, 1:28 pm, teejayvanslyke <teejay.vansl...@gmail.com> wrote:
> I too am having this issue. After following your suggestion to
> enclose instances of "user" in quotes, the indexing failed again
> claiming that UNIX_TIMESTAMP wasn't defined. It seems like my version
> of Ultrasphinx isn't properly configured for PostgreSQL and is using
> MySQL syntax (UNIX_TIMESTAMP is part of MySQL, I think).

The gitorious documentation mentions that the main site is deployed on
mysql but that it shouldn't have any db-specific dependencies. I am
also interested in getting this to run on PostgreSQL and ran into
these same issues. I will try the quote trick as well now that I
know.

Regarding UNIX_TIMESTAMP, this might be helpful (and should probably
be mentioned in HACKING):

# ~/src/gitorious
% cat vendor/plugins/ultrasphinx/lib/ultrasphinx/postgresql/
unix_timestamp.sql

/*
mysqlcompat-1.0b3
public domain
modified
UNIX_TIMESTAMP(date)
*/

CREATE OR REPLACE FUNCTION unix_timestamp(timestamp without time zone)
RETURNS bigint AS $$
SELECT EXTRACT(EPOCH FROM $1)::bigint
$$ VOLATILE LANGUAGE SQL;


-David