SQL Error in 5.4.0 - Manage Users page

[SteveBeauchemin]

Someone pointed out a Strange SQL error in the 5.4.0 instance I am setting up.

Admin -> Manage Users

Code: Select all

SQL Error [nagiosxi] : Unknown column 'login_attempts' in 'where clause'

Has anyone seen this yet?

SQL-Error.PNG

Edit: No advice necessary. caused my own problem. and Fixed the DB...

Code: Select all

mysql -h [DB host IP address] -u [nagiosxiUser] --password="[nagiosxiPassword]" \
--database=nagiosxi < /tmp/nagiosxi/nagiosxi/nagiosxi-db/mods/mysql/schema_01.sql
mysql -h [DB host IP address] -u [nagiosxiUser] --password="[nagiosxiPassword]" \
--database=nagiosxi < /tmp/nagiosxi/nagiosxi/nagiosxi-db/mods/mysql/schema_02.sql

I hope I learned my lesson here...

Steve B

[SteveBeauchemin]

Caused my own problem. Offloaded DB related. I pushed a 5.2.7 database to the 5.3.4 system. That removed some table updates.
After that the 5.4.0 upgrade did not apply the sql table changes I needed.

My existing system is RH6 XI 5.2.7 and I am playing with upgrade to 5.4.0 but also need to move to RH7.

I need to rethink my migration process.

Any suggestions on how to not pollute my new system with the evil stuff from my old system? Should I just 'import' all the old config files? What about the old DB stuff, abandon it? As long as I can copy the RRD files I can probably get away with it. Is there an OS upgrade method that lets me have a 'new and clean' Nagios XI install that has my current configurations and Database history?

Should the steps be:
Upgrade Old system to 5.4.0
Push all 3 DB to new system
copy nagios/etc/*stuff*
copy RRD to new system
pray...

I have to get on Red Hat 7. I have to get to Nagios XI 5.4.0. Am I biting off too much at once?

Thanks

Steve B

[dwhitfield]

As one of our resident user experts, I think you might be over thinking this one. Set up a RHEL 7 and install 5.4.0. Then import: https://assets.nagios.com/downloads/nag ... ios-XI.pdf

Now, there's not so much an official upgrade path for RHEL 6 to 7. I know with CentOS some people just change the repos. I've done that with ubuntu and had "success" in the past, but there's usually something to fiddle with. apt install -f of dpkg-reconfigure or something. I assume similar things would happen with CentOS, and I really don't know how RHEL is going to deal with just changing repos since you have to authenticate.

Please let us know if I missed something in your query!

[SteveBeauchemin]

If it were only me using the system I would not be paranoid. But...

I have more than 200 users of the system, and we have about 30 custom dashboards that upper upper managers look at.

In my migration, I am trying to minimize downtime, but I also want to end up on the latest versions of everything. I do have a Red Hat 7 running with the Nagios XI 5.4.0 like you suggested. I have the latest versions of rrdcached, NRPE, livestatus, gearman, mod_gearman, ramdisk, JMX, jolokia, NagVis, no more postgres, Offloaded DB, and every possible performance tweak I can think of. I even have separate Volumes for the OS and the Data on both the Core server and DB server.

I am using redis on my remote gearman so I can run tests using counters that save state based data. So I offload even more tests from my core server. I even added redis awareness to the check_oracle_health Consol Labs plugin v3.0.1 so I can run Oracle tests on the remote mod_gearman servers and offload more from my core server. I do not want to be concerned about state based data being messed up.

I have a lot of things percolating. Moving to a new OS is kind of traumatic. I have to make sure everything compiles on Red Hat 7 and still works properly. I need a seamless migration. A successful migration.

Instead of using import, I figured out that I can use mysqldump and some file copying. It is so much faster.
From the Offload DB doc, I tested the sql dump of all 3 databases - pushing them to the new system - as a migration method. It takes 10 minutes. I can copy the nagios/etc files ahead of time. Pushing the Databases is important because of the Users I have added, and Dashboards since they are stored in the nagiosxi database in a funky json-ish format.

So everything is new on my Nagios 5.4.0 - except that is is idle right now.
I plan to dump databases there, copy some files, and be running perfectly. That's my plan and I'm sticking to it.

I just have to remember to use nagiosxi-db/mods/mysql/schema_0?.sql to update the schema after the dump is complete. Maybe I can do that on my 5.2.7 server ahead of time. I can copy the correct nagios/etc/ config files ahead of time. Use rsync to push NagVis. Rsync the RRD files last to reduce gaps in the graphs (takes the longest time)

Whenever I migrate to a new system, I try very hard to have minimal downtime for the users. I don't want my users to even notice.

So yes, I am overthinking it a bit. But in the end, if I do it right, it is worth the extra effort.
I'll let you know how it goes. I have to schedule it and get approvals, but I think the last DB issue is okay now.

At my last count I have 4815 hosts and 40951 services running. That's a lot of Red if I make a mistake.
I guess you can close this if you like. Next week... fingers crossed.
Thanks.

Steve B

[ssax]

NOTE: ONLY RUN THIS ON THE NEW SERVER AND DO IT ON A TEST SERVER FIRST - DON'T RUN ON THE OLD SERVER

If you want to remove all historical data (but not CCM configs/Users/Userinfo/Admin settings):

*** Make sure that you have known-good XI backups/vm snapshots before making any modifications:

https://assets.nagios.com/downloads/nag ... ios-XI.pdf

Please copy the attached file to your new server's /tmp directory (after restoring) and then run these commands:

Code: Select all

cd /tmp
unzip xi_defaults.zip
sh delete_historical_data.sh

If you run into any issues, let us know.

[SteveBeauchemin]

Thanks for the information.

I actually want to preserve history on the new system, but your script shows me the opposite. By seeing what to destroy, I learn what to preserve.

Sean - you are a scary guy...

One of the things about a migration is that you need to preserve all the tweaks and addons you put in place. They all have configuration files that are outside the Nagios directory structure. They are in /etc or /etc/sysconfig or /etc/init.d or maybe they are in systemctl locations. Even FreeTDS has external configurations. You get all these things working perfectly. Then you have to migrate and hope you don't forget to transfer something. I definitely need to generate better documentation for the future. I feel bad for the folks that will have to take over support for Nagios if I ever change jobs. I think being A.D.D., and having OCD is a requirement to support Nagios in a large installation.

Please close this - we are in TMI turf...

Thanks

Steve B

[ssax]

Woops, my bad, I misread what you were looking for, I just did that last week for a customer so I figured I'd post it, closing per your request.