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

Community · Accepted Answer · 2017-04-13 12:13:48Z

12

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.

edited Apr 13, 2017 at 12:13

CommunityBot

11 silver badge

answered Jun 21, 2016 at 7:13

fguillen

39.5k25 gold badges167 silver badges240 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wscourge Over a year ago

FYI for MySQL 5.6.10 it results in Mysql2::Error: Unknown system variable 'max_statement_time'

Unixmonkey · Accepted Answer · 2012-12-18 18:22:20Z

5

# 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

answered Dec 18, 2012 at 18:22

Unixmonkey

18.8k7 gold badges60 silver badges82 bronze badges

6 Comments

fguillen Over a year ago

I don't see where there is official documentation for this, I have tried at the Rails guides and to the mysql2 gem documentation. I only see a reference to the timeout variable when you are configuring a sqlite3 db. Also there is not clear is here you are defining the connection timeout of the query execution timeout.

fguillen Over a year ago

Definitely timeout is not working for me with mysql2 gem connection. I have set up timeout: 1 and I have a sentence ActiveRecord::Base.connection.exec_query(SQL) that takes more that 2 seconds without any Timeout error

Unixmonkey Over a year ago

@fguillen This is a pretty old answer, and I think I tested with the mysql gem (and maybe SQLserver), but it seems with the mysql2 gem, you might need to set the wait_timeout key instead of timeout.

fguillen Over a year ago

looks like wait_timeout is for The number of seconds the server waits for activity on a noninteractive connection before closing it. There is not a clear way to set the time out for a query in Rails + MySQL, or at least I haven't found any yet. I think is important that we notice this and that we declare this answer as not correct or it can bring a lot of headaches.. as the one I have now ;)

Unixmonkey Over a year ago

@fguillen Have you tried setting read_timeout and write_timeout?

|

Collectives™ on Stack Overflow

Set Timeout Value for SQL query in Rails

2 Answers 2

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related