Need to recover from table crash

[corkyman]

I have several tables that crashed and needed repair.
Per your document https://assets.nagios.com/downloads/nag ... tabase.pdf I was able to force repair several tables with the command
myisamchk -r -f nagios_<corrupted_table> but some I could not:
1. nagios_logentries table -- the force repair ran but table is still marked as crashed
2. xi_commands and xi_events tables -- force repair did not run on them -- got an error: myisamchk: error: File 'xi_commands' doesn't exist
The table name is not in nagios_<table> format and your doc say to run it on tables in that format so may be this is an issue. How do I recover them then?

The result of the /usr/local/nagiosxi/cron/dbmaint.php commands is here

-bash-4.1$ php /usr/local/nagiosxi/cron/dbmaint.php
CREATING: /usr/local/nagiosxi/var/dbmaint.lock
CLEANING ndoutils TABLE 'commenthistory'...
SQL: DELETE FROM nagios_commenthistory WHERE entry_time < FROM_UNIXTIME(1459543118)
CLEANING ndoutils TABLE 'processevents'...
SQL: DELETE FROM nagios_processevents WHERE event_time < FROM_UNIXTIME(1459543118)
CLEANING ndoutils TABLE 'externalcommands'...
SQL: DELETE FROM nagios_externalcommands WHERE entry_time < FROM_UNIXTIME(1490474318)
CLEANING ndoutils TABLE 'logentries'...
SQL: DELETE FROM nagios_logentries WHERE logentry_time < FROM_UNIXTIME(1483303118)
SQL: SQL Error [ndoutils] :</b> Table './nagios/nagios_logentries' is marked as crashed and should be repairedCLEANING ndoutils TABLE 'notifications'...
SQL: DELETE FROM nagios_notifications WHERE start_time < FROM_UNIXTIME(1488400718)
CLEANING ndoutils TABLE 'contactnotifications'...
SQL: DELETE FROM nagios_contactnotifications WHERE start_time < FROM_UNIXTIME(1488400718)
CLEANING ndoutils TABLE 'contactnotificationmethods'...
SQL: DELETE FROM nagios_contactnotificationmethods WHERE start_time < FROM_UNIXTIME(1488400718)
CLEANING ndoutils TABLE 'statehistory'...
SQL: DELETE FROM nagios_statehistory WHERE state_time < FROM_UNIXTIME(1488400718)
SQL: SQL Error [ndoutils] :</b> Table './nagios/nagios_statehistory' is marked as crashed and should be repairedCLEANING ndoutils TABLE 'timedevents'...
SQL: DELETE FROM nagios_timedevents WHERE event_time < FROM_UNIXTIME(1491078818)
CLEANING ndoutils TABLE 'systemcommands'...
SQL: DELETE FROM nagios_systemcommands WHERE start_time < FROM_UNIXTIME(1491078818)
CLEANING ndoutils TABLE 'servicechecks'...
SQL: DELETE FROM nagios_servicechecks WHERE start_time < FROM_UNIXTIME(1491078818)
CLEANING ndoutils TABLE 'hostchecks'...
SQL: DELETE FROM nagios_hostchecks WHERE start_time < FROM_UNIXTIME(1491078818)
CLEANING ndoutils TABLE 'eventhandlers'...
SQL: DELETE FROM nagios_eventhandlers WHERE start_time < FROM_UNIXTIME(1491078818)
LASTOPT: 1488466201
INTERVAL: 60000
NOW: 1491079118
OPTTIME: 1492066201
CLEANING nagiosxi TABLE 'commands'...
SQL: DELETE FROM xi_commands WHERE processing_time < FROM_UNIXTIME(1491050318)
SQL: SQL Error [nagiosxi] :</b> Table './nagiosxi/xi_commands' is marked as crashed and should be repairedCLEANING nagiosxi TABLE 'events'...
SQL: DELETE FROM xi_events WHERE processing_time < FROM_UNIXTIME(1491050318)
SQL: SQL Error [nagiosxi] :</b> Table './nagiosxi/xi_events' is marked as crashed and should be repairedSQL1: SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL
SQL2: DELETE FROM xi_meta WHERE meta_id IN (SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL)
SQL: SQL Error [nagiosxi] :</b> Table './nagiosxi/xi_events' is marked as crashed and should be repairedCLEANING nagiosxi TABLE 'auditlog'...
SQL: DELETE FROM xi_auditlog WHERE log_time < FROM_UNIXTIME(1488487118)
CLEANING nagiosql TABLE 'logbook'...
SQL: DELETE FROM tbl_logbook WHERE time < FROM_UNIXTIME(1491050318)
Repair Complete: Removing Lock File

Please advise how to repair the tables.

[dwhitfield]

Take a backup (if you can...VM snapshot will work)

Then run the following commands in order:

Code: Select all

mysql -u ndoutils -pn@gweb nagios -e 'TRUNCATE TABLE nagios_logentries'
mysql -u ndoutils -pn@gweb nagiosxi -e 'TRUNCATE TABLE xi_meta'
mysql -u ndoutils -pn@gweb nagiosxi -e 'TRUNCATE TABLE xi_commands'
/usr/local/nagiosxi/scripts/repair_databases.sh

If you run into any errors (other than running the backup script because that will not likely work with crashed tables), please let us know.

[corkyman]

This fixed the issue with crashed tables--Nagios db is back to normal. Thank you and please close this incident.