'ActiveRecord::ConnectionNotEstablished'

[concept47]

I know this is a repeat of the infamous problem referenced
here, http://groups.google.com/group/rufus-ruby/browse_thread/thread/23a8fb060dd77e02

and here, http://jmettraux.wordpress.com/2008/09/14/the-scheduler-and-the-active_record/

but I can't get this to work properly for the life of me.
I have several call like this

    scheduler.every '15m' do

        Url.update_xxxxx

    end

that just go out to an rss feed and populate my db with new stories.
When I run it *without* :blocking => true, I my mysql connections slowly get used up until they hit my limit (even if I keep increasing it)

if I run it like this

    scheduler.every '15m' do

        Url.update_xxxxx

        ActiveRecord::Base.remove_connection 

    end

then I get a ''ActiveRecord::ConnectionNotEstablished'" error, which persists even if I do this

    scheduler.every '15m' do
        ActiveRecord::Base.verify_active_connections!

        Url.update_xxxxx

        ActiveRecord::Base.remove_connection 

    end

The only way to get this to run properly is with :blocking set to true, but then I have a problem with jobs stacked on top of each other that just hogs my cpu
Any ideas?

i'm using ruby 1.8.7 with rails 2.3.11, and rufus-scheduler (2.0.10)

[John Mettraux]

Hello,

could the new :mutex attribute help?

---8<---
scheduler.every '15m', :mutex => 'ar_mutex' do
ActiveRecord::Base.verify_active_connections!
Url.update_xxxxx
#ActiveRecord::Base.remove_connection
end
--->8---

Making sure that all the rufus jobs dealing with active record share the same
mutex [name]...

Could that help?

--
John Mettraux - http://lambda.io/jmettraux

[Ike Ofili]

> Hello,
>
> could the new :mutex attribute help?
>
> ---8<---
> scheduler.every '15m', :mutex => 'ar_mutex' do
>  ActiveRecord::Base.verify_active_connections!
>  Url.update_xxxxx
>  #ActiveRecord::Base.remove_connection
> end
> --->8---
>
> Making sure that all the rufus jobs dealing with active record share the same
> mutex [name]...
>
> Could that help?
>

hmmm, I'll try it, but I thought each thread would still open a new
Active record connection (which is what I'm trying to avoid)

[John Mettraux]

On Tue, May 08, 2012 at 12:22:38AM -0500, Ike Ofili wrote:
>
> hmmm, I'll try it, but I thought each thread would still open a new
> Active record connection (which is what I'm trying to avoid)

OK, if I remember correctly, ActiveRecord places its connections in a hash
where the key is the thread's object_id...

Cheers,

[Ike Ofili]

What would be the advantage of using mutexes in this particular case John?

[John Mettraux]

On Tue, May 08, 2012 at 12:31:32AM -0500, Ike Ofili wrote:
> What would be the advantage of using mutexes in this particular case John?

Now, thinking a bit more about, I'd say none, unless you have jobs that don't
deal with ActiveRecord (and wouldn't have to wait on the mutex).

John

[concept47]

Now, thinking a bit more about, I'd say none, unless you have jobs that don't
deal with ActiveRecord (and wouldn't have to wait on the mutex).

Ah right, so there nothing we can do, except (possibly) explicitly ActiveRecord::Base.establish_connection and ActiveRecord::Base.remove_connection before/after the ActiveRecord call? 

[John Mettraux]

On Mon, May 07, 2012 at 10:49:22PM -0700, concept47 wrote:
>
> Ah right, so there nothing we can do, except (possibly)
> explicitly ActiveRecord::Base.establish_connection
> and ActiveRecord::Base.remove_connection before/after the ActiveRecord
> call?

Yes. Probably.

---8<---
def ActiveRecord.connect(&block)

ActiveRecord::Base.establish_connection
block.call
ActiveRecord::Base.remove_connection
end
--->8---

Could help, maybe it already exists...

[Ike Ofili]

> Now, thinking a bit more about, I'd say none, unless you have jobs that don't
> deal with ActiveRecord (and wouldn't have to wait on the mutex).
>
> John

Another question for you, do jobs create new threads each time they
run? Or do they run in the same thread all the time?

[John Mettraux]

On Wed, May 09, 2012 at 03:14:04PM -0500, Ike Ofili wrote:
> > Now, thinking a bit more about, I'd say none, unless you have jobs that don't
> > deal with ActiveRecord (and wouldn't have to wait on the mutex).
>

> Another question for you, do jobs create new threads each time they
> run? Or do they run in the same thread all the time?

Hello Ike,

yes, by default each job executes in a new, dedicated thread, unless you use
:blocking => true.


Best regards,

[Ike Ofili]

Aaaaaah this explains my problem! :D
Each of my jobs starts a thread, opens up a new ActiveRecord
connection and then doesn't close it, so my connection pool gets maxed
out within a few hours.

I've tried using

ActiveRecord::Base.connection_pool.with_connection do |connection|
do something
end

but then I have to use the connection which means I can't call a class
method on my ActiveRecord model ... so close ... but yet so far :\

[concept47]

So here are my findings, hopefully they help someone else.

using 

ActiveRecord::Base.connection_pool.with_connection do |conn|

  User.find(1) 

end

or even better

ActiveRecord::Base.connection_pool.with_connection{ User.first }

should check out a connection from the pool set aside for Rails in your database.yml pool setting, let your active record call use it and then check it back in, solving our problem.
**But this only works in rails 3+** ... you can see the code change here

Rails 2.3 http://apidock.com/rails/v2.3.8/ActiveRecord/ConnectionAdapters/ConnectionPool/with_connection
Rails 3 http://apidock.com/rails/v3.0.0/ActiveRecord/ConnectionAdapters/ConnectionPool/with_connection

This guy (you should read all his stuff on threading and ActiveRecord if you're trying to make this work btw) explains the patch in this blog post
http://coderrr.wordpress.com/2009/05/05/activerecords-with_connection-is-now-useful/

AFAIK, nothing else seems to work for rails 2.3, I tried running

ActiveRecord::Base.connection_pool.clear_stale_cached_connections! every 5-10 minutes

tried  ActiveRecord::Base.clear_active_connections!

nothing worked, so its back to :blocking => true for me.
Thanks for all the help John!