Inquiry About nagiosxi DB error(Mysql)

[bizreach]

Dear Customer Service,

My name is Kazuya Nakahara and I am a infra engineer at Bizreach.
We use NagiosXi as a monitoring system.

There was a problem.

The other day, our company nagiosxi suddenly shut down.

After that, I output an error that caused the table to be damaged although it was started.

-----------------------------------------------------------------------------

myisamchk /var/lib/mysql/nagios/*.MYI

Checking MyISAM file: /var/lib/mysql/nagios/nagios_hoststatus.MYI
Data records: 545 Deleted blocks: 23
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
- check record delete-chain
myisamchk: error: Record at pos: 177956 is not remove-marked
myisamchk: error: record delete-link-chain corrupted
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 544 keys of 545
- check record links
myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 178084
MyISAM-table '/var/lib/mysql/nagios/nagios_hoststatus.MYI' is corrupted
Fix it using switch "-r" or "-o"
---------
---------

Checking MyISAM file: /var/lib/mysql/nagios/nagios_servicestatus.MYI
Data records: 6745 Deleted blocks: 44
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
myisamchk: warning: Size of datafile is: 2215572 Should be: 2209888
- check record delete-chain
myisamchk: error: Record at pos: 0 is not remove-marked
myisamchk: error: record delete-link-chain corrupted
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 6744 keys of 6745
- check record links
myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 2207552
MyISAM-table '/var/lib/mysql/nagios/nagios_servicestatus.MYI' is corrupted
Fix it using switch "-r" or "-o"

---------
-----------------------------------------------------------------------------
On this day, I restored it by running [repairmysql.sh]

Today, when we restarted to implement DB maintenance, we output the following error.

When looking at mysqld.log, there were multiple errors.
For that reason, I executed repairmysql.sh the same as the other day.


After that, I accessed it with the GUI but it did not work well, and as I looked at the system log, it seemed that many tables were damaged.

Commands:
mysqlcheck --auto-repair -c -o sample -u <user> -p

(Excerpt)

myisamchk: error: ‘/var/lib/mysql/nagios/nagios_conninfo.MYI’ is not a MyISAM-table
myisamchk: error: ‘/var/lib/mysql/nagios/nagios_customvariablestatus.MYI’ is not a MyISAM-table
myisamchk: error: ‘/var/lib/mysql/nagios/nagios_eventhandlers.MYI’ is not a MyISAM-table
myisamchk: error: ‘/var/lib/mysql/nagios/nagios_hosts.MYI’ is not a MyISAM-table
myisamchk: error: ‘/var/lib/mysql/nagios/nagios_hoststatus.MYI’ is not a MyISAM-table
myisamchk: error: ‘/var/lib/mysql/nagios/nagios_instances.MYI’ is not a MyISAM-table
myisamchk: error: ‘/var/lib/mysql/nagios/nagios_logentries.MYI’ is not a MyISAM-table
myisamchk: error: ‘/var/lib/mysql/nagios/nagios_programstatus.MYI’ is not a MyISAM-table
myisamchk: error: ‘/var/lib/mysql/nagios/nagios_servicestatus.MYI’ is not a MyISAM-table
myisamchk: error: ‘/var/lib/mysql/nagios/nagios_statehistory.MYI’ is not a MyISAM-table
myisamchk: error: ‘/var/lib/mysql/nagios/nagios_systemcommands.MYI’ is not a MyISAM-table

The other tables were normal.

Commands:
mysqlcheck --auto-repair -c -o nagios -u <user> -p
-----------------------------------------------------------------------------
(Excerpt)
Repairing tables
nagios.nagios_conninfo
Error : Incorrect file format 'nagios_conninfo'
error : Corrupt
nagios.nagios_customvariablestatus
Error : Incorrect file format 'nagios_customvariablestatus'
error : Corrupt
nagios.nagios_eventhandlers
Error : Incorrect file format 'nagios_eventhandlers'
error : Corrupt
nagios.nagios_hosts
Error : Incorrect file format 'nagios_hosts'
error : Corrupt
nagios.nagios_hoststatus
Error : Incorrect file format 'nagios_hoststatus'
error : Corrupt
nagios.nagios_instances
Error : Incorrect file format 'nagios_instances'
error : Corrupt
nagios.nagios_logentries
Error : Incorrect file format 'nagios_logentries'
error : Corrupt
nagios.nagios_programstatus
Error : Incorrect file format 'nagios_programstatus'
error : Corrupt
nagios.nagios_servicestatus
Error : Incorrect file format 'nagios_servicestatus'
error : Corrupt
nagios.nagios_statehistory
Error : Incorrect file format 'nagios_statehistory'
error : Corrupt
nagios.nagios_systemcommands
Error : Incorrect file format 'nagios_systemcommands'
error : Corrupt
-----------------------------------------------------------------------------

I confirmed the forum, but it was necessary to execute the following command on table, but are there other possible causes?

Commands:
repair table <some_table> USE_FRM;

Is there another way to respond?

We will provide you with the necessary information.

Sincerely,
Kazuya Nakahara

[tmcdonald]

I guess I'll start with the obvious question: Is someone shutting down the VM improperly? 95% of the time (no exaggeration) we see this issue, it is because someone shut down the server by "pulling the plug" on a VM, i.e. they clicked the "Shutdown Guest" button in vSphere or whatever management console they use. This is basically the same as pulling the power cord out of a running physical machine, and it causes all sorts of issues. It doesn't allow the databases time to finish whatever transactions they were running, which leads to DB corruption as we see here.

Barring that, machines don't tend to shut down on their own from withing. They will slow down or lock up, but rarely just power off.

[bizreach]

Thank you for your response.
Certainly at the first time, we shutdown from vsphere.

Thank you, I understood the cause.

Please tell me how to restore damaged table.

Or is it possible to restore from past data?
We keep Backup on a daily basis.
------------------------------------
/store/backups/mysql/daily/mysql

-rw-r--r-- 1 root root 144896 Nov 16 2016 mysql_2016-11-16_07h00m.水曜日.sql.gz
-rw-r--r-- 1 root root 144896 Nov 17 2016 mysql_2016-11-17_07h00m.木曜日.sql.gz
-rw-r--r-- 1 root root 144896 Nov 18 2016 mysql_2016-11-18_07h00m.金曜日.sql.gz
-rw-r--r-- 1 root root 144896 Nov 20 2016 mysql_2016-11-20_07h00m.日曜日.sql.gz
-rw-r--r-- 1 root root 144896 Nov 21 2016 mysql_2016-11-21_07h00m.月曜日.sql.gz
-rw-r--r-- 1 root root 144894 Apr 29 07:00 mysql_2018-04-29_07h00m.Sunday.sql.gz
-rw-r--r-- 1 root root 144893 Apr 30 07:00 mysql_2018-04-30_07h00m.Monday.sql.gz
-rw-r--r-- 1 root root 144894 May 1 07:00 mysql_2018-05-01_07h00m.Tuesday.sql.gz
-rw-r--r-- 1 root root 144894 May 4 07:00 mysql_2018-05-04_07h00m.Friday.sql.gz
-rw-r--r-- 1 root root 144896 May 9 07:00 mysql_2018-05-09_07h00m.Wednesday.sql.gz
-rw-r--r-- 1 root root 144895 May 10 07:00 mysql_2018-05-10_07h00m.Thursday.sql.gz

[tgriep]

Try this command that should fix the corrupted MYSQL files.

Code: Select all

mysqlcheck -f -r -u root -pnagiosxi --all-databases --use_frm

The use_frm option will rebuild the indexes in the MYSQL tables so let us know if this fixes the issue.

[bizreach]

Thank you for your response.
I was able to recover with the command you instructed.

I executed the shutdown command from the virtual host and it seems that this is probably the influence because the disk was damaged.

After restoration, an event occurs where the performance graph is not drawn.
Do you have something to think about?

Please lend me your wisdom.

Sincerely,
Kazuya Nakahara

[tgriep]

Are the graphs not updating for just that one host or is it all the graphs for all of the hosts not updating?
For that host, if you look at the details for those checks, are they updating and if you go to the Advanced Tab, do you see performance data?

[bizreach]

Not all graphs are displayed.
As you can see the past data can be seen.

However, it is not currently being drawn.

Is there a solution?

[tgriep]

Lets restart all of the processes that are used to process the performance data and graph it.
Run this as root to stop the processes.

Code: Select all

service nagios stop
service npcd stop
service crond stop

Then start the processes by running.

Code: Select all

service crond start
service npcd start
service nagios start

Wait for 20 minutes to see it the graphs start to populate.

If not, can you post or PM me your System Profile?
To get your system profile. Login to the Nagios XI GUI using a web browser.
Click the "Admin" > "System Profile" Menu
Click the "Download Profile" button
Save the profile.zip file and upload it to the ticket by clicking the "choose item" link at the bottom of the menu.