CREATE SEQUENCE not working in Couchbase Community Edition 7.6?

[m.staack]

Hello!
We are attempting to implement a sequence on Couchbase Community Edition 7.6.1

We get different error messages depending on the approach used:

• command: CREATE SEQUENCE DOKNR if not exists start with 100000 increment by 1 maxvalue 999999
• reply: invalid sequence name (near line 1, column 17)

fully qualified sequence name:

• command: CREATE SEQUENCE Bucket_Staging._default.DOKNR if not exists start with 100000 increment by 1 maxvalue 999999
• reply: “code”: 19100, “msg”: “Sequence support is not enabled for Bucket_Staging”

Is this a limitation in Community Edition?
Or are we missing some preparatory steps here?
Thank you and best regards, Markus

m.staack@cenit.com

[dh]

You have to suitably qualify sequence names - if you want to use just ‘DOKNR’ then query_context must be set to identify a bucket & scope; sequences are scope-level entities (there are no global sequences).

Ref: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/createsequence.html#sequence

A bucket must have a system scope & collection to support sequences:

https://docs.couchbase.com/server/current/learn/data/scopes-and-collections.html#the-_system-scope-and-its-collections

and the error seen in your second attempt is raised when the system collection doesn’t exist:

https://github.com/couchbase/query/blob/0280d9c29e5f98e232e2d1e48a5fe958f4fa86cf/sequences/sequences.go#L134

But the system collection will exist for suitably migrated buckets, or buckets created under 7.6 even on Community Edition, e.g.

cbq> create sequence default._default.q1;
{
    "requestID": "6caf6edc-e4d7-4c06-a80d-efbef3f420c3",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "26.896623ms",
        "executionTime": "26.72549ms",
        "resultCount": 0,
        "resultSize": 0,
        "serviceLoad": 25
    }
}
cbq> select nextval for default._default.q1, ds_version();
{
    "requestID": "c4bfc2f4-27ab-4895-aed2-d0ffbc8c3882",
    "signature": {
        "$1": "number",
        "$2": "string"
    },
    "results": [
    {
        "$1": 0,
        "$2": "7.6.2-3721-community"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "18.604942ms",
        "executionTime": "18.469477ms",
        "resultCount": 1,
        "resultSize": 61,
        "serviceLoad": 25
    }
}

HTH.

[m.staack]

Hej!
Thank you for your reply - I do not understand yet:
We created all of our buckets using the “Add Bucket” Link in the UI accessed through the browser.
They have been created with

• Bucket Type=Couchbase
• Memory Quota=
• Advanced bucket settings:
• Replicas: Enable (1)
• Ejection Method: Value only
• Bucket Priority: Default
• Minimum Durability Level: none
• Auto-Compaction: OFF
• Flush: ON

When I display a bucket using the “Buckets” section in the Browser UI, scope names “_default” and “_system” are displayed:

So I don’t understand why you claim the system collection would not exist.
Thank you and best regards, Markus

[m.staack]

Here is a second screenshot displaying the scopes of a bucket:

Thank you and best regards, Markus

[mreiche]

@m.staack

CREATE SEQUENCE DOKNR if not exists

When creating the sequence, you need to select a bucket and scope in the Context (top right).

.

[m.staack]

Hello!
Thank you for your reply.
When I select a Bucket and a context on the top right of the Query editor page, I get the same error message as with the fully qualifed sequence name:
“Sequence support is not enabled for bucket NN”
Thank you and best regards, Markus

[dh]

You should not be struggling to create a sequence if the _query collection exists for your bucket; the screenshot does suggest it should but you haven’t expanded the the scope.

You can confirm the collection exists with:

SELECT `bucket` FROM system:all_keyspaces WHERE id = "_query"

Then of course you can only create sequences for buckets which have this collection.

I hoped my cut-n-paste of creating a sequence in CE would illustrate it is indeed possible - however I did demonstrate on 7.6.2 (as it was what I had to hand) and you did state 7.6.1. Sorry.

This seems to be a bug in 7.6.1; please try 7.6.2 or 7.6.3.

HTH.

[edit: [MB-61333] - Couchbase Cloud corrected this problem in 7.6.2]

[m.staack]

Thank you for your reply.
I even created my own scope “myscope” in the bucket where I desire to create the sequence and afterwards selected the bucket and the scope in the Query Editor page to reflect this.
I still get the same error message … will try with 7.6.2 now …
Thank you and best regards, Markus