Skip to content

Recent additions to online indexing do not work for oracle #3408

Closed
@tadgh

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

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions