Nagios Support Forum

Support for Nagios products and services
https://support.nagios.com/forum/

Database corrupt after system crashed

https://support.nagios.com/forum/viewtopic.php?t=62399

Page 1 of 1

Database corrupt after system crashed

Posted: Mon Apr 26, 2021 3:28 pm
by andecontrol
Hi Team, at the moment our NagiosXI web interface is not showing the hosts and services status sumary
Image

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

Image


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?

Re: Database corrupt after system crashed

Posted: Tue Apr 27, 2021 11:26 am
by 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

Re: Database corrupt after system crashed

Posted: Tue Apr 27, 2021 1:20 pm
by 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
All times are UTC-05:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited