Can rspec change and use an innodb fulltext index in the same test?

Question

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?

Answer 1

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)