Offloaded MySQL db - table not found

[prmmover]

system - nagiosXI 2011R3.1
O/S - RHEL6 64-bit Virtual Machine on Citrix Xenserver
remote DB - MySQL 5.5 on RHEL6 physical machine

I've followed the steps in the library topic for offloading MySQL to a remote server )http://assets.nagios.com/downloads/nagi ... Server.pdf), and most of it is working fine (including fixing the config in /var/www/html/nagiosxi/config to allow the Core manager to find the remote DB as per this forum topic - http://support.nagios.com/forum/viewtop ... sql#p26913.

However, when I log into NagiosXI Web UI, I get the following error:
SQL: SQL Error [ndoutils] : Table 'nagiosql.nagios_programstatus' doesn't exist SQL: SQL Error [ndoutils] : Table 'nagiosql.nagios_programstatus' doesn't exist

ndo2db is configured to use the "nagios" database:
#####################################################################
# NDO2DB DAEMON CONFIG FILE
#####################################################################
...
db_servertype=mysql
db_host=10.2.50.46
db_port=3306
db_name=nagios
db_prefix=nagios_

And the table exists:

mysql> show tables;
+----------------------------------------+
| Tables_in_nagios |
+----------------------------------------+
...
| nagios_programstatus |
...

But, from the error, it seems it's trying to look into the nagiosql database, which of course doesn't have the table in it.

Thoughts?

-Gary

[scottwilkerson]

Look at section 2-b) on page 3 of http://assets.nagios.com/downloads/nagi ... Server.pdf

I am thinking you might have something wrong in this section of /usr/local/nagiosxi/html/config.inc.php

Code: Select all

“ndoutils” => array(
“dbtype” => 'mysql',
“dbserver” => '<IP_OF_MYSQL_SERVER>',
“user” => 'nagios',
“pwd” => 'nagios',
“db” => 'nagios',

[prmmover]

Sorry, should have posted those configs initially as well. Everything looks like it should there:

"ndoutils" => array(
"dbtype" => 'mysql',
"dbserver" => '10.2.50.46',
"user" => 'xxxxx',
"pwd" => 'xxxxxxx',
"db" => 'nagios',


"nagiosql" => array(
"dbtype" => 'mysql',
"dbserver" => '10.2.50.46',
"user" => 'xxxxxxx',
"pwd" => 'xxxxxx',
"db" => 'nagiosql',

[scottwilkerson]

What page in the web ui do you get this error?

[prmmover]

On login, I get a popup box with the initial error message.

Then, pretty much any page on the left navigation pane that has to deal with the status' like tactical overview, open/all service problems, hostgroup/service status, etc.

Although it's primarily showing this table as missing, it isn't limited to this table, under the notifications navi link, a similar error: SQL: SQL Error [ndoutils] : Table 'nagiosql.nagios_notifications' doesn't exist SQL: SQL Error [ndoutils] : Table 'nagiosql.nagios_notifications' doesn't exist

Also, dbmaint.log is telling me pretty much the same thing for all tables:# more dbmaint.log
CREATING: /usr/local/nagiosxi/var/dbmaint.lock
CLEANING ndoutils TABLE 'commenthistory'...
SQL: DELETE FROM nagios_commenthistory WHERE entry_time < FROM_UNIXTIME(1308082501)
SQL: SQL Error [ndoutils] :</b> Table 'nagiosql.nagios_commenthistory' doesn't existCLEANING ndoutils TABLE 'processevent
s'...

And, just making sure we're able to connect from the nagios machine to the db server with valid credentials:

# mysql -u nagiosadm -p -h 10.2.50.46 nagios
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11560
Server version: 5.5.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| nagios |
| nagiosql |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> use nagios;
Database changed
mysql> show tables;
+----------------------------------------+
| Tables_in_nagios |
+----------------------------------------+
| nagios_acknowledgements |
| nagios_commands |
| nagios_commenthistory |
| nagios_comments |
| nagios_configfiles |


I'm stumped.

You think I should try importing the tables into the nagiosql db to see if it clears the errors? I can always restore the dbs back to original state, as this is basically an initial install, I haven't added any hosts to monitor yet.

-Gary

[scottwilkerson]

I'm stumped.

You think I should try importing the tables into the nagiosql db to see if it clears the errors? I can always restore the dbs back to original state, as this is basically an initial install, I haven't added any hosts to monitor yet.

No don't do that.

Can you PM me a copy of your /usr/local/nagiosxi/html/config.inc.php and also run the following and post the results

Code: Select all

ls -l /usr/local/nagiosxi/html/

[prmmover]

I'll PM you the file in a sec, here's the output of the directory listing:

[root@pscls0105 var]# cd /usr/local/nagiosxi/html/
[root@pscls0105 html]# ls -l
total 216
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 about
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 account
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 addons
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 admin
-rwxr-xr-x 1 nagios nagios 19579 Jun 12 16:40 ajaxhelper.php
-rwxr-xr-x 1 nagios nagios 1533 Jun 12 16:40 ajaxproxy.php
drwxr-xr-x 3 nagios nagios 4096 Jun 12 16:40 backend
drwxr-xr-x 3 nagios nagios 4096 Jun 12 16:40 config
-rwxr-xr-x 1 nagios nagios 7832 Jun 13 16:01 config.inc.php
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 dashboards
drwxr-xr-x 4 nagios nagios 4096 Jun 12 16:40 db
drwxr-xr-x 3 nagios nagios 4096 Jun 12 16:40 downloads
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 help
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 images
drwxr-xr-x 11 nagios nagios 4096 Jun 12 16:40 includes
-rwxr-xr-x 1 nagios nagios 991 Jun 12 16:40 index.php
-rwxr-xr-x 1 nagios nagios 6758 Jun 12 16:40 install.php
-rwxr-xr-x 1 nagios nagios 78275 Jun 12 16:40 login.php
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 perfgraphs
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 reports
-rw-r--r-- 1 nagios nagios 14761 Jun 12 16:40 rr.php
-rwxr-xr-x 1 nagios nagios 5838 Jun 12 16:40 suggest.php
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 tools
-rwxr-xr-x 1 nagios nagios 3463 Jun 12 16:40 upgrade.php
drwxr-xr-x 2 nagios nagios 4096 Jun 12 16:40 views

[scottwilkerson]

Did you change any other php files under /usr/local/nagiosxi/html/ ?

[prmmover]

No, just the config.inc.php file. I went searching through a bunch of things, but didn't edit any as I didn't see any that had relevant db config info in them.

Only other file I changed was the one under /var/www/html/nagiosxi/config to set the db credentials for the Core Config Manager.

I'm going to put the original config files back to point to the local mysql db instance and see if I get the same errors.

[prmmover]

Ok, so putting back the original config files pointing to localhost and starting the local mysql db makes everything right again. Everything starts up OK, logging in shows no error, and no errors are showing on any of the status pages.

Re-editing the files to point to the remote db (and checking to make sure they're set with the right db names and credentials), the errors all come back.

I think I'm going to try firing up another clean, new VM, and performing another clean install of nagios, and create a couple clean new db's, and repeat this process.