Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
icinga-migration opened this issue Jun 16, 2016 · 8 comments

Comments

Projects
None yet
1 participant
@icinga-migration
Copy link
Member

commented Jun 16, 2016

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

This comment has been minimized.

Copy link
Member Author

commented Jun 17, 2016

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

This comment has been minimized.

Copy link
Member Author

commented Jun 17, 2016

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

This comment has been minimized.

Copy link
Member Author

commented Jun 22, 2016

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

This comment has been minimized.

Copy link
Member Author

commented Jun 22, 2016

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

This comment has been minimized.

Copy link
Member Author

commented Jun 22, 2016

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

This comment has been minimized.

Copy link
Member Author

commented Jun 23, 2016

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

This comment has been minimized.

Copy link
Member Author

commented Jun 24, 2016

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

This comment has been minimized.

Copy link
Member Author

commented Jun 28, 2016

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'

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.