Nagios Database offload Issue

[maxiz1987]

Hi All,

I have followed the instructions of this doc: https://assets.nagios.com/downloads/nag ... Server.pdf - And I am having several issues.

Scenario:

-- NagiosXI 5.x
-- Remote MySQL DB - We are not using the standard port

I have followed all the steps on the guide - Below you can validate the config


[root@ log]# grep db_ /usr/local/nagios/etc/ndo2db.cfg
ndo2db_user=nagios
ndo2db_group=nagios
db_servertype=mysql
db_host=x.x.x.170
db_port=1971
db_name=nagios
db_prefix=nagios_
db_user=nagios
db_pass=nagios


/usr/local/nagiosxi/html/config.inc.php


$cfg['db_info'] = array(
"nagiosxi" => array(
"dbtype" => 'mysql',
"dbserver" => 'x.x.x.170',
"user" => 'nagiosxi',
"pwd" => 'nagiosxi',
"db" => 'nagiosxi',
"dbmaint" => array( // variables affecting maintenance of db
"max_auditlog_age" => 30, // max time (in DAYS) to keep audit log entries
"max_commands_age" => 480, // max time (minutes) to keep commands
"max_events_age" => 480, // max time (minutes) to keep events
"optimize_interval" => 60, // time (in minutes) between db optimization runs
"repair_interval" => 0, // time (in minutes) between db repair runs
),
),
"ndoutils" => array(
"dbtype" => 'mysql',
"dbserver" => 'x.x.x.x.170',
"user" => 'nagios',
"pwd" => 'nagios',
"db" => 'nagios',
"dbmaint" => array( // variables affecting maintenance of ndoutils db

"nagiosql" => array(
"dbtype" => 'mysql',
"dbserver" => 'x.x.x.170',
"user" => 'nagiosql',
"pwd" => 'nagiosql',
"db" => 'nagiosql',
"dbmaint" => array( // variables affecting maintenance of db
"max_logbook_age" => 480, // max time (minutes) to keep log book records
"optimize_interval" => 60, // time (in minutes) between db optimization runs
"repair_interval" => 0, // time (in minutes) between db repair runs
),
),
);


/usr/local/nagiosxi/html/config.inc.php
[db]
server = x.x.x.170
port = 1971
database = nagiosql
username = nagiosql
password = nagiosql
[common]
install = passed
~


-------------------------------------------------------------------------------------------

Note: For obvious reasons I have wrote on this post the server IP address as x.x.x.170 - However, in the config file the IP it is OK.

So I am having two differents error:

Error 1)

On /var/log/httpd/error.log


[Mon Sep 05 18:25:22.832778 2016] [:error] [pid 12938] [client .137:50449] PHP Warning: mysql_pconnect(): Can't connect to MySQL server on '.170' (111) in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-mysql.inc.php on line 383
[Mon Sep 05 18:25:22.832928 2016] [:error] [pid 12938] [client .137:50449] PHP Notice: Undefined variable: result in /usr/local/nagiosxi/html/includes/db.inc.php on line 249
[Mon Sep 05 18:25:22.857104 2016] [:error] [pid 12938] [client .137:50449] PHP Warning: mysql_pconnect(): Can't connect to MySQL server on '.170' (111) in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-mysql.inc.php on line 383
[Mon Sep 05 18:25:22.857188 2016] [:error] [pid 12938] [client .137:50449] PHP Notice: Undefined variable: result in /usr/local/nagiosxi/html/includes/db.inc.php on line 249
[Mon Sep 05 18:25:22.905069 2016] [:error] [pid 12938] [client .137:50449] PHP Warning: mysql_pconnect(): Can't connect to MySQL server on '.170' (111) in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-mysql.inc.php on line 383
[Mon Sep 05 18:25:22.905150 2016] [:error] [pid 12938] [client .137:50449] PHP Notice: Undefined variable: result in /usr/local/nagiosxi/html/includes/db.inc.php on line 249


I realized that these functions are looking for the db connect strings on config.inc.php and this file has not any special direction for DB PORT - Is this an issue? I have also tried hardcording the port on the "dbserver" => IP -Pport - However it didn't work.

I also have the following error on /var/log/messages:



Sep 5 18:25:09 ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_commands SET instance_id='1', object_id='122', config_type='1', command_line='/usr/bin/php /usr/local/nagiosxi/scripts/handle_nagioscore_notification\.php --notification-type=host --contact=\"\$CONTACTNAME\$\" --contactemail=\"\$CONTACTEMAIL\$\" --type=\$NOTIFICATIONTYPE\$ --escalated=\"\$NOTIFICATIONISESCALATED\$\" --author=\"\$NOTIFICATIONAUTHOR\$\" --comments=\"\$NOTIFICATIONCOMMENT\$\" --host=\"\$HOSTNAME\$\" --hostaddress=\"\$HOSTADDRESS\$\" --hostalias=\"\$HOSTALIAS\$\" --hostdisplayname=\"\$HOSTDISPLAYNAME\$\" --hoststate=\$HOSTSTATE\$ --hoststateid=\$HOSTSTATEID\$ --lasthoststate=\$LASTHOSTSTATE\$ --lasthoststateid=\$LASTHOSTSTATEID\$ --hoststatetype=\$HOSTSTATETYPE\$ --currentattempt=\$HOSTATTEMPT\$ --maxattempts=\$MAXHOSTATTEMPTS\$ --hosteventid=\$HOSTEVENTID\$ --hostproblemid=\$HOSTPROBLEMID\$ --hostoutput=\"\$HOSTOUTPUT\$\" --longhostoutput=\"\$LONGHOSTOUTPUT\$\" --datetime=\"\$LONGDATETIME\$\"' ON DUPLICATE KEY UPDATE instance_id='1', object_id='122', config_type='1', command_line='/usr/bin/php /usr/local/nagiosxi/scripts/handle_nagioscore_notification\.php --notification-type=host --contact=\"\$CONTACTNAME\$\" --contactemail=\"\$CONTACTEMAIL\$\" --type=\$NOTIFICATIONTYPE\$ --escalated=\"\$NOTIFICATIONISESCALATED\$\" --author=\"\$NOTIFICATIONAUTHOR\$\" --comments=\"\$NOTIFICATIONCOMMENT\$\" --host=\"\$HOSTNAME\$\" --hostaddress=\"\$HOSTADDRESS\$\" --hostalias=\"\$HOSTALIAS\$\" --hostdisplayname=\"\$HOSTDISPLAYNAME\$\" --hoststate=\$HOSTSTATE\$ --hoststateid=\$HOSTSTATEID\$ --lasthoststate=\$LASTHOSTSTATE\$ --lasthoststateid=\$LASTHOSTSTATEID\$ --hoststatetype=\$HOSTSTATETYPE\$ --currentattempt=\$HOSTATTEMPT\$ --maxattempts=\$MAXHOSTATTEMPTS\$ --hosteventid=\$HOSTEVENTID\$ --hostproblemid=\$HOSTPROBLEMID\$ --hostoutput=\"\$HOSTOUTPUT\$\" --longhostoutput=\"\$LONGHOSTOUTPUT\$\" --datetime=\"\$LONGDATETIME\$\"''
Sep 5 18:25:09 ndo2db: mysql_error: 'Data too long for column 'command_line' at row 1'
Sep 5 18:25:09 ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_commands SET instance_id='1', object_id='123', config_type='1', command_line='/usr/bin/php /usr/local/nagiosxi/scripts/handle_nagioscore_event\.php --handler-type=service --host=\"\$HOSTNAME\$\" --service=\"\$SERVICEDESC\$\" --hostaddress=\"\$HOSTADDRESS\$\" --hoststate=\$HOSTSTATE\$ --hoststateid=\$HOSTSTATEID\$ --hosteventid=\$HOSTEVENTID\$ --hostproblemid=\$HOSTPROBLEMID\$ --servicestate=\$SERVICESTATE\$ --servicestateid=\$SERVICESTATEID\$ --lastservicestate=\$LASTSERVICESTATE\$ --lastservicestateid=\$LASTSERVICESTATEID\$ --servicestatetype=\$SERVICESTATETYPE\$ --currentattempt=\$SERVICEATTEMPT\$ --maxattempts=\$MAXSERVICEATTEMPTS\$ --serviceeventid=\$SERVICEEVENTID\$ --serviceproblemid=\$SERVICEPROBLEMID\$ --serviceoutput=\"\$SERVICEOUTPUT\$\" --longserviceoutput=\"\$LONGSERVICEOUTPUT\$\" --servicedowntime=\$SERVICEDOWNTIME\$' ON DUPLICATE KEY UPDATE instance_id='1', object_id='123', config_type='1', command_line='/usr/bin/php /usr/local/nagiosxi/scripts/handle_nagioscore_event\.php --handler-type=service --host=\"\$HOSTNAME\$\" --service=\"\$SERVICEDESC\$\" --hostaddress=\"\$HOSTADDRESS\$\" --hoststate=\$HOSTSTATE\$ --hoststateid=\$HOSTSTATEID\$ --hosteventid=\$HOSTEVENTID\$ --hostproblemid=\$HOSTPROBLEMID\$ --servicestate=\$SERVICESTATE\$ --servicestateid=\$SERVICESTATEID\$ --lastservicestate=\$LASTSERVICESTATE\$ --lastservicestateid=\$LASTSERVICESTATEID\$ --servicestatetype=\$SERVICESTATETYPE\$ --currentattempt=\$SERVICEATTEMPT\$ --maxattempts=\$MAXSERVICEATTEMPTS\$ --serviceeventid=\$SERVICEEVENTID\$ --serviceproblemid=\$SERVICEPROBLEMID\$ --serviceoutput=\"\$SERVICEOUTPUT\$\" --longserviceoutput=\"\$LONGSERVICEOUTPUT\$\" --servicedowntime=\$SERVICEDOWNTIME\$''
Sep 5 18:25:09 1 ndo2db: mysql_error: 'Data too long for column 'command_line' at row 1'
Sep 5 18:25:09 ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_commands SET instance_id='1', object_id='124', config_type='1', command_line='/usr/bin/php /usr/local/nagiosxi/scripts/handle_nagioscore_notification\.php --notification-type=service --contact=\"\$CONTACTNAME\$\" --contactemail=\"\$CONTACTEMAIL\$\" --type=\$NOTIFICATIONTYPE\$ --escalated=\"\$NOTIFICATIONISESCALATED\$\" --author=\"\$NOTIFICATIONAUTHOR\$\" --comments=\"\$NOTIFICATIONCOMMENT\$\" --host=\"\$HOSTNAME\$\" --hostaddress=\"\$HOSTADDRESS\$\" --hostalias=\"\$HOSTALIAS\$\" --hostdisplayname=\"\$HOSTDISPLAYNAME\$\" --service=\"\$SERVICEDESC\$\" --hoststate=\$HOSTSTATE\$ --hoststateid=\$HOSTSTATEID\$ --servicestate=\$SERVICESTATE\$ --servicestateid=\$SERVICESTATEID\$ --lastservicestate=\$LASTSERVICESTATE\$ --lastservicestateid=\$LASTSERVICESTATEID\$ --servicestatetype=\$SERVICESTATETYPE\$ --currentattempt=\$SERVICEATTEMPT\$ --maxattempts=\$MAXSERVICEATTEMPTS\$ --serviceeventid=\$SERVICEEVENTID\$ --serviceproblemid=\$SERVICEPROBLEMID\$ --serviceoutput=\"\$SERVICEOUTPUT\$\" --longserviceoutput=\"\$LONGSERVICEOUTPUT\$\" --datetime=\"\$LONGDATETIME\$\"' ON DUPLICATE KEY UPDATE instance_id='1', object_id='124', config_type='1', command_line='/usr/bin/php /usr/local/nagiosxi/scripts/handle_nagioscore_notification\.php --notification-type=service --contact=\"\$CONTACTNAME\$\" --contactemail=\"\$CONTACTEMAIL\$\" --type=\$NOTIFICATIONTYPE\$ --escalated=\"\$NOTIFICATIONISESCALATED\$\" --author=\"\$NOTIFICATIONAUTHOR\$\" --comments=\"\$NOTIFICATIONCOMMENT\$\" --host=\"\$HOSTNAME\$\" --hostaddress=\"\$HOSTADDRESS\$\" --hostalias=\"\$HOSTALIAS\$\" --hostdisplayname=\"\$HOSTDISPLAYNAME\$\" --service=\"\$SERVICEDESC\$\" --hoststate=\$HOSTSTATE\$ --hoststateid=\$HOSTSTATEID\$ --servicestate=\$SERVICESTATE\$ --servicestateid=\$SERVICESTATEID\$ --lastservicestate=\$LASTSERVICESTATE\$ --lastservicestateid=\$LASTSERVICESTATEID\$ --servicestatetype=\$SERVICESTATETYPE\$ --currentattempt=\$SERVICEATTEMPT\$ --maxattempts=\$MAXSERVICEATTEMPTS\$ --serviceeventid=\$SERVICEEVENTID\$ --serviceproblemid=\$SERVICEPROBLEMID\$ --serviceoutput=\"\$SERVICEOUTPUT\$\" --longserviceoutput=\"\$LONGSERVICEOUTPUT\$\" --datetime=\"\$LONGDATETIME\$\"''
Sep 5 18:25:09 ndo2db: mysql_error: 'Data too long for column 'command_line' at row 1'



I have no idea about this.


Last thing - The connection is OK


[root@ var]# echo 'STATUS;'| mysql -u nagios -p'nagios' -h .170 -P 1971 nagios
--------------
mysql Ver 15.1 Distrib 5.5.50-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 61
Current database: nagios
Current user: nagios@
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MySQL
Server version: 5.6.32-log MySQL Community Server (GPL)
Protocol version: 10
Connection: .170 via TCP/IP
Server characterset: latin1
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 1971
Uptime: 25 days 5 hours 52 min 45 sec

Threads: 2 Questions: 9710 Slow queries: 0 Opens: 461 Flush tables: 1 Open tables: 237 Queries per second avg: 0.004
--------------

[root@ var]# echo 'STATUS;'| mysql -u nagiosql -p'nagiosql' -h .170 -P 1971
--------------
mysql Ver 15.1 Distrib 5.5.50-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 62
Current database:
Current user: nagiosql@
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MySQL
Server version: 5.6.32-log MySQL Community Server (GPL)
Protocol version: 10
Connection: .170 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 1971
Uptime: 25 days 5 hours 52 min 56 sec

Threads: 2 Questions: 9715 Slow queries: 0 Opens: 461 Flush tables: 1 Open tables: 237 Queries per second avg: 0.004
--------------

[root@var]# echo 'STATUS;'| mysql -u nagiosxi -p'nagiosxi' -h 170 -P 1971
--------------
mysql Ver 15.1 Distrib 5.5.50-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 63
Current database:
Current user: nagiosxi@
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MySQL
Server version: 5.6.32-log MySQL Community Server (GPL)
Protocol version: 10
Connection: .170 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 1971
Uptime: 25 days 5 hours 53 min 4 sec

Threads: 2 Questions: 9720 Slow queries: 0 Opens: 461 Flush tables: 1 Open tables: 237 Queries per second avg: 0.004
--------------

[rkennedy]

This post seems to be pretty similar - https://support.nagios.com/forum/viewto ... 5&p=107741

You may need to change the datatype to text instead varchar as it probably can't handle all of the output.

Other then that, what issues are you experiencing with the offload?

[maxiz1987]

Hi !

I will try to change the database type and see what happen.

I am also having this issue with the conncetion - On my post I have exaplained that the database uses a different port than the default one. And when I am asked to change the data source config on NagiosXI there is no option for changing the port as it is on Nagios core config.

I am not sure if the log error that I see on httpd/error.conf are related to this.

Regards,

[mcapra]

I am also having this issue with the conncetion - On my post I have exaplained that the database uses a different port than the default one.

From this document:
https://assets.nagios.com/downloads/nag ... Server.pdf

A few different places are mentioned where the port would be changed from 3306 to whatever port you are using. If you simply do a find of "3306" in that doc, it should lead you to the places where the port would need to be changed.

[maxiz1987]

I am also having this issue with the conncetion - On my post I have exaplained that the database uses a different port than the default one.

From this document:
https://assets.nagios.com/downloads/nag ... Server.pdf

A few different places are mentioned where the port would be changed from 3306 to whatever port you are using. If you simply do a find of "3306" in that doc, it should lead you to the places where the port would need to be changed.

Hi! Thanks for replying - I have changed the port at the following files:

--> /usr/local/nagios/etc/ndo2db.cfg
--> /var/www/html/nagiosql/config/settings.php


However, the following file doesn't not have a place to configure a port of the db:

--> /usr/local/nagiosxi/html/config.inc.php

On this file you only have the following variables declaration:

----” => array(
This entry is for the nagios database. Under this entry, make sure the following are the values:
“dbtype” => 'mysql',
“dbserver” => '<IP_OF_MYSQL_OR_MARIADB_SERVER>',
“user” => '',
“pwd” => '',


I didn't inspect the code really deep, but as long as I have checked it - the xi takes this variables to make the connection to the DB.

So that is why I am asking this - Since I have the following error:

[Mon Sep 05 18:25:22.832778 2016] [:error] [pid 12938] [client .137:50449] PHP Warning: mysql_pconnect(): Can't connect to MySQL server on '.170' (111) in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-mysql.inc.php on line 383

Have any one followed the DB Offload procedure with a non standard 3306 port ?

Regards,

[Box293]

From what I understand, wherever there is this:
“dbserver” => '<IP_OF_MYSQL_OR_MARIADB_SERVER>',

It needs to be <IP_OF_MYSQL_OR_MARIADB_SERVER>:<PORT>

[maxiz1987]

From what I understand, wherever there is this:
“dbserver” => '<IP_OF_MYSQL_OR_MARIADB_SERVER>',

It needs to be <IP_OF_MYSQL_OR_MARIADB_SERVER>:<PORT>

Hi @Box293 - From what I understand when you are connecting to a remote mysql you have to specify the port with the --port=<> or -P<> flag - However, I will test it and I will let you know!

This will only apply to --> /usr/local/nagiosxi/html/config.inc.php - right ?

Regards,

[Box293]

This will only apply to --> /usr/local/nagiosxi/html/config.inc.php - right ?

Correct, this change is only required for the config.inc.php file.

[maxiz1987]

This will only apply to --> /usr/local/nagiosxi/html/config.inc.php - right ?

Correct, this change is only required for the config.inc.php file.

Hey @Box293 - the IP:PORT on the config.inc.php worked ok for me.

Thank you very much.

This thread can be closed.

Regards,