MYSQL errors in /var/log/messages

[opssupport]

Hey guys,
We are new here and are still trying to figure out Nagios in general.
We currently have an issue where our root partition is filling up really fast due to the /var/log/messages log filling up with errors from mySQL: (currently 4GB)

Apr 23 09:54:12 nagios-sng ndo2db: mysql_error: 'Incorrect file format 'nagios_servicestatus''
Apr 23 09:54:12 nagios-sng ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_servicestatus SET instance_id='1', service_object_id='1657', status_update_time=FROM_UNIXTIME(1398243252), output='OK - 172\.27\.105\.201: rta 0\.326ms, lost 0%', long_output='', perfdata='rta=0\.326ms;3000\.000;5000\.000;0; pl=0%;80;100;;', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='3', last_check=FROM_UNIXTIME(1398243247), next_check=FROM_UNIXTIME(1398243307), check_type='0', last_state_change=FROM_UNIXTIME(1398238387), last_hard_state_change=FROM_UNIXTIME(1398238387), last_hard_state='0', last_time_ok=FROM_UNIXTIME(1398243247), last_time_warning=FROM_UNIXTIME(0), last_time_unknown=FROM_UNIXTIME(0), last_time_critical=FROM_UNIXTIME(1398238327), state_type='1', last_notification=FROM_UNIXTIME(0), next_notification=FROM_UNIXTIME(0), no_more_notifications='0', notifications_enabled='1', problem_has_been_acknowledged='0', acknowledgement_type='0', current_notification_number='0', passive_checks_enabled='1', active_checks_enabled='1', event_handler_enabled='1', flap_detection_enabled='1', is_flapping='0', percent_state_change='0.000000', latency='0.185000', execution_time='0.006270', scheduled_downtime_depth='0', failure_prediction_enabled='1', process_performance_data='1', obsess_over_service='0', modified_service_attributes='0', event_handler='', check_command='check_xi_service_ping!3000\.0!80%!5000\.0!100%', normal_check_interval='1.000000', retry_check_interval='1.000000', check_timeperiod_object_id='67' ON DUPLICATE KEY UPDATE instance_id='1', service_object_id='1657', status_update_time=FROM_UNIXTIME(1398243252), output='OK - 172\.27\.105\.201: rta 0\.326ms, lost 0%', long_output='', perfdata='rta=0\.326ms;3000\.000;5000\.000;0; pl=0%;80;100;;', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='3', last_check=FROM_UNIXTIME(1398243247), next_check=FROM_UNIXTIME(1398243307), check_type='0', last_state_c
Apr 23 09:54:12 nagios-sng ndo2db: mysql_error: 'Incorrect file format 'nagios_servicestatus''
Apr 23 09:54:12 nagios-sng ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_systemcommands SET instance_id='1', start_time=FROM_UNIXTIME(1398243252), start_time_usec='461825', end_time=FROM_UNIXTIME(0), end_time_usec='0', command_line='/usr/local/nagios/libexec/eventhandlers/service_check SD-BACKUP-SNG \'SQL Server Agent\' 0 \'SQLSERVERAGENT: Started\'', 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(1398243252), start_time_usec='461825', end_time=FROM_UNIXTIME(0), end_time_usec='0', command_line='/usr/local/nagios/libexec/eventhandlers/service_check SD-BACKUP-SNG \'SQL Server Agent\' 0 \'SQLSERVERAGENT: Started\'', timeout='5', early_timeout='0', execution_time='0.000000', return_code='0', output='', long_output='''
Apr 23 09:54:12 nagios-sng ndo2db: mysql_error: 'Incorrect file format 'nagios_systemcommands''
Apr 23 09:54:12 nagios-sng ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_systemcommands SET instance_id='1', start_time=FROM_UNIXTIME(1398243252), start_time_usec='461825', end_time=FROM_UNIXTIME(1398243252), end_time_usec='468512', command_line='/usr/local/nagios/libexec/eventhandlers/service_check SD-BACKUP-SNG \'SQL Server Agent\' 0 \'SQLSERVERAGENT: Started\'', timeout='5', early_timeout='0', execution_time='0.006000', return_code='0', output='', long_output='' ON DUPLICATE KEY UPDATE instance_id='1', start_time=FROM_UNIXTIME(1398243252), start_time_usec='461825', end_time=FROM_UNIXTIME(1398243252), end_time_usec='468512', command_line='/usr/local/nagios/libexec/eventhandlers/service_check SD-BACKUP-SNG \'SQL Server Agent\' 0 \'SQLSERVERAGENT: Started\'', timeout='5', early_timeout='0', execution_time='0.006000', return_code='0', output='', long_output='''
Apr 23 09:54:12 nagios-sng ndo2db: mysql_error: 'Incorrect file format 'nagios_systemcommands''
Apr 23 09:54:12 nagios-sng ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_servicestatus SET instance_id='1', service_object_id='792', status_update_time=FROM_UNIXTIME(1398243252), output='SQLSERVERAGENT: Started', long_output='', perfdata='', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='1', last_check=FROM_UNIXTIME(1398243248), next_check=FROM_UNIXTIME(1398243428), check_type='0', last_state_change=FROM_UNIXTIME(1398072786), last_hard_state_change=FROM_UNIXTIME(1398072786), last_hard_state='0', last_time_ok=FROM_UNIXTIME(1398243248), last_time_warning=FROM_UNIXTIME(1385300082), last_time_unknown=FROM_UNIXTIME(0), last_time_critical=FROM_UNIXTIME(1398072606), state_type='1', last_notification=FROM_UNIXTIME(0), next_notification=FROM_UNIXTIME(0), no_more_notifications='0', notifications_enabled='1', problem_has_been_acknowledged='0', acknowledgement_type='0', current_notification_number='0', passive_checks_enabled='1', active_checks_enabled='1', event_handler_enabled='1', flap_detection_enabled='1', is_flapping='0', percent_state_change='0.000000', latency='0.187000', execution_time='0.109860', scheduled_downtime_depth='0', failure_prediction_enabled='1', process_performance_data='1', obsess_over_service='1', modified_service_attributes='0', event_handler='', check_command='check_xi_service_nsclient!duracell!SERVICESTATE!-l SQLSERVERAGENT -d SHOWALL!!!!!', normal_check_interval='3.000000', retry_check_interval='1.000000', check_timeperiod_object_id='67' ON DUPLICATE KEY UPDATE instance_id='1', service_object_id='792', status_update_time=FROM_UNIXTIME(1398243252), output='SQLSERVERAGENT: Started', long_output='', perfdata='', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='1', last_check=FROM_UNIXTIME(1398243248), next_check=FROM_UNIXTIME(1398243428), check_type='0', last_state_change=FROM_UNIXTIME(1398072786), last_hard_state_change=FROM_UNIXTIME(1398072786), last_hard_state='0'
Apr 23 09:54:12 nagios-sng ndo2db: mysql_error: 'Incorrect file format 'nagios_servicestatus''
Apr 23 09:54:12 nagios-sng ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_servicestatus SET instance_id='1', service_object_id='792', status_update_time=FROM_UNIXTIME(1398243252), output='SQLSERVERAGENT: Started', long_output='', perfdata='', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='1', last_check=FROM_UNIXTIME(1398243248), next_check=FROM_UNIXTIME(1398243428), check_type='0', last_state_change=FROM_UNIXTIME(1398072786), last_hard_state_change=FROM_UNIXTIME(1398072786), last_hard_state='0', last_time_ok=FROM_UNIXTIME(1398243248), last_time_warning=FROM_UNIXTIME(1385300082), last_time_unknown=FROM_UNIXTIME(0), last_time_critical=FROM_UNIXTIME(1398072606), state_type='1', last_notification=FROM_UNIXTIME(0), next_notification=FROM_UNIXTIME(0), no_more_notifications='0', notifications_enabled='1', problem_has_been_acknowledged='0', acknowledgement_type='0', current_notification_number='0', passive_checks_enabled='1', active_checks_enabled='1', event_handler_enabled='1', flap_detection_enabled='1', is_flapping='0', percent_state_change='0.000000', latency='0.187000', execution_time='0.109860', scheduled_downtime_depth='0', failure_prediction_enabled='1', process_performance_data='1', obsess_over_service='1', modified_service_attributes='0', event_handler='', check_command='check_xi_service_nsclient!duracell!SERVICESTATE!-l SQLSERVERAGENT -d SHOWALL!!!!!', normal_check_interval='3.000000', retry_check_interval='1.000000', check_timeperiod_object_id='67' ON DUPLICATE KEY UPDATE instance_id='1', service_object_id='792', status_update_time=FROM_UNIXTIME(1398243252), output='SQLSERVERAGENT: Started', long_output='', perfdata='', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='1', last_check=FROM_UNIXTIME(1398243248), next_check=FROM_UNIXTIME(1398243428), check_type='0', last_state_change=FROM_UNIXTIME(1398072786), last_hard_state_change=FROM_UNIXTIME(1398072786), last_hard_state='0'
Apr 23 09:54:12 nagios-sng ndo2db: mysql_error: 'Incorrect file format 'nagios_servicestatus''
Apr 23 09:54:12 nagios-sng ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_systemcommands SET instance_id='1', start_time=FROM_UNIXTIME(1398243252), start_time_usec='468751', end_time=FROM_UNIXTIME(0), end_time_usec='0', command_line='/usr/local/nagios/libexec/eventhandlers/service_check IREQ-SQL-PR-SNG \'OPSDBA_DatabasesStates\' 0 \'SQL_SD OK - No issues were found\. Total num of results: 0\'', 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(1398243252), start_time_usec='468751', end_time=FROM_UNIXTIME(0), end_time_usec='0', command_line='/usr/local/nagios/libexec/eventhandlers/service_check IREQ-SQL-PR-SNG \'OPSDBA_DatabasesStates\' 0 \'SQL_SD OK - No issues were found\. Total num of results: 0\'', timeout='5', early_timeout='0', execution_time='0.000000', return_code='0', output='', long_output='''
Apr 23 09:54:12 nagios-sng ndo2db: mysql_error: 'Incorrect file format 'nagios_systemcommands''
Apr 23 09:54:12 nagios-sng xinetd[1326]: EXIT: livestatus status=0 pid=20698 duration=1(sec)



I've tried reparing the DB, running a repair script:
/usr/local/nagiosxi/scripts/repairmysql.sh nagios

But it didnt solve the issue.

Also tried and failed to assist:
mysql -u ndoutils -pn@gweb nagios -e 'TRUNCATE TABLE nagios_logentries'
mysql -u ndoutils -pn@gweb nagios -e 'TRUNCATE TABLE nagios_notifications'


Can you please assist (with specific instructions) what should we do?
From reading about these errors in google, ive found that its related mostly to the mysql configuration, but im not very familiar with mysql so im not sure where to even begin.
In addition, we had an unexpected power outage in our farms a few days ago, could this also be related in anyway?

Thanks in advance!
Sharon Alper
SD opssupport.

[tmcdonald]

Closing thread as you have a ticket open for this as well.