Crashed Database Tables in Nagios might happen if the MySQL process appears to be consuming large amounts of CPU.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to resolve Nagios related errors.

In this context, we shall look into the causes of it and the steps to fix this Nagios database error.

Nature of Crashed Database Tables?

If we experience a high load on the Nagios XI server and the MySQL process (mysqld) consume large amounts of CPU, it may be a symptom of one or more crashed tables.

In order to find out if we have crashed database tables or any other errors, we can check the mysqld.log:

tail /var/log/mysqld.log

We 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

What triggers Crashed Database Tables in Nagios ?

Database corruption usually causes by power outages, low disk space, or improper shutting down of the Nagios XI server.

Nagios is constantly reading from and writing to the MySQL database, so killing the mysqld process will lead to crashed tables.

Many times 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.

However, to shut down the Nagios XI server, the proper way would be to issue the following command:

shutdown -h now

If the machine has insufficient disk space, we come across an error when we run the repair database script:

/usr/local/nagiosxi/scripts/repairmysql.sh: line 59: 11735 Segmentation fault (core dumped) $cmd $t –sort_buffer_size=256M
Timeout error occurred trying to start MySQL Daemon.
Starting mysqld: [FAILED]

[Looking for an easy fix to this Nagios error? Contact us now!]

How to fix Crashed Database Tables in Nagios ?

We can repair the Nagios and nagiosql databases using the following commands as the root user:

/usr/local/nagiosxi/scripts/repairmysql.sh nagios
/usr/local/nagiosxi/scripts/repairmysql.sh nagiosql
/usr/local/nagiosxi/scripts/repairmysql.sh nagiosxi

Alternatively, if we are running Nagios XI 2014 onwards, we can use:

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

This will repair both databases (Nagios and nagiosql) and  (nagiosxi in XI 5.x).

If we receive an error similar to this one:

SQL: DELETE FROM nagios_logentries WHERE logentry_time < FROM_UNIXTIME(1293570334)
SQL: SQL Error [ndoutils] :</b> Table ‘./nagios/nagios_logentries’ is marked as crashed and last (automatic?) repair failed CLEANING ndoutils TABLE ‘notifications’…

We may need to run a force repair on the tables using the commands below:

For RHEL 6|CentOS 6|Oracle Linux 6, execute;

service mysqld stop
cd /var/lib/mysql/nagios
myisamchk -r -f nagios_<corrupted_table>
service mysqld start
rm -f /usr/local/nagiosxi/var/dbmaint.lock
php /usr/local/nagiosxi/cron/dbmaint.php

For RHEL 7|CentOS 7|Oracle Linux 7|Debian 9, execute;

systemctl stop mariadb.service
cd /var/lib/mysql/nagios
myisamchk -r -f nagios_<corrupted_table>
systemctl start mariadb.service
rm -f /usr/local/nagiosxi/var/dbmaint.lock
php /usr/local/nagiosxi/cron/dbmaint.php

For Ubuntu 14, run;

service mysql stop
cd /var/lib/mysql/nagios
myisamchk -r -f nagios_<corrupted_table>
service mysql start
rm -f /usr/local/nagiosxi/var/dbmaint.lock
php /usr/local/nagiosxi/cron/dbmaint.php

For Debian 8|Ubuntu 16/18, execute;

systemctl stop mysql.service
cd /var/lib/mysql/nagios
myisamchk -r -f nagios_<corrupted_table>
systemctl start mysql.service
rm -f /usr/local/nagiosxi/var/dbmaint.lock
php /usr/local/nagiosxi/cron/dbmaint.php

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’
mysql -u ndoutils -pn@gweb nagios -e ‘TRUNCATE TABLE nagios_notifications’

These commands will clear all entries from the affected tables. After we truncate tables, we should repeat the repair process outlined above.

Finally, if we have run out of disk space on the Nagios XI server, clear unnecessary data. Once we have resolved the disk space issue, we should run the repair database script again.

[Still facing crashed database errors? We are here for you!]