Damaged tables can't be repaired

This support forum board is for support questions relating to Nagios XI, our flagship commercial network monitoring solution.
Locked
gormank
Posts: 1114
Joined: Tue Dec 02, 2014 12:00 pm

Damaged tables can't be repaired

Post by gormank »

This is in reference to this thread: https://support.nagios.com/forum/viewto ... 16&t=40465

That thread discusses the following not working. They still don't work to fix this issue.

Code: Select all

service mysqld stop; /usr/local/nagiosxi/scripts/repairmysql.sh nagios; service mysqld start
/usr/local/nagiosxi/scripts/repair_databases.sh
I've noticed that the DB is still complaining, although things in the WUI look ok.

Code: Select all

# grep Incorrect /var/log/messages | grep "Oct 12" | awk '{print $NF}' | sort | uniq -c
    253 'nagios_commands''
    506 'nagios_configfiles''
  14168 'nagios_configfilevariables''
    253 'nagios_contactgroup_members''
    253 'nagios_contactgroups''
    253 'nagios_contact_notificationcommands''
    253 'nagios_contacts''
    506 'nagios_contactstatus''
    253 'nagios_customvariables''
 249489 'nagios_customvariablestatus''
    253 'nagios_host_contacts''
    253 'nagios_hostgroup_members''
    253 'nagios_hostgroups''
   6323 'nagios_logentries''
    253 'nagios_runtimevariables''
    253 'nagios_service_contacts''
    253 'nagios_servicegroups''
  20541 'nagios_systemcommands''
    253 'nagios_timeperiods''
    253 'nagios_timeperiod_timeranges''
As you can see repairing the tables is not working. even when repairing using the command suggested in the referenced thread.

Code: Select all

# echo "repair table nagios_commands;" | mysql -t -u root -pnagiosxi nagios;
+------------------------+--------+----------+-----------------------------------------+
| Table                  | Op     | Msg_type | Msg_text                                |
+------------------------+--------+----------+-----------------------------------------+
| nagios.nagios_commands | repair | Error    | Incorrect file format 'nagios_commands' |
| nagios.nagios_commands | repair | error    | Corrupt                                 |
+------------------------+--------+----------+-----------------------------------------+

# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

# echo "repair table nagios_commands;" | mysql -t -u root -pnagiosxi nagios;
+------------------------+--------+----------+-----------------------------------------+
| Table                  | Op     | Msg_type | Msg_text                                |
+------------------------+--------+----------+-----------------------------------------+
| nagios.nagios_commands | repair | Error    | Incorrect file format 'nagios_commands' |
| nagios.nagios_commands | repair | error    | Corrupt                                 |
+------------------------+--------+----------+-----------------------------------------+
I need a solution...
Top
gormank
Posts: 1114
Joined: Tue Dec 02, 2014 12:00 pm

Re: Damaged tables can't be repaired

Post by gormank »

This is XI 2014R2.6 on RHEL6.7

Here's a sample from syslog.

Code: Select all

Oct 12 21:26:50 qa4am2mlnag002 ndo2db: mysql_error: 'Incorrect file format 'nagios_customvariablestatus''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: Error: mysql_query() failed for 'INSERT INtO nagios_customvariablestatus SET instance_id='1', object_id='6706',status_update_time=FROM_UNIXTIME(1476307611), has_been_modified='0', varname='XIWIZARD', varvalue='vmware' ON DUPLICATE KEY UPDATE instance_id='1', object_id='6706',status_update_time=FROM_UNIXTIME(1476307611), has_been_modified='0', varname='XIWIZARD', varvalue='vmware''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: mysql_error: 'Incorrect file format 'nagios_customvariablestatus''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: Error: mysql_query() failed for 'INSERT INtO nagios_customvariablestatus SET instance_id='1', object_id='6437',status_update_time=FROM_UNIXTIME(1476307611), has_been_modified='0', varname='XIWIZARD', varvalue='windowsserver' ON DUPLICATE KEY UPDATE instance_id='1', object_id='6437',status_update_time=FROM_UNIXTIME(1476307611), has_been_modified='0', varname='XIWIZARD', varvalue='windowsserver''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: mysql_error: 'Incorrect file format 'nagios_customvariablestatus''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_systemcommands SET instance_id='1', start_time=FROM_UNIXTIME(1476307611), start_time_usec='975939', end_time=FROM_UNIXTIME(0), end_time_usec='0', command_line='/bin/mv /var/nagiosramdisk/service-perfdata /var/nagiosramdisk/spool/xidpe/1476307611\.perfdata\.service', timeout='5', early_timeout='0', execution_time='0.000000', return_code='0', output='', long_output='' ON DUPLICATE KEY UPDATE instance_id='1', start_time=FROM_UNIXTIME(1476307611), start_time_usec='975939', end_time=FROM_UNIXTIME(0), end_time_usec='0', command_line='/bin/mv /var/nagiosramdisk/service-perfdata /var/nagiosramdisk/spool/xidpe/1476307611\.perfdata\.service', timeout='5', early_timeout='0', execution_time='0.000000', return_code='0', output='', long_output='''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: mysql_error: 'Incorrect file format 'nagios_systemcommands''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_systemcommands SET instance_id='1', start_time=FROM_UNIXTIME(1476307611), start_time_usec='975939', end_time=FROM_UNIXTIME(1476307611), end_time_usec='978662', command_line='/bin/mv /var/nagiosramdisk/service-perfdata /var/nagiosramdisk/spool/xidpe/1476307611\.perfdata\.service', timeout='5', early_timeout='0', execution_time='0.002000', return_code='0', output='', long_output='' ON DUPLICATE KEY UPDATE instance_id='1', start_time=FROM_UNIXTIME(1476307611), start_time_usec='975939', end_time=FROM_UNIXTIME(1476307611), end_time_usec='978662', command_line='/bin/mv /var/nagiosramdisk/service-perfdata /var/nagiosramdisk/spool/xidpe/1476307611\.perfdata\.service', timeout='5', early_timeout='0', execution_time='0.002000', return_code='0', output='', long_output='''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: mysql_error: 'Incorrect file format 'nagios_systemcommands''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_systemcommands SET instance_id='1', start_time=FROM_UNIXTIME(1476307611), start_time_usec='978757', end_time=FROM_UNIXTIME(0), end_time_usec='0', command_line='/bin/mv /var/nagiosramdisk/host-perfdata /var/nagiosramdisk/spool/xidpe/1476307611\.perfdata\.host', timeout='5', early_timeout='0', execution_time='0.000000', return_code='0', output='', long_output='' ON DUPLICATE KEY UPDATE instance_id='1', start_time=FROM_UNIXTIME(1476307611), start_time_usec='978757', end_time=FROM_UNIXTIME(0), end_time_usec='0', command_line='/bin/mv /var/nagiosramdisk/host-perfdata /var/nagiosramdisk/spool/xidpe/1476307611\.perfdata\.host', timeout='5', early_timeout='0', execution_time='0.000000', return_code='0', output='', long_output='''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: mysql_error: 'Incorrect file format 'nagios_systemcommands''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_systemcommands SET instance_id='1', start_time=FROM_UNIXTIME(1476307611), start_time_usec='978757', end_time=FROM_UNIXTIME(1476307611), end_time_usec='981000', command_line='/bin/mv /var/nagiosramdisk/host-perfdata /var/nagiosramdisk/spool/xidpe/1476307611\.perfdata\.host', timeout='5', early_timeout='0', execution_time='0.002000', return_code='0', output='', long_output='' ON DUPLICATE KEY UPDATE instance_id='1', start_time=FROM_UNIXTIME(1476307611), start_time_usec='978757', end_time=FROM_UNIXTIME(1476307611), end_time_usec='981000', command_line='/bin/mv /var/nagiosramdisk/host-perfdata /var/nagiosramdisk/spool/xidpe/1476307611\.perfdata\.host', timeout='5', early_timeout='0', execution_time='0.002000', return_code='0', output='', long_output='''
Oct 12 21:26:51 qa4am2mlnag002 ndo2db: mysql_error: 'Incorrect file format 'nagios_systemcommands''
Oct 12 21:26:52 qa4am2mlnag002 ndo2db: Error: mysql_query() failed for 'INSERT INtO nagios_customvariablestatus SET instance_id='1', object_id='5087',status_update_time=FROM_UNIXTIME(1476307612), has_been_modified='0', varname='XIWIZARD', varvalue='linux-server' ON DUPLICATE KEY UPDATE instance_id='1', object_id='5087',status_update_time=FROM_UNIXTIME(1476307612), has_been_modified='0', varname='XIWIZARD', varvalue='linux-server''
Oct 12 21:26:52 qa4am2mlnag002 ndo2db: mysql_error: 'Incorrect file format 'nagios_customvariablestatus''
Oct 12 21:26:52 qa4am2mlnag002 ndo2db: Error: mysql_query() failed for 'INSERT INtO nagios_customvariablestatus SET instance_id='1', object_id='5096',status_update_time=FROM_UNIXTIME(1476307612), has_been_modified='0', varname='XIWIZARD', varvalue='linux-server' ON DUPLICATE KEY UPDATE instance_id='1', object_id='5096',status_update_time=FROM_UNIXTIME(1476307612), has_been_modified='0', varname='XIWIZARD', varvalue='linux-server''
Oct 12 21:26:52 qa4am2mlnag002 ndo2db: mysql_error: 'Incorrect file format 'nagios_customvariablestatus''
Oct 12 21:26:52 qa4am2mlnag002 ndo2db: Error: mysql_query() failed for 'INSERT INtO nagios_customvariablestatus SET instance_id='1', object_id='6740',status_update_time=FROM_UNIXTIME(1476307612), has_been_modified='0', varname='XIWIZARD', varvalue='vmware' ON DUPLICATE KEY UPDATE instance_id='1', object_id='6740',status_update_time=FROM_UNIXTIME(1476307612), has_been_modified='0', varname='XIWIZARD', varvalue='vmware''
Oct 12 21:26:52 qa4am2mlnag002 ndo2db: mysql_error: 'Incorrect file format 'nagios_customvariablestatus''
Top
User avatar
tgriep
Madmin
Posts: 9190
Joined: Thu Oct 30, 2014 9:02 am

Re: Damaged tables can't be repaired

Post by tgriep »

Try running this on the XI server as root and see if it repairs all of the databases and fixes the indexes for you.

Code: Select all

mysqlcheck -f -r -u root -pnagiosxi --use-frm --all-databases
Then see if the errors are gone.
Be sure to check out our Knowledgebase for helpful articles and solutions!
Top
ssax
Dreams In Code
Posts: 7682
Joined: Wed Feb 11, 2015 12:54 pm

Re: Damaged tables can't be repaired

Post by ssax »

You can try using these:

Code: Select all

service nagios stop
service ndo2db stop
service mysqld stop
myisamchk --safe-recover /var/lib/mysql/nagios/*.MYI
service mysqld start
service ndo2db start
service nagios start
Or you can try this as a crazy last ditch effort:

Code: Select all

echo 'repair table nagios_logentries use_frm;' | mysql  -t -u root -pnagiosxi nagios
I've always had to restore the DB from a backup (or truncate the table and imporgt) when I got the Incorrect file format.
Top
gormank
Posts: 1114
Joined: Tue Dec 02, 2014 12:00 pm

Re: Damaged tables can't be repaired

Post by gormank »

You might want to read what I've posted since the crazy last ditch effort is what doesn't work and is what was suggested previously.
Top
avandemore
Posts: 1597
Joined: Tue Sep 27, 2016 4:57 pm

Re: Damaged tables can't be repaired

Post by avandemore »

What was suggested originally and since by Nagios support is to use the repair option with use_frm. This will attempt to repair indexes which is what appear to be broken. The information you have relayed in this thread doesn't include such an attempt. It's not a guarantee but that's why backups exist. Nagios can only depend on what the OS and supporting platform provide.
Previous Nagios employee
Top
gormank
Posts: 1114
Joined: Tue Dec 02, 2014 12:00 pm

Re: Damaged tables can't be repaired

Post by gormank »

This has been done many times. After the last time, nagios wont even start due to a host defined in hostgroups.cfg apparently not existing in the DB.
I suggest a case be opened since this issue isn't getting the needed support. BS statements like that's why backups and suggesting I do things already done, as well as not reading posts are not helpful. A helpful thing would be to point to a solution. I haven't seen a doc for recovering the DB. Perhaps a link?
Top
avandemore
Posts: 1597
Joined: Tue Sep 27, 2016 4:57 pm

Re: Damaged tables can't be repaired

Post by avandemore »

Here is our documentation on repairing a DB: Repairing The Database.
Previous Nagios employee
Top
gormank
Posts: 1114
Joined: Tue Dec 02, 2014 12:00 pm

Re: Damaged tables can't be repaired

Post by gormank »

We're at the restore stage now.

Close this
Top
avandemore
Posts: 1597
Joined: Tue Sep 27, 2016 4:57 pm

Re: Damaged tables can't be repaired

Post by avandemore »

Please see the Emergency Restore section.
Previous Nagios employee
Top
Locked

Return to “Nagios XI”