Duplicate Entry in MySQL after reload of icinga2

[Tux12Fun]

Everytime I call "service icinga2 reload" the Log is flooded of critical/IdoMysqlConnection: Error "Duplicate entry ... Messages.

Expected Behavior

No error should occur

Current Behavior

[2017-10-24 14:32:49 +0200] critical/IdoMysqlConnection: Error "Duplicate entry '1-5305-2017-08-24 10:43:36-3516' for key 'instance_id'" when executing query "DELETE FROM icinga_comments WHERE comment_time = FROM_UNIXTIME(1508848363) AND instance_id = 1 AND name = 'SK50115.*********.local!CASH_Hardware!sm01-1467892419-0' AND object_id = 1235;INSERT INTO icinga_comments (author_name, comment_data, comment_source, comment_time, comment_type, endpoint_object_id, entry_time, entry_time_usec, entry_type, expires, instance_id, internal_comment_id, is_persistent, name, object_id, session_token) VALUES ('Luchs Stefan', 'keine Kamera vorbaut', '1', FROM_UNIXTIME(1508848363), '1', 259, FROM_UNIXTIME(1508848363), '276061', '1', '0', 1, '2', '0', 'SK50115.*********.local!CASH_Hardware!sm01-1467892419-0', 1235, 1508848362);DELETE FROM icinga_scheduleddowntime WHERE entry_time = FROM_UNIXTIME(1503571416) AND instance_id = 1 AND name = 'da1.*********.local!MySQL_Health_Slow_Queries!sm01-1503571416-26103' AND object_id = 5305 AND scheduled_end_time = FROM_UNIXTIME(4091258608) AND scheduled_start_time = FROM_UNIXTIME(1503571408);INSERT INTO icinga_scheduleddowntime (actual_start_time, actual_start_time_usec, author_name, comment_data, downtime_type, duration, endpoint_object_id, entry_time, instance_id, internal_downtime_id, is_fixed, is_in_effect, name, object_id, scheduled_end_time, scheduled_start_time, session_token, trigger_time, was_started) VALUES (FROM_UNIXTIME(1503571408), '0', 'perras thomas', 'Freller only', '1', '0', 259, FROM_UNIXTIME(1503571416), 1, '3516', '1', '1', 'da1.*********.local!MySQL_Health_Slow_Queries!sm01-1503571416-26103', 5305, FROM_UNIXTIME(4091258608), FROM_UNIXTIME(1503571408), 1508848362, FROM_UNIXTIME(1503571416), '1');DELETE FROM icinga_scheduleddowntime WHERE entry_time = FROM_UNIXTIME(1504687852) AND instance_id = 1 AND name = 'dat1.*********.local!MySQL_Slow_Queries!sm01-1504687852-3589' AND object_id = 5396 AND scheduled_end_time = FROM_UNIXTIME(4092375046) AND scheduled_start_time = FROM_UNIXTIME(1504687846);INSERT INTO icinga_scheduleddowntime (actual_start_time, actual_start_time_usec, author_name, comment_data, downtime_type, duration, endpoint_object_id, entry_time, instance_id, internal_downtime_id, is_fixed, is_in_effect, name, object_id, scheduled_end_time, scheduled_start_time, session_token, trigger_time, was_started) VALUES (FROM_UNIXTIME(1504687846), '0', 'perras thomas', 'für Freller only', '1', '0', 259, FROM_UNIXTIME(1504687852), 1, '3515', '1', '1', 'dat1.*********.local!MySQL_Slow_Queries!sm01-1504687852-3589', 5396, FROM_UNIXTIME(4092375046), FROM_UNIXTIME(1504687846), 1508848362, FROM_UNIXTIME(1504687852), '1');DELETE FROM icinga_scheduleddowntime WHERE entry_time = FROM_UNIXTIME(1504687805) AND instance_id = 1 AND name = 'dat1.*********.local!MySQL_WriteBufferEfficiency!sm01-1504687805-3585' AND object_id = 5401 AND scheduled_end_time = FROM_UNIXTIME(4092374995) AND scheduled_start_time = FROM_UNIXTIME(1504687795);INSERT INTO icinga_scheduleddowntime (actual_start_time, actual_start_time_usec, author_name, comment_data, downtime_type, duration, endpoint_object_id, entry_time, instance_id, internal_downtime_id, is_fixed, is_in_effect, name, object_id, scheduled_end_time, scheduled_start_time, session_token, trigger_time, was_started) VALUES (FROM_UNIXTIME(1504687795), '0', 'perras thomas', 'nur für Freller interessant', '1', '0', 259, FROM_UNIXTIME(1504687805), 1, '3519', '1', '1', 'dat1.*********.local!MySQL_WriteBufferEfficiency!sm01-1504687805-3585', 5401, FROM_UNIXTIME(4092374995), FROM_UNIXTIME(1504687795), 1508848362, FROM_UNIXTIME(1504687805), '1');DELETE FROM icinga_scheduleddowntime WHERE entry_time = FROM_UNIXTIME(1503567622) AND instance_id = 1 AND name = 'dev1.*********.local!MySQL_Health_Querycache-Hitrate!sm01-1503567622-26100' AND object_id = 5468 AND scheduled_end_time = FROM_UNIXTIME(4091254814) AND scheduled_start_time = FROM_UNIXTIME(1503567614);INSERT INTO icinga_scheduleddowntime (actual_start_time, actual_start_time_usec, author_name, comment_data, downtime_type, duration, endpoint_object_id, entry_time, instance_id, internal_downtime_id, is_fixed, is_in_effect, name, object_id, scheduled_end_time, scheduled_start_time, session_token, trigger_time, was_started) VALUES (FROM_UNIXTIME(1503567614), '0', 'perras thomas', 'Freller only', '1', '0', 259, FROM_UNIXTIME(1503567622), 1, '3517', '1', '1', 'dev1.*********.local!MySQL_Health_Querycache-Hitrate!sm01-1503567622-26100', 5468, FROM_UNIXTIME(4091254814), FROM_UNIXTIME(1503567614), 1508848362, FROM_UNIXTIME(1503567622), '1');DELETE FROM icinga_scheduleddowntime WHERE entry_time = FROM_UNIXTIME(1503567646) AND instance_id = 1 AND name = 'dev1.*********.local!MySQL_Health_Tmp_Disk_Tables!sm01-1503567646-26102' AND object_id = 5476 AND scheduled_end_time = FROM_UNIXTIME(4091254842) AND scheduled_start_time = FROM_UNIXTIME(1503567642);INSERT INTO icinga_scheduleddowntime (actual_start_time, actual_start_time_usec, author_name, comment_data, downtime_type, duration, endpoint_object_id, entry_time, instance_id, internal_downtime_id, is_fixed, is_in_effect, name, object_id, scheduled_end_time, scheduled_start_time, session_token, trigger_time, was_started) VALUES (FROM_UNIXTIME(1503567642), '0', 'perras thomas', 'Freller only', '1', '0', 259, FROM_UNIXTIME(1503567646), 1, '3520', '1', '1', 'dev1.*********.local!MySQL_Health_Tmp_Disk_Tables!sm01-1503567646-26102', 5476, FROM_UNIXTIME(4091254842), FROM_UNIXTIME(1503567642), 1508848362, FROM_UNIXTIME(1503567646), '1');DELETE FROM icinga_scheduleddowntime WHERE entry_time = FROM_UNIXTIME(1504083606) AND instance_id = 1 AND name = 'e81.*********.local!SAP_BGRFC_ZDLVSTRLOC1!sm01-1504083606-2' AND object_id = 5577 AND scheduled_end_time = FROM_UNIXTIME(4091770668) AND scheduled_start_time = FROM_UNIXTIME(1504083468);INSERT INTO icinga_scheduleddowntime (actual_start_time, actual_start_time_usec, author_name, comment_data, downtime_type, duration, endpoint_object_id, entry_time, instance_id, internal_downtime_id, is_fixed, is_in_effect, name, object_id, scheduled_end_time, scheduled_start_time, session_token, trigger_time, was_started) VALUES (FROM_UNIXTIME(1504083468), '0', 'perras thomas', 'Check ist fehlerhaft', '1', '0', 259, FROM_UNIXTIME(1504083606), 1, '3521', '1', '1', 'e81.*********.local!SAP_BGRFC_ZDLVSTRLOC1!sm01-1504083606-2', 5577, FROM_UNIXTIME(4091770668), FROM_UNIXTIME(1504083468), 1508848362, FROM_UNIXTIME(1504083606), '1');DELETE FROM icinga_scheduleddowntime WHERE entry_time = FROM_UNIXTIME(1505463780) AND instance_id = 1 AND name = 'e81.*********.local!SAP_BGRFC__ZTM!sm01-1505463780-7070' AND object_id = 5583 AND scheduled_end_time = FROM_UNIXTIME(4093150970) AND scheduled_start_time = FROM_UNIXTIME(1505463770);INSERT INTO icinga_scheduleddowntime (actual_start_time, actual_start_time_usec, author_name, comment_data, downtime_type, duration, endpoint_object_id, entry_time, instance_id, internal_downtime_id, is_fixed, is_in_effect, name, object_id, scheduled_end_time, scheduled_start_time, session_token, trigger_time, was_started) VALUES (FROM_UNIXTIME(1505463770), '0', 'perras thomas', 'Check fehlerhaft?', '1', '0', 259, FROM_UNIXTIME(1505463780), 1, '3522', '1', '1', 'e81.*********.local!SAP_BGRFC__ZTM!sm01-1505463780-7070', 5583, FROM_UNIXTIME(4093150970), FROM_UNIXTIME(1505463770), 1508848362, FROM_UNIXTIME(1505463780), '1');DELETE FROM icinga_customvariablestatus WHERE object_id = 8283 AND varname = 'notes_base_url';INSERT INTO icinga_customvariablestatus (instance_id, is_json, object_id, status_update_time, varname, varvalue) VALUES (1, '0', 8283, FROM_UNIXTIME(1508848367), 'notes_base_url', 'https://dev1:4441/doku.php?id=hit:it_bereiche:sap:basis:systemdokumentationen:sm01:meldungsdoku:e31.*********24.com:start#');DELETE FROM icinga_customvariablestatus WHERE object_id = 8283 AND varname = 'notification';INSERT INTO icinga_customvariablestatus (instance_id, is_json, object_id, status_update_time, varname, varvalue) VALUES (1, '1', 8283, FROM_UNIXTIME(1508848367), 'notification', '{}');DELETE FROM icinga_customvariablestatus WHERE object_id = 8283 AND varname = 'nrpe_command';INSERT INTO icinga_customvariablestatus (instance_id, is_json, object_id, status_update_time, varname, varvalue) VALUES (1, '0', 8283, FROM_UNIXTIME(1508848367), 'nrpe_command', 'check_load');DELETE FROM icinga_services WHERE service_object_id = 8283;INSERT INTO icinga_services (action_url, active_checks_enabled, check_command_args, check_command_object_id, check_interval, config_hash, 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', '', 40, '2.500000', '1a28ab571a47a39e19806c1aae2eb80ad2a54b8b3c9430695b45d12d9c5e6fe6', '1', 'CPU Load', '1', '0', '1', '150', '30', 654, '', '', 1, '0', '30', '5', '', 'https://dev1:4441/doku.php?id=hit:it_bereiche:sap:basis:systemdokumentationen:sm01:meldungsdoku:e31.*********24.com:start#cpu_load', '1', '1', '0', '0', '0', '0', '0', '0', '1', '1', '0.500000', 8283, '0', '0', '0', '0');DELETE FROM icinga_servicestatus WHERE service_object_id = 8283;INSERT INTO icinga_servicestatus (acknowledgement_type, active_checks_enabled, check_command, check_type, current_check_attempt, current_notification_number, current_state, endpoint_object_id, event_handler, event_handler_enabled, flap_detection_enabled, has_been_checked, instance_id, is_flapping, is_reachable, last_hard_state, last_hard_state_change, last_state_change, max_check_attempts, next_check, normal_check_interval, notifications_enabled, original_attributes, passive_checks_enabled, percent_state_change, 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', 'check_nrpe', '0', '1', '0', '3', 259, '', '1', '0', '0', 1, '0', '1', '3', FROM_UNIXTIME(1508848228), FROM_UNIXTIME(1508848228), '5', FROM_UNIXTIME(1508848509), '2.500000', '1', 'null', '1', '0', '0', '1', '0.500000', '0', 8283, '1', '0', FROM_UNIXTIME(1508848367));DELETE FROM icinga_customvariablestatus WHERE object_id = 8285 AND varname = 'notes_base_url';INSERT INTO icinga_customvariablestatus (instance_id, is_json, object_id, status_update_time, varname, varvalue) VALUES (1, '0', 8285, FROM_UNIXTIME(1508848367), 'notes_base_url', 'https://dev1:4441/doku.php?id=hit:it_bereiche:sap:basis:systemdokumentationen:sm01:meldungsdoku:e31.*********24.com:start#');DELETE FROM icinga_customvariablestatus WHERE object_id = 8285 AND varname = 'notification';INSERT INTO icinga_customvariablestatus (instance_id, is_json, object_id, status_update_time, varname, varvalue) VALUES (1, '1', 8285, FROM_UNIXTIME(1508848367), 'notification', '{}');DELETE FROM icinga_customvariablestatus WHERE object_id = 8285 AND varname = 'nrpe_command';INSERT INTO icinga_customvariablest
[2017-10-24 14:32:49 +0200] critical/IdoMysqlConnection: Exception during database operation: Verify that your database is operational!
[2017-10-24 14:32:49 +0200] critical/IdoMysqlConnection: Error "MySQL server has gone away" when executing query "DELETE FROM icinga_comments WHERE instance_id = 1 AND session_token <> 1508848362"
[2017-10-24 14:32:49 +0200] critical/IdoMysqlConnection: Exception during database operation: Verify that your database is operational!
[2017-10-24 14:32:53 +0200] information/WorkQueue: #5 (ApiListener, RelayQueue) items: 0, rate: 185.467/s (11128/min 11128/5min 11128/15min);
[2017-10-24 14:32:53 +0200] information/WorkQueue: #6 (ApiListener, SyncQueue) items: 0, rate:  0/s (0/min 0/5min 0/15min);
[2017-10-24 14:32:53 +0200] information/WorkQueue: #8 (IdoMysqlConnection, ido-mysql) items: 0, rate: 6050.78/s (363047/min 363047/5min 363047/15min);
[2017-10-24 14:32:53 +0200] information/WorkQueue: #7 (InfluxdbWriter, influxdb) items: 0, rate:  0/s (0/min 0/5min 0/15min);
[2017-10-24 14:32:56 +0200] information/IdoMysqlConnection: MySQL IDO instance id: 1 (schema version: '1.14.2')

Possible Solution

Steps to Reproduce (for bugs)

1. Cleanup Database and Statefiles (import mysql Schema into a fresh DB)
2. start icinga2
3. --> Everything works fine
4. service icinga2 reload
5. --> Log is flooded of Duplicate Messages

Context

Cant reload icinga2 currently

Your Environment

• Version used (icinga2 --version):
  icinga2 - The Icinga 2 network monitoring daemon (version: r2.7.1-1)

Copyright (c) 2012-2017 Icinga Development Team (https://www.icinga.com/)
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: /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: /run/icinga2/icinga2.pid

System information:
Platform: Ubuntu
Platform version: 14.04.2 LTS, Trusty Tahr
Kernel: Linux
Kernel version: 3.13.0-55-generic
Architecture: x86_64

Build information:
Compiler: GNU 4.8.2
Build host: 02a0e862c14c

• Operating System and version:
  Ubuntu 16.04 LTS
• Enabled features (icinga2 feature list):
  Disabled features: compatlog debuglog gelf graphite icingastatus livestatus opentsdb statusdata syslog
  Enabled features: api checker command ido-mysql influxdb mainlog notification perfdata
• Icinga Web 2 version and modules (System - About):
• Config validation (icinga2 daemon -C):

information/cli: Icinga application loader (version: r2.7.1-1)
information/cli: Loading configuration file(s).
information/ConfigItem: Committing config item(s).
information/ApiListener: My API identity: sm01.*********.local
warning/InfluxdbWriter: 'socket_timeout' option has no effect and will be removed in Icinga 2 v2.8
warning/ApplyRule: Apply rule 'SWDEP__2__HIR_Shelf_4-06' (in /etc/icinga2/conf.d/*********/monitoring_objects/network/tk/hir/HIR_Shelf_4-06.conf: 26:1-26:51) for type 'Dependency' does not match anywhere!
warning/ApplyRule: Apply rule 'SWDEP__2__HIR_Shelf_4-20' (in /etc/icinga2/conf.d/*********/monitoring_objects/network/tk/hir/HIR_Shelf_4-20.conf: 26:1-26:51) for type 'Dependency' does not match anywhere!
...
winformation/ConfigItem: Instantiated 1 ApiUser.
information/ConfigItem: Instantiated 1 ApiListener.
information/ConfigItem: Instantiated 3 Zones.
information/ConfigItem: Instantiated 1 FileLogger.
information/ConfigItem: Instantiated 1 Endpoint.
information/ConfigItem: Instantiated 4 NotificationCommands.
information/ConfigItem: Instantiated 5322 Notifications.
information/ConfigItem: Instantiated 257 CheckCommands.
information/ConfigItem: Instantiated 3602 Downtimes.
information/ConfigItem: Instantiated 593 Hosts.
information/ConfigItem: Instantiated 1 IcingaApplication.
information/ConfigItem: Instantiated 109 HostGroups.
information/ConfigItem: Instantiated 1 EventCommand.
information/ConfigItem: Instantiated 5 Comments.
information/ConfigItem: Instantiated 6577 Dependencies.
information/ConfigItem: Instantiated 10 TimePeriods.
information/ConfigItem: Instantiated 52 Users.
information/ConfigItem: Instantiated 7280 Services.
information/ConfigItem: Instantiated 4 ServiceGroups.
information/ConfigItem: Instantiated 3533 ScheduledDowntimes.
information/ConfigItem: Instantiated 1 ExternalCommandListener.
information/ConfigItem: Instantiated 1 CheckerComponent.
information/ConfigItem: Instantiated 1 InfluxdbWriter.
information/ConfigItem: Instantiated 1 PerfdataWriter.
information/ConfigItem: Instantiated 1 NotificationComponent.
information/ConfigItem: Instantiated 1 IdoMysqlConnection.
information/ScriptGlobal: Dumping variables to file '/var/cache/icinga2/icinga2.vars'
information/cli: Finished validating the configuration file(s).

• If you run multiple Icinga 2 instances, the zones.conf file (or icinga2 object list --type Endpoint and icinga2 object list --type Zone) from all affected nodes.

[Crunsher]

Ignore the previous comment, what we want is this:
SELECT * FROM icinga_scheduleddowntime WHERE object_id = 5305;

[Crunsher]

Closed as duplicate of #5623

You can find a workaround here until we release 2.7.2 (soon) which will fix this issue.