Database repair script had error in Nagios DB

[brucej543]

Nagios IX 5.6.5 Got error message that database repair needed to be run. System was not responsive. Needed to hard reboot system in order to even get logged in to the server. Ran database repair script and got an error on the Nagios DB. Error on myisamchk: error: 'nagios_systemcommands' is not a MyISAM-table. Attached it the error and the output of the repair script and the system profile.

[brucej543]

Additionally have one user ID that Nagios will not let log in. Account is active AD account and has the allow login login even if AD auth fails. other uses are not having an issue.

[benjaminsmith]

Hello @brucej543,

Not sure if the issues are related to database corruption, but let's get the database issue taken care of first and then see if you're still having issues with the login for that user.

When the system was not responsive, I believe the backend database (nagios) daemon was not running. You can verify this with the following command.

Code: Select all

systemctl status ndo2db

1. Run the following to force a repair on all the databases

Code: Select all

mysqlcheck -r -f -uroot -pnagiosxi --all-databases --use_frm

2. Post the otput of the following query to check the size of the databse tables.

Code: Select all

echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');" | mysql -uroot -pnagiosxi --table

3. Let the system run for awhile and check the database log for errors.

Code: Select all

tail -n 50 /var/log/mariadb/mariadb.log

[brucej543]

Hi benjaminsmith, on running the sqlcheck command, should the Nagios app be do?wn or can it be up to run this command?

[benjaminsmith]

Hi @brucej543,

Good question. It can be running and the database service must be running to execute the mysqlcheck command.

Also, if you haven't made one recently, I would backup the server before making any changes.

[brucej543]

Attached is the output of the commands requested.

[benjaminsmith]

Hi Bruce,

The log entries table is so large it's likely causing performance/database issues. I would recommend truncating this table by running the following command.

Code: Select all

echo 'DELETE FROM nagios_logentries WHERE entry_time < (NOW() - INTERVAL 30 DAY);' |mysql -t -u root -pnagiosxi nagios

Then run the Nagios XI repair script once more:

Code: Select all

/usr/local/nagiosxi/scripts/repair_databases.sh

Let the server run for a while and send over a new system profile if you are still having issues. Thanks.

[brucej543]

Repair DB ran successfully:
[root@bcnagios01 /]# echo 'DELETE FROM nagios_logentries WHERE entry_time < (NOW() - INTERVAL 30 DAY);' |mysql -t -u root -pnagiosxi nagios
[root@bcnagios01 /]# /usr/local/nagiosxi/scripts/repair_databases.sh

-------

===============
REPAIR COMPLETE
===============

=======================
nagios database repair succeeded
nagiosql database repair succeeded
nagiosxi database repair succeeded

[root@bcnagios01 /]#

[benjaminsmith]

Hi Bruce,

Great. If everything is working as expected, let's keep this thread open for a while and let us know if you experience any issues over the next few days.

[brucej543]

great. I will send another system profile later today.
Thanks for you support.

RE: the one user login issue, we can take that up next week.