Graceful handling of database failure

[Mitchell K. Jackson]

Good afternoon,

Is there a graceful way for a catalyst app to handle when the database
server goes away?  If this happens while a process is holding open a
database connection it's already been using, there's not even a
database error to the log. The only error is "read data timeout in 40
seconds", and an error 500 to the web browser.

I would like my app to be able to deliver a  more useful error to the
end user, such as "Lost database connection."  If I can retain control
of the app in this event, I'll have it send me an sms for good
measure.  Any ideas how I could go about this?

Regards,

/Mitchell K. Jackson

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[J. Shirley]

On Tue, Jun 17, 2008 at 2:31 PM, Mitchell K. Jackson
<mitchell.k.jackson@...> wrote:

> Good afternoon,
>
> Is there a graceful way for a catalyst app to handle when the database
> server goes away?  If this happens while a process is holding open a
> database connection it's already been using, there's not even a
> database error to the log. The only error is "read data timeout in 40
> seconds", and an error 500 to the web browser.
>
> I would like my app to be able to deliver a  more useful error to the
> end user, such as "Lost database connection."  If I can retain control
> of the app in this event, I'll have it send me an sms for good
> measure.  Any ideas how I could go about this?
>
> Regards,
>
> /Mitchell K. Jackson
>

That's really up to the model you are using.  Catalyst doesn't handle
databases :)

If you are using DBIx::Class, I'd be surprised if it doesn't do the
right thing already.

As far as catching the error, make sure that your model throws a
proper exception and you can catch that in the end action.

Something like this is what I tend to use:

sub render_end : Private ActionClass('RenderView') { }

sub end : Private {
   my ( $self, $c ) = @_;
   $c->forward('render_end');
   if ( @{$c->error} ) {
      # Handle errors
   }
}

In our apps, if we're not running under debug I bake an email and send
it off to Catalyst::View::Email.  I then will set
$c->stash->{template} to something like 'errors/500.tt' and then
re-call the forward to render_end.

-J

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Mitch Jackson]

> If you are using DBIx::Class, I'd be surprised if it doesn't do the
> right thing already.

> As far as catching the error, make sure that your model throws a
> proper exception and you can catch that in the end action.
>
> Something like this is what I tend to use:
>
> sub render_end : Private ActionClass('RenderView') { }
>
> sub end : Private {
>   my ( $self, $c ) = @_;
>   $c->forward('render_end');
>   if ( @{$c->error} ) {
>      # Handle errors
>   }
> }

J,

Thanks for the help.  I tried the approach you suggested, but it's not
working for me.  Yes, I am using DBix::Class, and yes, it does seem to
do The Right Thing and somehow throw a $c->error that makes it out to
the log.

I wrote an action similar to the one you suggest ( code attached below
) and threw a bad username into the database connection string.

Watching Debug output, it appears the end action is never called when
the error happens.  The debug output looks like this:

=============================================================
You can connect to your server at http://myapp.com:3000
DBI connect('table:localhost','bad_username',...) failed: Access
denied for user 'bad_username'@'localhost' (using password: YES) at
/usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 839
DBI connect('table:localhost','bad_username',...) failed: Access
denied for user 'bad_username'@'localhost' (using password: YES) at
/usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 839
[info] *** Request 1 (0.200/s) [3553] [Thu Jun 19 12:35:54 2008] ***
[debug] "GET" request for "/" from "127.0.0.1"
[debug] Found sessionid "efa809c86e12c56089e8aaaf24e62e56ec23f7ba" in cookie
[error] DBIx::Class::ResultSet::find_or_create(): DBI Connection
failed: Access denied for user 'bad_username'@'localhost' (using
password: YES) at
/usr/lib/perl5/site_perl/5.8.8/Catalyst/Plugin/Session/Store/DBIC/Delegate.pm
line 33
[info] Request took 0.326993s (3.058/s)
.----------------------------------------------------------------+-----------.
| Action                                                         | Time      |
+----------------------------------------------------------------+-----------+
| /begin                                                         | 0.000333s |
| /index                                                         | 0.039518s |
'----------------------------------------------------------------+-----------'

[error] Caught exception in engine
"DBIx::Class::ResultSet::find_or_create(): DBI Connection failed:
Access denied for user 'bad_username'@'localhost' (using password:
YES) at /usr/lib/perl5/site_perl/5.8.8/Catalyst/Plugin/Session/Store/DBIC/Delegate.pm
line 33"
=============================================================

Since /end is never called, the error handling never happens.

Is there some other way to tie into the error handling, or am I
missing something?  Following is code I'm using for end action:

Thanks,

/Mitch

=============================================================
sub render_end : ActionClass('RenderView') {}

sub end : {
# The default error handling of Catalyst gets pre-empted here with our own,
# unless the app is operating in debug mode.

# First, we forward to the standard renderer end action.  After, we
# check for errors.  If they exist, we handle the errors and clear them from
# catalyst, but add them to the stash..  Then we override the template
# to use error500 and display an error by forwarding back to the renderview
# a second time.

    my ( $self, $c ) = @_;

$c->log->debug( 'On a DBIx::Class Connection error this statement
never happens' );

    $c->forward('render_end');
    return unless @{ $c->error };

    # If we're running under debug mode, allow default error handling
    # and stack trace.  Otherwise, override error handling
    return if $c->debug();

    # Collect and clear catalyst's error stack, creating a copy into stash
    my @errors = grep { 1 } shift @{$c->error};
    $c->stash->{errors} = \@errors;

    for my $error ( @errors ) {
        $c->log->error( $error );
    }

    # Re-process final rendering w/o the error stack
    $c->stash->{template} = '500.nonav.tt2';
    $c->detach('render_end');
}

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Mitch Jackson]

> Is there some other way to tie into the error handling, or am I
> missing something?  Following is code I'm using for end action:

Dear self,

It looks like you can overload the internal catalyst function
$c->finalize_error.  Perhaps this is what you're looking for.

/Mitch

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[J. Shirley]

On Thu, Jun 19, 2008 at 10:56 AM, Mitch Jackson <perimus@...> wrote:
[snip]

> =============================================================
> sub render_end : ActionClass('RenderView') {}
>
> sub end : {

"sub end : Private {" is what you need.  The Private attribute is important.

That should do it for you.

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Mitch Jackson]

>> sub end : {
>
> "sub end : Private {" is what you need.  The Private attribute is important.

Odd thing was, when I added the Private attribute as per your original
example, it broke the app somehow.  I'll look at that some more.

Thanks,

/Mitch

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Oleg Pronin]

You can use DBIx::Class with DBIx::RetryOverDisconnects as DBI handle and you will never got db server away message (it will reconnect as long as required until database is up and no queries and transactions will be lost).

2008/6/18 Mitchell K. Jackson <mitchell.k.jackson@...>:

Good afternoon,

Is there a graceful way for a catalyst app to handle when the database
server goes away?  If this happens while a process is holding open a
database connection it's already been using, there's not even a
database error to the log. The only error is "read data timeout in 40
seconds", and an error 500 to the web browser.

I would like my app to be able to deliver a  more useful error to the
end user, such as "Lost database connection."  If I can retain control
of the app in this event, I'll have it send me an sms for good
measure.  Any ideas how I could go about this?

Regards,

/Mitchell K. Jackson

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Mitch Jackson]

> You can use DBIx::Class with DBIx::RetryOverDisconnects as DBI handle and
> you will never got db server away message (it will reconnect as long as
> required until database is up and no queries and transactions will be lost).

Looks cool, but it's got a big warning on it saying it's not stable
for production.  Also, if the database server goes away for 5 minutes,
then instead of error 500 pages, now the website is just timing out to
the browser. That's less descriptive for the end user, when what I'm
aiming at is giving more of a "we've got a handle on things"
appearance to the errors when they happen.

/Mitch

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Oleg Pronin]

I use it for 3 years. It might be used in production i think.
About 5 minutes: there are a set of attributes (ReconnectRetries, ReconnectTimeout, etc) - you can manipulate how long it will reconnect.

2008/6/20 Mitch Jackson <perimus@...>:

> You can use DBIx::Class with DBIx::RetryOverDisconnects as DBI handle and
> you will never got db server away message (it will reconnect as long as
> required until database is up and no queries and transactions will be lost).

Looks cool, but it's got a big warning on it saying it's not stable
for production.  Also, if the database server goes away for 5 minutes,
then instead of error 500 pages, now the website is just timing out to
the browser. That's less descriptive for the end user, when what I'm
aiming at is giving more of a "we've got a handle on things"
appearance to the errors when they happen.

/Mitch

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Mitch Jackson]

On Fri, Jun 20, 2008 at 1:25 PM, Oleg Pronin <syber.rus@...> wrote:
> I use it for 3 years. It might be used in production i think.
> About 5 minutes: there are a set of attributes (ReconnectRetries,
> ReconnectTimeout, etc) - you can manipulate how long it will reconnect.


Thanks for the info!

/Mitch

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[mike-220]

On Tue, Jun 17, 2008 at 5:27 PM, J. Shirley <jshirley@...> wrote:

> On Tue, Jun 17, 2008 at 2:31 PM, Mitchell K. Jackson
> <mitchell.k.jackson@...> wrote:
>> Good afternoon,
>>
>> Is there a graceful way for a catalyst app to handle when the database
>> server goes away?  If this happens while a process is holding open a
>> database connection it's already been using, there's not even a
>> database error to the log. The only error is "read data timeout in 40
>> seconds", and an error 500 to the web browser.
>>
>> I would like my app to be able to deliver a  more useful error to the
>> end user, such as "Lost database connection."  If I can retain control
>> of the app in this event, I'll have it send me an sms for good
>> measure.  Any ideas how I could go about this?
>>
>> Regards,
>>
>> /Mitchell K. Jackson
>>
>
>
> That's really up to the model you are using.  Catalyst doesn't handle
> databases :)
>
> If you are using DBIx::Class, I'd be surprised if it doesn't do the
> right thing already.
>
> As far as catching the error, make sure that your model throws a
> proper exception and you can catch that in the end action.
>
> Something like this is what I tend to use:

[snip]

are you using Catalyst::Plugin::Session::Store::DBIC like he is?  we're
using code identical to what you suggested, but it doesn't work for
exceptions generated in finalize_headers.

try it.  take your database down and start the application.

any suggestions?

-mike

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/

[Matt S Trout-2]

On Wed, Jul 16, 2008 at 12:57:58PM -0500, mike wrote:

> On Tue, Jun 17, 2008 at 5:27 PM, J. Shirley <jshirley@...> wrote:
> > On Tue, Jun 17, 2008 at 2:31 PM, Mitchell K. Jackson
> > <mitchell.k.jackson@...> wrote:
> >> Good afternoon,
> >>
> >> Is there a graceful way for a catalyst app to handle when the database
> >> server goes away?  If this happens while a process is holding open a
> >> database connection it's already been using, there's not even a
> >> database error to the log. The only error is "read data timeout in 40
> >> seconds", and an error 500 to the web browser.
> >>
> >> I would like my app to be able to deliver a  more useful error to the
> >> end user, such as "Lost database connection."  If I can retain control
> >> of the app in this event, I'll have it send me an sms for good
> >> measure.  Any ideas how I could go about this?
> >>
> >> Regards,
> >>
> >> /Mitchell K. Jackson
> >>
> >
> >
> > That's really up to the model you are using.  Catalyst doesn't handle
> > databases :)
> >
> > If you are using DBIx::Class, I'd be surprised if it doesn't do the
> > right thing already.
> >
> > As far as catching the error, make sure that your model throws a
> > proper exception and you can catch that in the end action.
> >
> > Something like this is what I tend to use:
>
> [snip]
>
> are you using Catalyst::Plugin::Session::Store::DBIC like he is?  we're
> using code identical to what you suggested, but it doesn't work for
> exceptions generated in finalize_headers.

I think that the exception thrown by the session store should be considered
a bug.

Or at least the whole "exception in engine BLAM" result.

Not entirely sure what the best way is of fixing it though - anybody got
any ideas?

--
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/

_______________________________________________
List: Catalyst@...
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@.../
Dev site: http://dev.catalyst.perl.org/