Apply config error after migrating to external MySQL

[corkyman]

I have migrated our preproduction environment to an external MySQL cluster. After the migration the GUI is working fine and is showing hosts and services but Apply Configuration received one error:

Checking objects...
Error: There are no services defined!
Checked 0 services.

The GUI shows 361 services.

What could go wrong here?

[rkennedy]

Did you move over all of the data that was on the existing MySQL server? It sounds like your new SQL server may be missing the data somewhere.

[corkyman]

I am contacting the DBA to confirm and possibly move the data again.

[rkennedy]

Sounds good - let us know how it goes.

[corkyman]

I stopped Nagios and my dba dropped the db, copied it again and I have the same problem--apply config failed

[rkennedy]

Hmm, let's verify a few things:
- What DB and version is running on the offloaded machine?
- Please run the following on both machines and identify each machine (time will need to match up - some of these commands will fail.)

Code: Select all

grep "date.timezone" /etc/php.ini
ls -l /etc/localtime
php -r 'echo date("D M j G:i:s T Y")."\n";'
date
mysql -unagiosxi -pn@gweb -e "SELECT NOW();"

- Also, please run this against the offloaded DB, and paste the result:

Code: Select all

echo 'use nagiosql; select * from `tbl_host` limit 5;' | mysql -uroot -pnagiosxi -h ip.of.offloaded.db

- This too:

Code: Select all

echo 'use nagiosql; describe tbl_host;' | mysql -uroot -pnagiosxi -h 127.0.0.1

[corkyman]

Primary Nagios
-bash-4.1$ grep "date.timezone" /etc/php.ini
; http://www.php.net/manual/en/datetime.c ... e.timezone
date.timezone = US/Eastern
-bash-4.1$ ls -l /etc/localtime
lrwxrwxrwx 1 root root 30 Feb 23 2016 /etc/localtime -> /usr/share/zoneinfo/US/Eastern
-bash-4.1$ php -r 'echo date("D M j G:i:s T Y")."\n";'
Thu Nov 17 13:22:19 EST 2016
-bash-4.1$ date
Thu Nov 17 13:22:28 EST 2016
-bash-4.1$ mysql -unagiosxi -pn@gweb -e "SELECT NOW();"
+---------------------+
| NOW() |
+---------------------+
| 2016-11-17 13:22:39 |
+---------------------+

Backup Nagios
-bash-4.1$ grep "date.timezone" /etc/php.ini
; http://www.php.net/manual/en/datetime.c ... e.timezone
date.timezone = US/Eastern
-bash-4.1$ ls -l /etc/localtime
lrwxrwxrwx 1 root root 30 Feb 23 2016 /etc/localtime -> /usr/share/zoneinfo/US/Eastern
-bash-4.1$ php -r 'echo date("D M j G:i:s T Y")."\n";'
Thu Nov 17 13:24:40 EST 2016
-bash-4.1$ date
Thu Nov 17 13:24:42 EST 2016
-bash-4.1$ mysql -unagiosxi -pn@gweb -e "SELECT NOW();"
+---------------------+
| NOW() |
+---------------------+
| 2016-11-17 13:24:50 |
+---------------------+


mysql> select * from `tbl_host` limit 5;
+----+-----------------------------+-------------------------+---------------------+--------------------------+---------+--------------------+------------+-----------------------+---------------+--------------+-------------------------+---------------+--------------------+----------------+----------------+-----------------------+------------------------+--------------+------------------+-----------------+---------------------+---------------+-----------------------+--------------------+---------------------+------------------------+------------------------+-------------------+---------------------------+------------------------------+----------+---------------------+----------------+---------------------------+-----------------------+---------------------+--------------------------+----------------------+-----------------------+------------------+-------+-----------+------------+--------------------+----------------+------------+--------------------+-----------+-----------+---------------+------+--------+---------------------+---------------+-----------+
| id | host_name | alias | display_name | address | parents | parents_tploptions | hostgroups | hostgroups_tploptions | check_command | use_template | use_template_tploptions | initial_state | max_check_attempts | check_interval | retry_interval | active_checks_enabled | passive_checks_enabled | check_period | obsess_over_host | check_freshness | freshness_threshold | event_handler | event_handler_enabled | low_flap_threshold | high_flap_threshold | flap_detection_enabled | flap_detection_options | process_perf_data | retain_status_information | retain_nonstatus_information | contacts | contacts_tploptions | contact_groups | contact_groups_tploptions | notification_interval | notification_period | first_notification_delay | notification_options | notifications_enabled | stalking_options | notes | notes_url | action_url | icon_image | icon_image_alt | vrml_image | statusmap_image | 2d_coords | 3d_coords | use_variables | name | active | last_modified | access_rights | config_id |
+----+-----------------------------+-------------------------+---------------------+--------------------------+---------+--------------------+------------+-----------------------+---------------+--------------+-------------------------+---------------+--------------------+----------------+----------------+-----------------------+------------------------+--------------+------------------+-----------------+---------------------+---------------+-----------------------+--------------------+---------------------+------------------------+------------------------+-------------------+---------------------------+------------------------------+----------+---------------------+----------------+---------------------------+-----------------------+---------------------+--------------------------+----------------------+-----------------------+------------------+-------+-----------+------------+--------------------+----------------+------------+--------------------+-----------+-----------+---------------+------+--------+---------------------+---------------+-----------+
| 11 | vhldvngmn001.tvlport.net | Dev Gearman Worker - FE | | vhldvngmn001.tvlport.net | 0 | 2 | 1 | 2 | 0!!!!!!!! | 1 | 2 | | 5 | 5 | 1 | 2 | 2 | 7 | 2 | 2 | NULL | 0 | 2 | NULL | NULL | 2 | | 2 | 2 | 2 | 1 | 2 | 0 | 2 | 60 | 7 | NULL | | 2 | | | | | snmppenguin.png | | | snmppenguin.png | | | 1 | | 1 | 2016-03-10 15:42:23 | NULL | 1 |
| 2 | vhlgnngxi072.tvlport.net | Dev Nagios XI Slave | Nagios XI Slave | vhlgnngxi072.tvlport.net | 0 | 2 | 1 | 2 | 0!!!!!!!! | 1 | 2 | | 5 | 5 | 1 | 2 | 2 | 7 | 2 | 2 | NULL | 0 | 2 | NULL | NULL | 2 | | 2 | 2 | 2 | 1 | 2 | 1 | 2 | 60 | 7 | 0 | | 1 | | | | | nagiosxiserver.png | | | nagiosxiserver.png | | | 1 | | 1 | 2016-04-14 17:47:47 | NULL | 1 |
| 3 | vhlgnngrg071.tvlport.net | Dev Graphite Server | Dev Graphite Server | 10.5.197.42 | 0 | 2 | 1 | 2 | 0!!!!!!!! | 1 | 2 | | 1 | 1 | 1 | 1 | 1 | 7 | 2 | 2 | NULL | 0 | 2 | NULL | NULL | 2 | | 2 | 2 | 2 | 1 | 2 | 0 | 2 | 60 | 7 | 0 | | 1 | | | | | snmppenguin.png | | | snmppenguin.png | | | 1 | | 1 | 2016-05-12 18:12:23 | NULL | 1 |
| 5 | APPDHPDVGW0020V.tvlport.net | | | 10.5.193.81 | 0 | 2 | 1 | 2 | 3!!!!!!!! | 1 | 2 | | 5 | 5 | 1 | 2 | 2 | 7 | 2 | 2 | NULL | 0 | 2 | NULL | NULL | 2 | | 2 | 2 | 2 | 1 | 2 | 0 | 2 | 60 | 7 | 0 | d,u,r,f, | 1 | | | | | win_server.png | | | win_server.png | | | 1 | | 1 | 2016-03-23 14:26:32 | NULL | 1 |
| 7 | vhldvngmn002.tvlport.net | Dev Gearman Worker - FE | | 10.4.113.231 | 0 | 2 | 0 | 2 | 0!!!!!!!! | 1 | 2 | | 5 | 5 | 1 | 2 | 2 | 7 | 2 | 2 | NULL | 0 | 2 | NULL | NULL | 2 | | 2 | 2 | 2 | 1 | 2 | 0 | 2 | 60 | 7 | NULL | | 2 | | | | | snmppenguin.png | | | snmppenguin.png | | | 1 | | 1 | 2016-03-10 15:11:53 | NULL | 1 |
+----+-----------------------------+-------------------------+---------------------+--------------------------+---------+--------------------+------------+-----------------------+---------------+--------------+-------------------------+---------------+--------------------+----------------+----------------+-----------------------+------------------------+--------------+------------------+-----------------+---------------------+---------------+-----------------------+--------------------+---------------------+------------------------+------------------------+-------------------+---------------------------+------------------------------+----------+---------------------+----------------+---------------------------+-----------------------+---------------------+--------------------------+----------------------+-----------------------+------------------+-------+-----------+------------+--------------------+----------------+------------+--------------------+-----------+-----------+---------------+------+--------+---------------------+---------------+-----------+
5 rows in set (0.00 sec)

mysql> describe tbl_host;
+------------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------+---------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| host_name | varchar(255) | NO | MUL | NULL | |
| alias | varchar(255) | NO | | NULL | |
| display_name | varchar(255) | YES | | | |
| address | varchar(255) | NO | | NULL | |
| parents | tinyint(3) unsigned | NO | | 0 | |
| parents_tploptions | tinyint(3) unsigned | NO | | 2 | |
| hostgroups | tinyint(3) unsigned | NO | | 0 | |
| hostgroups_tploptions | tinyint(3) unsigned | NO | | 2 | |
| check_command | text | YES | | NULL | |
| use_template | tinyint(3) unsigned | NO | | 0 | |
| use_template_tploptions | tinyint(3) unsigned | NO | | 2 | |
| initial_state | varchar(20) | YES | | | |
| max_check_attempts | int(11) | YES | | NULL | |
| check_interval | int(11) | YES | | NULL | |
| retry_interval | int(11) | YES | | NULL | |
| active_checks_enabled | tinyint(3) unsigned | NO | | 2 | |
| passive_checks_enabled | tinyint(3) unsigned | NO | | 2 | |
| check_period | int(11) | NO | | 0 | |
| obsess_over_host | tinyint(3) unsigned | NO | | 2 | |
| check_freshness | tinyint(3) unsigned | NO | | 2 | |
| freshness_threshold | int(11) | YES | | NULL | |
| event_handler | int(11) | NO | | 0 | |
| event_handler_enabled | tinyint(3) unsigned | NO | | 2 | |
| low_flap_threshold | int(11) | YES | | NULL | |
| high_flap_threshold | int(11) | YES | | NULL | |
| flap_detection_enabled | tinyint(3) unsigned | NO | | 2 | |
| flap_detection_options | varchar(20) | YES | | | |
| process_perf_data | tinyint(3) unsigned | NO | | 2 | |
| retain_status_information | tinyint(3) unsigned | NO | | 2 | |
| retain_nonstatus_information | tinyint(3) unsigned | NO | | 2 | |
| contacts | tinyint(3) unsigned | NO | | 0 | |
| contacts_tploptions | tinyint(3) unsigned | NO | | 2 | |
| contact_groups | tinyint(3) unsigned | NO | | 0 | |
| contact_groups_tploptions | tinyint(3) unsigned | NO | | 2 | |
| notification_interval | int(11) | YES | | NULL | |
| notification_period | int(11) | NO | | 0 | |
| first_notification_delay | int(11) | YES | | NULL | |
| notification_options | varchar(20) | YES | | | |
| notifications_enabled | tinyint(3) unsigned | NO | | 2 | |
| stalking_options | varchar(20) | YES | | | |
| notes | varchar(255) | YES | | | |
| notes_url | varchar(255) | YES | | | |
| action_url | varchar(255) | YES | | | |
| icon_image | varchar(255) | YES | | | |
| icon_image_alt | varchar(255) | YES | | | |
| vrml_image | varchar(255) | YES | | | |
| statusmap_image | varchar(255) | YES | | | |
| 2d_coords | varchar(255) | YES | | | |
| 3d_coords | varchar(255) | YES | | | |
| use_variables | tinyint(3) unsigned | NO | | 0 | |
| name | varchar(255) | NO | | NULL | |
| active | enum('0','1') | NO | | 1 | |
| last_modified | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| access_rights | varchar(8) | YES | | NULL | |
| config_id | tinyint(3) unsigned | NO | | 0 | |
+------------------------------+---------------------+------+-----+-------------------+-----------------------------+
56 rows in set (0.00 sec)

[ssax]

Please attach (or PM) a sanitized copy of these files:

Code: Select all

/usr/local/nagiosxi/html/config.inc.php
/usr/local/nagios/etc/ndo2db.cfg
/var/www/html/nagiosql/config/settings.php

Thank you

[corkyman]

Files are uploaded

[ssax]

Locking, will continue support through the ticket.