Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

after an update /opt/opennms/bin/install -dis is failing on duplicate key value violates unique constraint "monitoringlocations_pkey"

Description

Performing an update:

  • systemctl stop opennms.service

  • yum update

  • /opt/opennms/bin/install -dis

Following lines were displayed:

[root@dtc-s4049l][449]/home/ethouraud #/opt/opennms/bin/install -dis
OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
==============================================================================
OpenNMS Installer
==============================================================================

Configures PostgreSQL tables, users, and other miscellaneous settings.

DEBUG: Platform is IPv6 ready: true

Updating : opennms-webapp-jetty-19.0.0-1.noarch 7/16

Updating : opennms-19.0.0-1.noarch 8/16
Cleanup : opennms-18.0.4-1.noarch 9/16
Cleanup : 1:java-1.7.0-openjdk-1.7.0.121-2.6.8.0.el7_3.x86_64 10/16
Cleanup : 1:java-1.7.0-openjdk-headless-1.7.0.121-2.6.8.0.el7_3.x8 11/16
Cleanup : 1:java-1.7.0-openjdk-devel-1.7.0.121-2.6.8.0.el7_3.x86_6 12/16
Cleanup : opennms-webapp-jetty-18.0.4-1.noarch 13/16
Cleanup : opennms-core-18.0.4-1.noarch 14/16
warning: /opt/opennms/etc/poller-config.properties saved as /opt/opennms/etc/poller-config.properties.rpmsave
Cleanup : jicmp-1.4.6-1.x86_64 15/16
Cleanup : jicmp6-1.2.4-1.x86_64 16/16
Verifying : opennms-webapp-jetty-19.0.0-1.noarch 1/16
Verifying : opennms-core-19.0.0-1.noarch 2/16
Verifying : 1:java-1.7.0-openjdk-devel-1.7.0.131-2.6.9.0.el7_3.x86_6 3/16
Verifying : jicmp6-2.0.2-1.el7.centos.x86_64 4/16
Verifying : 1:java-1.7.0-openjdk-1.7.0.131-2.6.9.0.el7_3.x86_64 5/16
Verifying : opennms-19.0.0-1.noarch 6/16
Verifying : jicmp-2.0.3-1.el7.centos.x86_64 7/16
Verifying : 1:java-1.7.0-openjdk-headless-1.7.0.131-2.6.9.0.el7_3.x8 8/16
Verifying : opennms-core-18.0.4-1.noarch 9/16
Verifying : 1:java-1.7.0-openjdk-1.7.0.121-2.6.8.0.el7_3.x86_64 10/16
Verifying : 1:java-1.7.0-openjdk-devel-1.7.0.121-2.6.8.0.el7_3.x86_6 11/16
Verifying : jicmp6-1.2.4-1.x86_64 12/16
Verifying : jicmp-1.4.6-1.x86_64 13/16
Verifying : opennms-webapp-jetty-18.0.4-1.noarch 14/16
Verifying : 1:java-1.7.0-openjdk-headless-1.7.0.121-2.6.8.0.el7_3.x8 15/16
Verifying : opennms-18.0.4-1.noarch 16/16

Updated:
java-1.7.0-openjdk.x86_64 1:1.7.0.131-2.6.9.0.el7_3
java-1.7.0-openjdk-devel.x86_64 1:1.7.0.131-2.6.9.0.el7_3
java-1.7.0-openjdk-headless.x86_64 1:1.7.0.131-2.6.9.0.el7_3
jicmp.x86_64 0:2.0.3-1.el7.centos
jicmp6.x86_64 0:2.0.2-1.el7.centos
opennms.noarch 0:19.0.0-1
opennms-core.noarch 0:19.0.0-1
opennms-webapp-jetty.noarch 0:19.0.0-1

Complete!
[root@dtc-s4049l][449]/home/ethouraud #/opt/opennms/bin/install -dis
OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
==============================================================================
OpenNMS Installer
==============================================================================

Configures PostgreSQL tables, users, and other miscellaneous settings.

DEBUG: Platform is IPv6 ready: true

  • searching for libjicmp.so:

  • trying to load /usr/lib64/libjicmp.so: OK

  • searching for libjicmp6.so:

  • trying to load /usr/lib64/libjicmp6.so: OK

  • searching for libjrrd.so:

  • trying to load /usr/lib64/libjrrd.so: NO

  • trying to load /usr/lib64/libjrrd.so: NO

  • trying to load /libjrrd.so: NO

  • trying to load /opt/lib/libjrrd.so: NO

  • trying to load /opt/sfw/lib/libjrrd.so: NO

  • trying to load /usr/ccs/lib/libjrrd.so: NO

  • trying to load /opt/opennms/lib/libjrrd.so: NO

  • trying to load /opt/opennms/lib/linux64/libjrrd.so: NO

  • trying to load /usr/java/packages/lib/amd64/libjrrd.so: NO

  • trying to load /usr/lib64/libjrrd.so: NO

  • trying to load /lib64/libjrrd.so: NO

  • trying to load /lib/libjrrd.so: NO

  • trying to load /usr/lib/libjrrd.so: NO

  • trying to load /usr/lib/jni/libjrrd.so: NO

  • trying to load /usr/lib/libjrrd.so: NO

  • trying to load /usr/local/lib/libjrrd.so: NO

  • trying to load /opt/NMSjicmp/lib/32/libjrrd.so: NO

  • trying to load /opt/NMSjicmp/lib/64/libjrrd.so: NO

  • trying to load /opt/NMSjicmp6/lib/32/libjrrd.so: NO

  • trying to load /opt/NMSjicmp6/lib/64/libjrrd.so: NO

  • Failed to load the optional jrrd library.

  • This error is not fatal, since jrrd is only required for optional features.

  • For more information, see http://www.opennms.org/index.php/jrrd

  • searching for libjrrd2.so:

  • trying to load /usr/lib64/libjrrd2.so: NO

  • trying to load /usr/lib64/libjrrd2.so: NO

  • trying to load /libjrrd2.so: NO

  • trying to load /opt/lib/libjrrd2.so: NO

  • trying to load /opt/sfw/lib/libjrrd2.so: NO

  • trying to load /usr/ccs/lib/libjrrd2.so: NO

  • trying to load /opt/opennms/lib/libjrrd2.so: NO

  • trying to load /opt/opennms/lib/linux64/libjrrd2.so: NO

  • trying to load /usr/java/packages/lib/amd64/libjrrd2.so: NO

  • trying to load /usr/lib64/libjrrd2.so: NO

  • trying to load /lib64/libjrrd2.so: NO

  • trying to load /lib/libjrrd2.so: NO

  • trying to load /usr/lib/libjrrd2.so: NO

  • trying to load /usr/lib/jni/libjrrd2.so: NO

  • trying to load /usr/lib/libjrrd2.so: NO

  • trying to load /usr/local/lib/libjrrd2.so: NO

  • trying to load /opt/NMSjicmp/lib/32/libjrrd2.so: NO

  • trying to load /opt/NMSjicmp/lib/64/libjrrd2.so: NO

  • trying to load /opt/NMSjicmp6/lib/32/libjrrd2.so: NO

  • trying to load /opt/NMSjicmp6/lib/64/libjrrd2.so: NO

  • Failed to load the optional jrrd2 library.

  • This error is not fatal, since jrrd2 is only required for optional features.

  • For more information, see http://www.opennms.org/index.php/jrrd2
    10:16:57.147 [Main] WARN org.opennms.install.Installer - Could not create file: /opt/opennms/etc/libraries.properties

  • using SQL directory... /opt/opennms/etc

  • using create.sql... /opt/opennms/etc/create.sql
    10:16:57.156 [Main] INFO org.opennms.core.schema.Migrator - validating database version

  • using 'postgres' as the PostgreSQL user for OpenNMS

  • using 'opennms' as the PostgreSQL database name for OpenNMS
    10:16:57.295 [Main] INFO org.opennms.core.schema.Migrator - validating database version
    10:16:57.310 [Main] INFO org.opennms.core.schema.Migrator - adding PL/PgSQL support to the database, if necessary
    10:16:57.319 [Main] INFO org.opennms.core.schema.Migrator - PL/PgSQL call handler exists
    10:16:57.321 [Main] INFO org.opennms.core.schema.Migrator - PL/PgSQL language exists

  • checking if database "opennms" is unicode... ALREADY UNICODE

  • Checking for old import files in /opt/opennms/etc... DONE

  • Running migration for changelog: URL [jar:file:/opt/opennms/lib/org.opennms.core.schema-19.0.0-liquibase.jar!/changelog.xml]
    INFO 2/14/17 10:17 AM:liquibase: Successfully acquired change log lock
    INFO 2/14/17 10:17 AM:liquibase: Reading from databasechangelog
    INFO 2/14/17 10:17 AM:liquibase: Reading from databasechangelog
    SEVERE 2/14/17 10:17 AM:liquibase: Change Set 19.0.0/changelog.xml::19.0.0-insert-default-monitoringlocation::seth failed. Error: Error executing SQL INSERT INTO monitoringlocations (id, monitoringarea) VALUES ('localhost', 'localhost'): ERROR: duplicate key value violates unique constraint "monitoringlocations_pkey"
    Detail: Key (id)=(localhost) already exists.
    liquibase.exception.DatabaseException: Error executing SQL INSERT INTO monitoringlocations (id, monitoringarea) VALUES ('localhost', 'localhost'): ERROR: duplicate key value violates unique constraint "monitoringlocations_pkey"
    Detail: Key (id)=(localhost) already exists.
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)
    at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1091)
    at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:1075)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:317)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
    at liquibase.Liquibase.update(Liquibase.java:114)
    at org.opennms.core.schema.Migrator.migrate(Migrator.java:477)
    at org.opennms.install.Installer.install(Installer.java:273)
    at org.opennms.install.Installer.main(Installer.java:989)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.opennms.bootstrap.Bootstrap$4.run(Bootstrap.java:460)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "monitoringlocations_pkey"
    Detail: Key (id)=(localhost) already exists.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2158)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:291)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:432)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:358)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:305)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:291)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:269)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:265)
    at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
    ... 16 more
    INFO 2/14/17 10:17 AM:liquibase: Successfully released change log lock
    java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.opennms.bootstrap.Bootstrap$4.run(Bootstrap.java:460)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: org.opennms.core.schema.MigrationException: unable to migrate the database
    at org.opennms.core.schema.Migrator.migrate(Migrator.java:479)
    at org.opennms.install.Installer.install(Installer.java:273)
    at org.opennms.install.Installer.main(Installer.java:989)
    ... 6 more
    Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set 19.0.0/changelog.xml::19.0.0-insert-default-monitoringlocation::seth:
    Reason: liquibase.exception.DatabaseException: Error executing SQL INSERT INTO monitoringlocations (id, monitoringarea) VALUES ('localhost', 'localhost'): ERROR: duplicate key value violates unique constraint "monitoringlocations_pkey"
    Detail: Key (id)=(localhost) already exists.:
    Caused By: Error executing SQL INSERT INTO monitoringlocations (id, monitoringarea) VALUES ('localhost', 'localhost'): ERROR: duplicate key value violates unique constraint "monitoringlocations_pkey"
    Detail: Key (id)=(localhost) already exists.:
    Caused By: ERROR: duplicate key value violates unique constraint "monitoringlocations_pkey"
    Detail: Key (id)=(localhost) already exists.
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:347)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
    at liquibase.Liquibase.update(Liquibase.java:114)
    at org.opennms.core.schema.Migrator.migrate(Migrator.java:477)
    ... 8 more
    Caused by: liquibase.exception.DatabaseException: Error executing SQL INSERT INTO monitoringlocations (id, monitoringarea) VALUES ('localhost', 'localhost'): ERROR: duplicate key value violates unique constraint "monitoringlocations_pkey"
    Detail: Key (id)=(localhost) already exists.
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)
    at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1091)
    at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:1075)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:317)
    ... 12 more
    Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "monitoringlocations_pkey"
    Detail: Key (id)=(localhost) already exists.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2158)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:291)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:432)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:358)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:305)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:291)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:269)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:265)
    at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
    ... 16 more

Environment

CentOS Linux release 7.3.1611 (Core) Linux 3.10.0-514.6.1.el7.x86_64 #1 SMP Wed Jan 18 13:06:36 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

Acceptance / Success Criteria

None

Lucidchart Diagrams

Activity

Mark Mahacek February 15, 2017 at 10:59 AM

After running the command <code>DELETE FROM monitoringlocations WHERE id = 'localhost';</code> I was able to successfully complete the install -did upgrade.

However, I am still unable to start services. I will continue that discussion on issue 9101.

Seth Leger February 15, 2017 at 10:37 AM

Merged PR, marking as fixed.

commit 086854ab503e6668435b416ed9c5d6328fb942ee

Ronny Trommer February 15, 2017 at 5:57 AM

The solution to your problem is described here: https://wiki.opennms.org/wiki/Upgrade_Guide:_18_to_19#Database_migration

Markus von Rüden February 15, 2017 at 5:48 AM

Mark Mahacek February 14, 2017 at 4:57 PM

Sorry. I didn't mean to move this to In Review status.

Fixed

Details

Assignee

Reporter

Components

Sprint

Fix versions

Affects versions

Priority

PagerDuty

Created February 14, 2017 at 5:24 AM
Updated February 15, 2017 at 3:36 PM
Resolved February 15, 2017 at 10:37 AM