Couchbase query success and fail loop

For some reason, API return successful response and then error then success like this in loop for a new request.

Example…
request 1, success
request 2, fail
request 3, success
request 4, fail
and so on…

But call from Couchbase API always success

curl --location --request GET '127.0.0.1:8093/query/service?statement=SELECT%20META%28%60configs%60%29.id%20AS%20__id%2C%20META%28%60configs%60%29.cas%20AS%20__cas%2C%20storeNumber%2C%20countryCode%20FROM%20%60configs%60%20WHERE%20countryCode%20%3D%20%27TH%27%20AND%20isStoreActive%20%3D%20true%20AND%20storeNumber%20IS%20NOT%20NULL%20AND%20META%28%29.id%20IS%20NOT%20NULL%20AND%20META%28%29.cas%20IS%20NOT%20NULL%20AND%20%60_class%60%20%3D%20%22com.xxx.intl.common.api.entity.StoreEntity%22' \
--header 'Authorization: Basic ....'

What can be the root cause of this issue? However the exception throw from Couchbase

com.couchbase.client.core.error.InternalServerFailureException: Internal Couchbase Server error
	at com.couchbase.client.core.io.netty.query.QueryChunkResponseParser.errorsToThrowable(QueryChunkResponseParser.java:142)
    Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException: 
Error has been observed at the following site(s):
	|_ checkpoint ⇢ Handler com.xxx.yyy.controller.StoreController#getAllActiveStores(String, String, String, boolean) [DispatcherHandler]
Stack trace:
		at com.couchbase.client.core.io.netty.query.QueryChunkResponseParser.errorsToThrowable(QueryChunkResponseParser.java:142)
		at java.util.Optional.map(Optional.java:215)
		at com.couchbase.client.core.io.netty.query.QueryChunkResponseParser.error(QueryChunkResponseParser.java:117)
		at com.couchbase.client.core.io.netty.chunk.ChunkedMessageHandler.lambda$maybeCompleteResponseWithFailure$1(ChunkedMessageHandler.java:260)
		at java.util.Optional.orElseGet(Optional.java:267)
		at com.couchbase.client.core.io.netty.chunk.ChunkedMessageHandler.maybeCompleteResponseWithFailure(ChunkedMessageHandler.java:259)
		at com.couchbase.client.core.io.netty.chunk.ChunkedMessageHandler.channelRead(ChunkedMessageHandler.java:191)
		at com.couchbase.client.core.deps.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at com.couchbase.client.core.deps.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at com.couchbase.client.core.deps.io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at com.couchbase.client.core.deps.io.netty.channel.CombinedChannelDuplexHandler$DelegatingChannelHandlerContext.fireChannelRead(CombinedChannelDuplexHandler.java:436)
		at com.couchbase.client.core.deps.io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324)
		at com.couchbase.client.core.deps.io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296)
		at com.couchbase.client.core.deps.io.netty.channel.CombinedChannelDuplexHandler.channelRead(CombinedChannelDuplexHandler.java:251)
		at com.couchbase.client.core.deps.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at com.couchbase.client.core.deps.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at com.couchbase.client.core.deps.io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at com.couchbase.client.core.deps.io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
		at com.couchbase.client.core.deps.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at com.couchbase.client.core.deps.io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at com.couchbase.client.core.deps.io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
		at com.couchbase.client.core.deps.io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:795)
		at com.couchbase.client.core.deps.io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:475)
		at com.couchbase.client.core.deps.io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:378)
		at com.couchbase.client.core.deps.io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)
		at com.couchbase.client.core.deps.io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
		at com.couchbase.client.core.deps.io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
		at java.lang.Thread.run(Thread.java:748)

The thing that change recently on DB side is the data size from ten thousand of record to million records. but the output for the query still same around 1xx record.

Query

SELECT META(`configs`).id AS __id, META(`configs`).cas AS __cas, storeNumber, countryCode FROM `configs` WHERE countryCode = 'TH' AND isStoreActive = true AND storeNumber IS NOT NULL AND META().id IS NOT NULL AND META().cas IS NOT NULL AND `_class` = "com.xxx.intl.common.api.entity.StoreEntity"

Index

CREATE INDEX `adv_isStoreActive_countryCode_storeNumber` ON `configs`(`isStoreActive`,`countryCode`,`storeNumber`) WHERE (`_class` = "com.xxx.intl.common.api.entity.StoreEntity") WITH { "nodes":[ "xx.xx.xx.xx:8091","xx.xx.xx.yy:8091" ], "num_replica":1 }

Document

{
  "storeNumber": "9918",
  "associatedStoreNumber": 238,
  "ipAddress": "xx.yy.xx.zz",
  "isStoreActive": true,
  "associatedStore": {
    "partNumber": 0,
    "stream": "string",
    "codeLevel": 0
  },
  "handheldIpAddress": "172.30.86.1",
  "extendedDetails": {
    "blob": "string"
  },
  "countryCode": "UK",
  "latLong": "115.1100° N,100.9812° E",
  "storeFormat": "Super",
  "alternateIpAddresses": [],
  "storeName": "ABC Store",
  "currency": "บาท",
  "storelineDetails": {
    "password": "test123",
    "baseUrl": "someUrl.com",
    "username": "user"
  },
  "_class": "com.xxx.intl.common.api.entity.StoreEntity",
  "applications": []
}

Controller

@GetMapping("/status")
    public Mono<ResponseEntity<ResponseModel>> getAllActiveStores(
            @RequestHeader(value = "userId", required = true) String userId,
            @RequestHeader(value = "Authorization", required = true) String authorization,
            @RequestHeader(value = "countryCode", required = true) String countryCode,
            @RequestParam(name = "active", required = true) boolean active) {
        try {

            ServiceReqHeader serviceReqHeader = new ServiceReqHeader(authorization, countryCode, userId);
            validateReqHeader(serviceReqHeader);

            Mono<List<StoreEntity>> fluxStoreEntity = storeService
                    .getAllActiveStores(serviceReqHeader.getCountryCode(), active)
                    .collectList();

            Function<List<StoreEntity>, Mono<ResponseEntity<ResponseModel>>> function = storeEntityRes -> {
                try {
                    logger.debug(Constants.GENERATING_RESPONSE_BACK, storeEntityRes);
                    ResponseStoreListDataModel responseDataModel = new ResponseStoreListDataModel();
                    responseDataModel.setStoreEntities(storeEntityRes);
                    ResponseModel responseModel = ResponseModel.success(responseDataModel);

                    return Mono.just(new ResponseEntity<ResponseModel>(responseModel, HttpStatus.OK));
                } catch (Exception exception) {
                    logger.error(Constants.ERROR_WHILE_RENDING, exception);
                }
                return null;
            };

            return fluxStoreEntity.flatMap(function)
                    .switchIfEmpty(Mono.just(new ResponseEntity<ResponseModel>(
                            new ResponseModel(null, Constants.NO_RECORD_FOUND_CODE, Constants.NO_RECORD_MESSAGE),
                            HttpStatus.NOT_FOUND)));

        } catch (ResponseStatusException exception) {
            throw exception;
        } catch (Exception exception) {

            ResponseStatusException responseStatusException = new ResponseStatusException(
                    HttpStatus.INTERNAL_SERVER_ERROR,
                    Constants.PROCESSING_REQUEST_IN_CONTROLLER,
                    exception
            );

            logger.error(Constants.EXCEPTION_OCCURED_WHILE_PROCESSING_REQUEST, responseStatusException);
            throw responseStatusException;
        }
    }

Service

@Override
    public Flux<StoreEntity> getAllActiveStores(String countryCode, boolean active) throws CommonApiBaseException {
        try {
            logger.debug("finding all store  for Country Code : {}, for status: {}", countryCode, active);
            return storeRepository.getAllActiveStores(countryCode.toUpperCase(), active);
        } catch (ResponseStatusException exception) {
            throw exception;
        } catch (Exception exception) {
            ResponseStatusException responseStatusException = new ResponseStatusException(
                    HttpStatus.INTERNAL_SERVER_ERROR,
                    Constants.SERVICE_EXCEPTION,
                    exception
            );
            logger.error(Constants.EXCEPTION_OCCURED_WHILE_PROCESSING_REQUEST_SERVICE, responseStatusException);
            throw responseStatusException;
        }
    }

Repository

 @Query(
            "SELECT META().id as __id, META().cas as __cas, c.storeNumber, c.countryCode "
                    + "FROM `#{#n1ql.bucket}` c "
                    + "WHERE c.countryCode = $1 "
                    + "AND c.isStoreActive = $2 "
                    + "AND c.storeNumber IS NOT NULL "
                    + "AND META().id IS NOT NULL "
                    + "AND META().cas IS NOT NULL "
                    + "AND #{#n1ql.filter}"
    )
    Flux<StoreEntity> getAllActiveStores(String countryCode, boolean isStoreActive);

Dependency

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-couchbase-reactive</artifactId>
</dependency>



Enterprise Edition 6.0.1 build 2037

See if this helps anything.

CREATE INDEX `ix1` ON `configs`(`countryCode`, `isStoreActive`,`storeNumber`, META().cas)
WHERE (`_class` = "com.xxx.intl.common.api.entity.StoreEntity");

SELECT META(`configs`).id AS __id, META(`configs`).cas AS __cas, storeNumber, countryCode
FROM `configs`
WHERE `_class` = "com.xxx.intl.common.api.entity.StoreEntity"
      AND countryCode = 'TH'
      AND isStoreActive = true
      AND storeNumber IS NOT NULL;

Is there any significant between the current Query and the new suggest one?

Query from the Couchbase GUI or from Couchbase Rest API, there is no problem. but query from the Java code with Couchbase Reactive library generate the exception.

Not much difference. New one uses covering. “Java code with Couchbase Reactive library” out of my expertise.

For some reason, API return successful response and then error then success like this in loop for a new request.

Example…
request 1, success
request 2, fail
request 3, success
request 4, fail
and so on…

Two query nodes, one is bad and gives some “Internal Couchbase Server error”