Problems after offloading MySQL to Remote server

[anthv123]

I followed the steps at https://assets.nagios.com/downloads/nag ... Server.pdf to offload MySQL to a remote server on a fresh install of nagiosxi. After migrating data, updating config files, and restarting ndo2db and nagios the following occurs:
* Attempting to "Apply Configuration" without actually making any changes (e.g. adding more hosts and services) fails with "ERROR: 0 services configured" even though the localhost host and services are present in CCM.
* This "Apply Configuration" failure causes my config files to rollback to the last version where db_host was set to "localhost"

I have verified there is a host and a service config file in /usr/local/nagios/etc/hosts/ and /usr/local/nagios/etc/services/ for localhost. I have verified the remote database also has records for these. I have verified I can access the remote database from the nagiosxi server using the correct username and password for each database. I have verified mysql is NOT running on the nagiosxi server.

Flipping the config files back to "localhost" and starting mysql on the nagiosxi server fixes this problem.

I'm running version 5.2.9 of nagiosxi on CentOS 6.

Any chance there are more config files to update than are listed in the linked document? How can I further diagnose what's happening?

[Box293]

I suspect the errors you are getting are misleading, the actual issue has to do with the offloaded configuration, not with service issues.

Revert the changes so your server is using the offloaded DB.

Run these commands and send back the output:

Code: Select all

cd /usr/local/nagiosxi/scripts/
./reconfigure_nagios.sh

What is output in the SSH session?

[anthv123]

Output from /usr/local/nagiosxi/scripts/reconfigure_nagios.sh

Code: Select all

URL: http://localhost/nagiosxi/includes/components/ccm/
CMDLINE
/usr/bin/wget --save-cookies nagiosql.cookies --keep-session-cookies http://localhost/nagiosxi/includes/components/ccm/ --no-check-certificate --post-data 'submit=Login&hidelog=true&loginSubmitted=true&username=nagiosxi&password=<redacted>' -O nagiosql.login--2016-08-24 20:45:04--  http://localhost/nagiosxi/includes/components/ccm/
Resolving localhost... ::1, 127.0.0.1
Connecting to localhost|::1|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: “nagiosql.login”

    [ <=>                                                                                                                                                 ] 31,322      --.-K/s   in 0.03s   

2016-08-24 20:45:05 (1.01 MB/s) - “nagiosql.login” saved [31322]

LOGIN SUCCESSFUL!
IMPORTING CONFIG FILES...URL: http://localhost/nagiosxi/includes/components/ccm/
Array
(
)
RESETTING PERMS
URL: http://localhost/nagiosxi/includes/components/ccm/
CMDLINE
/usr/bin/wget --save-cookies nagiosql.cookies --keep-session-cookies http://localhost/nagiosxi/includes/components/ccm/ --no-check-certificate --post-data 'submit=Login&hidelog=true&loginSubmitted=true&username=nagiosxi&password=<redacted>' -O nagiosql.login--2016-08-24 20:45:05--  http://localhost/nagiosxi/includes/components/ccm/
Resolving localhost... ::1, 127.0.0.1
Connecting to localhost|::1|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: “nagiosql.login”

    [ <=>                                                                                                                                                 ] 31,322      --.-K/s   in 0.03s   

2016-08-24 20:45:05 (1.04 MB/s) - “nagiosql.login” saved [31322]

LOGIN SUCCESSFUL!
URL: http://localhost/nagiosxi/includes/components/ccm/
CMDLINE:
/usr/bin/wget --load-cookies=nagiosql.cookies http://localhost/nagiosxi/includes/components/ccm/ --no-check-certificate --post-data 'cmd=apply&type=writeConfig' -O nagiosql.export.monitoring
--2016-08-24 20:45:06--  http://localhost/nagiosxi/includes/components/ccm/
Resolving localhost... ::1, 127.0.0.1
Connecting to localhost|::1|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: “nagiosql.export.monitoring”

    [  <=>                                                                                                                                                ] 15,274      15.9K/s   in 0.9s    

2016-08-24 20:45:07 (15.9 KB/s) - “nagiosql.export.monitoring” saved [15274]

WRITE CONFIGS SUCCESSFUL!
OUTPUT: 
Nagios Core 4.1.1
Copyright (c) 2009-present Nagios Core Development Team and Community Contributors
Copyright (c) 1999-2009 Ethan Galstad
Last Modified: 08-19-2015
License: GPL

Website: https://www.nagios.org
Reading configuration data...
   Read main config file okay...
   Read object config files okay...

Running pre-flight check on configuration data...

Checking objects...
Error: There are no services defined!
	Checked 0 services.
	Checked 1 hosts.
	Checked 1 host groups.
	Checked 0 service groups.
	Checked 2 contacts.
	Checked 2 contact groups.
	Checked 124 commands.
	Checked 8 time periods.
	Checked 0 host escalations.
	Checked 0 service escalations.
Checking for circular paths...
	Checked 1 hosts
	Checked 0 service dependencies
	Checked 0 host dependencies
	Checked 8 timeperiods
Checking global event handlers...
Checking obsessive compulsive processor commands...
Checking misc settings...

Total Warnings: 0
Total Errors:   1

***> One or more problems was encountered while running the pre-flight check...

     Check your configuration file(s) to ensure that they contain valid
     directives and data defintions.  If you are upgrading from a previous
     version of Nagios, you should be aware that some variables/definitions
     may have been removed or modified in this version.  Make sure to read
     the HTML documentation regarding the config files, as well as the
     'Whats New' section to find out what has changed.
RET: 1
/usr/local/nagiosxi/nom/checkpoints/nagioscore/errors /usr/local/nagiosxi/scripts
tar: Removing leading `/' from member names
/usr/local/nagiosxi/scripts
LATEST NOM SNAPSHOT: /usr/local/nagiosxi/nom/checkpoints/nagioscore/1472070003.tar.gz
/ /usr/local/nagiosxi/scripts
RESTORING NOM SNAPSHOT : /usr/local/nagiosxi/nom/checkpoints/nagioscore/1472070003.tar.gz
/usr/local/nagiosxi/scripts
RESETTING PERMS

I found this forum post regarding the sql_mode global mysql setting: https://support.nagios.com/forum/viewto ... l&start=10
Updating my remote mysql to have sql_mode='' allows me to apply my configuration, but I still see an error on the home page of:

SQL: SQL Error [ndoutils] : Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause SQL: SQL Error [ndoutils] : Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)

[ssax]

You may have ONLY_FULL_GROUP_BY turned on, please send us your /etc/my.cnf from your offloaded DB.

Thank you

[anthv123]

my.cnf:

Code: Select all

[mysql]

# CLIENT #
port                           = 3306

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
lower_case_table_names         = 1
pid-file                       = /var/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 32M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = ""
sysdate-is-now                 = 1
innodb                         = FORCE
innodb-strict-mode             = 1

# DATA STORAGE #
datadir                        = /var/mysql

# BINARY LOGGING #
log-bin                        = /var/mysql/mysql-bin
expire-logs-days               = 7
sync-binlog                    = 1

# REPLICATION #
read-only                      = 0
skip-slave-start               = 1
log-slave-updates              = 1
relay-log                      = /var/mysql/relay-bin
slave-net-timeout              = 60

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 1000
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DSYNC
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2g

# LOGGING #
log-error                      = /var/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/mysql/mysql-slow.log

server-id                      = 1

[anthv123]

It looks like I needed to restart mysql for the change, i.e. changing sql-mode to an empty string, to fully take affect. I am no longer seeing that error on the home page and can apply configuration.

[rkennedy]

Awesome! Are we good to mark this thread as resolved?