oracle-r2dbc icon indicating copy to clipboard operation
oracle-r2dbc copied to clipboard

Published 20 hours ago • verified publisher icon oracle

R2DBC treating warning as exception

Open rathoreamrsingh opened this issue 2 years ago • 8 comments

Hi,

I am using R2DBC with oracle, where I am trying to get some details from a view. This view is having some warning while executing it which is not an exception but R2DBC is treating that as exception and is failing.

If I run the exact same query with limit of 9 or less records it works fine. I tried this view with Spring data JPA and there it works perfectly fine.

Database used in this case is Oracle

Please have a look on this below log for more details.

2022-09-09 15:03:51,876 INFO  [reactor-http-nio-2] com.gic.eagle.txnHld.handler.VWODAHoldingEglHandler: trying to get view data from ODA RE from [01Jan1991] to [09Jan2022]
2022-09-09 15:04:09,993 DEBUG [ForkJoinPool.commonPool-worker-3] org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec: Executing SQL statement [SELECT * from <View_Name> WHERE DATA_DT >= :P0_startDate AND DATA_DT <=:P1_endDate]
2022-09-09 15:04:10,384 ERROR [ForkJoinPool.commonPool-worker-3] reactor.util.Loggers$Slf4JLogger: Operator called default onErrorDropped
reactor.core.Exceptions$StaticThrowable: Operator has been terminated
2022-09-09 15:04:10,411 ERROR [ForkJoinPool.commonPool-worker-3] com.gic.eagle.txnHld.handler.TxnHldDataTransHandler: Error [executeMany; SQL [SELECT * from <View_Name> WHERE DATA_DT >= :P0_startDate AND DATA_DT <=:P1_endDate]; Warning: execution completed with warning; nested exception is oracle.r2dbc.impl.OracleR2dbcExceptions$OracleR2dbcException: [17110] [99999] Warning: execution completed with warning]
org.springframework.r2dbc.UncategorizedR2dbcException: executeMany; SQL [SELECT * from <View_Name> WHERE DATA_DT >= :P0_startDate AND DATA_DT <=:P1_endDate]; Warning: execution completed with warning; nested exception is oracle.r2dbc.impl.OracleR2dbcExceptions$OracleR2dbcException: [17110] [99999] Warning: execution completed with warning
	at org.springframework.r2dbc.connection.ConnectionFactoryUtils.convertR2dbcException(ConnectionFactoryUtils.java:238)
	at org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$8(DefaultDatabaseClient.java:147)
	at reactor.core.publisher.Flux.lambda$onErrorMap$29(Flux.java:6943)
	at reactor.core.publisher.Flux.lambda$onErrorResume$30(Flux.java:6996)
	at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:94)
	at reactor.core.publisher.FluxUsingWhen$UsingWhenSubscriber.deferredError(FluxUsingWhen.java:398)
	at reactor.core.publisher.FluxUsingWhen$RollbackInner.onComplete(FluxUsingWhen.java:475)
	at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2058)
	at reactor.core.publisher.MonoNext$NextSubscriber.onComplete(MonoNext.java:102)
	at reactor.core.publisher.MonoNext$NextSubscriber.onComplete(MonoNext.java:102)
	at oracle.r2dbc.impl.AsyncLock$UsingConnectionSubscriber.onComplete(AsyncLock.java:497)
	at reactor.core.publisher.StrictSubscriber.onComplete(StrictSubscriber.java:123)
	at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2058)
	at org.reactivestreams.FlowAdapters$FlowToReactiveSubscriber.onComplete(FlowAdapters.java:221)
	at oracle.jdbc.internal.CompletionStageUtil$IteratorSubscription.emitComplete(CompletionStageUtil.java:805)
	at oracle.jdbc.internal.CompletionStageUtil$BatchItemPublisher.subscribeToBatch(CompletionStageUtil.java:622)
	at oracle.jdbc.internal.CompletionStageUtil$BatchItemPublisher.lambda$subscribe$0(CompletionStageUtil.java:593)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
	at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:837)
	at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506)
	at java.base/java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:2073)
	at oracle.jdbc.driver.T4CTTIfun.lambda$doRPCAsync$0(T4CTTIfun.java:348)
	at oracle.jdbc.driver.T4CTTIfun.lambda$receiveRPCAsync$1(T4CTTIfun.java:474)
	at oracle.jdbc.driver.RestrictedLock.lambda$runUnrestricted$0(RestrictedLock.java:428)
	at oracle.jdbc.driver.RestrictedLock.callUnrestricted(RestrictedLock.java:447)
	at oracle.jdbc.driver.RestrictedLock.runUnrestricted(RestrictedLock.java:427)
	at oracle.jdbc.driver.PhysicalConnection.lambda$initializeAsyncExecutor$4(PhysicalConnection.java:1270)
	at java.base/java.util.concurrent.ForkJoinTask$RunnableExecuteAction.exec(ForkJoinTask.java:1426)
	at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:290)
	at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1020)
	at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1656)
	at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1594)
	at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:183)
Caused by: oracle.r2dbc.impl.OracleR2dbcExceptions$OracleR2dbcException: Warning: execution completed with warning
	at oracle.r2dbc.impl.OracleR2dbcExceptions.toR2dbcException(OracleR2dbcExceptions.java:217)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
	at java.base/java.util.stream.Stream$2.tryAdvance(Stream.java:1301)
	at java.base/java.util.stream.StreamSpliterators$WrappingSpliterator.lambda$initPartialTraversalState$0(StreamSpliterators.java:294)
	at java.base/java.util.stream.StreamSpliterators$AbstractWrappingSpliterator.fillBuffer(StreamSpliterators.java:206)
	at java.base/java.util.stream.StreamSpliterators$AbstractWrappingSpliterator.doAdvance(StreamSpliterators.java:161)
	at java.base/java.util.stream.StreamSpliterators$WrappingSpliterator.tryAdvance(StreamSpliterators.java:300)
	at java.base/java.util.Spliterators$1Adapter.hasNext(Spliterators.java:681)
	at reactor.core.publisher.FluxIterable.subscribe(FluxIterable.java:133)
Caused by: oracle.r2dbc.impl.OracleR2dbcExceptions$OracleR2dbcException: Warning: execution completed with warning

	at reactor.core.publisher.FluxStream.subscribe(FluxStream.java:71)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8466)
	at reactor.core.publisher.FluxConcatArray$ConcatArraySubscriber.onComplete(FluxConcatArray.java:258)
	at reactor.core.publisher.FluxConcatArray.subscribe(FluxConcatArray.java:78)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8466)
	at reactor.core.publisher.FluxConcatArray$ConcatArrayDelayErrorSubscriber.onComplete(FluxConcatArray.java:443)
	at reactor.core.publisher.FluxConcatArray.subscribe(FluxConcatArray.java:73)
	at reactor.core.publisher.InternalFluxOperator.subscribe(InternalFluxOperator.java:62)
	at reactor.core.publisher.FluxDefer.subscribe(FluxDefer.java:54)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8466)
	at reactor.core.publisher.FluxFlatMap$FlatMapMain.onNext(FluxFlatMap.java:426)
	at reactor.core.publisher.FluxOnAssembly$OnAssemblySubscriber.onNext(FluxOnAssembly.java:539)
	at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:122)
	at reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onNext(MonoFlatMapMany.java:250)
	at reactor.core.publisher.FluxUsingWhen$UsingWhenSubscriber.onNext(FluxUsingWhen.java:345)
	at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
	at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:200)
	at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:122)
	at reactor.core.publisher.FluxConcatArray$ConcatArraySubscriber.onNext(FluxConcatArray.java:201)
	at reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onNext(MonoFlatMapMany.java:250)
	at reactor.core.publisher.Operators$ScalarSubscription.request(Operators.java:2398)
	at reactor.core.publisher.MonoFlatMapMany$FlatMapManyMain.onSubscribeInner(MonoFlatMapMany.java:150)
	at reactor.core.publisher.MonoFlatMapMany$FlatMapManyMain.onNext(MonoFlatMapMany.java:189)
	at reactor.core.publisher.MonoCreate$DefaultMonoSink.success(MonoCreate.java:172)
	at oracle.r2dbc.impl.AsyncLock.lambda$get$2(AsyncLock.java:163)
	at oracle.r2dbc.impl.AsyncLock.unlock(AsyncLock.java:122)
	at oracle.r2dbc.impl.AsyncLock$UsingConnectionSubscriber.terminate(AsyncLock.java:510)
	at oracle.r2dbc.impl.AsyncLock$UsingConnectionSubscriber.onComplete(AsyncLock.java:496)
	at reactor.core.publisher.StrictSubscriber.onComplete(StrictSubscriber.java:123)
	at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2058)
	at org.reactivestreams.FlowAdapters$FlowToReactiveSubscriber.onComplete(FlowAdapters.java:221)
	at oracle.jdbc.internal.CompletionStageUtil$IteratorSubscription.emitComplete(CompletionStageUtil.java:805)
	at oracle.jdbc.internal.CompletionStageUtil$IteratorSubscription.emitItems(CompletionStageUtil.java:752)
	... 6 common frames omitted
Caused by: java.sql.SQLWarning: Warning: execution completed with warning
	at oracle.jdbc.driver.DatabaseError.addSqlWarning(DatabaseError.java:1078)
Caused by: java.sql.SQLWarning: Warning: execution completed with warning

	at oracle.jdbc.driver.DatabaseError.addSqlWarning(DatabaseError.java:1122)
	at oracle.jdbc.driver.DatabaseError.addSqlWarning(DatabaseError.java:1135)
	at oracle.jdbc.driver.T4CPreparedStatement.handleOALL8Failure(T4CPreparedStatement.java:478)
	at oracle.jdbc.driver.T4CPreparedStatement.lambda$doOall8Async$0(T4CPreparedStatement.java:221)
	at oracle.jdbc.driver.T4C8Oall.lambda$doOALLAsync$0(T4C8Oall.java:583)
	at oracle.jdbc.driver.T4CTTIfun.lambda$receiveRPCAsync$1(T4CTTIfun.java:474)
	at oracle.jdbc.driver.RestrictedLock.lambda$runUnrestricted$0(RestrictedLock.java:428)
	at oracle.jdbc.driver.RestrictedLock.callUnrestricted(RestrictedLock.java:447)
	at oracle.jdbc.driver.RestrictedLock.runUnrestricted(RestrictedLock.java:427)
	at oracle.jdbc.driver.PhysicalConnection.lambda$initializeAsyncExecutor$4(PhysicalConnection.java:1270)
	... 6 common frames omitted
2022-09-09 15:17:17,034 INFO  [reactor-http-nio-3] com.gic.eagle.txnHld.handler.VWODAHoldingEglHandler: trying to get

Originally raised on https://github.com/spring-projects/spring-data-r2dbc/issues/784

rathoreamrsingh avatar Sep 13 '22 07:09 rathoreamrsingh

Thanks @rathoreamrsingh, this issue identifies a problem with Oracle R2DBC: We don't have a way to filter warnings.

To correct this, I'll need to declare a new interface: oracle.r2dbc.OracleR2dbcWarning

With this interface, user code we be able to filter warnings like this:

result.filter(segment -> !(segment instanceof OracleR2dbcWarning))

I'll hope to find time for making this fix soon.

Michael-A-McMahon avatar Sep 14 '22 19:09 Michael-A-McMahon

As a temporary workaround, you might checking for a SQLWarning as a cause:

result.filter(segment -> 
  !(segment instance of Result.Message)
  || !(((Result.Message)segment).exception().getCause() instanceof SQLWarning))

This is ugly though. I'll still want to make the proposed fix.

Michael-A-McMahon avatar Sep 14 '22 20:09 Michael-A-McMahon

Hi @Michael-A-McMahon Thanks a lot for the update. Please update it soon Thanks

rathoreamrsingh avatar Sep 19 '22 07:09 rathoreamrsingh

Hi @Michael-A-McMahon, I tried the workaround provided by you but it doesn't seems to be working, I am getting the same result. Is it possible for you to put whole class code? Just wanted to check I am importing and using the correct classes. Thanks

rathoreamrsingh avatar Sep 19 '22 07:09 rathoreamrsingh

Hi @rathoreamrsingh, looks like I typed some code that didn't compile ("instance of").

Below is a full class that does compile (note the TODOs):

import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.Result;
import reactor.core.publisher.Flux;

import java.sql.SQLWarning;
import java.time.Duration;
import java.time.LocalDateTime;

public class Warning {
  public static void main(String[] args) {
    Flux.usingWhen(
      ConnectionFactories.get(
        // TODO: Change URL for your database
        "r2dbc:oracle://host:port/service-name")
        .create(),
      connection ->
        Flux.from(connection.createStatement(
          // TODO: Change <View_Name> to your view name
          "SELECT * from <View_Name> WHERE DATA_DT >= :P0_startDate AND DATA_DT <=:P1_endDate")
            .bind(0, LocalDateTime.now().minus(Duration.ofDays(2)))
            .bind(1, LocalDateTime.now().minus(Duration.ofDays(1)))
          .execute())
          .flatMap(result ->
            result.filter(segment -> !isWarning(segment))
              .getRowsUpdated()),
      Connection::close)
      .toStream()
      .forEach(System.out::println);
  }

  /**
   * @return {@code true} if a result Segment is a warning, or {@code false} if
   * not
   */
  private static boolean isWarning(Result.Segment segment) {
    if (!(segment instanceof Result.Message))
      return false;

    Result.Message message = (Result.Message) segment;
    return message.exception().getCause() instanceof SQLWarning;
  }


}

Michael-A-McMahon avatar Sep 20 '22 19:09 Michael-A-McMahon

Thanks a lot for the update.

rathoreamrsingh avatar Sep 22 '22 02:09 rathoreamrsingh

I have some code written to add the OracleR2dbcWarning class, and it seems to be working well.

I've just noticed that this issue was originally spotted in #82, but I failed to recognize it at the time.

So multiple programmers are having an issue with this: That's is a good indication that Oracle R2DBC is doing something wrong :(

I'm thinking the right thing to do is to make warnings an "opt-in" feature, rather than "opt-out". Looking at JDBC, warnings are "opt-in": They are not thrown from methods that execute SQL. Instead, you may "opt-in" by calling a getWarnings method.

I think we'd all be happier if Oracle R2DBC were to follow the same model. You may opt-in to receiving warnings by calling Result.flatMap(Function<Segment, T>). Your function may then consume Message segments and report any warnings it finds. But if you don't call faltMap, then you are opting out of receiving warnings. Oracle R2DBC would not emit them as exceptions with onError. Instead, it will just silently drop the warning.

Any thoughts about this? I hope this will be an improvement, but please let me know if you have other ideas.

Michael-A-McMahon avatar Sep 28 '22 17:09 Michael-A-McMahon

This seems to be a good solution to me. There will be some consistency between JDBC and R2DBC for this feature.

rathoreamrsingh avatar Oct 04 '22 05:10 rathoreamrsingh