Using an external database behind a firewall


#1

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


#2

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


#3

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


#4

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


#5

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.