Not able to apply configuration after Mysql Offload

[ellism]

ndo2db.cfg look OK. I made it look like what the Nagios XI - Offloading MySQL to Remote Server document said.

nmap command:
Starting Nmap 6.47 ( http://nmap.org ) at 2016-02-10 01:28 EST
Nmap scan report for 192.168.7.80
Host is up (0.000088s latency).
Not shown: 997 closed ports
PORT STATE SERVICE VERSION
22/tcp open ssh OpenSSH 5.3 (protocol 2.0)
111/tcp open rpcbind 2-4 (RPC #100000)
3306/tcp open mysql MySQL 5.7.10

One thing I am curious about, it seems you still need to have mysqld running on the nagios XI server even though you have offloaded to a remote MySQL server. The document says to only create two databases, one called "nagios" the other " nagiosql" and then a mysqldump from the original server of the two databases nagios and nagiosql. The main reason I need to offload is the that Nagios is using a very old version of MySQl 5.1.73 which fails all the IA security findings. Why does nagios still use such an old version. I performed and offload install using Nagios XI 5.2.3 so I would think they should include the latest MySQL. Starting not to like Nagios.....

[hsmith]

One thing I am curious about, it seems you still need to have mysqld running on the nagios XI server even though you have offloaded to a remote MySQL server

If you move the nagoisxi database to the other server as well, that should not be the case. We have a system here that is using mysql completely offloaded and not running at all on the local machine. The document does not reflect that because it's not 100% tested, since we moved away from the nagioxi database being in postgresql in previous versions.

The document says to only create two databases, one called "nagios" the other " nagiosql" and then a mysqldump from the original server of the two databases nagios and nagiosql.

That is correct, but if you're looking to move everything, you should move the nagiosxi database as well.

The main reason I need to offload is the that Nagios is using a very old version of MySQl 5.1.73 which fails all the IA security findings. Why does nagios still use such an old version.

This is the latest version in the CentOS/RHEL repository. They are generally very good about making sure they don't leave insecure software sitting in there. If you try to update MySQL via yum, it is going to tell you you're on the latest version. Here's a nice quote from ServerFault I just pulled up:

Red Hat (and Ubuntu for that matter) never, ever publish a new major version of software into their package repositories for a given OS version, for the sake of stability - you don't want to run an upgrade and suddenly have your config not work.

Instead, they backport security fixes and important bugfixes into the version of the software that was 'stable' when the OS was released, avoiding the introduction of new features (and their bugs).

I performed and offload install using Nagios XI 5.2.3 so I would think they should include the latest MySQL

I think I explained that pretty well in the above posts. These are the versions supported on the operating system you are using.

Starting not to like Nagios.....

I'm sorry that you feel this way. If you have any feedback you would like to share with me, I'm all ears. Hopefully after reading my posts above you understand why things are the way that they are.

As far as resolving the database issue, versions of MySQL that are not available on the official repositories are not tested by us, so we cannot confirm whether or not it is going to work. Upload your /usr/local/nagiosxi/html/config.inc.php for us to take a look at.

[ellism]

Just spent about 3 hours with Nagios Tech support and figured it out.

On the Nagios XI server you need to do a export LC=C

Since I am using MySQL 5.7.10 on the remote MySQL server you need to do:

mysql> set GLOBAL sql_mode = ' '; (basically just two tick marks);

SQL cannot have ONLY_FULL_GROUP_BY set, which was interfering with the services for some reason.

Better document this one if you plan on offloading to MySQL 5.7.x

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

[lmiltchev]

Can you apply configuration now? Is you issue fully resolved?

[ellism]

Yes the problem is solved.

ONLY_FULL_GROUP_BY

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.

I understand about what RedHat keeps in its repo for Mysql. The issue is for government security regulations this is considered a CAT I, also having the database on the same machine is a no-no as well, this is why I am having to separate the two.

So basically everything was correct except those two statements. I think Nagios is going to add this to their documentation.

Thanks for all the help, good forum with lots of good help.

[rkennedy]

It took a while, but glad to see it resolved!

I'm going to close this thread out now, feel free to open a new one if you ever need assistance in the future.