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