Skip to:

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

OpenNMS Upgrade Script fails with Postgresql 9 :: 'syntax error at or near "$1"'

Description

After using OpenNMS 1.12.0 for quite a while, we tried upgrading it to a more recent version. Following the suggestions and the requirements of the RPM packages we upgraded JDK to version 8 and postgresql to version 9.
Unfortunatly we couldn't complete the upgrade because of an error while running /opt/opennms/bin/install -dis
The error occurs during schema upgrade:

12:29:09.889 [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
    12:29:09.895 [Main] INFO org.opennms.core.schema.Migrator - validating database version

  • using 'postgres' as the PostgreSQL user for OpenNMS

  • using 'opennms_prod_177' as the PostgreSQL database name for OpenNMS
    12:29:09.972 [Main] INFO org.opennms.core.schema.Migrator - validating database version
    12:29:09.982 [Main] INFO org.opennms.core.schema.Migrator - adding PL/PgSQL support to the database, if necessary
    12:29:09.986 [Main] INFO org.opennms.core.schema.Migrator - PL/PgSQL call handler exists
    12:29:09.987 [Main] INFO org.opennms.core.schema.Migrator - PL/PgSQL language exists

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

  • Checking for old import files in /opt/opennms/etc... DONE
    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:497)
    at org.opennms.bootstrap.Bootstrap$3.run(Bootstrap.java:366)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
    Position: 13
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
    at org.opennms.core.db.install.InstallerDb.databaseSetUser(InstallerDb.java:2024)
    at org.opennms.install.Installer.install(Installer.java:270)
    at org.opennms.install.Installer.main(Installer.java:985)
    ... 6 more

The corresponding postresql log error is:

postgres[21051]: [6-1] 2015-06-10 12:16:38 CEST postgres postgres ERROR: syntax error at or near "$1" at character 13
postgres[21051]: [6-2] 2015-06-10 12:16:38 CEST postgres postgres STATEMENT: ALTER TABLE $1 OWNER TO $2
postgres[21051]: [7-1] 2015-06-10 12:16:38 CEST postgres postgres LOG: unexpected EOF on client connection

So we went back to Upgrade in steps. First upgrade was to 1.12.9-2, after that to 14 and then to 16. Unfortunatly the error existed in each version - our database had already been migrated to postgresql 9 - and there is no turning back. We are still running OpenNMS 1.12.9 (though without successfully running the install script).

In a test installation we used an older database, still version 8, where the install script performed without error.

In Postgresql 9 it doesn't matter if we use the current database, an empty one, one created with the create.sql file or no database at all - the error occures regardless.

Funny thing, this error was already reported a while back (http://issues.opennms.org/browse/NMS-4206) and was supposedly fixed. Either this doesn't apply to the RPM release of OpenNMS or is just another (new) error.

Is there a hotfix available? Or a different approach to install schema upgrades? And then running the install tools without the schema upgrades?

Environment

SLES 11.3 Postgres 9.1 JDK 7.79 / 8.45

Acceptance / Success Criteria

None

Attachments

5
  • 23 May 2016, 06:16 AM
  • 23 May 2016, 06:15 AM
  • 23 Jul 2015, 12:33 PM
  • 23 Jul 2015, 12:57 AM
  • 23 Jul 2015, 12:57 AM

Lucidchart Diagrams

Activity

Show:

Steve d February 2, 2017 at 11:30 AM

Damit Jim I'm a network admin not a database admin! (must be said in Dr. McCoy's voice)
Can you give me a clue or a link on how to apply the patch listed?
ty

Alexander Pyhalov May 23, 2016 at 6:15 AM

Patch to make installer work in simple cases

Alexander Pyhalov May 23, 2016 at 6:13 AM
Edited

--- opennms-18.0.0-1/core/db-install/src/main/java/org/opennms/core/db/install/InstallerDb.java 2016-05-09 18:24:57.000000000 +0300 +++ InstallerDb.java 2016-05-23 13:12:02.577830904 +0300 @@ -1640,11 +1640,9 @@ while (rs.next()) { objects.add(rs.getString("TABLE_NAME")); } - final PreparedStatement st = getAdminConnection().prepareStatement("ALTER TABLE ? OWNER TO ?"); + final Statement st = getAdminConnection().createStatement(); for (final String objName : objects) { - st.setString(1, objName); - st.setString(2, m_user); - st.execute(); + st.execute("ALTER TABLE " + objName +" OWNER TO " + m_user); } st.close(); }

Alexander Pyhalov May 23, 2016 at 6:07 AM

Once again... I see this issue present since 1.10 or even earlier. The issue is that PostgreSQL JDBC drivers doesn't like getAdminConnection().prepareStatement("ALTER TABLE ? OWNER TO ?"); And I doubt that it should like it. Do this without prepared statement, and installer will work...

Seth Leger November 2, 2015 at 11:44 AM

Since our default configuration uses template1 as the admin database and I think that this is the "right thing to do" and this seems to work properly, I'm going to mark this issue as won't fix.

Won't Fix

Details

Assignee

Reporter

Labels

HB Grooming Date

Components

Affects versions

Due date

Priority

PagerDuty

Created June 10, 2015 at 7:01 AM
Updated October 19, 2021 at 5:43 PM
Resolved October 19, 2021 at 5:39 PM