Icinga Reporting with PostgreSQL Testing/Issues

mtdeguzis · March 31, 2020, 2:13pm

Reference: https://github.com/Icinga/icingaweb2-module-reporting/pull/26

Out of curiosity, I updated to this and tested it. Everything seems to work except the schedule run, and the standard debug/verbose options don’t help. The icinga2 and icingaweb2 logs don’t seem to show activity during the run. I won’t dwell on it too much until this official, but I can try to help test as well.

icingacli reporting schedule run --verbose --debug

Following https://github.com/Icinga/icingaweb2-module-reporting/blob/master/doc/02-Installation.md
Note that for posgres, I’m fairly sure the charset should be utf8?

Only other thing I noticed too is the backend database is icingaweb2’s db after setup:

resources.ini:

[reporting]
type = "db"
db = "pgsql"
host = "icinga-prod-postgres-01"
port = "5432"
dbname = "reporting"
username = "reporting"
password = "*********"
charset = "utf8"
persistent = "0"

mtdeguzis · March 31, 2020, 2:22pm

Swapped the module backend databse to “reporting” as a test, which results in:

SQLSTATE[08006] [7] fe_sendauth: no password supplied

#0 /usr/share/icingaweb2/modules/ipl/vendor/ipl/sql/src/Connection.php(145): PDO->__construct(String, String, String, Array)
#1 /usr/share/icingaweb2/modules/ipl/vendor/ipl/sql/src/Connection.php(160): ipl\Sql\Connection->createPdoAdapter()
#2 /usr/share/icingaweb2/modules/ipl/vendor/ipl/sql/src/Connection.php(311): ipl\Sql\Connection->connect()
#3 /usr/share/icingaweb2/modules/reporting/library/Reporting/RetryConnection.php(13): ipl\Sql\Connection->prepexec(String, Array)
#4 /usr/share/icingaweb2/modules/ipl/vendor/ipl/sql/src/Connection.php(330): Icinga\Module\Reporting\RetryConnection->prepexec(String, Array)
#5 /usr/share/icingaweb2/modules/reporting/application/controllers/ReportsController.php(41): ipl\Sql\Connection->select(Object(ipl\Sql\Select))
#6 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Module\Reporting\Controllers\ReportsController->indexAction()
#7 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#8 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#9 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#10 /usr/share/php/Icinga/Application/webrouter.php(99): Icinga\Application\Web->dispatch()
#11 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#12 {main}

… despite .pgpass in Icinga users /home directory. I’m not sure how this was set to icingaweb2_db before, but it seems it should be set to reporting

github.com

Icinga/icingaweb2-module-reporting/blob/feature/postgresql/library/Reporting/Database.php#L12


// Icinga Reporting | (c) 2018 Icinga GmbH | GPLv2


namespace Icinga\Module\Reporting;


use Icinga\Application\Config;
use Icinga\Data\ResourceFactory;
use ipl\Sql;


trait Database
{
    protected function getDb($resource = null)
    {
        $config = new Sql\Config(ResourceFactory::getResourceConfig(
            $resource ?: Config::module('reporting')->get('backend', 'resource', 'reporting')
        ));


        $config->options = [
            \PDO::ATTR_DEFAULT_FETCH_MODE   => \PDO::FETCH_OBJ,
            \PDO::MYSQL_ATTR_INIT_COMMAND   => "SET SESSION SQL_MODE='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE"
                . ",ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'"
        ];

theFeu · April 2, 2020, 9:42am

Hello there!

Thank you for letting us know
We’ll be looking into it!

Have a nice day,
Feu

elippmann · April 8, 2020, 9:13am

Yes, the resource must be set to reporting. Does the database specified for reporting in the resources.ini exist (both database and schema wise) and is accessible for the configured credentials?

Also, the scheduler does nothing as long as you did not configure any schedule for your reports.
With schedules, you have the possibility to automatically run configured reports.

The reporting module ist just the reporting “framework”. For IDO reports please also install https://github.com/Icinga/icingaweb2-module-idoreports.

Both reporting and idoreports have a postgresql feature branch which will be merged soon.