Set timeout value for SQL query in Rails

Question

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

However, the request saves time. Is it possible to change the timeout value for this particular query instead of changing the timeout value for all queries in database.yml ?

thanks

Answer 1

 # 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 

Answer 2

We must be careful with the timeout variables, most of them are related to the connection timeout, and not the request timeout itself.

It seems that prior to MySQL 5.7.4, the only way to kill a long query was with the mysql kill command , which I’m not sure that you will lose the client / server connection too, so your Rails process may become unusable.

In MySQL 5.7.4, the system variable max_statement_time appears , which allows you to configure the server exactly to what the original question "Execution timeout for SELECT statements" poses.

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

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

To verify that the variable is set correctly in your ActiveRecord connection, you can run it in the 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 do not have access to MySQL> = 5.7.4, so I can not check these conclusions, I will be grateful if someone confirms it.