CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
Huy
Huy

Reputation: 11206

Set Timeout Value for SQL query in Rails

I want to execute a very long query in Rails using ActiveRecord::Base.connection.execute(sql).

However, the query keeps timing out. Is it possible to change the timeout value for this specific query instead of having to change the timeout value for all queries in database.yml?

Thanks

Upvotes: 8

Views: 10739

Answers (2)

fguillen
fguillen

Reputation: 38888

We have to be careful with the timeout variables, most of them are related to connection timeouts and not for the query timeout itself.

Looks like until MySQL 5.7.4 the only way to kill a long query was through the mysql command kill which I am not sure if you also will lost the connection client/server so maybe your Rails process will become unusable.

In MySQL 5.7.4 appears the system variable max_statement_time which allows to configure the server exactly to what the original question is asking "The execution timeout for SELECT statements".

To set this system variable through Rails you can use the option variables is your database.yml.

development:
  adapter: mysql2
  [...]
  variables:
    max_statement_time: 60000 # 1 minute

To check that the variable has been set properly in your ActiveRecord connection you can run this in a Rails console:

ActiveRecord::Base.connection.execute('show variables like "%max_execution_time%"').to_a

PS: the system variable is renamed to max_execution_time in MySQL 5.7.8

PS2: I have not any access to a MySQL >= 5.7.4 so I can not test these conclusions, I will appreciate if someone confirms it.

Upvotes: 12

Unixmonkey
Unixmonkey

Reputation: 18784

# in database.yml
production: &prod
  adapter: whatever
  timeout: 5000 

long_connection_production:
  <<: prod
  timeout: 10000

# app/models/long_connection.rb
class LongConnection < ActiveRecord::Base
  establish_connection "long_connection_#{Rails.env}"

  def self.do_thing_that_takes_a_long_time
    connection.execute(sql)
  end
end

Upvotes: 5

Related Questions