Corrupt nagios_hostchecks table

[johndoe]

Having a problem with a table that appears it went crazy...

After seeing alot of these:

Code: Select all

Mar  8 20:38:05 localhost ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_hostchecks SET instance_id='1', host_object_id='180', check_type='0', is_raw_check='0', current_check_attempt='1', max_check_attempts='5', state='0', state_type='1', start_time=FROM_UNIXTIME(1362775075), start_time_usec='255919', end_time=FROM_UNIXTIME(1362775085), end_time_usec='86893', timeout='30', early_timeout='0', execution_time='0.011200', latency='0.255000', return_code='0', output='OK - 10\.20\.1\.2: rta 1\.267ms, lost 0%', long_output='', perfdata='rta=1\.267ms;3000\.000;5000\.000;0; pl=0%;80;100;;', command_object_id='35', command_args='3000\.0!80%!5000\.0!100%', command_line='' ON DUPLICATE KEY UPDATE instance_id='1', host_object_id='180', check_type='0', is_raw_check='0', current_check_attempt='1', max_check_attempts='5', state='0', state_type='1', start_time=FROM_UNIXTIME(1362775075), start_time_usec='255919', end_time=FROM_UNIXTIME(1362775085), end_time_usec='86893', timeout='30', early_timeout='0', execution_time='0.011200', latency='0.255000', return_code='0', output='OK - 10\.20\.1\.2: rta 1\.267ms, lost 0%', long_output='', perfdata='rta=1\.267ms;3000\.000;5000\.000;0; pl=0%;80;100;;''
Mar  8 20:38:05 localhost ndo2db: mysql_error: 'Incorrect file format 'nagios_hostchecks''

I tried the repairing as mentioned here: http://assets.nagios.com/downloads/nagi ... tabase.pdf

but that didnt't fix it...

the files show a file size of 0, what can i do to get this table back?

Code: Select all

mysql> repair table nagios_hostchecks;
+--------------------------+--------+----------+-------------------------------------------+
| Table                    | Op     | Msg_type | Msg_text                                  |
+--------------------------+--------+----------+-------------------------------------------+
| nagios.nagios_hostchecks | repair | Error    | Incorrect file format 'nagios_hostchecks' |
| nagios.nagios_hostchecks | repair | error    | Corrupt                                   |
+--------------------------+--------+----------+-------------------------------------------+
2 rows in set (0.00 sec)

[root@host nagios]# ls -lha nagios_hostchecks.
ls: cannot access nagios_hostchecks.: No such file or directory
[root@host nagios]# ls -lha nagios_hostchecks.*
-rw-rw---- 1 mysql mysql 34K Nov 28 09:42 nagios_hostchecks.frm
-rw-rw---- 1 mysql mysql   0 Jan 30 10:10 nagios_hostchecks.MYD
-rw-rw---- 1 mysql mysql   0 Jan 30 10:10 nagios_hostchecks.MYI

[scottwilkerson]

Incorrect file format may be unrecoverable. If you have the daily mysql backup you might be able to restore one from before it went bad

like so

Code: Select all

[root@localhost libexec]# ll /store/backups/mysql/daily/nagios
total 12864
-rw-r--r-- 1 root root 2096673 Mar  3 07:00 nagios_2013-03-03_07h00m.Sunday.sql.gz
-rw-r--r-- 1 root root 2089834 Mar  4 07:00 nagios_2013-03-04_07h00m.Monday.sql.gz
-rw-r--r-- 1 root root 2084577 Mar  5 07:00 nagios_2013-03-05_07h00m.Tuesday.sql.gz
-rw-r--r-- 1 root root 2259455 Mar  6 07:00 nagios_2013-03-06_07h00m.Wednesday.sql.gz
-rw-r--r-- 1 root root 2274535 Mar  7 07:00 nagios_2013-03-07_07h00m.Thursday.sql.gz
-rw-r--r-- 1 root root 2355496 Mar  8 07:00 nagios_2013-03-08_07h00m.Friday.sql.gz
[root@localhost libexec]# gunzip /store/backups/mysql/daily/nagios/nagios_2013-03-08_07h00m.Friday.sql.gz
[root@localhost libexec]# cat /store/backups/mysql/daily/nagios/nagios_2013-03-08_07h00m.Friday.sql|mysql -pnagiosxi nagios

If that doesn't work the best bet would be to recreate the table.

Code: Select all

mysql -pnagiosxi nagios

DROP TABLE IF EXISTS `nagios_hostchecks`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `nagios_hostchecks` (
  `hostcheck_id` int(11) NOT NULL AUTO_INCREMENT,
  `instance_id` smallint(6) NOT NULL DEFAULT '0',
  `host_object_id` int(11) NOT NULL DEFAULT '0',
  `check_type` smallint(6) NOT NULL DEFAULT '0',
  `is_raw_check` smallint(6) NOT NULL DEFAULT '0',
  `current_check_attempt` smallint(6) NOT NULL DEFAULT '0',
  `max_check_attempts` smallint(6) NOT NULL DEFAULT '0',
  `state` smallint(6) NOT NULL DEFAULT '0',
  `state_type` smallint(6) NOT NULL DEFAULT '0',
  `start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `start_time_usec` int(11) NOT NULL DEFAULT '0',
  `end_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end_time_usec` int(11) NOT NULL DEFAULT '0',
  `command_object_id` int(11) NOT NULL DEFAULT '0',
  `command_args` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `command_line` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `timeout` smallint(6) NOT NULL DEFAULT '0',
  `early_timeout` smallint(6) NOT NULL DEFAULT '0',
  `execution_time` double NOT NULL DEFAULT '0',
  `latency` double NOT NULL DEFAULT '0',
  `return_code` smallint(6) NOT NULL DEFAULT '0',
  `output` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `long_output` varchar(8192) NOT NULL DEFAULT '',
  `perfdata` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
  PRIMARY KEY (`hostcheck_id`),
  UNIQUE KEY `instance_id` (`instance_id`,`host_object_id`,`start_time`,`start_time_usec`)
) ENGINE=MyISAM AUTO_INCREMENT=6256168 DEFAULT CHARSET=utf8 COMMENT='Historical host checks';
/*!40101 SET character_set_client = @saved_cs_client */;

[johndoe]

table had to be recreated, thanks