Recent additions to online indexing do not work for oracle #3408
Closed
Description
When migration 6_0_0.20220207.1
occurs i receive the following on oracle DB version 12.2.0.1:
Caused by: ca.uhn.fhir.jpa.migrate.taskdef.BaseTask$1: HAPI-0061: Failed during task 6_0_0.20220207.1: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [create index IDX_SP_DATE_HASH_V2 on HFJ_SPIDX_DATE(HASH_IDENTITY, SP_VALUE_LOW, SP_VALUE_HIGH, RES_ID, PARTITION_ID) ONLINE DEFERRED]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02000: missing INVALIDATION keyword
; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [create index IDX_SP_DATE_HASH_V2 on HFJ_SPIDX_DATE(HASH_IDENTITY, SP_VALUE_LOW, SP_VALUE_HIGH, RES_ID, PARTITION_ID) ONLINE DEFERRED]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02000: missing INVALIDATION keyword
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3756)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3736)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1063)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
at jdk.internal.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at net.ttddyy.dsproxy.proxy.StatementProxyLogic.performQueryExecutionListener(StatementProxyLogic.java:316) [1 skipped]
at net.ttddyy.dsproxy.proxy.StatementProxyLogic.access$700(StatementProxyLogic.java:37)
at net.ttddyy.dsproxy.proxy.StatementProxyLogic$1.execute(StatementProxyLogic.java:123)
at net.ttddyy.dsproxy.listener.MethodExecutionListenerUtils.invoke(MethodExecutionListenerUtils.java:42)
at net.ttddyy.dsproxy.proxy.StatementProxyLogic.invoke(StatementProxyLogic.java:120)
at net.ttddyy.dsproxy.proxy.jdk.PreparedStatementInvocationHandler.invoke(PreparedStatementInvocationHandler.java:37)
at jdk.proxy2/jdk.proxy2.$Proxy56.executeUpdate(Unknown Source)
at ca.uhn.fhir.jpa.migrate.taskdef.BaseTask.lambda$executeSql$0(BaseTask.java:141) [5 skipped]
at ca.uhn.fhir.jpa.migrate.taskdef.BaseTask.executeSql(BaseTask.java:138) [1 skipped]
at ca.uhn.fhir.jpa.migrate.taskdef.AddIndexTask.doExecute(AddIndexTask.java:89)
... 61 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-02000: missing INVALIDATION keyword
According to the documentation here we are lacking a keyword in our oracle online indexing.
[{ DEFERRED | IMMEDIATE } INVALIDATION](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ALTER-INDEX.html#GUID-D8F648E7-8C07-4C89-BB71-862512536558__DEFERREDIMMEDIATEINVALIDATION-42A7C77E)
This clause lets you control when the database invalidates dependent cursors while creating the index. It has the same semantics here as for the ALTER INDEX statement. Refer to { DEFERRED | IMMEDIATE } INVALIDATION in the documentation on ALTER INDEX for the full semantics of this clause.
Metadata
Assignees
Labels
No labels