Force stop running ActiveRecord, avoid mysql max_execution_time

Question

We have app with specs: Ruby 2.6.5, Rails 4.2.11, MySQL 5.7.33 (max_execution_time = 1200000, 20minutes)

With huge data, for example, company data, sometimes when we do query it took time so long. So I decided to use Timeout::timeout to force stop query, but it seems won't work.

This is the sample code

begin
  max_time = 600 # 10minutes

  Timeout::timeout(max_time) do
    company = Company.where(location: 'West').last
  end
rescue => e
  company = nil
end

The expectation is, if query still processing, it should stop in 10 minutes. But it stops in 20 minutes, max timeout of MySQL.

I have checked with this, it can stop in 5 seconds

Timeout::timeout(5) { sleep(10) }

But with this, it still stop in 10 seconds

Timeout::timeout(5) { Company.select('SLEEP(10)').limit(1) }

Is it possible to stop query with rails?

Answer 1

I have solved the issue with this one,

begin
  query = <<-SQL
    SELECT /*+ MAX_EXECUTION_TIME(600000) */ id, location
    FROM companies
    WHERE location = 'West'
    ORDER BY id DESC LIMIT 1;
  SQL

  result = Company.find_by_sql(query)
  company = result.last
rescue => e
  company = nil
end

Maybe there is another approach.