Nagios upgrade problems from 2014R1.2 to 5.3.4

[monit_burb]

Hi,

I have a 2014R1.2 version of nagios xi with nagios core 4.0.7. I've been testing the update without much success. First I though it was related to some bad configuration but ssax from the support team helped me fixed the configuration issues I had. I tried to run it again and as before it ends with the following DDBB errors:

Code: Select all

DASHLET NAME: rss_dashlet
CMD=grep register_dashlet /usr/local/nagiosxi/tmp/rqr24/rss_dashlet/rss_dashlet.           inc.php | wc -l
1
OUT=1
Dashlet looks ok...
CMD: cp -rf /usr/local/nagiosxi/tmp/rqr24/rss_dashlet /usr/local/nagiosxi/html/i           ncludes/dashlets/
CMD: rm -rf /usr/local/nagiosxi/tmp/rqr24
CMD: . /usr/local/nagiosxi/var/xi-sys.cfg && chown -R $nagiosuser /usr/local/nag           iosxi/html/includes/dashlets/rss_dashlet


Done!
Dashlets installed/updated OK
ALTER TABLE
ALTER TABLE
UPDATE 59
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE SEQUENCE
CREATE TABLE
ERROR 1060 (42S21) at line 39: Duplicate column name 'exclude'

I did saw that nagios core is the only think that gets update from 4.0.7 to 4.1.1.

I tried to run again the upgrade and got the following error:

Code: Select all

DASHLET NAME: rss_dashlet
CMD=grep register_dashlet /usr/local/nagiosxi/tmp/roin7/rss_dashlet/rss_dashlet.inc.php | wc -l
1
OUT=1
Dashlet looks ok...
CMD: cp -rf /usr/local/nagiosxi/tmp/roin7/rss_dashlet /usr/local/nagiosxi/html/includes/dashlets/
CMD: rm -rf /usr/local/nagiosxi/tmp/roin7
CMD: . /usr/local/nagiosxi/var/xi-sys.cfg && chown -R $nagiosuser /usr/local/nagiosxi/html/includes/dashlets/rss_dashlet


Done!
Dashlets installed/updated OK
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:2: ERROR:  column "api_key" of relation "xi_users" already exists
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:3: ERROR:  column "api_enabled" of relation "xi_users" already exists
UPDATE 59
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:8: ERROR:  column "login_attempts" of relation "xi_users" already exists
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:9: ERROR:  column "last_attempt" of relation "xi_users" already exists
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:10: ERROR:  column "last_password_change" of relation "xi_users" already exists
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:13: ERROR:  column "last_login" of relation "xi_users" already exists
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:14: ERROR:  column "last_edited" of relation "xi_users" already exists
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:15: ERROR:  column "last_edited_by" of relation "xi_users" already exists
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:16: ERROR:  column "created_by" of relation "xi_users" already exists
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:17: ERROR:  column "created_time" of relation "xi_users" already exists
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:24: ERROR:  relation "xi_eventqueue_eventqueue_id_seq" already exists
psql:nagiosxi/nagiosxi-db/mods/pgsql/schema_01.sql:33: ERROR:  relation "xi_eventqueue" already exists
ERROR 1060 (42S21) at line 39: Duplicate column name 'exclude'

I found the thread https://support.nagios.com/forum/viewto ... 16&t=40525 that seems similar.

I was looking to the upgrade script but it have been modified since that post because the lines mention have no reference to mysql, but I guess now are 828 to 847 so I tried commenting those and run it again. This time it finish at the following line

Code: Select all

Saving old init script to /tmp/nagios.init.orig.3...
Saving old crontab to /tmp/nagios.crontab.orig...
Removing XI cron jobs...
Saving old crontab to /tmp/root.crontab.orig...
Removing XI cron jobs...

Using echo I narrowed it down to the flowing lines where it tries to update the sudoers

Code: Select all

 937 echo "fail after crontab"
    938 # Fix/update sudoers
    939 if [ $UPDATE_SUDOERS -eq 1 ]; then
    940         ./install-sudoers
    941 fi
    942 echo "Fail after sudoers"

And inside the install-sudoers it fails at the first 2 lines

Code: Select all

# Remove old sudoers entries
grep -v NAGIOSXI /etc/sudoers > /etc/sudoers.new
mv -f /etc/sudoers.new /etc/sudoers

I running the update script as root so I don't know what might be the issue

Attached is the upgrade.log file with some of the updates. I didn't include the last upgrade with the last runs as I'm getting always the same now.

[avandemore]

Are you running this as root?

In the extracted directory there are two files:

uninstall-crontab-root and uninstall-crontab-nagios. Change the first line from #!/bin/bash to #!/bin/bash -x and try again.

[monit_burb]

Are you running this as root?

In the extracted directory there are two files:

uninstall-crontab-root and uninstall-crontab-nagios. Change the first line from #!/bin/bash to #!/bin/bash -x and try again.

Hi, check my last edit. I do run it as root and it fail latter on the install-sudoers step as it does not even arrives to

Code: Select all

 # Remove any old /etc/sudoers.d/nagiosxi files
rm -rf /etc/sudoers.d/nagiosxi

[avandemore]

If your upgrade.log is still the same as you posted above, please follow my previous instructions:

In the extracted directory there are two files:

uninstall-crontab-root and uninstall-crontab-nagios. Change the first line from #!/bin/bashto #!/bin/bash -x and try again.

If it's different, please post a current upgrade.log.

[monit_burb]

Finally managed to update it.

At the end, it was a permissions issue as I have LDAP managing the sudo privileges on the servers but realized that it had never worked properly on my nagios server. As this is a a VM I restored to the initial state with a snapshot, fixed the sudo issues, run the upgrade again and now it worked like a charm!!

After revising it I can see my BPI configuration have been deleted and now it again only the default demo set up :S
I overwrite the content of /usr/local/nagiosxi/html/includes/components/nagiosbpi/bpi.conf with what I have on my production server and is working fine now.

Now I'm thinking.... As this is an old server that was initially nagiosxi 2011 and then upgraded to 2014R1.2, over the years a lot of crap have been piling up in there.

The main idea is to move nagios to a new server with all its config, rrd files, etc. so we move from centos 6 to centos 7 both 64bit following https://assets.nagios.com/downloads/nag ... ios-XI.pdf

The best approach will be??? migrate and update or upgrade and migrate?

A) Create a new server with mi current 2014R.12 version do a migration and then an upgrade the new server to 5.3.4

B) Update mi current server to 5.3.4 and then migrate to the new server already with the last version

[avandemore]

There is a specific approach which should be followed and that's the one outlined in the document. Basically migrate then upgrade. However, prior to the upgrade you may wish to consider this as well:

https://support.nagios.com/kb/article.php?id=560

Theoretically, PostgreSQL can be removed or at least disabled on a system which has successfully followed those steps.

[monit_burb]

There is a specific approach which should be followed and that's the one outlined in the document. Basically migrate then upgrade. However, prior to the upgrade you may wish to consider this as well:

https://support.nagios.com/kb/article.php?id=560

Theoretically, PostgreSQL can be removed or at least disabled on a system which has successfully followed those steps.

In the PostgreSQL document it does not specify the version of nagiosXI required to do it. I guess it can be don with my 2014R1.2 no? I'm going to give it a try

[avandemore]

Nagios XI version should be 5.x and up. It might work with 2014 version, that's completely untested.

Some have reported better results using Mysql workbench to do the conversion.

[monit_burb]

Nagios XI version should be 5.x and up. It might work with 2014 version, that's completely untested.

Some have reported better results using Mysql workbench to do the conversion.

I just gave it a try on my test environment that now is 5.3.4 and is not working NAgiosxi load but a couple of username and pass that i tested don't work.

This is all the errors I got in the dump import.
mysql -u root -p nagiosxi -h ESBARLMONDB05 --force < nagiosxi.mysql
ERROR 1153 (08S01) at line 3410: Got a packet bigger than 'max_allowed_packet' bytes
ERROR 1054 (42S22) at line 7507: Unknown column 'api_key' in 'field list'

I'll keep testing

[avandemore]

I can give you some alternative information to attempt this, but if something goes wrong this is out of scope so you'll be on your own.

First on your MySQL/MariaDB instance you'll need this stuff:

You can use something like this:

Code: Select all

# Create "nagiosxi" default database schema

CREATE DATABASE `nagiosxi`;
CREATE USER 'nagiosxi'@'localhost' IDENTIFIED BY 'n@gweb';
GRANT ALL ON nagiosxi.* TO 'nagiosxi'@'localhost';
USE `nagiosxi`;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_auditlog` (
    `auditlog_id` int auto_increment,
    `log_time` timestamp,
    `source` text,
    `user` varchar(200),
    `type` int,
    `message` text,
    `ip_address` varchar(45),
    `details` text,
    primary key (`auditlog_id`),
    index using btree (`log_time`),
    index using btree (`user`),
    index using btree (`type`),
    index using btree (`ip_address`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_commands` (
    `command_id` int auto_increment,
    `group_id` int default 0,
    `submitter_id` int default 0,
    `beneficiary_id` int default 0,
    `command` int NOT NULL,
    `submission_time` timestamp not null,
    `event_time` timestamp not null,
    `frequency_type` int default 0,
    `frequency_units` int default 0,
    `frequency_interval` int default 0,
    `processing_time` timestamp,
    `status_code` int default 0,
    `result_code` int default 0,
    `command_data` text,
    `result` text,
    primary key (`command_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_events` (
    `event_id` int auto_increment,
    `event_time` timestamp,
    `event_source` smallint,
    `event_type` smallint default 0 not null,
    `status_code` smallint default 0 not null,
    `processing_time` timestamp,
    primary key (`event_id`),
    index using btree (`event_source`),
    index using btree (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_meta` (
    `meta_id` int auto_increment,
    `metatype_id` int default 0,
    `metaobj_id` int default 0,
    `keyname` varchar(128) not null,
    `keyvalue` text,
    primary key (`meta_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_options` (
    `option_id` int auto_increment,
    `name` varchar(128) not null,
    `value` text,
    primary key (`option_id`),
    index using btree (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_sysstat` (
    `sysstat_id` int auto_increment,
    `metric` varchar(128) not null,
    `value` text,
    `update_time` timestamp not null,
    primary key (`sysstat_id`),
    index using btree (`metric`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_usermeta` (
    `usermeta_id` int auto_increment,
    `user_id` int not null,
    `keyname` varchar(255) not null,
    `keyvalue` longtext,
    `autoload` smallint default 0,
    primary key (`usermeta_id`),
    index using btree (`autoload`),
    constraint `user_unique_key` unique (`user_id`, `keyname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_users` (
    `user_id` int auto_increment,
    `username` varchar(64) not null,
    `password` varchar(128) not null,
    `name` varchar(100),
    `email` varchar(128) not null,
    `backend_ticket` varchar(128),
    `enabled` smallint default 1,
    primary key (`user_id`),
    unique(`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_eventqueue` (
    `eventqueue_id` int auto_increment,
    `event_time` timestamp,
    `event_source` smallint,
    `event_type` smallint default 0 not null,
    `event_meta` text,
    primary key (`eventqueue_id`),
    unique(`eventqueue_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;USE `nagiosxi`;

ALTER TABLE xi_usermeta MODIFY keyvalue LONGTEXT;

ALTER TABLE xi_users ADD COLUMN api_key varchar(128) NULL;
ALTER TABLE xi_users ADD COLUMN api_enabled smallint DEFAULT 0 NOT NULL;

UPDATE xi_users SET api_enabled = 1, api_key = backend_ticket;

# Account security features
ALTER TABLE xi_users ADD COLUMN login_attempts smallint(6) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_attempt int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_password_change int(10) DEFAULT 0 NOT NULL;

# Security information
ALTER TABLE xi_users ADD COLUMN last_login int(11) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_edited int(11) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_edited_by int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN created_by int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN created_time int(11) DEFAULT 0 NOT NULL;

# Event Queue table for more efficient Global Event Handlers
CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_eventqueue` (
    `eventqueue_id` int auto_increment,
    `event_time` int,
    `event_source` smallint,
    `event_type` smallint default 0 not null,
    `event_meta` text,
    primary key (`eventqueue_id`),
    unique(`eventqueue_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;USE `nagiosxi`;

http://mysqlworkbench.org/2012/11/how-t ... on-wizard/
Or use a dump of the Postgres nagiosxi db with some converter, there are more than one. Restore the conversion to the newly created MySQL nagiosxi db.


Then you'll need to update /usr/local/nagiosxi/html/config.inc.php to point to the correct nagiosxi db.