Internal Server Error on first launch - lastval() issue?

I’m trying to get Nextcloud running with CockroachDB instead of Postgresql. Cockroach is largely postegres compatible. I’m finding that upon the initial run (docker compose up) I get the opening page where I set the admin user and password. But then it just spins for a long time and finally results in an Internal Server Error.

From the Nextcloud logs, the only error I see is

"File": "/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php",
        "Line": 26,
        "Previous": {
          "Exception": "PDOException",
          "Message": "SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR:  lastval(): lastval is not yet defined in this session",
          "Code": "55000",
          "Trace": [
            {
              "file": "/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php",
              "line": 82,
              "function": "query",
              "class": "PDO",
              "type": "->",
              "args": [
                "SELECT lastval()"
              ]

Will this error result in an Internal Server Error? Or is some other problem occurring that is not being logged?

btw, I DO see tables created in the database during this initial setup so communication does appear to be working. (Cockroach has a nice dashboard)

tl;dr;
The fix is to run this command to make CockroachDB behave more like PostgreSQL.

docker-compose exec <DB-CONTAINER-NAME> bash -c \
    'cockroach sql --url="$(cat server.url)" --execute \'SET CLUSTER SETTING sql.defaults.serial_normalization = "virtual_sequence";\''

# Now dump the database and re-create it
docker-compose exec <DB-CONTAINER-NAME> bash -c \
    'cockroach sql --url="$(cat server.url)" --execute \'DROP DATABASE IF EXISTS nextcloud; CREATE DATABASE nextcloud;\''

What I have figured out
I, too, was getting this error, but after 4-5 nights of debugging and searching the web, I now know what the problem is.

I am new to CockroachDB and there is mostly the pain of it all.

By default CockroachDB have disabled the function lastval(), this is done for preformants. So to resolve this problem, one has to change the serial_normalization from rowid to virtual_sequence.
Sources:

Now based on what I have read, people keep saying that one should be using the SQL feature RETURNING instead lastval(), cause of performance and does work with CockroachDB default setting, I tested it by modifying some of the code in Nextcloud for testing, quickly realized that it would need to fix this multiple places.
So, I have dropped it for now, my interest is to try to use CockroachDB as a PostgreSQL replacement and see how it performs and of course if I run into other errors.

I am however curious to why Nextcloud seems to use lastval() instead of RETURNING since the using of lastval() require 2 database request there RETURNING only requires 1.
I assume that one of the other database doesn’t support RETURNING, but I don’t know.

Anyway, hope this helps :slight_smile: