Nagios XI - Crashed Database Tables
Crashed Database Tables
If you experience high load on your Nagios XI server and the MySQL process (mysqld) appears to be consuming large amounts of CPU, it may be a symptom of one or more crashed tables. In order to find out if you have crashed database tables or any other errors, you can check the mysqld.log:
You may see entries similar to this one:
150110 9:05:01 [ERROR] /usr/libexec/mysqld: Table './nagios/nagios_logentries' is marked as crashed and last (automatic?) repair failed
Database corruption is usually caused by power outages, running out of disk space or improper shutting down the Nagios XI server. Nagios is constantly reading from and writing to the mysql database, so killing the mysqld process (i.e. with "kill -9" or "killall mysqld") will lead to crashed tables.
Many time users would right-click on the Nagios XI VM in VMWare Workstation/ESXi/Vsphere and select "Power Off". This is wrong - it is like pulling off the power cord. The correct way for shutting down the Nagios XI server would be to issue the following command in the command line:
shutdown -h now
If the Nagios XI machine has insufficient disk space then you may see errors like this when the repair database script is run:
/usr/local/nagiosxi/scripts/repairmysql.sh: line 59: 11735 Segmentation fault (core dumped) $cmd $t --sort_buffer_size=256M
You can repair the nagios and nagiosql databases by running the following commands in the command line as the root user:
Alternatively, if you are running Nagios XI 2014 onwards, you can use:
which will actually repair both databases (nagios and nagiosql) (and nagiosxi in XI 5.x).
If you receive an error, similar to this one:
SQL: DELETE FROM nagios_logentries WHERE logentry_time < FROM_UNIXTIME(1293570334)
You may need to run a force repair on the tables using commands below:
RHEL 7 | CentOS 7 | Oracle Linux 7 | Debian 9
systemctl stop mariadb.service
Debian 8 | Ubuntu 16/18
systemctl stop mysql.service
In certain instances, it may be necessary to truncate (empty) one or more tables. The following commands provide examples of how to truncate both the nagios_logentries and nagios_notifications tables in the nagios MySQL database:
mysql -u ndoutils -pn@gweb nagios -e 'TRUNCATE TABLE nagios_logentries'
Important: Running these commands will clear all entries from the affected tables. After you truncate tables, you should repeat the repair process outlined above.
Further information: Repairing The Nagios XI Database
If you have run out of disk space on your Nagios XI server, the following KB article can provide solutions to resolving this issue:
Once you've resolved the disk space issue you should run the repair database script again.
For any support related questions please visit the Nagios Support Forums at:
|Posted by: lmiltchev - Tue, Jan 27, 2015 at 12:57 PM. This article has been viewed 40682 times.|
|Online URL: https://support.nagios.com/kb/article/nagios-xi-crashed-database-tables-24.html|
Powered by PHPKB (Knowledge Base Software)