Some check are not showing via the Xi UI

[nthaler]

I tried the command, but had also asked our DB Admin to take a look. Here is was he found.

The max size of varchar in this version of mysql is 21845, and I
cannot change that column as suggested. Here is the warning message:

show create table nagios_servicestatus;
==>
...
`check_command` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
...

alter table nagios_servicestatus modify check_command varchar(65535) not null;
==>
ERROR 1074 (42000): Column length too big for column 'check_command'
(max = 21845); use BLOB or TEXT instead

What should we do? Which type to use for that columns?

[tgriep]

Try setting it to 1024 instead by running the following.

Code: Select all

echo "use nagios;alter table nagios_servicestatus modify check_command varchar(1024) not null;" |mysql -u nagios -p#####  nagios -h 10.220.50.1

[nthaler]

From my DM Admin I made the change (after fighting with some other oddities
in that table). We now have:

...
`check_command` varchar(1024) NOT NULL,

Will I need to restart the monitoring engine or DB for this to take affect?

[tgriep]

What oddities did your DM Admin have with that table? That might help out on troubleshooting the issue.

To restart the processes, login as root to the Xi server and run the following commands.

Code: Select all

service nagios stop
service ndo2db restart
service nagios start

Take a look in the /var/log/message file on the XI server for any errors similar to the example below after restarting the daemons.
If you see any new ones, post them here.

Code: Select all

Dec 14 14:30:30 nagios-02 ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_servicestatus SET instance_id='1', service_object_id='6625', status_update_time=FROM_UNIXTIME(1481754630), output='HTTP OK: HTTP/1\.1 200 OK - 72268 bytes in 0\.085 second response time', long_output='', perfdata='time=0\.085357s;;;0\.000000 size=72268B;;;0', current_state='0', has_been_checked='1', should_be_scheduled='1', current_check_attempt='1', max_check_attempts='5', last_check=FROM_UNIXTIME(1481754630), next_check=FROM_UNIXTIME(1481754930), check_type='0', last_state_change=FROM_UNIXTIME(1480363547), last_hard_state_change=FROM_UNIXTIME(1472156636), last_hard_state='0', last_time_ok=FROM_UNIXTIME(1481754630), last_time_warning=FROM_UNIXTIME(1472156636), last_time_unknown=FROM_UNIXTIME(0), last_time_critical=FROM_UNIXTIME(1480363547), 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.000000', execution_time='0.087860', scheduled_downtime_depth='0', failure_prediction_enabled='0', process_performance_data='1', obsess_over_service='1', modified_service_attributes='1', event_handler='', check_command='check_url_content_match!neoreviews\.aappublications\.org!\'/content/17/8\?current-issue=y/cgi/search\?sendit=Search&pubdate_year=&volume=&firstpage=&author1=&author2=&title=CPAP&andorexacttitle=and&titleabstract=&andorexacttitleabs=and&fulltext=&andorexactfulltext=and&SITESECTION=vj&fmonth=Jan&fyear=2000&tmonth=Oct&tyear=2006&fdatedef=1\+January\+2000&tdatedef=1\+October\+2006&flag=&RESULTFORMAT=1&hits=10&hitsbrief=25&sortspec=relevance&sortspecbrief=relevance\'!\'Newborn\'!!!!!', normal_check_interval='5.000000', retry_check_interval='2.000000', check_timeperiod_object_id='125' ON D
Dec 14 14:30:30 nagios-02 ndo2db: mysql_error: 'Data too long for column 'check_command' at row 1'

[nthaler]

I did the restarts and we are seeing services that were missing from the UI before.

FYI
From my DB Admin
The table in question, nagios..nagios_servicestatus, has some default
date values of '0000-00-00 00:00:00'. This particular version of
mysql, 5.6.32-78.0, doesn't allow null values in date fields.
Therefore, ALTER TABLE command didn't want to work.When I tried
changing the "sql_mode", and dropping NO_ZERO_IN_DATE,NO_ZERO_DATE
from sql_mode, the server didn't allow me to make those changes. It
took some playing and trickery to finally change sql_mode.
Only after that, was I able to change the varchar(255) to
varchar(1024) in the column `check_command`. -Harv

Thank you for your help

[tgriep]

That is good to hear that it is working now.
Keep an eye on that log file and is you see any similar errors, post them here so we can view then and recommend a fix.