[dev.icinga.com #9634] DB IDO: Do not update endpointstatus table on config updates

[icinga-migration]

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

Created by lebvanih on 2015-07-15 12:41:11 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2015-07-16 11:25:18 +00:00)
Target Version: 2.3.8
Last Update: 2015-07-20 08:47:32 +00:00 (in Redmine)

Icinga Version: 2.3.7
Backport?: Already backported
Include in Changelog: 1

---

Hello,

After each restart of the icinga2 instance (clean install of today) IDO encounter an issue while trying to insert a row in the endpointstatus table (unique constraint). Emptying these 2 tables (endpointstatus and endpoints) solve the issue until next restart.

Portion of the icinga.log (debug activated):

[2015-07-15 14:11:33 +0200] debug/IdoPgsqlConnection: Query: BEGIN
[2015-07-15 14:11:33 +0200] debug/IdoPgsqlConnection: Query: UPDATE icinga_endpoints SET config_type = E'1',  endpoint_object_id = 1,  identity = E'icinga-master',  instance_id = 1,  node = E'icinga-master' WHERE endpoint_object_id = 1
[2015-07-15 14:11:33 +0200] debug/IdoPgsqlConnection: Query: INSERT INTO icinga_endpointstatus (endpoint_object_id, identity, instance_id, is_connected, node, status_update_time) VALUES (1, E'icinga-master', 1, E'1', E'icinga-master', TO_TIMESTAMP(1436962293))
[2015-07-15 14:11:33 +0200] critical/IdoPgsqlConnection: Error "ERROR:  duplicate key value violates unique constraint "uq_endpointstatus"
DETAIL:  Key (endpoint_object_id)=(1) already exists.
" when executing query "INSERT INTO icinga_endpointstatus (endpoint_object_id, identity, instance_id, is_connected, node, status_update_time) VALUES (1, E'icinga-master', 1, E'1', E'icinga-master', TO_TIMESTAMP(1436962293))"
Context:
    (0) Reconnecting to PostgreSQL IDO database 'ido-pgsql'

This issue seem related to this one : #9586 (even if the table name in our case is endpointstatus and not endpoints).

OS : Debian 8.1
Repo : Icinga
DB Version: Postgresql 9.4
Packages version:

ii  icinga2                              2.3.7~jessie                 amd64        host and network monitoring system
ii  icinga2-bin                          2.3.7~jessie                 amd64        host and network monitoring system - daemon
ii  icinga2-common                       2.3.7~jessie                 all          host and network monitoring system - common files
ii  icinga2-doc                          2.3.7~jessie                 all          host and network monitoring system - documentation
ii  icinga2-ido-pgsql                    2.3.7~jessie                 amd64        host and network monitoring system - PostgreSQL support
ii  icingaweb2                           2.0.0~rc1~jessie             all          simple and responsive web interface for Icinga
ii  icingaweb2-module-doc                2.0.0~rc1~jessie             all          simple and responsive web interface for Icinga - documentation module
ii  icingaweb2-module-monitoring         2.0.0~rc1~jessie             all          simple and responsive web interface for Icinga - monitoring module
ii  icingaweb2-module-setup              2.0.0~rc1~jessie             all          simple and responsive web interface for Icinga - setup module
ii  php-icinga                           2.0.0~rc1~jessie             all          PHP library to communicate with and use Icinga

Thanks,

Changesets

2015-07-16 11:25:11 +00:00 by (unknown) 69e6fc4

DB IDO: Do not update endpointstatus table on config updates

fixes #9634

2015-07-20 08:18:55 +00:00 by (unknown) 6d6826c

DB IDO: Do not update endpointstatus table on config updates

fixes #9634

---

Relations:

• relates #9634

[icinga-migration]

Updated by mj84 on 2015-07-16 08:05:50 +00:00

I just noticed this as well and found out the following workaround:

1. tail -f /var/log/icinga2/icinga2.log
2. delete the offending line from psql table icinga_endpointstatus
3. repeat until no more errors appear in icinga2.log

(dropping all lines in the table at once didn't work for me as long as icinga2 is running)

This also seems to work:

1. service icinga2 stop
2. delete * from icinga_endpointstatus;
3. service icinga2 start

[icinga-migration]

Updated by gbeutner on 2015-07-16 08:47:03 +00:00

• Status changed from New to Assigned
• Assigned to set to mfriedrich

[icinga-migration]

Updated by mfriedrich on 2015-07-16 11:18:56 +00:00

• Subject changed from DB IDO pgSQL : Constraint violation "uq_endpointstatus" on service reload to DB IDO: Do not update endpointstatus table on config updates
• Target Version set to 2.4.0
• Estimated Hours set to 2

[icinga-migration]

Updated by mfriedrich on 2015-07-16 11:22:45 +00:00

Tests

Problem is only reproducible with PostgreSQL.

Fixed status updates:

systemctl stop icinga2

su - postgres
psql icinga

icinga=# truncate table icinga_endpointstatus;
TRUNCATE TABLE
icinga=# select * from icinga_endpointstatus;
 endpointstatus_id | instance_id | endpoint_object_id | status_update_time | identity | node | is_connected | zone_object_id 
-------------------+-------------+--------------------+--------------------+----------+------+--------------+----------------
(0 Zeilen)

sytemctl start icinga2

icinga=# select * from icinga_endpointstatus;
 endpointstatus_id | instance_id | endpoint_object_id |   status_update_time   |       identity       |         node         | is_connected | zone_object_id 
-------------------+-------------+--------------------+------------------------+----------------------+----------------------+--------------+----------------
               259 |           1 |                  1 | 2015-07-16 13:20:59+02 | nbmif.int.netways.de | nbmif.int.netways.de |            1 |          29335
               260 |           1 |              29336 | 2015-07-16 13:20:59+02 | test                 | nbmif.int.netways.de |            0 |          29337
(2 Zeilen)

systemctl reload icinga2

icinga=# select * from icinga_endpointstatus;
 endpointstatus_id | instance_id | endpoint_object_id |   status_update_time   |       identity       |         node         | is_connected | zone_object_id 
-------------------+-------------+--------------------+------------------------+----------------------+----------------------+--------------+----------------
               259 |           1 |                  1 | 2015-07-16 13:21:49+02 | nbmif.int.netways.de | nbmif.int.netways.de |            1 |          29335
               260 |           1 |              29336 | 2015-07-16 13:21:49+02 | test                 | nbmif.int.netways.de |            0 |          29337
(2 Zeilen)

[icinga-migration]

Updated by mfriedrich on 2015-07-16 11:24:41 +00:00

Similar for MySQL.

MariaDB [icinga]> truncate table icinga_endpointstatus;
Query OK, 0 rows affected (0.03 sec)

MariaDB [icinga]> select * from icinga_endpointstatus;
Empty set (0.00 sec)

MariaDB [icinga]> select * from icinga_endpointstatus;
+-------------------+-------------+--------------------+---------------------+----------------------+----------------------+--------------+----------------+
| endpointstatus_id | instance_id | endpoint_object_id | status_update_time  | identity             | node                 | is_connected | zone_object_id |
+-------------------+-------------+--------------------+---------------------+----------------------+----------------------+--------------+----------------+
|                 1 |           1 |                  1 | 2015-07-16 13:23:52 | nbmif.int.netways.de | nbmif.int.netways.de |            1 |          13730 |
|                 2 |           1 |              13740 | 2015-07-16 13:23:52 | test                 | nbmif.int.netways.de |            0 |          13741 |
+-------------------+-------------+--------------------+---------------------+----------------------+----------------------+--------------+----------------+
2 rows in set (0.00 sec)

MariaDB [icinga]> select * from icinga_endpointstatus;
+-------------------+-------------+--------------------+---------------------+----------------------+----------------------+--------------+----------------+
| endpointstatus_id | instance_id | endpoint_object_id | status_update_time  | identity             | node                 | is_connected | zone_object_id |
+-------------------+-------------+--------------------+---------------------+----------------------+----------------------+--------------+----------------+
|                 1 |           1 |                  1 | 2015-07-16 13:24:09 | nbmif.int.netways.de | nbmif.int.netways.de |            1 |          13730 |
|                 2 |           1 |              13740 | 2015-07-16 13:24:09 | test                 | nbmif.int.netways.de |            0 |          13741 |
+-------------------+-------------+--------------------+---------------------+----------------------+----------------------+--------------+----------------+
2 rows in set (0.00 sec)

[icinga-migration]

Updated by Anonymous on 2015-07-16 11:25:18 +00:00

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

Applied in changeset 69e6fc4.

[icinga-migration]

Updated by mfriedrich on 2015-07-17 20:40:16 +00:00

• Relates set to 9586

[icinga-migration]

Updated by mfriedrich on 2015-07-20 08:24:12 +00:00

• Priority changed from Normal to High
• Target Version changed from 2.4.0 to 2.3.8
• Backport? changed from TBD to Yes

[icinga-migration]

Updated by mfriedrich on 2015-07-20 08:47:32 +00:00

Verified ok with support/2.3.