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 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

Answer 1

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.