Dancer2::Plugin::Database and database errors

Hi, note that as the DBI doc says: "The DBI resets $h->err to undef before almost all DBI method calls, so the value only has a short lifespan." Likely the Dancer2 plugin is making multiple calls wrapped into one method.

Did you try accessing the error via the DBI dynamic attributes $DBI::err and $DBI::errstr (although they are probably cleared at the same time)? Or turning on tracing so you can see the output directly (set $ENV{DBI_TRACE} to a positive integer)?

Hope this helps!

Thanks for the reply. The $DBI::err and $DBI::errstr were also uninitialized. But looking at that, it kind of tells me something -- because I know blinking good and well that these are not "undefined." They are just in a different namespace. So at least this gave me an idea to start hacking on...

Use of uninitialized value $DBI::err in concatenation (.) or string at
+ /home/svcanagios/n2s/bin/../lib/NagiosToServiceNow/json.pm line 698.
[download]

As for the trace, I'm not sure how that'd work since this is a web server. I might try that, but at this time, there variables are not in scope, so I'm not sure running a trace would help. As always, I'm open for suggestions.

David Lee Crites, Author and DevOps Dude
Devops: lee@critesclan.com
Author: davidlee@adoniah.co
quad alii ut vivere

"because I know blinking good and well that these are not "undefined." They are just in a different namespace."

No, they really are undefined, almost certainly for the reason I stated: they are reset by DBI after another call on the handle is made subsequent to the one you are interested in, and before you check them. (Those variables cannot be in a "different namespace" as they are global.)

Hope this helps!



The way forward always starts with a minimal test.

"As for the trace, I'm not sure how that'd work ..."

Did you try it?

$ DBI_TRACE=1 plackup bin/app.psgi
[download]

Hope this helps!



The way forward always starts with a minimal test.

In the interest of documenting the debugging process I'm going through (please overlook some stupidity, given that I've been up at least 60 of the last 72 hours), I tried this:

        my @last_records = database('current')->quick_select('states',
            {
                host_name => $data->{'host_name'},
                name => $data->{'service_description'}
                },
            { order_by => { desc => 'id' } }
            ) or $delstatus .= '/error from quick_select call;' .
                ' ERR [' . Dancer::Plugin::Database::Core::DBI->err . 
+'];' .
                ' ERRSTR [' . Dancer::Plugin::Database::Core::DBI->err
+str . ']';
[download]

And got this error:

[NagiosToServiceNow::json:40284] error @2018-09-22 20:32:46> Route exc
+eption: Can't locate object method "err" via package "Dancer::Plugin:
+:Database::Core::DBI" (perhaps you forgot to load "Dancer::Plugin::Da
+tabase::Core::DBI"?) at /home/svcanagios/n2s/bin/../lib/NagiosToServi
+ceNow/json.pm line 707. in /home/svcanagios/n2s/bin/../local/lib/perl
+5/Dancer2/Core/App.pm l. 1473
[download]

I was trying to fully-quality the variable. So that didn't work, so I tried:

        my @last_records = database('current')->quick_select('states',
            {
                host_name => $data->{'host_name'},
                name => $data->{'service_description'}
                },
            { order_by => { desc => 'id' } }
            ) or $delstatus .= '/error from quick_select call;' .
                ' ERR [' . $Dancer::Plugin::Database::Core::DBI::err .
+ '];' .
                ' ERRSTR [' . $Dancer::Plugin::Database::Core::DBI::er
+rstr . ']';
[download]

and got:

Use of uninitialized value $Dancer::Plugin::Database::Core::DBI::err i
+n concatenation (.) or string at /home/svcanagios/n2s/bin/../lib/Nagi
+osToServiceNow/json.pm line 707.
Use of uninitialized value $Dancer::Plugin::Database::Core::DBI::errst
+r in concatenation (.) or string at /home/svcanagios/n2s/bin/../lib/N
+agiosToServiceNow/json.pm line 707.
[NagiosToServiceNow::json:57311] debug @2018-09-22 20:39:02> QUICK SEL
+ECT STATUS: --=> checking for: [tibintprdsac2p.wsgc.com][AIXERRPT] fo
+r last output entry./error from quick_select call; ERR []; ERRSTR []/
+found [0] to delete <=-- in /home/svcanagios/n2s/bin/../lib/NagiosToS
+erviceNow/json.pm l. 719
[download]

asdf

        my @last_records = database('current')->quick_select('states',
            {
                host_name => $data->{'host_name'},
                name => $data->{'service_description'}
                },
            { order_by => { desc => 'id' } }
            ) or $delstatus .= '/error from quick_select call;' .
                ' ERR [' . $Dancer::Plugin::Database::Core::DBI->err .
+ '];' .
                ' ERRSTR [' . $Dancer::Plugin::Database::Core::DBI->er
+rstr . ']';
[download]

and got:

[NagiosToServiceNow::json:40572] error @2018-09-22 20:43:11> Route exc
+eption: Can't call method "err" on an undefined value at /home/svcana
+gios/n2s/bin/../lib/NagiosToServiceNow/json.pm line 707. in /home/svc
+anagios/n2s/bin/../local/lib/perl5/Dancer2/Core/App.pm l. 1473
[download]

By now you're probably realizing that my frenzied mind is lost. Sadly, I am in serious need of some very real help from my fellow monks on this, or I'm afraid I'll just go crazy... :(

David Lee Crites, Author and DevOps Dude
Devops: lee@critesclan.com
Author: davidlee@adoniah.co
quad alii ut vivere

Hi again, have you tried the documented way to access the DBI error, by using a hook?

database_error

Called when a database error is raised by DBI. Receives two parameters: the error message being returned by DBI, and the database handle in question.

(See https://metacpan.org/pod/Dancer2::Plugin::Database#HOOKS)

Edit: I spun up a test:

Create a DB with no tables:

$ sqlite3 test.db
[download]

In config.yml:

plugins:
    Database:
        driver: SQLite
        database: 'test.db'
[download]

app.psgi:

#!/usr/bin/env perl
  
use strict; use warnings;

use FindBin;
use lib "$FindBin::Bin/../lib";

use TestApp;
TestApp->to_app;
[download]

TestApp.pm:

package TestApp;
use Dancer2;
use Dancer2::Plugin::Database;

hook 'database_error' => sub {
    my $err = shift;
    warn "\nThe DB error: `$err`";
};

get '/' => sub {
    eval { database->quick_insert('no_such_table', { foo => 'bar' }) }
+;
    'Hello, world';
};

1;
[download]

Log output:

HTTP::Server::PSGI: Accepting connections at http://0:5000/
[TestApp:4783] core @2018-09-23 09:52:38> looking for get / in /home/n
+ick/perl5/perlbrew/perls/perl-5.28.0/lib/site_perl/5.28.0/Dancer2/Cor
+e/App.pm l. 36
[TestApp:4783] core @2018-09-23 09:52:38> Entering hook core.app.befor
+e_request in (eval 305) l. 1
[TestApp:4783] debug @2018-09-23 09:52:38> Adding sqlite_unicode to DB
+I connection params to enable UTF-8 support in /home/nick/perl5/perlb
+rew/perls/perl-5.28.0/lib/site_perl/5.28.0/Dancer2/Plugin.pm l. 543
[TestApp:4783] core @2018-09-23 09:52:38> Entering hook plugin.databas
+e.database_connected in (eval 305) l. 1
[TestApp:4783] core @2018-09-23 09:52:38> Entering hook plugin.databas
+e.database_error in (eval 305) l. 1

The DB error: `DBD::SQLite::db do failed: no such table: no_such_table
+` at /home/nick/dev/TestApp/bin/../lib/TestApp.pm line 11.
DBD::SQLite::db do failed: no such table: no_such_table at /home/nick/
+perl5/perlbrew/perls/perl-5.28.0/lib/site_perl/5.28.0/Dancer/Plugin/D
+atabase/Core/Handle.pm line 310.
...
[download]

Hope this helps!

Yes, I tried using the database_error hook. Here's the code:

hook 'database_error' => sub {
    my $errstr = shift;
    my $dbh = shift;

    var errstr => $errstr;
};
[download]

I would then check for errstr, but it was never set. I checked three ways: 1) the way I showed here (which still is in the code), 2) by issuing a debug() call, and 3) having it die "some msg." None of them triggered. So it is as if the error is in the plugin, not in the database.

I was going to try to debug it further to put in a but report, but, frankly, I built a workaround. I have two teams at work waiting for this to be working, so I didn't have the luxury of time.

You also asked if I tried the debug; no I didn't. I should, and perhaps when the pressure is off and I can go through the code a little more to clean it up, I will -- I bookmarked this thread just in case suggestions like yours helped.

Thanks for your help.

David Lee Crites, Author and DevOps Dude
Devops: lee@critesclan.com
Author: davidlee@adoniah.co
quad alii ut vivere

It occurred to me that someone might ask why I don't just go back to using my own database code, and let it go. Good question; I have no answer. I figured that for this particular project, I'd use what appeared to be the "Dancer2 stuff" instead of writing my own. If no suggestions for getting around this come up, then I'll be forced to go back to doing my own.

This project answers on both API (using JSON) and straight web pages. At this time there is no authorization code at all, since it is strictly internal to our group. It is an interface between Nagios-XI and ServiceNow, but will also interact with other applications in the future (Slack/PagerDuty/etc). I also want to take the core code and publish it in github as an example for others who are writing APIs. So that is part of what made me thing I'd be better off using more "off the shelf" modules than writing my own.