Forum: JRuby Call .setQueryTimeout() for ActiveRecord statements

Hello,

My goal is to call the .setQueryTimeout() method on some underlying 
Statement object(s) used by ActiveRecord to execute queries on a MySQL 
database.

Does anyone know of a mechanism to either access the underlying 
Statement object myself to call this method, or can anyone suggest some 
approach that would cause .setQueryTimeout() to be called for each new 
Statement object created by ActiveRecord?

(I have seen 
http://stackoverflow.com/questions/5480780/how-do-... 
which suggests a solution that solves a different problem. 
Mysql::OPT_READ_TIMEOUT and Mysql::OPT_WRITE_TIMEOUT are very different 
things than Query Timeout, which actually causes a KILL QUERY statement 
to be executed on the server.)

Regards,

Kolbe

If you use the jdbcmysql driver, you can do a lot with the url 
parameter.
With it you can pass in lower level configuration settings to the
underlying jdbc connection (for mysql these are documented at http://
dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html).
I've experimented with doing something like what you're saying with the
socketTimeout and connectTimeout parameters. We want to both limit the 
max
time a query can take, but also don't want to hang on connecting to the
database either.

If I want to set socketTimeout to 500ms and connectTimeout to 500ms, you
can have a database.yml that looks like:

production:
  user: root
  password: xyz123
  adapter: jdbcmysql

url: 
"jdbc:mysql://db_host_name/my_service_production?socketTimeout=500&connectTimeout=500"


And in your Gemfile:

gem 'activerecord-jdbcmysql-adapter', :platforms => [:jruby]


I haven't played with setQueryTimeout however, and not sure if it can be
configured in a similar way. And to be completely candid, I haven't 
rolled
this setting out to a production server yet. I'm also not sure if you'd
want just setQueryTimeout or if you'd still want socket/connect timeouts
also configured.

Hello Richie,

On 28Nov , 2012, at 11:06, Richie Vos <richie@groupon.com> wrote:

> If you use the jdbcmysql driver, you can do a lot with the url parameter. With 
it you can pass in lower level configuration settings to the underlying jdbc 
connection (for mysql these are documented at 
http://dev.mysql.com/doc/refman/5.0/en/connector-j...). 
I've experimented with doing something like what you're saying with the 
socketTimeout and connectTimeout parameters. We want to both limit the max time a 
query can take, but also don't want to hang on connecting to the database either.
>
> I haven't played with setQueryTimeout however, and not sure if it can be 
configured in a similar way. And to be completely candid, I haven't rolled this 
setting out to a production server yet. I'm also not sure if you'd want just 
setQueryTimeout or if you'd still want socket/connect timeouts also configured.

I might still want the socketTimeout and connectTimeout parameters to be 
set, and you're right that those can easily be set in the Connector/J 
connection URL, but there's no support for doing something similar with 
QueryTimeout, unfortunately. This could be a great feature request for 
those guys, but I'm still left wondering about some other way to call 
the setQueryTimeout() method for the time being :-/

Regards,

Kolbe