Home » Categories » Products » Nagios XI » Troubleshooting » Common Problems

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:

tail /var/log/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    

 

 

Possible Causes

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
Timeout error occurred trying to start MySQL Daemon.
Starting mysqld: [FAILED]

 

 

Possible Solutions

You can repair the nagios and nagiosql databases by running the following commands in the command line 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 you are running Nagios XI 2014 onwards, you can use:

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

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)
SQL: SQL Error [ndoutils] :</b> Table './nagios/nagios_logentries' is marked as crashed and last (automatic?) repair failed CLEANING ndoutils TABLE 'notifications'...

 

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

RHEL 6 | CentOS 6 | Oracle Linux 6

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

 

RHEL 7 | CentOS 7 | Oracle Linux 7 | Debian 9

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

 

Ubuntu 14

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

 

Debian 8 | Ubuntu 16/18

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'

 

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:

Running Out Of Disk Space On The Nagios XI VM

Once you've resolved the disk space issue you should run the repair database script again.

 

 

Final Thoughts

For any support related questions please visit the Nagios Support Forums at:

http://support.nagios.com/forum/

5 (2)
Article Rating (2 Votes)
Rate this article
  • Icon PDFExport to PDF
  • Icon MS-WordExport to MS Word
Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
Nagios XI - Last Check Time Not Updating
Viewed 7291 times since Tue, Jan 6, 2015
Nagios XI - Common Upgrade Failures And Solutions
Viewed 3774 times since Thu, Jun 29, 2017
Nagios XI - Hosts Pending/Gray
Viewed 3920 times since Tue, Jan 6, 2015
Nagios XI - Upgrade errors - root.crontab.orig: cannot overwrite existing file
Viewed 1475 times since Tue, Jan 26, 2016
Nagios XI - Unable To Login Using Two Factor Authentication
Viewed 1943 times since Tue, Apr 10, 2018
Nagios XI - Optimizing The PHP Settings File
Viewed 5615 times since Thu, Jul 13, 2017
Nagios XI - Warning: Duplicate definition found for contact ’xi_default_contact’
Viewed 1868 times since Tue, Jan 26, 2016
Nagios XI - Downloading A System Profile
Viewed 1925 times since Wed, Jul 19, 2017
Nagios XI - Notifications Not Sending In XI 5.3.0
Viewed 1701 times since Tue, Oct 4, 2016
Nagios XI - ERROR: unable to open include file: /etc/mrtg/conf.d/xxxxxxx.cfg
Viewed 3014 times since Wed, Jun 1, 2016