[dev.icinga.com #11962] Overflow in current_notification_number column in DB IDO MySQL

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/11962

Created by vvv on 2016-06-16 22:28:06 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2016-06-22 10:15:03 +00:00)
Target Version: 2.5.0
Last Update: 2016-06-28 18:22:45 +00:00 (in Redmine)

Icinga Version: 2.4.10
Backport?: Not yet backported
Include in Changelog: 1

---

I just upgraded to 2.4.10 and I'm having connection issues to the database and this error

[2016-06-16 22:17:04 +0000] critical/IdoMysqlConnection: Exception during database operation: Verify that your database is operational!
[2016-06-16 22:17:04 +0000] information/IdoMysqlConnection: MySQL IDO instance id: 1 (schema version: '1.14.0')
[2016-06-16 22:17:08 +0000] critical/IdoMysqlConnection: Error "Out of range value for column 'current_notification_number' at row 1" when executing query "INSERT INTO icinga_customvariables (config_type, instance_id, is_json, object_id, session_token, varname, varvalue) VALUES ('1', 1, '0', 1355, 1466115424, 'nrpe_arguments', '/opt');INSERT INTO icinga_customvariables (config_type, instance_id, is_json, object_id, session_token, varname, varvalue)

I can query the database manually.

Changesets

2016-06-22 10:13:49 +00:00 by mfriedrich 067cb8d

DB IDO: Fix overflow in current_notification_number column (MySQL)

fixes #11962

[icinga-migration]

Updated by mfriedrich on 2016-06-17 06:28:30 +00:00

• Status changed from New to Feedback
• Assigned to set to vvv

Can you please extract the query which contains 'current_notification_number'?
Further please add the output of icinga2 --version

[icinga-migration]

Updated by vvv on 2016-06-17 19:05:23 +00:00

icinga2 - The Icinga 2 network monitoring daemon (version: v2.4.10)

Copyright © 2012-2016 Icinga Development Team (https://www.icinga.org/)
License GPLv2+: GNU GPL version 2 or later <http://gnu.org/licenses/gpl2.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Application information:
Installation root: /usr
Sysconf directory: /etc
Run directory: /var/run
Local state directory: /var
Package data directory: /usr/share/icinga2
State path: /var/lib/icinga2/icinga2.state
Modified attributes path: /var/lib/icinga2/modified-attributes.conf
Objects path: /var/cache/icinga2/icinga2.debug
Vars path: /var/cache/icinga2/icinga2.vars
PID path: /var/run/icinga2/icinga2.pid

System information:
Platform: CentOS
Platform version: 6.7 (Final)
Kernel: Linux
Kernel version: 2.6.32-573.el6.x86_64
Architecture: x86_64

[2016-06-17 19:01:02 +0000] critical/IdoMysqlConnection: Error "Out of range value for column 'current_notification_number' at row 1" when executing query "INSERT INTO icinga_customvariables (config_type, instance_id, is_json, object_id, session_token, varname, varvalue) VALUES ('1', 1, '0', 2653, 1466190057, 'nrpe_arguments', '/opt');INSERT INTO icinga_customvariables (config_type, instance_id, is_json, object_id, session_token, varname, varvalue) VALUES ('1', 1, '0', 2653, 1466190057, 'nrpe_command', 'check_disk_arg');INSERT INTO icinga_customvariablestatus (instance_id, is_json, object_id, session_token, status_update_time, varname, varvalue) VALUES (1, '0', 2653, 1466190057, FROM_UNIXTIME(1466190062), 'nrpe_arguments', '/opt');INSERT INTO icinga_customvariablestatus (instance_id, is_json, object_id, session_token, status_update_time, varname, varvalue) VALUES (1, '0', 2653, 1466190057, FROM_UNIXTIME(1466190062), 'nrpe_command', 'check_disk_arg');INSERT INTO icinga_services (action_url, active_checks_enabled, check_command_args, check_command_object_id, check_interval, config_type, display_name, event_handler_enabled, flap_detection_enabled, freshness_checks_enabled, freshness_threshold, high_flap_threshold, host_object_id, icon_image, icon_image_alt, instance_id, is_volatile, low_flap_threshold, max_check_attempts, notes, notes_url, notification_interval, notifications_enabled, notify_on_critical, notify_on_downtime, notify_on_flapping, notify_on_recovery, notify_on_unknown, notify_on_warning, passive_checks_enabled, process_performance_data, retry_interval, service_object_id, stalk_on_critical, stalk_on_ok, stalk_on_unknown, stalk_on_warning) VALUES ('', '1', '', 2491, '5', '1', 'check_disk_opt', '1', '0', '1', '300', '30', 2538, '', '', 1, '0', '30', '3', '', '', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', 2653, '0', '0', '0', '0');INSERT INTO icinga_servicestatus (acknowledgement_type, active_checks_enabled, check_command, check_source, check_type, current_check_attempt, current_notification_number, current_state, endpoint_object_id, event_handler, event_handler_enabled, execution_time, flap_detection_enabled, has_been_checked, instance_id, is_flapping, is_reachable, last_check, last_hard_state, last_hard_state_change, last_notification, last_state_change, last_time_critical, last_time_ok, last_time_warning, latency, long_output, max_check_attempts, next_check, next_notification, normal_check_interval, notifications_enabled, original_attributes, output, passive_checks_enabled, percent_state_change, perfdata, problem_has_been_acknowledged, process_performance_data, retry_check_interval, scheduled_downtime_depth, service_object_id, should_be_scheduled, state_type, status_update_time) VALUES ('0', '1', 'nrpe', 'icingatest', '0', '1', '42290', '2', 2461, '', '1', '0.016252040863037109', '0', '1', 1, '0', '1', FROM_UNIXTIME(1466115385), '2', FROM_UNIXTIME(1465812067), FROM_UNIXTIME(1466190057), FROM_UNIXTIME(1465812067), FROM_UNIXTIME(1466115385), FROM_UNIXTIME(1464738637), FROM_UNIXTIME(1465811859), '0.00064992904663085938', '', '3', FROM_UNIXTIME(1466190188), FROM_UNIXTIME(1466190117), '5', '1', 'null', 'DISK CRITICAL - free space: /opt 411 MB (4% inode=96%):', '1', '0', '/opt=9296MB;9210;9722;0;10234', '0', '1', '1', '0', 2653, '1', '1', FROM_UNIXTIME(1466190062));UPDATE icinga_customvariables SET config_type = '1', instance_id = 1, is_json = '0', object_id = 2654, session_token = 1466190057, varname = 'nrpe_command', varvalue = 'check_procs_chef' WHERE object_id = 2654 AND varname = 'nrpe_command';UPDATE icinga_customvariablestatus SET instance_id = 1, is_json = '0', object_id = 2654, session_token = 1466190057, status_update_time = FROM_UNIXTIME(1466190062), varname = 'nrpe_command', varvalue = 'check_procs_chef' WHERE object_id = 2654 AND varname = 'nrpe_command';UPDATE icinga_services SET action_url = '', active_checks_enabled = '1', check_command_args = '', check_command_object_id = 2491, check_interval = '5', config_type = '1', display_name = 'check_chef_proc', event_handler_enabled = '1', flap_detection_enabled = '0', freshness_checks_enabled = '1', freshness_threshold = '300', high_flap_threshold = '30', host_object_id = 2537, icon_image = '', icon_image_alt = '', instance_id = 1, is_volatile = '0', low_flap_threshold = '30', max_check_attempts = '3', notes = '', notes_url = '', notification_interval = '1', notifications_enabled = '1', notify_on_critical = '1', notify_on_downtime = '1', notify_on_flapping = '1', notify_on_recovery = '1', notify_on_unknown = '1', notify_on_warning = '1', passive_checks_enabled = '1', process_performance_data = '1', retry_interval = '1', service_object_id = 2654, stalk_on_critical = '0', stalk_on_ok = '0', stalk_on_unknown = '0', stalk_on_warning = '0' WHERE service_object_id = 2654;UPDATE icinga_servicestatus SET acknowledgement_type = '0', active_checks_enabled = '1', check_command = 'nrpe', check_source = 'icingatest', check_type = '0', current_check_attempt = '1', current_notification_number = '0', current_state = '0', endpoint_object_id = 2461, event_handler = '', event_handler_enabled = '1', execution_time = '0.021357059478759766', flap_detection_enabled = '0', has_been_checked = '1', instance_id = 1, is_flapping = '0', is_reachable = '1', last_check = FROM_UNIXTIME(1466115435), last_hard_state = '0', last_hard_state_change = FROM_UNIXTIME(1464984098), last_state_change = FROM_UNIXTIME(1464984038), last_time_critical = FROM_UNIXTIME(1464983796), last_time_ok = FROM_UNIXTIME(1466115435), latency = '0.00081801414489746094', long_output = '', max_check_attempts = '3', next_check = FROM_UNIXTIME(1466190327), next_notification = FROM_UNIXTIME(1466190117), normal_check_interval = '5', notifications_enabled = '1', original_attributes = 'null', output = 'PROCS OK: 1 process with args \'/var/run/chef/client.pid\'', passive_checks_enabled = '1', percent_state_change = '0', perfdata = '', problem_has_been_acknowledged = '0', process_performance_data = '1', retry_check_interval = '1', scheduled_downtime_depth = '0', service_object_id = 2654, should_be_scheduled = '1', state_type = '1', status_update_time = FROM_UNIXTIME(1466190062) WHERE service_object_id = 2654"

Not sure if that's all you need. Please let me know if you need anything more.

[icinga-migration]

Updated by gbeutner on 2016-06-22 07:28:03 +00:00

• Category set to DB IDO
• Status changed from Feedback to Assigned
• Assigned to changed from vvv to mfriedrich

[icinga-migration]

Updated by mfriedrich on 2016-06-22 10:13:30 +00:00

• Subject changed from IDO Errors to Overflow in current_notification_number column in DB IDO MySQL
• Target Version set to 2.5.0

Try these ALTER statements. The schema changes will be applied in 2.5.0 as well.

ALTER TABLE icinga_hoststatus MODIFY COLUMN current_notification_number int unsigned default 0;
ALTER TABLE icinga_servicestatus MODIFY COLUMN current_notification_number int unsigned default 0;

PostgreSQL is not affected, the column type already is INTEGER.

[icinga-migration]

Updated by mfriedrich on 2016-06-22 10:15:03 +00:00

• Status changed from Assigned to Resolved
• Done % changed from 0 to 100

Applied in changeset 067cb8d.

[icinga-migration]

Updated by vvv on 2016-06-23 23:30:01 +00:00

I'm getting these errors with the alter table command.

mysql> ALTER TABLE icinga_hoststatus MODIFY COLUMN current_notification_number int unsigned default 0;
ERROR 1067 (42000): Invalid default value for 'status_update_time'
mysql> ALTER TABLE icinga_servicestatus MODIFY COLUMN current_notification_number int unsigned default 0;
ERROR 1067 (42000): Invalid default value for 'status_update_time'

[icinga-migration]

Updated by mfriedrich on 2016-06-24 07:28:29 +00:00

Strange. That looks like another error not necessarily related to the ALTER statements.

Which MySQL version are you using?

mysql --version

And please show create table output for both icinga_hoststatus and icinga_servicestatus tables.

mysql icinga -e 'show create table icinga_hoststatus\G'
mysql icinga -e 'show create table icinga_servicestatus\G'

[icinga-migration]

Updated by vvv on 2016-06-28 18:22:45 +00:00

[root@dc5-icinga1-1-np ~]# mysql --version
mysql Ver 14.14 Distrib 5.7.12-5, for Linux (x86_64) using 6.0

[root@dc5-icinga1-1-np ~]# mysql -u icinga -p icinga -e 'show create table icinga_hoststatus\G'
Enter password:

* 1. row*
Table: icinga_hoststatus
Create Table: CREATE TABLE `icinga_hoststatus` (
`hoststatus_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`instance_id` bigint(20) unsigned DEFAULT '0',
`host_object_id` bigint(20) unsigned DEFAULT '0',
`status_update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`output` text,
`long_output` text,
`perfdata` text,
`check_source` text,
`current_state` smallint(6) DEFAULT '0',
`has_been_checked` smallint(6) DEFAULT '0',
`should_be_scheduled` smallint(6) DEFAULT '0',
`current_check_attempt` smallint(6) DEFAULT '0',
`max_check_attempts` smallint(6) DEFAULT '0',
`last_check` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`next_check` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`check_type` smallint(6) DEFAULT '0',
`last_state_change` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_hard_state_change` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_hard_state` smallint(6) DEFAULT '0',
`last_time_up` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_time_down` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_time_unreachable` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`state_type` smallint(6) DEFAULT '0',
`last_notification` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`next_notification` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`no_more_notifications` smallint(6) DEFAULT '0',
`notifications_enabled` smallint(6) DEFAULT '0',
`problem_has_been_acknowledged` smallint(6) DEFAULT '0',
`acknowledgement_type` smallint(6) DEFAULT '0',
`current_notification_number` smallint(6) DEFAULT '0',
`passive_checks_enabled` smallint(6) DEFAULT '0',
`active_checks_enabled` smallint(6) DEFAULT '0',
`event_handler_enabled` smallint(6) DEFAULT '0',
`flap_detection_enabled` smallint(6) DEFAULT '0',
`is_flapping` smallint(6) DEFAULT '0',
`percent_state_change` double DEFAULT '0',
`latency` double DEFAULT '0',
`execution_time` double DEFAULT '0',
`scheduled_downtime_depth` smallint(6) DEFAULT '0',
`failure_prediction_enabled` smallint(6) DEFAULT '0',
`process_performance_data` smallint(6) DEFAULT '0',
`obsess_over_host` smallint(6) DEFAULT '0',
`modified_host_attributes` int(11) DEFAULT '0',
`original_attributes` text,
`event_handler` text,
`check_command` text,
`normal_check_interval` double DEFAULT '0',
`retry_check_interval` double DEFAULT '0',
`check_timeperiod_object_id` bigint(20) unsigned DEFAULT '0',
`is_reachable` smallint(6) DEFAULT '0',
`endpoint_object_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`hoststatus_id`),
UNIQUE KEY `object_id` (`host_object_id`),
KEY `hoststatus_i_id_idx` (`instance_id`),
KEY `hoststatus_stat_upd_time_idx` (`status_update_time`),
KEY `hoststatus_current_state_idx` (`current_state`),
KEY `hoststatus_check_type_idx` (`check_type`),
KEY `hoststatus_state_type_idx` (`state_type`),
KEY `hoststatus_last_state_chg_idx` (`last_state_change`),
KEY `hoststatus_notif_enabled_idx` (`notifications_enabled`),
KEY `hoststatus_problem_ack_idx` (`problem_has_been_acknowledged`),
KEY `hoststatus_act_chks_en_idx` (`active_checks_enabled`),
KEY `hoststatus_pas_chks_en_idx` (`passive_checks_enabled`),
KEY `hoststatus_event_hdl_en_idx` (`event_handler_enabled`),
KEY `hoststatus_flap_det_en_idx` (`flap_detection_enabled`),
KEY `hoststatus_is_flapping_idx` (`is_flapping`),
KEY `hoststatus_p_state_chg_idx` (`percent_state_change`),
KEY `hoststatus_latency_idx` (`latency`),
KEY `hoststatus_ex_time_idx` (`execution_time`),
KEY `hoststatus_sch_downt_d_idx` (`scheduled_downtime_depth`)
) ENGINE=InnoDB AUTO_INCREMENT=1232635 DEFAULT CHARSET=latin1 COMMENT='Current host status information'
[root@dc5-icinga1-1-np ~]# mysql -u icinga -p icinga -e 'show create table icinga_servicestatus\G'
Enter password:

* 1. row*
Table: icinga_servicestatus
Create Table: CREATE TABLE `icinga_servicestatus` (
`servicestatus_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`instance_id` bigint(20) unsigned DEFAULT '0',
`service_object_id` bigint(20) unsigned DEFAULT '0',
`status_update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`output` text,
`long_output` text,
`perfdata` text,
`check_source` text,
`current_state` smallint(6) DEFAULT '0',
`has_been_checked` smallint(6) DEFAULT '0',
`should_be_scheduled` smallint(6) DEFAULT '0',
`current_check_attempt` smallint(6) DEFAULT '0',
`max_check_attempts` smallint(6) DEFAULT '0',
`last_check` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`next_check` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`check_type` smallint(6) DEFAULT '0',
`last_state_change` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_hard_state_change` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_hard_state` smallint(6) DEFAULT '0',
`last_time_ok` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_time_warning` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_time_unknown` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_time_critical` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`state_type` smallint(6) DEFAULT '0',
`last_notification` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`next_notification` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`no_more_notifications` smallint(6) DEFAULT '0',
`notifications_enabled` smallint(6) DEFAULT '0',
`problem_has_been_acknowledged` smallint(6) DEFAULT '0',
`acknowledgement_type` smallint(6) DEFAULT '0',
`current_notification_number` smallint(6) DEFAULT '0',
`passive_checks_enabled` smallint(6) DEFAULT '0',
`active_checks_enabled` smallint(6) DEFAULT '0',
`event_handler_enabled` smallint(6) DEFAULT '0',
`flap_detection_enabled` smallint(6) DEFAULT '0',
`is_flapping` smallint(6) DEFAULT '0',
`percent_state_change` double DEFAULT '0',
`latency` double DEFAULT '0',
`execution_time` double DEFAULT '0',
`scheduled_downtime_depth` smallint(6) DEFAULT '0',
`failure_prediction_enabled` smallint(6) DEFAULT '0',
`process_performance_data` smallint(6) DEFAULT '0',
`obsess_over_service` smallint(6) DEFAULT '0',
`modified_service_attributes` int(11) DEFAULT '0',
`original_attributes` text,
`event_handler` text,
`check_command` text,
`normal_check_interval` double DEFAULT '0',
`retry_check_interval` double DEFAULT '0',
`check_timeperiod_object_id` bigint(20) unsigned DEFAULT '0',
`is_reachable` smallint(6) DEFAULT '0',
`endpoint_object_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`servicestatus_id`),
UNIQUE KEY `object_id` (`service_object_id`),
KEY `servicestatus_i_id_idx` (`instance_id`),
KEY `srvcstatus_stat_upd_time_idx` (`status_update_time`),
KEY `srvcstatus_current_state_idx` (`current_state`),
KEY `srvcstatus_check_type_idx` (`check_type`),
KEY `srvcstatus_state_type_idx` (`state_type`),
KEY `srvcstatus_last_state_chg_idx` (`last_state_change`),
KEY `srvcstatus_notif_enabled_idx` (`notifications_enabled`),
KEY `srvcstatus_problem_ack_idx` (`problem_has_been_acknowledged`),
KEY `srvcstatus_act_chks_en_idx` (`active_checks_enabled`),
KEY `srvcstatus_pas_chks_en_idx` (`passive_checks_enabled`),
KEY `srvcstatus_event_hdl_en_idx` (`event_handler_enabled`),
KEY `srvcstatus_flap_det_en_idx` (`flap_detection_enabled`),
KEY `srvcstatus_is_flapping_idx` (`is_flapping`),
KEY `srvcstatus_p_state_chg_idx` (`percent_state_change`),
KEY `srvcstatus_latency_idx` (`latency`),
KEY `srvcstatus_ex_time_idx` (`execution_time`),
KEY `srvcstatus_sch_downt_d_idx` (`scheduled_downtime_depth`)
) ENGINE=InnoDB AUTO_INCREMENT=12006946 DEFAULT CHARSET=latin1 COMMENT='Current service status information'