Error showing under Notifications Tab

[pratikmehta003]

Hi There,

I recently came across this error in Nagios XI under Notifications Tab..

error under Notifications tab.PNG

[ssax]

You'll need to repair the databases, please follow this guide:

https://assets.nagios.com/downloads/nag ... tabase.pdf

[pratikmehta003]

Thanks.. any reason why this would have happened?

[ssax]

Usually related to non-graceful mysql/mariadb service restarts or hard-powering off the system but can be caused by high IO wait.

What is the output of these commands:

Code: Select all

sar
top -c -n3

Please PM me a copy of your profile, you can download it from Admin > System Profile by clicking the Download Profile button.

Additionally, please send the output of these commands:
- NOTE: You may need to adjust the -h 127.0.0.1, the -uroot, and -pnagiosxi in the first command if your DB is offloaded to another server and/or you've changed the root mysql password

Code: Select all

echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');" | mysql -h 127.0.0.1 -uroot -pnagiosxi --table

This next command may fail, that's okay, not all systems run postgresql, send the output anyways:

Code: Select all

echo "SELECT relname as Table, pg_size_pretty(pg_total_relation_size(relid)) As Size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as ExternalSize FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;" | psql nagiosxi nagiosxi

[pratikmehta003]

Hi,

I ran the repair script and attached output for the same.. i dont see that error anymore..

DBrepair output.txt

also find below sar output:

Linux 3.10.0-1127.19.1.el7.x86_64 (ucprngapprd01) 01/23/2021 _x86_64_(4 CPU)

12:00:01 AM CPU %user %nice %system %iowait %steal %idle
12:10:01 AM all 1.29 0.00 0.40 0.10 0.00 98.21
12:20:01 AM all 1.28 0.00 0.41 0.05 0.00 98.26
12:30:01 AM all 1.27 0.00 0.39 0.05 0.00 98.30
12:40:01 AM all 1.26 0.00 0.40 0.07 0.00 98.26
12:50:01 AM all 1.27 0.00 0.39 0.05 0.00 98.28
01:00:01 AM all 1.25 0.00 0.41 0.10 0.00 98.25
01:10:01 AM all 1.28 0.00 0.42 0.20 0.00 98.11
01:20:01 AM all 1.25 0.00 0.39 0.09 0.00 98.27
01:30:01 AM all 1.27 0.00 0.40 0.05 0.00 98.27
01:40:01 AM all 1.27 0.00 0.40 0.06 0.00 98.27
01:50:01 AM all 1.25 0.00 0.39 0.07 0.00 98.29
02:00:01 AM all 1.27 0.00 0.39 0.04 0.00 98.30
02:10:01 AM all 1.26 0.00 0.42 0.21 0.00 98.12
02:20:01 AM all 1.26 0.00 0.40 0.04 0.00 98.30
02:30:01 AM all 1.28 0.00 0.40 0.04 0.00 98.28
02:40:02 AM all 1.24 0.00 0.39 0.05 0.00 98.31
02:50:01 AM all 1.25 0.00 0.39 0.05 0.00 98.31
03:00:01 AM all 1.27 0.00 0.40 0.06 0.00 98.27
03:10:01 AM all 1.25 0.00 0.41 0.27 0.00 98.08
03:20:01 AM all 1.26 0.01 0.40 0.07 0.00 98.25
03:30:01 AM all 1.25 0.00 0.40 0.05 0.00 98.30
03:40:01 AM all 1.26 0.00 0.40 0.07 0.00 98.27

03:40:01 AM CPU %user %nice %system %iowait %steal %idle
03:50:01 AM all 1.25 0.00 0.39 0.04 0.00 98.31
04:00:01 AM all 1.25 0.00 0.39 0.04 0.00 98.32
04:10:02 AM all 1.28 0.00 0.40 0.12 0.00 98.20
04:20:01 AM all 1.25 0.00 0.40 0.04 0.00 98.31
04:30:01 AM all 1.27 0.00 0.40 0.04 0.00 98.29
04:40:01 AM all 1.26 0.00 0.39 0.06 0.00 98.29
04:50:01 AM all 1.27 0.00 0.41 0.06 0.00 98.27
05:00:01 AM all 1.25 0.00 0.40 0.05 0.00 98.30
05:10:01 AM all 1.27 0.00 0.41 0.07 0.00 98.26
05:20:01 AM all 1.25 0.00 0.40 0.13 0.00 98.22
05:30:01 AM all 1.26 0.00 0.40 0.10 0.00 98.24
05:40:01 AM all 1.26 0.00 0.39 0.06 0.00 98.29
05:50:01 AM all 1.25 0.00 0.40 0.05 0.00 98.31
06:00:01 AM all 1.25 0.00 0.39 0.05 0.00 98.30
06:10:01 AM all 1.25 0.00 0.40 0.09 0.00 98.27
06:20:01 AM all 1.26 0.00 0.41 0.11 0.00 98.22
06:30:02 AM all 1.27 0.00 0.40 0.04 0.00 98.29
06:40:01 AM all 1.24 0.00 0.39 0.05 0.00 98.32
06:50:01 AM all 1.29 0.00 0.41 0.04 0.00 98.26
07:00:01 AM all 1.23 0.00 0.39 0.06 0.00 98.32
07:10:01 AM all 1.26 0.00 0.40 0.08 0.00 98.26
07:20:01 AM all 1.27 0.00 0.39 0.05 0.00 98.29

07:20:01 AM CPU %user %nice %system %iowait %steal %idle
07:30:01 AM all 1.26 0.00 0.41 0.14 0.00 98.18
07:40:02 AM all 1.25 0.00 0.41 0.07 0.00 98.27
07:50:01 AM all 1.25 0.00 0.40 0.07 0.00 98.28
08:00:01 AM all 1.25 0.00 0.39 0.08 0.00 98.27
08:10:01 AM all 1.25 0.00 0.40 0.09 0.00 98.26
08:20:01 AM all 1.26 0.00 0.40 0.07 0.00 98.26
08:30:01 AM all 1.25 0.00 0.41 0.18 0.00 98.16
08:40:01 AM all 1.26 0.00 0.41 0.06 0.00 98.27
08:50:02 AM all 1.25 0.00 0.40 0.09 0.00 98.26
09:00:01 AM all 1.27 0.00 0.40 0.05 0.00 98.28
09:10:01 AM all 1.24 0.00 0.40 0.08 0.00 98.27
09:20:01 AM all 1.27 0.00 0.40 0.07 0.00 98.26
09:30:01 AM all 1.28 0.00 0.42 0.12 0.00 98.17
09:40:01 AM all 1.25 0.00 0.39 0.08 0.00 98.27
09:50:02 AM all 1.27 0.00 0.40 0.08 0.00 98.25
10:00:01 AM all 1.24 0.00 0.40 0.08 0.00 98.28
10:10:01 AM all 1.26 0.00 0.41 0.10 0.00 98.24
10:20:01 AM all 1.26 0.00 0.40 0.10 0.00 98.24
10:30:01 AM all 1.24 0.00 0.40 0.06 0.00 98.30
10:40:01 AM all 1.26 0.00 0.41 0.18 0.00 98.15
10:50:01 AM all 1.25 0.00 0.39 0.06 0.00 98.30
11:00:01 AM all 1.27 0.00 0.41 0.05 0.00 98.27

11:00:01 AM CPU %user %nice %system %iowait %steal %idle
11:10:01 AM all 1.25 0.00 0.40 0.06 0.00 98.28
11:20:01 AM all 1.25 0.00 0.40 0.05 0.00 98.30
11:30:01 AM all 1.24 0.00 0.39 0.04 0.00 98.32
11:40:01 AM all 1.27 0.00 0.42 0.14 0.00 98.17
11:50:02 AM all 4.85 0.00 2.25 7.82 0.00 85.08
12:00:01 PM all 15.47 0.00 5.23 4.30 0.00 75.00
12:10:01 PM all 24.78 0.00 5.02 4.33 0.00 65.87
12:20:01 PM all 27.44 0.00 4.65 0.61 0.00 67.31
Average: all 2.15 0.00 0.60 0.30 0.00 96.95

top -c -n3 output:
top - 12:32:47 up 78 days, 23:06, 1 user, load average: 1.51, 1.21, 1.16
Tasks: 264 total, 1 running, 263 sleeping, 0 stopped, 0 zombie
%Cpu(s): 26.0 us, 3.6 sy, 0.0 ni, 69.7 id, 0.7 wa, 0.0 hi, 0.1 si, 0.0 st
KiB Mem : 2921348 total, 267852 free, 1704000 used, 949496 buff/cache
KiB Swap: 2097148 total, 2041092 free, 56056 used. 1076004 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12483 nagios 20 0 10844 1160 844 S 16.6 0.0 0:12.63 /usr/local+
12485 nagios 20 0 10844 1152 844 S 16.6 0.0 0:13.25 /usr/local+
12484 nagios 20 0 10844 1148 844 S 8.6 0.0 0:11.12 /usr/local+
12482 nagios 20 0 10844 1148 840 S 8.3 0.0 0:11.85 /usr/local+
12486 nagios 20 0 10844 1152 844 S 8.3 0.0 0:13.29 /usr/local+
51015 mysql 20 0 2616276 241972 9448 S 2.0 8.3 1:43.44 /usr/libex+
15942 nagios 20 0 203692 11544 4052 S 1.3 0.4 0:00.04 python /us+
15945 nagios 20 0 203692 11544 4052 S 1.3 0.4 0:00.04 python /us+
15946 nagios 20 0 203692 11544 4052 S 1.3 0.4 0:00.04 python /us+
15948 nagios 20 0 203696 11552 4052 S 1.3 0.4 0:00.04 python /us+
15951 nagios 20 0 203692 11540 4052 S 1.3 0.4 0:00.04 python /us+
15952 nagios 20 0 203696 12072 4488 S 1.3 0.4 0:00.04 python /us+
15950 nagios 20 0 203692 11544 4052 S 1.0 0.4 0:00.03 python /us+
1020 root 20 0 308664 7128 5436 S 0.7 0.2 70:53.38 /usr/sbin/+
9 root 20 0 0 0 0 S 0.3 0.0 124:20.59 [rcu_sched]
493 root 20 0 39488 5836 5684 S 0.3 0.2 200:50.30 /usr/lib/s+
12481 nagios 20 0 182056 13268 3840 S 0.3 0.5 0:02.61 /usr/local+

and i had another point which i missed to mention earlier.. for any disk parameter, when i do the run check command under CCM-> services, then it shows like this.. however we are getting alerts and able to modify the threshold and apply config.. so need some input on this..
attached output here

disk parameter.PNG

[pratikmehta003]

also sent u private message with Profile zip file...

and below is the SQL command output:

+--------------------------------------------+------------+
| Table | Size in MB |
+--------------------------------------------+------------+
| nagios_acknowledgements | 0.00 |
| nagios_commands | 0.02 |
| nagios_commenthistory | 4.18 |
| nagios_comments | 0.08 |
| nagios_configfiles | 0.01 |
| nagios_configfilevariables | 0.01 |
| nagios_contact_addresses | 0.00 |
| nagios_contact_notificationcommands | 0.01 |
| nagios_contactgroup_members | 0.00 |
| nagios_contactgroups | 0.00 |
| nagios_contactnotificationmethods | 21.37 |
| nagios_contactnotifications | 22.60 |
| nagios_contacts | 0.00 |
| nagios_contactstatus | 0.00 |
| nagios_customvariables | 0.10 |
| nagios_customvariablestatus | 0.10 |
| nagios_dbversion | 0.00 |
| nagios_downtimehistory | 0.09 |
| nagios_eventhandlers | 0.01 |
| nagios_externalcommands | 0.00 |
| nagios_flappinghistory | 0.02 |
| nagios_host_contactgroups | 0.00 |
| nagios_host_contacts | 0.01 |
| nagios_host_parenthosts | 0.00 |
| nagios_hostchecks | 0.02 |
| nagios_hostdependencies | 0.00 |
| nagios_hostescalation_contactgroups | 0.00 |
| nagios_hostescalation_contacts | 0.00 |
| nagios_hostescalations | 0.00 |
| nagios_hostgroup_members | 0.00 |
| nagios_hostgroups | 0.00 |
| nagios_hosts | 0.01 |
| nagios_hoststatus | 0.03 |
| nagios_instances | 0.00 |
| nagios_logentries | 6514.92 |
| nagios_notifications | 30.54 |
| nagios_objects | 0.20 |
| nagios_processevents | 0.05 |
| nagios_programstatus | 0.00 |
| nagios_runtimevariables | 0.00 |
| nagios_scheduleddowntime | 0.04 |
| nagios_service_contactgroups | 0.00 |
| nagios_service_contacts | 0.11 |
| nagios_service_parentservices | 0.00 |
| nagios_servicechecks | 1.50 |
| nagios_servicedependencies | 0.00 |
| nagios_serviceescalation_contactgroups | 0.00 |
| nagios_serviceescalation_contacts | 0.00 |
| nagios_serviceescalations | 0.00 |
| nagios_servicegroup_members | 0.01 |
| nagios_servicegroups | 0.00 |
| nagios_services | 0.30 |
| nagios_servicestatus | 0.73 |
| nagios_statehistory | 2.85 |
| nagios_systemcommands | 0.04 |
| nagios_timedeventqueue | 0.00 |
| nagios_timedevents | 0.00 |
| nagios_timeperiod_timeranges | 0.00 |
| nagios_timeperiods | 0.00 |
| tbl_command | 0.06 |
| tbl_contact | 0.03 |
| tbl_contactgroup | 0.03 |
| tbl_contacttemplate | 0.03 |
| tbl_domain | 0.03 |
| tbl_host | 0.03 |
| tbl_hostdependency | 0.03 |
| tbl_hostescalation | 0.03 |
| tbl_hostextinfo | 0.03 |
| tbl_hostgroup | 0.03 |
| tbl_hosttemplate | 0.03 |
| tbl_info | 0.17 |
| tbl_lnkContactToCommandHost | 0.02 |
| tbl_lnkContactToCommandService | 0.02 |
| tbl_lnkContactToContactgroup | 0.02 |
| tbl_lnkContactToContacttemplate | 0.02 |
| tbl_lnkContactToVariabledefinition | 0.02 |
| tbl_lnkContactgroupToContact | 0.02 |
| tbl_lnkContactgroupToContactgroup | 0.02 |
| tbl_lnkContacttemplateToCommandHost | 0.02 |
| tbl_lnkContacttemplateToCommandService | 0.02 |
| tbl_lnkContacttemplateToContactgroup | 0.02 |
| tbl_lnkContacttemplateToContacttemplate | 0.02 |
| tbl_lnkContacttemplateToVariabledefinition | 0.02 |
| tbl_lnkHostToContact | 0.02 |
| tbl_lnkHostToContactgroup | 0.02 |
| tbl_lnkHostToHost | 0.02 |
| tbl_lnkHostToHostgroup | 0.02 |
| tbl_lnkHostToHosttemplate | 0.02 |
| tbl_lnkHostToVariabledefinition | 0.02 |
| tbl_lnkHostdependencyToHost_DH | 0.02 |
| tbl_lnkHostdependencyToHost_H | 0.02 |
| tbl_lnkHostdependencyToHostgroup_DH | 0.02 |
| tbl_lnkHostdependencyToHostgroup_H | 0.02 |
| tbl_lnkHostescalationToContact | 0.02 |
| tbl_lnkHostescalationToContactgroup | 0.02 |
| tbl_lnkHostescalationToHost | 0.02 |
| tbl_lnkHostescalationToHostgroup | 0.02 |
| tbl_lnkHostgroupToHost | 0.02 |
| tbl_lnkHostgroupToHostgroup | 0.02 |
| tbl_lnkHosttemplateToContact | 0.02 |
| tbl_lnkHosttemplateToContactgroup | 0.02 |
| tbl_lnkHosttemplateToHost | 0.02 |
| tbl_lnkHosttemplateToHostgroup | 0.02 |
| tbl_lnkHosttemplateToHosttemplate | 0.02 |
| tbl_lnkHosttemplateToVariabledefinition | 0.02 |
| tbl_lnkServiceToContact | 0.11 |
| tbl_lnkServiceToContactgroup | 0.02 |
| tbl_lnkServiceToHost | 0.06 |
| tbl_lnkServiceToHostgroup | 0.02 |
| tbl_lnkServiceToServicegroup | 0.02 |
| tbl_lnkServiceToServicetemplate | 0.08 |
| tbl_lnkServiceToVariabledefinition | 0.06 |
| tbl_lnkServicedependencyToHost_DH | 0.02 |
| tbl_lnkServicedependencyToHost_H | 0.02 |
| tbl_lnkServicedependencyToHostgroup_DH | 0.02 |
| tbl_lnkServicedependencyToHostgroup_H | 0.02 |
| tbl_lnkServicedependencyToService_DS | 0.02 |
| tbl_lnkServicedependencyToService_S | 0.02 |
| tbl_lnkServicedependencyToServicegroup_DS | 0.02 |
| tbl_lnkServicedependencyToServicegroup_S | 0.02 |
| tbl_lnkServiceescalationToContact | 0.02 |
| tbl_lnkServiceescalationToContactgroup | 0.02 |
| tbl_lnkServiceescalationToHost | 0.02 |
| tbl_lnkServiceescalationToHostgroup | 0.02 |
| tbl_lnkServiceescalationToService | 0.02 |
| tbl_lnkServiceescalationToServicegroup | 0.02 |
| tbl_lnkServicegroupToService | 0.02 |
| tbl_lnkServicegroupToServicegroup | 0.02 |
| tbl_lnkServicetemplateToContact | 0.02 |
| tbl_lnkServicetemplateToContactgroup | 0.02 |
| tbl_lnkServicetemplateToHost | 0.02 |
| tbl_lnkServicetemplateToHostgroup | 0.02 |
| tbl_lnkServicetemplateToServicegroup | 0.02 |
| tbl_lnkServicetemplateToServicetemplate | 0.02 |
| tbl_lnkServicetemplateToVariabledefinition | 0.02 |
| tbl_lnkTimeperiodToTimeperiod | 0.02 |
| tbl_logbook | 0.02 |
| tbl_mainmenu | 0.02 |
| tbl_permission | 0.02 |
| tbl_permission_inactive | 0.02 |
| tbl_service | 0.31 |
| tbl_servicedependency | 0.03 |
| tbl_serviceescalation | 0.03 |
| tbl_serviceextinfo | 0.03 |
| tbl_servicegroup | 0.03 |
| tbl_servicetemplate | 0.03 |
| tbl_session | 0.02 |
| tbl_session_locks | 0.02 |
| tbl_settings | 0.03 |
| tbl_submenu | 0.02 |
| tbl_timedefinition | 0.02 |
| tbl_timeperiod | 0.03 |
| tbl_user | 0.03 |
| tbl_variabledefinition | 0.11 |
| xi_auditlog | 1.95 |
| xi_auth_tokens | 0.13 |
| xi_cmp_ccm_backups | 0.02 |
| xi_cmp_favorites | 0.03 |
| xi_cmp_nagiosbpi_backups | 0.06 |
| xi_cmp_trapdata | 0.03 |
| xi_cmp_trapdata_log | 0.03 |
| xi_commands | 0.02 |
| xi_deploy_agents | 0.02 |
| xi_deploy_jobs | 0.02 |
| xi_eventqueue | 0.03 |
| xi_events | 0.05 |
| xi_meta | 1.52 |
| xi_mibs | 0.05 |
| xi_options | 0.03 |
| xi_sessions | 0.03 |
| xi_sysstat | 0.03 |
| xi_usermeta | 0.05 |
| xi_users | 0.03 |
+--------------------------------------------+------------+

[ssax]

I see the PM but there is no profile attached, please re-attach.

If you don't see the message that means the table has been fixed.

That other issue is an escaping issue in the CCM, development is aware of it but there isn't a fix right now.

This here is showing you had almost 8% IO wait (which generally means slow disks/storage or slow network between VM and disk or VM host and storage, you would need to have you VM admins/storage/network admins investigate to see why that's occurring and if there's anything they can do for it like moving to faster storage/etc).

11:50:02 AM all 4.85 0.00 2.25 7.82 0.00 85.08

IO wait is the number 2 reason for crashed tables (other than non-graceful shutdowns)/

Given you are seeing any IO wait, try changing ALL THREE Optimize Intervals in Admin > Performance Settings > Databases tab and click the Update Settings button. See if that helps alleviate it. What can happen is that if one optimize isn't finished before the next one starts you can get crashing of tables.

I have some other instructions (convert your MyISAM tables to InnoDB so they don't crash again) but I need your profile to tailor them for your system.

[pratikmehta003]

have sent u twice again.. let me know if u got it..

And m seeing that error again under notifications tab...

Regarding the optmization settings change, i see all three are set to value of '60'. So what should it be set to?

[ssax]

Regarding the optmization settings change, i see all three are set to value of '60'. So what should it be set to?

Set all three of them to 300.

Profile received.

First, take an XI backup just in case you need to restore (I have yet to have someone have issues doing this).

https://assets.nagios.com/downloads/nag ... ios-XI.pdf

Then run these commands:
- Note: If you changed the root mysql password you will need to change it in each of the 6 commands below

Code: Select all

mysql -h 127.0.0.1 -uroot -pnagiosxi nagios -e "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as '' FROM information_schema.tables AS tb WHERE table_schema = 'nagios' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE' ORDER BY table_name DESC;" > ~/nagios_convert_myisam_to_innodb.sql
mysql -h 127.0.0.1 -uroot -pnagiosxi nagiosql -e "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as '' FROM information_schema.tables AS tb WHERE table_schema = 'nagiosql' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE' ORDER BY table_name DESC;" > ~/nagiosql_convert_myisam_to_innodb.sql
mysql -h 127.0.0.1 -uroot -pnagiosxi -e "SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as '' FROM information_schema.tables AS tb WHERE table_schema = 'nagiosxi' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE' ORDER BY table_name DESC;" > ~/nagiosxi_convert_myisam_to_innodb.sql
mysql -h 127.0.0.1 -uroot -pnagiosxi nagios < ~/nagios_convert_myisam_to_innodb.sql
mysql -h 127.0.0.1 -uroot -pnagiosxi nagiosql < ~/nagiosql_convert_myisam_to_innodb.sql
mysql -h 127.0.0.1 -uroot -pnagiosxi nagiosxi < ~/nagiosxi_convert_myisam_to_innodb.sql

That will convert all your tables to InnoDB which will no longer crash.

[pratikmehta003]

I ran the commands.. attached output for the same.. its reporting the same crash command for notifications for 1 command.. rest all executed..

Output of 6 commands.txt