Question
HCL Technologies
IN
Last activity: 7 Aug 2023 10:34 EDT
Error Getting Current Database time
Hi ,
when i am executing the pega 8.5 upgrade or install script i am getting the below error
Hi ,
when i am executing the pega 8.5 upgrade or install script i am getting the below error
2020-12-03 21:14:12,104 [ervice-registry-task] [ STANDARD] [ ] [ ] (rsistence.DatabaseTimeProvider) ERROR - Could not retrieve the database time com.pega.pegarules.pub.PRRuntimeException: PRRuntimeException at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.execute(NativeSQLListExecutor.java:85) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.execute(NativeSQLListExecutor.java:52) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.PRDataProviderImpl.executeNativeSQL(PRDataProviderImpl.java:1663) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.PRDataProviderForModulesImpl.executeNativeSQL(PRDataProviderForModulesImpl.java:138) ~[prprivate-data.jar:8.5.1-208] at com.pega.platform.serviceregistry.internal.database.persistence.DatabaseTimeProvider.get(DatabaseTimeProvider.java:61) ~[service-registry.jar:8.5.1-208] at com.pega.platform.serviceregistry.internal.database.tasks.FailedServiceCleaner.execute(FailedServiceCleaner.java:62) ~[service-registry.jar:8.5.1-208] at com.pega.platform.serviceregistry.internal.database.tasks.TaskExecutor.execute(TaskExecutor.java:74) ~[service-registry.jar:8.5.1-208] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_222] at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) ~[?:1.8.0_222] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) ~[?:1.8.0_222] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) ~[?:1.8.0_222] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_222] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_222] at com.pega.platform.serviceregistry.internal.PrThreadFactory$PrpcThread.run(PrThreadFactory.java:71) ~[service-registry.jar:8.5.1-208] Caused by: com.pega.pegarules.data.internal.rd.queryexec.ExecuteQueryException: An error occured on executing the query for the report definition - There was a problem getting a list: code: 933 SQLState: 42000 Message: ORA-00933: SQL command not properly ended
DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
| SQL Code: 933 | SQL State: 42000
at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:84) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:51) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.executeQuery(NativeSQLListExecutor.java:135) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.executeSql(NativeSQLListExecutor.java:117) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.execute(NativeSQLListExecutor.java:82) ~[prprivate-data.jar:8.5.1-208] ... 13 more Caused by: com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 933 SQLState: 42000 Message: ORA-00933: SQL command not properly ended
DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
| SQL Code: 933 | SQL State: 42000
at com.pega.pegarules.data.internal.access.ExceptionInformation.createAppropriateExceptionDueToDBFailure(ExceptionInformation.java:385) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:364) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.Lister.convertSqlExceptionToDatabaseException(Lister.java:618) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.Lister.listWithResultPackager(Lister.java:434) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.Lister.list(Lister.java:190) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:3027) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:3018) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:98) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:75) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:51) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.executeQuery(NativeSQLListExecutor.java:135) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.executeSql(NativeSQLListExecutor.java:117) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.execute(NativeSQLListExecutor.java:82) ~[prprivate-data.jar:8.5.1-208] ... 13 more Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3887) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1079) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-java7-2.4.12.jar:?] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-java7-2.4.12.jar:?] at com.pega.pegarules.data.internal.store.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:531) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.Lister.listWithResultPackager(Lister.java:415) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.Lister.list(Lister.java:190) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:3027) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:3018) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:98) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:75) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:51) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.executeQuery(NativeSQLListExecutor.java:135) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.executeSql(NativeSQLListExecutor.java:117) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.execute(NativeSQLListExecutor.java:82) ~[prprivate-data.jar:8.5.1-208] ... 13 more Caused by: oracle.jdbc.OracleDatabaseException: ORA-00933: SQL command not properly ended
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:747) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3887) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1079) ~[ojdbc8- Proprietary information hidden.jar: Proprietary information hidden.0] at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-java7-2.4.12.jar:?] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-java7-2.4.12.jar:?] at com.pega.pegarules.data.internal.store.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:531) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.Lister.listWithResultPackager(Lister.java:415) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.Lister.list(Lister.java:190) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:3027) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:3018) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:98) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:75) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.queryexec.NativeSQLQueryExecutor.executeQuery(NativeSQLQueryExecutor.java:51) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.executeQuery(NativeSQLListExecutor.java:135) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.executeSql(NativeSQLListExecutor.java:117) ~[prprivate-data.jar:8.5.1-208] at com.pega.pegarules.data.internal.sqlapi.exec.NativeSQLListExecutor.execute(NativeSQLListExecutor.java:82) ~[prprivate-data.jar:8.5.1-208] ... 13 more
Any idea what the root cause or the issue could be?
Pegasystems Inc.
IN
Are you performing inplace or out of place upgrade?
Could you check and confirm whether the All db privileges have been granted as per the upgrade guide?
Refer the below Pega 8.5 guide.
Pegasystems Inc.
IN
You can grant following privilege and rerun the upgrade. If you are not sure if you have missed any privileges
grant all privileges on pr_sys_serviceregistry to base user
HCL Technologies
IN
Hi Sushant.
I tried by granting that privilege. I am still facing the same issue and error. But it gives Upgrade success full message. Even i am able to start the server and login to the application. But in the Logs alone this error is thrown once in evey 5-10 min.
HCLTech
US
Hi Sushant,
Upgrade is successful, Post upgrade while server start up regisrty service fails. Attaching the complete log
[ ] [ ] ( external.async.IStartupTask) INFO - Awaiting for StartupTasks (parallel cache loading to finish)... [java] 2020-12-25 04:35:48,044 [_IN_SERVICE_REGISTRY] [ STANDARD] [ ] [ ] ( external.async.IStartupTask) INFO - Initializing service registry... [java] 2020-12-25 04:35:48,044 [_IN_SERVICE_REGISTRY] [ STANDARD] [ ] [ ] ( external.async.IStartupTask) INFO - Initializing service registry in embedded environment since the persistence classes exist [java] 2020-12-25 04:35:48,061 [_IN_SERVICE_REGISTRY] [ STANDARD] [ ] [ ] (rsistence.DatabaseTimeProvider) ERROR - Could not retrieve the database time
Proximus
BE
Hello,
I faced the same and table pr_sys_serviceregistry was present in DATA & RULES schema.
When I faced the issue the synonym was defined in the table located in RULES schema while it should be in the table in the data schema.
By changing this synonym to the correct table in correct schema, it worked fine.
Regards
Anthony
HCLTech
US
Hi Anthony,
Thanks for the reply, in my case pr_sys_serviceregistry table exists only in PegaDATA schema and Synonym is not present for it. I have created a synonym for this table in PegaData Scheme and even after creating its behaving the same.
1. Should this table be present in PegaRules Schema as well?
2. Can you share the Create Synonym ddl for this table in PegaData Schema
Regards,
Dharani
Proximus
BE
Hello,
Have you been able to find a solution? the one I mentioned fixed the issue in PEGA 8.4 but I now face the same as you after upgrade to 8.5.
Thank you
Regards
Anthony
Proximus
BE
What's your DB version?
We identified the issue with the below OOTB query:
SELECT SYSDATE AS "CurrentDateTime" FROM DATA_SCHEMA.pr_sys_serviceregistry "SSR" OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
For Oracle this one is compatible from oracle 12 only, while we're still with Oracle 11 which is flagged as compatible with PEGA 8.5.2
Well, this is our findings for our environment for the same issue.
There's a SR open for this our side.
if any new info, it will be posted in this post PEGA 8.5.2 & ORA-00933 | Collaboration Center
Regards
Anthony