Rails - May 2006 - Using an external database behind a firewall

Hi everyone,

There''s a separate database I''m using to get mail group info
for our
users (Sympa), and I''ve got Rails configured to pull data directly
from it via an entry in database.yml

The issue we''re having is that the database server in question is
behind an internal firewall that has idle timeouts set at 30 minutes.
If the connection isn''t used (like, say, overnight), the firewall
kills it.  But Rails thinks it''s still open!  Not so good.

Any ideas on how I can check for that and work around it?

This is also a point in my favor for caching the data locally and
updating it every so often.  That would be "live enough" for me, but
not for others.

Thanks!

Sean

> There''s a separate database I''m using to get mail group
info for our
> users (Sympa), and I''ve got Rails configured to pull data directly
> from it via an entry in database.yml
>
> The issue we''re having is that the database server in question is
> behind an internal firewall that has idle timeouts set at 30 minutes.
> If the connection isn''t used (like, say, overnight), the firewall
> kills it.  But Rails thinks it''s still open!  Not so good.
>
> Any ideas on how I can check for that and work around it?
>
> This is also a point in my favor for caching the data locally and
> updating it every so often.  That would be "live enough" for me,
but
> not for others.
http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/AbstractAdapter.html#M000753

maybe a combination of active() and reconnect! would do the trick?

Or failing that a cron job that runs every 20 minutes and performs a 
simple query?

-philip
>
> Thanks!
>
> Sean
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

So, something like this?  (Assuming the AR model corresponding to the
external DB is Mailinglist):

def show
  @person = Person.find(params[:id])

  reconnect! if !Mailinglist.connection.active?
  @mailinglists = Mailinglist.find_for_user(@person.email)
end

Looks exactly like what the doctor ordered!  Thanks!

As far as the Rails-way, where would I handle whether or not this DB
was even available?  (Given the firewall''s history, that''s a
real
risk.)

Sean

On 5/25/06, Philip Hallstrom <rails@philip.pjkh.com>
wrote:
> > There''s a separate database I''m using to get mail
group info for our
> > users (Sympa), and I''ve got Rails configured to pull data
directly
> > from it via an entry in database.yml
> >
> > The issue we''re having is that the database server in
question is
> > behind an internal firewall that has idle timeouts set at 30 minutes.
> > If the connection isn''t used (like, say, overnight), the
firewall
> > kills it.  But Rails thinks it''s still open!  Not so good.
> >
> > Any ideas on how I can check for that and work around it?
> >
> > This is also a point in my favor for caching the data locally and
> > updating it every so often.  That would be "live enough" for
me, but
> > not for others.
>
>
http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/AbstractAdapter.html#M000753
>
> maybe a combination of active() and reconnect! would do the trick?
>
> Or failing that a cron job that runs every 20 minutes and performs a
> simple query?
>
> -philip
>
> >
> > Thanks!
> >
> > Sean
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Er, that would be Mailinglist.connection.reconnect! of course.

On 5/25/06, Sean Hussey <seanhussey@gmail.com>
wrote:
> So, something like this?  (Assuming the AR model corresponding to the
> external DB is Mailinglist):
>
> def show
>   @person = Person.find(params[:id])
>
>   reconnect! if !Mailinglist.connection.active?
>   @mailinglists = Mailinglist.find_for_user(@person.email)
> end
>
> Looks exactly like what the doctor ordered!  Thanks!
>
> As far as the Rails-way, where would I handle whether or not this DB
> was even available?  (Given the firewall''s history,
that''s a real
> risk.)
>
> Sean
>
> On 5/25/06, Philip Hallstrom <rails@philip.pjkh.com> wrote:
> > > There''s a separate database I''m using to get
mail group info for our
> > > users (Sympa), and I''ve got Rails configured to pull
data directly
> > > from it via an entry in database.yml
> > >
> > > The issue we''re having is that the database server in
question is
> > > behind an internal firewall that has idle timeouts set at 30
minutes.
> > > If the connection isn''t used (like, say, overnight), the
firewall
> > > kills it.  But Rails thinks it''s still open!  Not so
good.
> > >
> > > Any ideas on how I can check for that and work around it?
> > >
> > > This is also a point in my favor for caching the data locally and
> > > updating it every so often.  That would be "live
enough" for me, but
> > > not for others.
> >
> >
http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/AbstractAdapter.html#M000753
> >
> > maybe a combination of active() and reconnect! would do the trick?
> >
> > Or failing that a cron job that runs every 20 minutes and performs a
> > simple query?
> >
> > -philip
> >
> > >
> > > Thanks!
> > >
> > > Sean
> > > _______________________________________________
> > > Rails mailing list
> > > Rails@lists.rubyonrails.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
>

One day I''ll actually learn to try this out before replying.

Anyway, it looks like Rails thinks the connection is still active,
even though the firewall has killed it.  As such, active? returns
true, and so we don''t reconnect.

Is it not advisable to connect/disconnect every time I want to get at
that data?  What a bear.  Maybe if my exception handler catches and
reconnects...but that could bring up other issues.

Besides, it doesn''t look like I''m getting an Exception per se.
"Broken Pipe" is all.

On 5/25/06, Sean Hussey <seanhussey@gmail.com>
wrote:
> Er, that would be Mailinglist.connection.reconnect! of course.
>
> On 5/25/06, Sean Hussey <seanhussey@gmail.com> wrote:
> > So, something like this?  (Assuming the AR model corresponding to the
> > external DB is Mailinglist):
> >
> > def show
> >   @person = Person.find(params[:id])
> >
> >   reconnect! if !Mailinglist.connection.active?
> >   @mailinglists = Mailinglist.find_for_user(@person.email)
> > end
> >
> > Looks exactly like what the doctor ordered!  Thanks!
> >
> > As far as the Rails-way, where would I handle whether or not this DB
> > was even available?  (Given the firewall''s history,
that''s a real
> > risk.)
> >
> > Sean
> >
> > On 5/25/06, Philip Hallstrom <rails@philip.pjkh.com> wrote:
> > > > There''s a separate database I''m using to
get mail group info for our
> > > > users (Sympa), and I''ve got Rails configured to
pull data directly
> > > > from it via an entry in database.yml
> > > >
> > > > The issue we''re having is that the database server
in question is
> > > > behind an internal firewall that has idle timeouts set at 30
minutes.
> > > > If the connection isn''t used (like, say,
overnight), the firewall
> > > > kills it.  But Rails thinks it''s still open!  Not
so good.
> > > >
> > > > Any ideas on how I can check for that and work around it?
> > > >
> > > > This is also a point in my favor for caching the data
locally and
> > > > updating it every so often.  That would be "live
enough" for me, but
> > > > not for others.
> > >
> > >
http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/AbstractAdapter.html#M000753
> > >
> > > maybe a combination of active() and reconnect! would do the
trick?
> > >
> > > Or failing that a cron job that runs every 20 minutes and
performs a
> > > simple query?
> > >
> > > -philip
> > >
> > > >
> > > > Thanks!
> > > >
> > > > Sean
> > > > _______________________________________________
> > > > Rails mailing list
> > > > Rails@lists.rubyonrails.org
> > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > >
> > > _______________________________________________
> > > Rails mailing list
> > > Rails@lists.rubyonrails.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> >
>