Nagios Support Forum

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

SQL error on various pages, new install

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

Page 1 of 1

SQL error on various pages, new install

Posted: Wed Aug 22, 2012 6:37 pm
by fpjohn
Hi,

Just joined up here, the company I work for tasked me with evaluating Nagios. I got the setup complete and the initial configuration done. I'm noticing this error message all over the place on our system though and I can't seem to get any monitors going (just trying to do a simple ping monitor for now)...

SQL: SQL Error [ndoutils] : Incorrect file format 'nagios_programstatus'

I'm not sure what other information to provide so please ask.

Thanks for any assistance!

Re: SQL error on various pages, new install

Posted: Thu Aug 23, 2012 9:14 am
by scottwilkerson
We will need to try the brute force repair. If it's highly damaged it may take more than one run:

Code: Select all

myisamchk -r -f /var/lib/mysql/nagios/nagios_*.MYI


If that doesn't work, we will need to drop the nagios_programstatus table and
rebuild it.

Code: Select all

mysql -pnagiosxi nagios
DROP TABLE nagios_programstatus;

CREATE TABLE IF NOT EXISTS `nagios_programstatus` (
  `programstatus_id` int(11) NOT NULL auto_increment,
  `instance_id` smallint(6) NOT NULL default '0',
  `status_update_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `program_start_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `program_end_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `is_currently_running` smallint(6) NOT NULL default '0',
  `process_id` int(11) NOT NULL default '0',
  `daemon_mode` smallint(6) NOT NULL default '0',
  `last_command_check` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_log_rotation` datetime NOT NULL default '0000-00-00 00:00:00',
  `notifications_enabled` smallint(6) NOT NULL default '0',
  `active_service_checks_enabled` smallint(6) NOT NULL default '0',
  `passive_service_checks_enabled` smallint(6) NOT NULL default '0',
  `active_host_checks_enabled` smallint(6) NOT NULL default '0',
  `passive_host_checks_enabled` smallint(6) NOT NULL default '0',
  `event_handlers_enabled` smallint(6) NOT NULL default '0',
  `flap_detection_enabled` smallint(6) NOT NULL default '0',
  `failure_prediction_enabled` smallint(6) NOT NULL default '0',
  `process_performance_data` smallint(6) NOT NULL default '0',
  `obsess_over_hosts` smallint(6) NOT NULL default '0',
  `obsess_over_services` smallint(6) NOT NULL default '0',
  `modified_host_attributes` int(11) NOT NULL default '0',
  `modified_service_attributes` int(11) NOT NULL default '0',
  `global_host_event_handler` varchar(255) character set latin1 NOT NULL
default '',
  `global_service_event_handler` varchar(255) character set latin1 NOT NULL
default '',
  PRIMARY KEY  (`programstatus_id`),
  UNIQUE KEY `instance_id` (`instance_id`)
) ENGINE=MyISAM  COMMENT='Current program status information';

Re: SQL error on various pages, new install

Posted: Thu Aug 23, 2012 12:44 pm
by fpjohn
Hi Scott,

Thanks for the reply... I ran the first command a few times and it didn't solve the issue.

The second part, I'm not sure i'm doing this in the right place. When I do the first line, I get this: -bash: syntax error near unexpected token `)'

Re: SQL error on various pages, new install

Posted: Thu Aug 23, 2012 1:00 pm
by scottwilkerson
I made this one command for you.

Copy the whole thing and paste it in the command line

Code: Select all

echo "DROP TABLE nagios_programstatus;
CREATE TABLE IF NOT EXISTS nagios_programstatus (
  programstatus_id int(11) NOT NULL auto_increment,
  instance_id smallint(6) NOT NULL default '0',
  status_update_time datetime NOT NULL default '0000-00-00 00:00:00',
  program_start_time datetime NOT NULL default '0000-00-00 00:00:00',
  program_end_time datetime NOT NULL default '0000-00-00 00:00:00',
  is_currently_running smallint(6) NOT NULL default '0',
  process_id int(11) NOT NULL default '0',
  daemon_mode smallint(6) NOT NULL default '0',
  last_command_check datetime NOT NULL default '0000-00-00 00:00:00',
  last_log_rotation datetime NOT NULL default '0000-00-00 00:00:00',
  notifications_enabled smallint(6) NOT NULL default '0',
  active_service_checks_enabled smallint(6) NOT NULL default '0',
  passive_service_checks_enabled smallint(6) NOT NULL default '0',
  active_host_checks_enabled smallint(6) NOT NULL default '0',
  passive_host_checks_enabled smallint(6) NOT NULL default '0',
  event_handlers_enabled smallint(6) NOT NULL default '0',
  flap_detection_enabled smallint(6) NOT NULL default '0',
  failure_prediction_enabled smallint(6) NOT NULL default '0',
  process_performance_data smallint(6) NOT NULL default '0',
  obsess_over_hosts smallint(6) NOT NULL default '0',
  obsess_over_services smallint(6) NOT NULL default '0',
  modified_host_attributes int(11) NOT NULL default '0',
  modified_service_attributes int(11) NOT NULL default '0',
  global_host_event_handler varchar(255) character set latin1 NOT NULL
default '',
  global_service_event_handler varchar(255) character set latin1 NOT NULL
default '',
  PRIMARY KEY  (programstatus_id),
  UNIQUE KEY instance_id (instance_id)
) ENGINE=MyISAM  COMMENT='Current program status information';"|mysql -pnagiosxi nagios

Re: SQL error on various pages, new install

Posted: Thu Aug 23, 2012 1:42 pm
by fpjohn
It seems to work ok, but stalls when it gets to the last line...

I let it sit there for a few minutes but it never moved passed it. If I hit enter I get this:

>
> ) ENGINE=MyISAM COMMENT='Current program status information';"|mysql -pnagiosxi nagios
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@fpdcmonitor ~]#

Re: SQL error on various pages, new install

Posted: Thu Aug 23, 2012 3:36 pm
by scottwilkerson
Ok, this is likely caused by you changing the default mysql password

by default it is nagiosxi so change the end of the command from

Code: Select all

mysql -pnagiosxi nagios
to

Code: Select all

mysql -p<YOUR_MYSQL_PASSWORD> nagios
no spaces after the -p

Re: SQL error on various pages, new install

Posted: Thu Aug 23, 2012 3:44 pm
by fpjohn
ah ok. made the change and it did run. however the problem is still there. here's the results from the run...

->
-> DROP TABLE nagios_programstatus;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql -p<YOUR_MYSQL_PASSWORD> nagios ***I did put the password here, just removed it for this****

DROP TABLE nagios_programstatus' at line 1
mysql>
mysql>
mysql>
mysql> CREATE TABLE IF NOT EXISTS `nagios_programstatus` (
->
-> `programstatus_id` int(11) NOT NULL auto_increment,
->
-> `instance_id` smallint(6) NOT NULL default '0',
->
-> `status_update_time` datetime NOT NULL default '0000-00-00 00:00:00',
->
-> `program_start_time` datetime NOT NULL default '0000-00-00 00:00:00',
->
-> `program_end_time` datetime NOT NULL default '0000-00-00 00:00:00',
->
-> `is_currently_running` smallint(6) NOT NULL default '0',
->
-> `process_id` int(11) NOT NULL default '0',
->
-> `daemon_mode` smallint(6) NOT NULL default '0',
->
-> `last_command_check` datetime NOT NULL default '0000-00-00 00:00:00',
->
-> `last_log_rotation` datetime NOT NULL default '0000-00-00 00:00:00',
->
-> `notifications_enabled` smallint(6) NOT NULL default '0',
->
-> `active_service_checks_enabled` smallint(6) NOT NULL default '0',
->
-> `passive_service_checks_enabled` smallint(6) NOT NULL default '0',
->
-> `active_host_checks_enabled` smallint(6) NOT NULL default '0',
->
-> `passive_host_checks_enabled` smallint(6) NOT NULL default '0',
->
-> `event_handlers_enabled` smallint(6) NOT NULL default '0',
->
-> `flap_detection_enabled` smallint(6) NOT NULL default '0',
->
-> `failure_prediction_enabled` smallint(6) NOT NULL default '0',
->
-> `process_performance_data` smallint(6) NOT NULL default '0',
->
-> `obsess_over_hosts` smallint(6) NOT NULL default '0',
->
-> `obsess_over_services` smallint(6) NOT NULL default '0',
->
-> `modified_host_attributes` int(11) NOT NULL default '0',
->
-> `modified_service_attributes` int(11) NOT NULL default '0',
->
-> `global_host_event_handler` varchar(255) character set latin1 NOT NULL
->
-> default '',
->
-> `global_service_event_handler` varchar(255) character set latin1 NOT NULL
->
-> default '',
->
-> PRIMARY KEY (`programstatus_id`),
->
-> UNIQUE KEY `instance_id` (`instance_id`)
->
-> ) ENGINE=MyISAM COMMENT='Current program status information';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>
mysql>

Re: SQL error on various pages, new install

Posted: Thu Aug 23, 2012 4:04 pm
by scottwilkerson
I sent you an email.

Re: SQL error on various pages, new install

Posted: Fri Aug 24, 2012 9:22 am
by scottwilkerson
This has been resolved. Many tables were corrupted and had to be re-built.

For other forum viewers, this can happen it the power is lost abruptly to your system, most notable if you do not do a proper shutdown on the system before shutting off a VM. When shutting down a VM you should always run the following from the console before powering it off

Code: Select all

shutdown -h now
All times are UTC-05:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited