SQL queries, any idea how to set timeouts? (max_execution_time)

[ek926m]

Hello everyone,

I read an interesting article about timeouts in MySQL. In his example he uses ActiveRecords method "optimizer_hints" to enable the MySQL query based timeout functionality:

Product.optimizer_hints("MAX_EXECUTION_TIME(10000)").all
# SELECT /*+ MAX_EXECUTION_TIME(10000) */ `products`.* FROM `products`

Is there any similar functionality in Sequel available? If not, any way to achieve that goal of implementing timeouts?

Thanks in advance and greetings from Germany :)

[jeremyevans]

The mysql2 gem supports some timeout options, not sure if that works in your use case: https://github.com/brianmario/mysql2#connection-options. This wouldn't support a separate timeout per query, though.

It's not difficult to add support for such hints:

DB.extend_datasets do
  def select_select_sql(sql)
    super
    if timeout = opts[:max_execution_time]
      sql << " /*+ MAX_EXECUTION_TIME(" << timeout << ') */'
    end
  end

  def max_execution_time(timeout)
    clone(:max_execution_time=>timeout.to_i.to_s)
  end
end

DB[:products].max_execution_time(10000).all
# SELECT /*+ MAX_EXECUTION_TIME(10000) */ * FROM `products`

[ek926m]

@jeremyevans thank you very much for your response, I will check it out if it works for me :)