ThinkingSphinx::SphinxError: unknown column: 'created_at'

[theotherdon]

Overview

First off, thanks for all of the hard work on this gem.

I'm trying to upgrade from v3 to v5, but I'm hitting a snag that's preventing me from upgrading. When attempting to add a record to the index, I get an error that says ThinkingSphinx::SphinxError: unknown column: 'created_at'.

Versions

• thinking-sphinx: 5.0.0
• Sphinx: 3.3.1
• Rails: 4.2.11.1
• MySQL: 5.7.32

Steps to reproduce

Unfortunately, I think the steps to reproduce are going to be a bit tricky as I'm working on this in a big legacy app for a client. That said, the steps that I'm taking to reproduce are:

1. Upgrade the Sphinx binary.
2. Upgrade thinking-sphinx.
3. Clear out old Sphinx data with bundle exec rake ts:clear.
4. Regenerate the configuration with bundle exec rake ts:configure.
5. Regenerate the index with bundle exec rake ts:index INDEX_FILTER=term.
6. Start the Rails console and run: Term.realtime_index(123).
7. Observe the following error is thrown (abbreviated): ThinkingSphinx::SphinxError: unknown column: 'created_at' - REPLACE INTO term_core.

What I've tried

I tried clearing the index data (as mentioned in #1043) with bundle exec rake ts:clear, regenerating the configuration, and then regenerating the indexes with bundle exec rake ts:index INDEX_FILTER=term (checking to make sure that all data was removed from db/sphinx/development, but no dice. I've tried Googling around and looking through the docs. However, I can't find anything to point me in the right direction to figure out what's going on. Additionally, we have a field in our index file for the created_at column: has created_at, type: :timestamp. Any idea what's going on here or what I can try?

[pat]

Hi Donald - glad to hear you're upgrading, but this problem's definitely an annoying one. And I'm afraid I don't yet know what the problem is… but it's sounding like Sphinx doesn't know that attribute exists, even though it's in the index definition on the Ruby side of things. 🤔

Can you confirm that the attribute definition is in the term_core index in the generated Sphinx configuration file? (config/development.sphinx.conf) - and, just to confirm, has the Sphinx daemon been stopped (ideally prior to running ts:clear) and started again (prior to running ts:index)?

[theotherdon]

Wow, thanks for the lightning-fast response.

Here's what I'm seeing in the config/development.sphinx.conf file (abbreviated with ellipses). This is what you're looking for, right?

index term_core
{
...
    rt_attr_timestamp = created_at
...
}

Can you confirm that the attribute definition is in the term_core index in the generated Sphinx configuration file? (config/development.sphinx.conf)

Yes. I accidentally left that out of the steps to reproduce. I just ran the following command one more time to be sure: bundle exec rake ts:stop && bundle exec rake ts:clear && bundle exec rake ts:configure && bundle exec rake ts:index INDEX_FILTER=term && bundle exec rake ts:start

and, just to confirm, has the Sphinx daemon been stopped (ideally prior to running ts:clear) and started again (prior to running ts:index)?

[pat]

This doesn't seem to be the error that you're dealing with - but: with real-time indices, Sphinx needs to be running before you call ts:index (because it's interacting with the daemon to insert data, rather than processing index files separately to the daemon). So - does putting ts:start before ts:index help?

[theotherdon]

Gotcha. I tried that and it unfortunately didn't help. Still getting the same unknown column error. Here's the command I ran for reference: bundle exec rake ts:stop && bundle exec rake ts:clear && bundle exec rake ts:configure && bundle exec rake ts:start && bundle exec rake ts:index INDEX_FILTER=term

[pat]

Hmm… can you confirm - after running ts:stop - that there are no searchd processes running on your machine? ps aux | grep searchd should do the trick.

It's just that I'm wondering whether there's an old daemon that for some reason hasn't stopped and is getting in the way of things.

[theotherdon]

Right, that's a good point. Unfortunately, it doesn't look like there are any processes running. I ran ps aux | grep searchd to check to make sure no searchd processes were running. I attached a screenshot for reference.

[pat]

Sorry for the slow response - the chaos of moving house - but the screenshot there is very useful! I hadn't realised you were now using Sphinx 3.3 - they've removed timestamp attributes, and I've got a new release forthcoming that stores these values as integers (which is not a big deal, because Sphinx was expecting UNIX-epoch time values as integers in previous releases).

If you want to give the latest code a shot to confirm it works for you, you can update your Gemfile as follows:

gem "thinking-sphinx", "~> 5.0",
  :git    => "https://github.com/pat/thinking-sphinx.git",
  :branch => "develop",
  :ref    => "7d3bcdbf85"

And I'll try to get a new gem release out soon!

[pat]

Just published v5.1.0 which has the Sphinx v3.3 fixes, so hopefully that gets everything working again for you!

[theotherdon]

Thanks @pat! Sorry for the delay in getting back to you. I'm trying it out now. I'll let you know how it goes.

[theotherdon]

@pat Sorry for the delay in getting back to you again. Looks like that release did the trick. Thanks for your help on this!