Page 1 of 1
Problems after offloading MySQL to Remote server
Posted: Tue Aug 23, 2016 5:36 pm
by 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?
Re: Problems after offloading MySQL to Remote server
Posted: Tue Aug 23, 2016 10:51 pm
by 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?
Re: Problems after offloading MySQL to Remote server
Posted: Wed Aug 24, 2016 3:53 pm
by 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(),...)
Re: Problems after offloading MySQL to Remote server
Posted: Wed Aug 24, 2016 4:52 pm
by ssax
You may have ONLY_FULL_GROUP_BY turned on, please send us your /etc/my.cnf from your offloaded DB.
Thank you
Re: Problems after offloading MySQL to Remote server
Posted: Wed Aug 24, 2016 5:15 pm
by 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
Re: Problems after offloading MySQL to Remote server
Posted: Wed Aug 24, 2016 5:19 pm
by 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.
Re: Problems after offloading MySQL to Remote server
Posted: Thu Aug 25, 2016 9:01 am
by rkennedy
Awesome! Are we good to mark this thread as resolved?