mjuricek1 Absent Member.
Absent Member.
1115 views

upgrade AR 1.5 to AR 2.0


Hi Guys,

I am trying to upgrade AR from version 1.5 to 2.0. But I am getting this
error when I log in.

catalina.out :

2016-07-21 14:40:29,384 ERROR [org.hibernate.util.JDBCExceptionReporter]
(http-nio-8080-exec-2) ERROR: relation "sod_case_info" does not exist
Position: 45
com.netiq.iac.persistence.spi.exception.IacPersistenceException:
org.hibernate.exception.SQLGrammarException: could not execute query
at
com.netiq.iac.persistence.dao.DataTransactor.execute(DataTransactor.java:132)
at
com.netiq.iac.persistence.service.SoDPolicyService.getSoDCaseCount(SoDPolicyService.java:1960)
at
com.netiq.iac.server.rest.SoDService.getSoDCases(SoDService.java:1845)
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
com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
at
com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProv
ider.java:205)
at
com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
at
com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
at
com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)

.....

Caused by: org.postgresql.util.PSQLException: ERROR: relation
"fulfillment_item_combined" does not exist
Position: 1596
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
at
org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
at
org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
at
org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
at
org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
at
org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
at
org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
.... 65 more
2016-07-21 14:40:29,649 ERROR
[com.netiq.iac.server.rest.FulfillmentService] (http-nio-8080-exec-10)
[AR] Encountered unexpected error: could not execute query


How can I solve this? Obviously the DB schema is wrong. I tried install
AR with DB option as "Update Existing..." and "Create a new..." but the
result is the same. The Error persists.
Thanks for a help.

Regards,
Milan


--
mjuricek
------------------------------------------------------------------------
mjuricek's Profile: https://forums.netiq.com/member.php?userid=1616
View this thread: https://forums.netiq.com/showthread.php?t=56307

0 Likes
5 Replies
Micro Focus Expert
Micro Focus Expert

Re: upgrade AR 1.5 to AR 2.0

On 7/21/16 8:57 AM, mjuricek wrote:
>
> Hi Guys,
>
> I am trying to upgrade AR from version 1.5 to 2.0. But I am getting this
> error when I log in.
>
> catalina.out :
>
> 2016-07-21 14:40:29,384 ERROR [org.hibernate.util.JDBCExceptionReporter]
> (http-nio-8080-exec-2) ERROR: relation "sod_case_info" does not exist
> Position: 45
> com.netiq.iac.persistence.spi.exception.IacPersistenceException:
> org.hibernate.exception.SQLGrammarException: could not execute query
> at
> com.netiq.iac.persistence.dao.DataTransactor.execute(DataTransactor.java:132)
> at
> com.netiq.iac.persistence.service.SoDPolicyService.getSoDCaseCount(SoDPolicyService.java:1960)
> at
> com.netiq.iac.server.rest.SoDService.getSoDCases(SoDService.java:1845)
> 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
> com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
> at
> com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProv
> ider.java:205)
> at
> com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
> at
> com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
> at
> com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
>
> ....
>
> Caused by: org.postgresql.util.PSQLException: ERROR: relation
> "fulfillment_item_combined" does not exist
> Position: 1596
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
> at
> org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
> at
> org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
> at
> org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
> at
> org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
> at
> org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
> at
> org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
> at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
> at org.hibernate.loader.Loader.doQuery(Loader.java:802)
> at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
> at org.hibernate.loader.Loader.doList(Loader.java:2533)
> ... 65 more
> 2016-07-21 14:40:29,649 ERROR
> [com.netiq.iac.server.rest.FulfillmentService] (http-nio-8080-exec-10)
> [AR] Encountered unexpected error: could not execute query
>
>
> How can I solve this? Obviously the DB schema is wrong. I tried install
> AR with DB option as "Update Existing..." and "Create a new..." but the
> result is the same. The Error persists.
> Thanks for a help.
>
> Regards,
> Milan
>
>

Greetings,

1) Did you have any Reviews running? Did you have any schedules still
enabled? Did you Bulk Data update enabled?

2) How did you move / upgrade the PostgreSQL database? AR 1.5 required
PostgreSQL version 9.4.2 and AR 2.0 requires PostgreSQL version 9.5.1.

3) What were the exact steps that you followed to upgrade?



--
Sincerely,
Steven Williams
Lead Software Engineer
Micro Focus
0 Likes
mjuricek1 Absent Member.
Absent Member.

Re: upgrade AR 1.5 to AR 2.0


I stopped all running reviews before an upgrade. Also I used Purge
utility to do some cleanups. But, OK, I did not upgrade PostgreSQL and
just install new AR (I am using the newest OSP from IDM 4.5.4 in my
demo).
....back to DB. This is a tricky part from my point of view. I am using
bundled Tomcat and PostgreSQL. But these components are not possible to
upgrade by native NetIQ installer. So I assume the AR installation
should update DB schema on the older version as well. Isn't it?
Otherwise I have to export all data from DB, remove old DB, install new
one and import all data back. And from my point of view this is not
approach what should be used in upgrade process.
Is it possible to run the update of the schema only and to see what's
exactly wrong?

Regards,
Milan


--
mjuricek
------------------------------------------------------------------------
mjuricek's Profile: https://forums.netiq.com/member.php?userid=1616
View this thread: https://forums.netiq.com/showthread.php?t=56307

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: upgrade AR 1.5 to AR 2.0

On 7/22/16 5:34 AM, mjuricek wrote:
>
> I stopped all running reviews before an upgrade. Also I used Purge
> utility to do some cleanups. But, OK, I did not upgrade PostgreSQL and
> just install new AR (I am using the newest OSP from IDM 4.5.4 in my
> demo).
> ...back to DB. This is a tricky part from my point of view. I am using
> bundled Tomcat and PostgreSQL. But these components are not possible to
> upgrade by native NetIQ installer. So I assume the AR installation
> should update DB schema on the older version as well. Isn't it?
> Otherwise I have to export all data from DB, remove old DB, install new
> one and import all data back. And from my point of view this is not
> approach what should be used in upgrade process.
> Is it possible to run the update of the schema only and to see what's
> exactly wrong?
>
> Regards,
> Milan
>
>

Greetings,
Access Review does not support and in-place upgrade of the Tomcat,
JRE, ActiveMQ, and PostgreSQL. One has to make sure all of those
components meet the minimum versions as outlined in the system
requirements. Therefore, you could:

1) Export the three (3) databases from the old version of PostgreSQL

2) On a new Linux server install the minimum versions of Tomcat, JRE,
ActiveMQ, and PostgreSQL as outlined in the documentation

3) Import the three (3) databases and re-create the arrptuser

4) Then proceed with the upgrade of the database schema.

--
Sincerely,
Steven Williams
Lead Software Engineer
Micro Focus
0 Likes
mjuricek1 Absent Member.
Absent Member.

Re: upgrade AR 1.5 to AR 2.0


Hi Steven,

finally done. Thanks for a hint.
Here are the steps what I did and which can help others:
0. *provide backups and stop tomcat*
/etc/init.d/idmapps_tomcat_init stop
1. *create a dump of PostgreSQL DB*
/opt/netiq/idm/apps/postgres/bin/pg_dumpall -U postgres >
/tmp/ar_db_full_backup_20160725.sql
2. *uninstall PostgreSQL: *
cd /opt/netiq/idm/apps/postgres/
../uninstall-postgresql
3. *install a new PostgreSQL*
cd /root/install/ar20/
../TomcatPostgreSQL.bin
4. *import DB dump.*
/opt/netiq/idm/apps/postgres/bin/psql -U postgres <
/tmp/ar_db_full_backup_20160725.sql
5.* upgrade AR.*
cd /root/install/ar20
../access-review-install-linux.bin
!!! note: do not create/update DB now but store SQL commands.
The SQLs are stored in: /opt/netiq/idm/apps/accessreview/sql/
6. *Update tables manually - following command create the important view
which are missing for a new AR2.0*
/opt/netiq/idm/apps/postgres/bin/psql -U ardcs -d ardcs <
/opt/netiq/idm/apps/accessreview/sql/dcs-init.sql
/opt/netiq/idm/apps/postgres/bin/psql -U arops -d arops <
/opt/netiq/idm/apps/accessreview/sql/ops-init.sql
/opt/netiq/idm/apps/postgres/bin/psql -U arwf -d arwf <
/opt/netiq/idm/apps/accessreview/sql/wf-init.sql

NOTE: is very important to provide updates as ardcs user for DSC tables,
as arops for OPS tables and arwf for WF tables. If you do it as postgres
user then Access Review will have no sufficient rights.


Regards,
Milan


--
mjuricek
------------------------------------------------------------------------
mjuricek's Profile: https://forums.netiq.com/member.php?userid=1616
View this thread: https://forums.netiq.com/showthread.php?t=56307

0 Likes
Micro Focus Expert
Micro Focus Expert

Re: upgrade AR 1.5 to AR 2.0

On 7/25/16 6:10 AM, mjuricek wrote:
>
> Hi Steven,
>
> finally done. Thanks for a hint.
> Here are the steps what I did and which can help others:
> 0. *provide backups and stop tomcat*
> /etc/init.d/idmapps_tomcat_init stop
> 1. *create a dump of PostgreSQL DB*
> /opt/netiq/idm/apps/postgres/bin/pg_dumpall -U postgres >
> /tmp/ar_db_full_backup_20160725.sql
> 2. *uninstall PostgreSQL: *
> cd /opt/netiq/idm/apps/postgres/
> ./uninstall-postgresql
> 3. *install a new PostgreSQL*
> cd /root/install/ar20/
> ./TomcatPostgreSQL.bin
> 4. *import DB dump.*
> /opt/netiq/idm/apps/postgres/bin/psql -U postgres <
> /tmp/ar_db_full_backup_20160725.sql
> 5.* upgrade AR.*
> cd /root/install/ar20
> ./access-review-install-linux.bin
> !!! note: do not create/update DB now but store SQL commands.
> The SQLs are stored in: /opt/netiq/idm/apps/accessreview/sql/
> 6. *Update tables manually - following command create the important view
> which are missing for a new AR2.0*
> /opt/netiq/idm/apps/postgres/bin/psql -U ardcs -d ardcs <
> /opt/netiq/idm/apps/accessreview/sql/dcs-init.sql
> /opt/netiq/idm/apps/postgres/bin/psql -U arops -d arops <
> /opt/netiq/idm/apps/accessreview/sql/ops-init.sql
> /opt/netiq/idm/apps/postgres/bin/psql -U arwf -d arwf <
> /opt/netiq/idm/apps/accessreview/sql/wf-init.sql
>
> NOTE: is very important to provide updates as ardcs user for DSC tables,
> as arops for OPS tables and arwf for WF tables. If you do it as postgres
> user then Access Review will have no sufficient rights.
>
>
> Regards,
> Milan
>
>

Greetings,
The above steps are missing a very, very important step that is
outlined in the documentation when one elects to utilize a SQL file:


Section 7.1 Configuring the Database after Installation:
https://www.netiq.com/documentation/access-review-2/access-review-user/data/b1ihbdzf.html

Specifically step #4 in Section 7.1.1 Configuring the PostgreSQL
Database for Access Review:
https://www.netiq.com/documentation/access-review-2/access-review-user/data/b1ihbdzf.html#b1ictlih

"
To populate the global configuration values in the database, enter the
following command:
"


If you do not perform this step then the new configuration settings for
AR 2.0 will will not be added to the database which will result in
runtime issues.

For example, one will experience the problem outlined in the thread:

"Invalid feature service key: [ApprovalPolicyService] - AR2.0"


--
Sincerely,
Steven Williams
Lead Software Engineer
Micro Focus
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.