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 #9586] DB IDO: endpoint* tables are cleared on reload causing constraint violations #3136

Closed
icinga-migration opened this issue Jul 8, 2015 · 8 comments
Labels
area/db-ido Database output bug Something isn't working
Milestone

Comments

@icinga-migration
Copy link

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

Created by smadmin on 2015-07-08 11:56:00 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2015-07-08 12:10:03 +00:00)
Target Version: 2.3.7
Last Update: 2015-07-17 20:39:58 +00:00 (in Redmine)

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

In the icinga.log we see the following error concerning IdoPgsqlConnection failing to insert in endpoints table.

[2015-07-08 12:52:10 +0200] critical/IdoPgsqlConnection: Error "ERROR:  duplicate key value violates unique constraint "uq_endpoints"
DETAIL:  Key (instance_id, config_type, endpoint_object_id)=(1, 1, 1) already exists.
" when executing query "INSERT INTO icinga_endpoints (config_type, endpoint_object_id, identity, instance_id, node) VALUES (E'1', 1, E'server1.example.com', 1, E'server1.example.com')"
Context:
        (0) Reconnecting to PostgreSQL IDO database 'ido-pgsql'

Querying the table shows no entries. If the statement is executed directly in a psql shell it works, and the endpoint_id indicates that there were some entries before. But after a reload of icinga the table entries are removed and the error returns.

We had some trouble both nodes not writing in the database perhaps because of this.

Database is postgresql 9.2.10 from RHEL7.

Changesets

2015-07-08 11:58:12 +00:00 by (unknown) 3fe6b56

DB IDO: Don't clear endpoint* tables on start/reload

fixes #9586

2015-07-13 08:26:01 +00:00 by (unknown) 8545c50

DB IDO: Don't clear endpoint* tables on start/reload

fixes #9586

Relations:

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-07-08 11:57:35 +00:00

  • Subject changed from IdoPgsqlConnection error concerning endpoints to DB IDO: endpoint* tables are cleared on reload causing constraint violations
  • Status changed from New to Assigned
  • Assigned to set to mfriedrich
  • Target Version set to 2.4.0
  • Estimated Hours set to 1

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-07-08 11:57:50 +00:00

  • Relates set to 5565

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-07-08 11:58:00 +00:00

  • Relates set to 5636

@icinga-migration
Copy link
Author

Updated by Anonymous on 2015-07-08 12:10:03 +00:00

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

Applied in changeset 3fe6b56.

@icinga-migration
Copy link
Author

Updated by gbeutner on 2015-07-13 08:26:12 +00:00

  • Target Version changed from 2.4.0 to 2.3.7
  • Backport? changed from TBD to Yes

@icinga-migration
Copy link
Author

Updated by dsargrad on 2015-07-17 18:56:52 +00:00

Anonymous wrote:

Applied in changeset 3fe6b56.

We have the following version installed:

root@support-server2:~# icinga2 --version
icinga2 - The Icinga 2 network monitoring daemon (version: r2.3.7-1)

We are seeing the same problem:

[2015-07-17 16:37:20 +0000] warning/IdoPgsqlConnection: Exception during database operation: Verify that your database is operational!
[2015-07-17 16:37:30 +0000] information/IdoPgsqlConnection: pgSQL IDO instance id: 1 (schema version: '1.13.0')
[2015-07-17 16:37:30 +0000] 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'support-server2.ssdse.sensis.com', 1, E'1', E'support-server2.ssdse.sensis.com', TO_TIMESTAMP(1437151050))"
Context:
        (0) Reconnecting to PostgreSQL IDO database 'ido-pgsql'

Oddly we are able to patch the database as follows:

icinga2idopgsql=# truncate icinga_endpoints;
TRUNCATE TABLE
icinga2idopgsql=# truncate icinga_endpointstatus ;
TRUNCATE TABLE

Then the exception goes away, and everything is fine. The way the fix (https://dev.icinga.org/projects/i2/repository/revisions/3fe6b56628dfc359dd8cd02c41de766eb44f0204/diff/lib/db\_ido/dbconnection.cpp ) reads it seems that you dont want to truncate these tables... Whereas for us truncating these tables makes the problem go away.

So im a bit confused.

Why does truncating seem to fix the problem?

What version is this fixed in, and when will that be available for download?

We currently install icinga2 after performing the following commands:

add-apt-repository ppa:formorer/icinga
apt-get update
apt-get install icinga2

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2015-07-17 20:39:58 +00:00

This isn't the correct issue. The error you are seeing is described in #9634

@icinga-migration
Copy link
Author

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

  • Relates set to 9634

@icinga-migration icinga-migration added bug Something isn't working area/db-ido Database output labels Jan 17, 2017
@icinga-migration icinga-migration added this to the 2.3.7 milestone Jan 17, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/db-ido Database output bug Something isn't working
Projects
None yet
Development

No branches or pull requests

1 participant