Upgrading Couchbase Docker Container Yields Different Application Behavior

Couchbase Server
,
1

I’m using an application on Java 11 and the Java SDK 3.4.7. I use the Couchbase Docker container for local development as well as run integration tests against. Everything works as expected when using 7.6.1 and previous versions. I simply upgraded the container to 7.6.3 and it broke some application tests. The ORDER BY clause is no longer working as expected from the application.

Sample document data model:
ID: MEMCW:999:A29963521B99

{
    "propCode": "MEMCW",
    "roomNumber": "999",
    "macAddress": "A29963521B99",
    "lastReportedTime": 1603715193,
    "lastStatus": {
        "serial": "A11B07C0000405",
        "hardware_info": {
            "display": {
                "status": 1,
                "cec_status": 0,
                "mpi_status": 1,
                "cable_status": 0,
                "mpi_tv_status": "ON",
                "edid_product_code": "0001",
                "edid_product_name": "LG",
                "edid_serial_number": "01010101",
                "status_last_change": 1625080667,
                "edid_manufacture_week": "1",
                "edid_manufacture_year": "2019",
                "edid_manufacturer_name": "GSM",
                "mpi_tv_status_last_change": 1625080671
            }
        }
    }
}

Query:

SELECT propCode,
       roomNumber,
       macAddress,
       lastReportedTime
FROM lastStatuses AS hdmiDisconnect
WHERE hdmiDisconnect.propCode = $propCode
    AND lastStatus.hardware_info.display.status = 1
    AND lastStatus.hardware_info.display.cable_status = 0
    AND MILLIS_TO_STR(hdmiDisconnect.lastReportedTime * 1000, '1111-11-11') BETWEEN $startDate AND $endDate
ORDER BY hdmiDisconnect.lastReportedTime DESC
OFFSET $offset
LIMIT $limit;

These are non-covering indexes in place for other services.

CREATE INDEX index_lastStatuses_lastReportedTime ON connectedroom.ecmp.lastStatuses(lastReportedTime);
CREATE INDEX index_lastStatuses_composite ON connectedroom.ecmp.lastStatuses(propCode, roomNumber, macAddress);

When I query from the localhost container UI, it works as expected sorting by lastReportedTime DESC.

Couchbase Docker container 7.6.3 query UI results:

[
  {
    "propCode": "MEMCW",
    "roomNumber": "141",
    "macAddress": "A81843521B21",
    "lastReportedTime": 1603854693
  },
  {
    "propCode": "MEMCW",
    "roomNumber": "444",
    "macAddress": "A29963521B22",
    "lastReportedTime": 1603754693
  },
  {
    "propCode": "MEMCW",
    "roomNumber": "999",
    "macAddress": "A29963521B99",
    "lastReportedTime": 1603715193
  },
  {
    "propCode": "MEMCW",
    "roomNumber": "119",
    "macAddress": "A81843521B21",
    "lastReportedTime": 1603254693
  }
]

Application code:

@Override
public List<HdmiDisconnect> getAllHdmiDisconnects(String propCode,
    ConnectedRoomAnalyticsApi.GetAllHdmiDisconnectsParams getAllHdmiDisconnectsParams, int offset, int limit) {

  final String statement =
      "       SELECT propCode,"
          + "        roomNumber,"
          + "        macAddress,"
          + "        lastReportedTime"
          + " FROM " + LAST_STATUSES + " AS hdmiDisconnect"
          + " WHERE hdmiDisconnect.propCode = $propCode"
          + "    AND lastStatus.hardware_info.display.status = 1"
          + "    AND lastStatus.hardware_info.display.cable_status = 0"
          + "    AND MILLIS_TO_STR(hdmiDisconnect.lastReportedTime * 1000, '1111-11-11')"
          + "      BETWEEN $startDate AND $endDate"
          + " ORDER BY hdmiDisconnect.lastReportedTime DESC"
          + " OFFSET $offset"
          + " LIMIT $limit";

  try {
    return scope.query(
            statement,
            queryOptions()
                .parameters(JsonObject.create()
                    .put("propCode", propCode)
                    .put("startDate", getAllHdmiDisconnectsParams.getStartDate().toString())
                    .put("endDate", getAllHdmiDisconnectsParams.getEndDate().toString())
                    .put("offset", offset)
                    .put("limit", limit))
                .scanConsistency(QueryScanConsistency.REQUEST_PLUS)
                .metrics(true)
                .readonly(true)
                .adhoc(false))
        .rowsAs(HdmiDisconnect.class);
  } catch (CouchbaseException e) {
    LOGGER.error("query or index error: " + e.getMessage());
    throw new DeviceDataRepositoryException();
  }
}

Application query results in different order:

[
    {
        "propCode": "MEMCW",
        "roomNumber": "119",
        "macAddress": "A81843521B21",
        "lastReportedTime": 1603254693
    },
    {
        "propCode": "MEMCW",
        "roomNumber": "141",
        "macAddress": "A81843521B21",
        "lastReportedTime": 1603854693
    },
    {
        "propCode": "MEMCW",
        "roomNumber": "444",
        "macAddress": "A29963521B22",
        "lastReportedTime": 1603754693
    },
    {
        "propCode": "MEMCW",
        "roomNumber": "999",
        "macAddress": "A29963521B99",
        "lastReportedTime": 1603715193
    }
]

After upgrading the container from 7.6.1 to 7.6.3, the application no longer sorts properly. This behavior occurs when using any container version greater than 7.6.1, 7.6.2 to 7.6.5. I see MB-63069 in the release notes but I’m not sure it applies?

Changing the Java SDK version from 3.4.7 to 3.7.9 doesn’t have any effect. This scenario can been seen across 8 tests. The sorting order appears random. Is there any obvious query behavior that may be causing this issue?

2

After upgrading the container from 7.6.1 to 7.6.3, the application no longer sorts properly.

I don’t understand - the first results with the caption “Couchbase Docker container 7.6.3 query UI results” appear to be in the correct order.

Can you open a case with customer support for them to investigate?

3

When the 7.6.3 container is running, I log into the container through the localhost:8091 UI and the query sorts properly. No change in behavior when interacting with the db directly.

When I curl the Java application endpoint or run the integration tests against the container, I receive a different sort order.

4

AFAIK, they all use the same rest endpoint. Something doesn’t add up. Can you look in http_access.log to see if the same request is logged for both?

Another thing we could do is setting the threshold for logging queries in system:completed_sessions to 1ms, run the query both ways and compare the logging in completed_sessions.

See Developer Portal | Couchbase
use completed-limit=1

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.