6

I have an oddly specific problem. Let's say I have this table in a Rails project:

create_table "documents", force: true do |t|
   t.text    "tags"
end
add_index "documents", ["tags"], name: "index_documents_on_tags", type: :fulltext

I have an integration test that creates a few Document instances with varying tag combinations, which the method I'm trying to test should return by way of a fulltext search. Unfortunately, it turns out that InnoDB doesn't rebuild its fulltext indices until the current transaction ends, meaning that my search comes up empty.

If I build the test data in fixtures (e.g. in advance, outside of the transaction that rspec uses for each test) it all works fine, but is there any way for me to tweak the data and run a search against it within the same test?

Andrew
  • 318
  • 1
  • 6

3 Answers3

2

Tricky but fixable. Bear with me.

Step 1

Add this wonderful helper by @mattias (https://stackoverflow.com/a/7703220/537648)

def without_transactional_fixtures(&block)
  self.use_transactional_fixtures = false

  before(:all) do
    DatabaseCleaner.strategy = :truncation
  end

  yield

  after(:all) do
    DatabaseCleaner.strategy = :transaction
  end
end

Step 2

Add this before block to your rspec examples

Sample usage:

describe "doing my thing" do
  before do
    # This will rebuild the indexes. You need it before each example
    ActiveRecord::Base.connection.execute("ANALYZE TABLE `searchables`")
  end

  without_transactional_fixtures do
    it "does something without transaction fixtures" do
      ...
    end
  end
end

Bonus Step

If you are getting this error:

ActiveRecord::StatementInvalid: Mysql2::Error: SAVEPOINT active_record_1 does not exist: ROLLBACK TO SAVEPOINT active_record_1

Be careful when using FactoryBot/FactoryGirl. Use let! instead of let if you need to create objects to the searchable table.

Example:

describe '.search' do
    without_transactional_fixtures do
      let! (:campaign0) { create(:campaign, io_number: 'C0-1234-4321', status: 'completed') }
      let! (:campaign1) { create(:campaign, io_number: "C1-4321-4321") }

      before do
        ActiveRecord::Base.connection.execute("ANALYZE TABLE `searchables`")
      end
...

Thank you @awaage (https://stackoverflow.com/a/13732210/537648)

viktor_vangel
  • 894
  • 9
  • 16
  • Couple of updates I'd make to the `without_transactional_fixtures` method in step one. First, `use_transactional_fixtures` is deprecated, I believe, so you can instead use `use_transactional_tests`. The other is you'll want to probably wipe the database still after this block of tests is done. So, you can add `DatabaseCleaner.start` to the before block, and `DatabaseCleaner.clean` to the after block. Thanks so much, though, super helpful post. – Matt Barry May 19 '23 at 14:33
0

I had the same question and didn't find a very good solution. One thing you can do is use a tool like DatabaseCleaner and change your strategy for those tests from "transaction" to "truncation".

geofflane
  • 2,681
  • 22
  • 21
  • Aye, I never found a good solution either. Truncation cleanup wasn't an option, and we ultimately abandoned the fulltext index anyway. If we'd stuck with it, at this point I think I'd have bit the bullet and made do with whatever I could make in the fixtures. – Andrew Jun 27 '15 at 17:50
0

I had the same issue, and resolved it by manually create the fulltext index in the test. Example: for your case

create_table "documents", force: true do |t|
   t.text    "tags"
end
add_index "documents", ["tags"], name: "index_documents_on_tags", type: :fulltext

in your test:

before do
  @index_key = "index_documents_on_tags_#{Time.current.to_i}"
  ActiveRecord::Base.connection.execute("CREATE FULLTEXT INDEX #{@index_key} ON documents(tags)")
end

after do
  ActiveRecord::Base.connection.execute("ALTER TABLE documents DROP INDEX #{@index_key}")
end
gagoit
  • 1