CREATE SCOPE and CREATE COLLECTION in the same query

Couchbase Server
,
1

I’m running
Enterprise Edition 7.2.3 build 6705 locally in a container. Is it possible to create a scope and its collections in the same N1QL query? Neither Java SDK nor the Couchbase UI let me do this:

CREATE SCOPE default.store;
CREATE COLLECTION default.store.movies;
CREATE COLLECTION default.store.customers;

When trying to run the statements above in the query editor it returns:

[
  {
    "_sequence_num": 1,
    "_sequence_query": "CREATE SCOPE default.store;",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  },
  {
    "_sequence_num": 2,
    "_sequence_query": "\nCREATE COLLECTION default.store.movies;",
    "_sequence_query_status": "Internal Server Error",
    "_sequence_result": [
      {
        "code": 12021,
        "msg": "Scope not found in CB datastore default:default.store",
        "query_from_user": "explain \nCREATE COLLECTION default.store.movies;"
      }
    ]
  }
]

However, if I attempt to run the CREATE COLLECTION part for the second time, I get:

[
  {
    "_sequence_num": 1,
    "_sequence_query": "CREATE COLLECTION default.store.movies;",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  },
  {
    "_sequence_num": 2,
    "_sequence_query": "\nCREATE COLLECTION default.store.customers;",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  }
]
2

The creation and propagation of artifacts (buckets, scopes, collections, indexes etc) is not synchronous. So while you have made a request to “CREATE SCOPE default.store”, that scope may not exist an instant later. It’s even possible for one node to know about a just-created-scope, while the creation of the scope has not been propagated to another node.
You’ll run into a lot of this when writing tests, and be motivated to write various waitUntilxyz() utility methods.

1 Like
3

If that’s the case, what does it mean when a CompletableFuture returned from async().query() completes? Here, in this example, each operation takes ~45ms yet the CREATE COLLECTION statements still fail every few runs:

public static void main(String[] args) {
    var options = new AtomicReference<ClusterOptions>();
    var cluster = new AtomicReference<Cluster>();

    time("CONNECT", () -> {
        options.set(ClusterOptions.clusterOptions("default", "default")
            .environment(environment -> environment.maxNumRequestsInRetry(3)
                .loggingMeterConfig(config -> config.enabled(false))));
        cluster.set(Cluster.connect("127.0.0.1", options.get()));
    });

    time("AWAIT", () -> cluster.get().bucket("default").waitUntilReady(Duration.ofSeconds(5)));

    time("CREATE", () -> {
        var couchbase = cluster.get().async();
        couchbase.query("CREATE SCOPE default.store")
            .thenCompose(result -> couchbase.query("CREATE COLLECTION default.store.movies"))
            .thenCompose(result -> couchbase.query("CREATE COLLECTION default.store.customers"))
            .join();
    });

    time("CLOSE", () -> cluster.get().close());
}

private static void time(String sectionLabel, Runnable runnable) {
    try {
        var time = System.nanoTime();
        runnable.run();
        System.out.println(sectionLabel + ": " + (System.nanoTime() - time) / 1_000_000 + "ms");
    } catch (Exception error) {
        System.err.println(error.getMessage());
    }
}

CONNECT: 372ms
AWAIT: 264ms
CREATE: 132ms
CLOSE: 49ms

This seems like an eternity in CPU time for a mere request to create something asynchronously, considering we’re dealing with one node on localhost with the entirety of its data sitting on top of tmpfs.

4

It means the request has been sent to the server and the server accepted the request (i.e. there were no syntax errors in it). It doesn’t mean that the server has completed creating the scope/collection.

Here, in this example, each operation takes ~45ms yet the CREATE COLLECTION statements still fail every few runs

Yes. So you’d best make yourself a “waitUntilScopeExists(scopeName)” and and a “waitUntilCollectionExists(scopeName, collectionName)”.

SELECT * from system:scopes where name = ?
SELECT * from system:keyspaces where scope = ? and name = ?

However - when you start testing with multiple nodes - you’ll find that even though an artifact was created on one node, it may not yet be propagated to all nodes.

1 Like
5

Oh, I see… So in order to wait for that to happen, I should poll Couchbase using these queries until they return documents with datastore_id for every entry in system:datastores, right?

1 Like
6

Yes, that’s one way. I looked in our test code and we check the Manager api for scopes and collections:

/pools/default/buckets/{bucketName}/scopes

And look for the scope and collection in the result.

The Cluster can provide an httpClient.

    httpClient = cluster.httpClient();
    HttpTarget target = HttpTarget.manager();
    HttpPath path = HttpPath.of("pools/default/buckets/{}/scopes", "travel-sample");
    HttpResponse res = httpClient.get(target, path);
    System.out.println(res.contentAsString());
7

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