Database corrupt after system crashed

[andecontrol]

Hi Team, at the moment our NagiosXI web interface is not showing the hosts and services status sumary


Instead the Nagios Core Config Manager can show us all of these hosts and services




First, we ran the following command to repair the database

Code: Select all

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

And this is the output

DATABASE: nagios
TABLE:
/var/lib/mysql/nagios ~
- recovering (with sort) MyISAM-table 'nagios_acknowledgements'
Data records: 2
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_commands'
Data records: 168
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_commenthistory'
Data records: 3527
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_comments'
Data records: 5
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_configfiles'
Data records: 1
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_configfilevariables'
Data records: 131
- Fixing index 1

---------

- recovering (with sort) MyISAM-table 'nagios_contact_addresses'
Data records: 29
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_contactgroup_members'
Data records: 82
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_contactgroups'
Data records: 7
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_contact_notificationcommands'
Data records: 56
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_contactnotificationmethods'
Data records: 315
- Fixing index 1
- Fixing index 2
- Fixing index 3

---------

- recovering (with sort) MyISAM-table 'nagios_contactnotifications'
Data records: 315
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4

---------

- recovering (with sort) MyISAM-table 'nagios_contacts'
Data records: 28
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_contactstatus'
Data records: 28
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_customvariables'
Data records: 3434
- Fixing index 1
- Fixing index 2
- Fixing index 3

---------

- recovering (with sort) MyISAM-table 'nagios_customvariablestatus'
Data records: 3434
- Fixing index 1
- Fixing index 2
- Fixing index 3

---------

- recovering (with keycache) MyISAM-table 'nagios_dbversion'
Data records: 1

---------

- recovering (with sort) MyISAM-table 'nagios_downtimehistory'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_eventhandlers'
Data records: 15
- Fixing index 1
- Fixing index 2
/usr/bin/myisamchk: Error on delete of '/var/lib/mysql/nagios/nagios_eventhandle rs.MYD' (Errcode: 5)
myisamchk: error: 5 when fixing table
MyISAM-table 'nagios_eventhandlers' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by no t using the --quick (-q) flag

---------

- recovering (with sort) MyISAM-table 'nagios_externalcommands'
Data records: 9
- Fixing index 1

---------

- recovering (with sort) MyISAM-table 'nagios_flappinghistory'
Data records: 7044
- Fixing index 1

---------

- recovering (with sort) MyISAM-table 'nagios_hostchecks'
Data records: 259
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_host_contactgroups'
Data records: 159
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_host_contacts'
Data records: 112
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_hostdependencies'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_hostescalation_contactgroups'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_hostescalation_contacts'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_hostescalations'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_hostgroup_members'
Data records: 120
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_hostgroups'
Data records: 17
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_host_parenthosts'
Data records: 40
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_hosts'
Data records: 127
- Fixing index 1
- Fixing index 2
- Fixing index 3

---------

- recovering (with sort) MyISAM-table 'nagios_hoststatus'
Data records: 127
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6
- Fixing index 7
- Fixing index 8
- Fixing index 9
- Fixing index 10
- Fixing index 11
- Fixing index 12
- Fixing index 13
- Fixing index 14
- Fixing index 15
- Fixing index 16
- Fixing index 17
- Fixing index 18
- Fixing index 19
/usr/bin/myisamchk: Error on delete of '/var/lib/mysql/nagios/nagios_hoststatus. MYD' (Errcode: 5)
myisamchk: error: 5 when fixing table
MyISAM-table 'nagios_hoststatus' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by no t using the --quick (-q) flag

---------

- recovering (with sort) MyISAM-table 'nagios_instances'
Data records: 1
- Fixing index 1

---------

- recovering (with sort) MyISAM-table 'nagios_logentries'
Data records: 5309336
- Fixing index 1
Key 1 - Found wrong stored record at 504352744
Key 1 - Found wrong stored record at 504356852
Wrong bytesec: 99- 97- 47 at 508579844; Skipped
Wrong bytesec: 0- 85- 0 at 508583936; Skipped
- Fixing index 2
- Fixing index 3
- Fixing index 4
Data records: 5309333

---------

- recovering (with sort) MyISAM-table 'nagios_notifications'
Data records: 216
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4

---------

- recovering (with sort) MyISAM-table 'nagios_objects'
Data records: 5396
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6

---------

- recovering (with sort) MyISAM-table 'nagios_processevents'
Data records: 1330
- Fixing index 1

---------

- recovering (with sort) MyISAM-table 'nagios_programstatus'
Data records: 1
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_runtimevariables'
Data records: 17
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_scheduleddowntime'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_servicechecks'
Data records: 5008
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4

---------

- recovering (with sort) MyISAM-table 'nagios_service_contactgroups'
Data records: 1889
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_service_contacts'
Data records: 3183
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_servicedependencies'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_serviceescalation_contactgroups'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_serviceescalation_contacts'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_serviceescalations'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_servicegroup_members'
Data records: 3103
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_servicegroups'
Data records: 11
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_service_parentservices'
Data records: 0
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_services'
Data records: 3320
- Fixing index 1
- Fixing index 2
- Fixing index 3

---------

- recovering (with sort) MyISAM-table 'nagios_servicestatus'
Data records: 3320
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6
- Fixing index 7
- Fixing index 8
- Fixing index 9
- Fixing index 10
- Fixing index 11
- Fixing index 12
- Fixing index 13
- Fixing index 14
- Fixing index 15
- Fixing index 16
- Fixing index 17
- Fixing index 18
- Fixing index 19

---------

- recovering (with sort) MyISAM-table 'nagios_statehistory'
Data records: 138830
- Fixing index 1
- Fixing index 2
- Fixing index 3

---------

- recovering (with sort) MyISAM-table 'nagios_systemcommands'
Data records: 84
- Fixing index 1
- Fixing index 2
- Fixing index 3

---------

- recovering (with sort) MyISAM-table 'nagios_timedeventqueue'
Data records: 0
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6

---------

- recovering (with sort) MyISAM-table 'nagios_timedevents'
Data records: 0
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6

---------

- recovering (with sort) MyISAM-table 'nagios_timeperiods'
Data records: 35
- Fixing index 1
- Fixing index 2

---------

- recovering (with sort) MyISAM-table 'nagios_timeperiod_timeranges'
Data records: 222
- Fixing index 1
- Fixing index 2
Job for mariadb.service failed because a fatal signal was delivered to the control process. See "systemctl status mariadb.service" and "journalctl -xe" for details.
~

===============
REPAIR COMPLETE
===============
DATABASE: nagiosql
TABLE:
/var/lib/mysql/nagiosql ~
DATABASE: nagiosxi
TABLE:
/var/lib/mysql/nagiosxi ~

=======================
nagios database repair FAILED, please check output above!

When we check mariadb.log,we see these errors

InnoDB: cannot calculate statistics for table nagiosxi/xi_events
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/ ... oting.html
210426 16:01:35 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table nagiosxi/xi_events does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See http://dev.mysql.com/doc/refman/5.5/en/ ... oting.html
how you can resolve the problem.

We attacht the system profile if it's required

How can we solve this without having to restore the DB backup?

[gsmith]

Hi,

You can attempt to repair the tables having problems. It looks like they are in the nagios database and are:
'nagios_eventhandlers'
'nagios_hoststatus'

First try: "Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag"

This article from the MySQL reference manual can be helpful:https://dev.mysql.com/doc/refman/8.0/en ... epair.html

PLEASE NOTE:
myisamchk should not be run while anyone is accessing any of the affected tables. It is also best to make a backup before running.

Thanks

[ssax]

In addition to @gsmith's post, include the output of these commands:

Code: Select all

ls -l /var/lib/mysql/nagios
ls -l /var/lib/mysql/nagiosql
ls -l /var/lib/mysql/nagiosxi