[prev in list] [next in list] [prev in thread] [next in thread] 

List:       opennms-buglist
Subject:    [Buglist] [Bug 4003] New: Postgres database upgrade fails in InstallerDb.databaseSetUser on Windows
From:       bugzilla () opennms ! org (bugzilla at opennms ! org)
Date:       2010-07-30 20:44:01
Message-ID: bug-4003-627 () http ! bugzilla ! opennms ! org/
[Download RAW message or body]

http://bugzilla.opennms.org/show_bug.cgi?id=4003

           Summary: Postgres database upgrade fails in
                    InstallerDb.databaseSetUser on Windows
           Product: OpenNMS
           Version: 1.8.1
          Platform: PC
        OS/Version: Windows XP/2003 (NT 5.1)
            Status: NEW
          Severity: major
          Priority: P1
         Component: Installation
        AssignedTo: buglist at opennms.org
        ReportedBy: papanon at sierrasystems.com


Running the EnterpriseDB windows package for PostgreSQL 8.3. The installer
fails when attempting to upgrade from OpenNMS 1.8.0 to 1.8.1 (but also appears
to happen with upgrading from older upgrades to 1.7+).

The problem appears to be the same as that identified by Daniel Greske in June
on the -install mailing list:

When I run the installer ($OPENNMS_HOME/bin/install -l /usr/local/lib 
-dis) to complete the upgrade, everything goes well until this line:
[...]

* using 'opennms' as the PostgreSQL database name for OpenNMS
- checking if database "opennms" is unicode... ALREADY UNICODE
- Checking for old import files in /opt/opennms/etc... DONE
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: 
syntax error at or near "$1"
**Position: 13
** * * *at 
org.postgresql.core.v3.QueryExecutorImpl.receiveEr
rorResponse(QueryExecutorImpl.java:2062)
** * * *at 
org.postgresql.core.v3.QueryExecutorImpl.processRe
sults(QueryExecutorImpl.java:1795)
** * * *at 
org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:257)
** * * *at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execut
e(AbstractJdbc2Statement.java:479)
** * * *at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execut
eWithFlags(AbstractJdbc2Statement.java:367)
** * * *at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execut
e(AbstractJdbc2Statement.java:360)
** * * *at 
org.opennms.netmgt.dao.db.InstallerDb.databaseSetU ser(InstallerDb.java:1940)
** * * *at 
org.opennms.install.Installer.install(Installer.ja va:278)
** * * *at 
org.opennms.install.Installer.main(Installer.java: 859)



I did a little more digging and it looks like the PostgreSQL driver on Windows
is sometimes returning tables, indices, and sequences during the statement

ResultSet rs = getAdminConnection().getMetaData().getTables(null, "public",
"%", null);

The subsequent ALTER TABLE statement then fails on the first non-table object
encountered in the HashSet built from that ResultSet.

One way to ensure that only tables are being acted upon would be to add some
filtering to the databaseSetUser() method. Something like the following might
work as a workaround for the PostgreSQL behaviour without affecting the results
other drivers.

    public void databaseSetUser() throws SQLException {
        ResultSet rs = getAdminConnection().getMetaData().getTables(null,
"public", "%", null);
        HashSet<String> objects = new HashSet<String>();
        while (rs.next()) {
            if (rs.getString("TABLE_TYPE").equals("TABLE"))
                objects.add(rs.getString("TABLE_NAME"));
        }
        PreparedStatement st = getAdminConnection().prepareStatement("ALTER
TABLE ? OWNER TO ?");
        for (String objName : objects) {
            st.setString(1, objName);
            st.setString(2, m_user);
            st.execute();
        }
        st.close();
    }


I am currently running into this on Windows with the PG 8.4-701 JDBC3 driver. I
have had success doing a db upgrade to 1.8 under CentOS 5 with the same PG
8.4-701 driver against a Linux RPM Postgres db, so maybe it's a difference
between the Windows and Linux PostgreSQL implementations.


-- 
Configure bugmail: http://bugzilla.opennms.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.

[prev in list] [next in list] [prev in thread] [next in thread]