Nextcloud - 用户登录失败 - Doctrine \\ DBAL \\ DBALException - 错误42601

Question

I' currently trying to install nc16 (16.0.0.1) on a Centos 7 server with Apache, PostgreSQL and PHP 7.2.19. Installation worked so far as the admin user is created and can login.

Now, i've created a new group users and a new "user" user for non-admin usage. Seemed to work, too.

When i try to login as that new user, all i've got is a "Internal server error". Log tells about a DB-related problem:

{
  "reqId": "XQtjdz6@ZzXEMrXsLfs-vgAAAAE",
  "level": 3,
  "time": "2019-06-20T10:44:07+00:00",
  "remoteAddr": "x.y.z.a",
  "user": "...",
  "app": "index",
  "method": "GET",
  "url": "/index.php/apps/files/",
  "message": {
    "Exception": "Doctrine\\DBAL\\Exception\\SyntaxErrorException",
    "Message": "An exception occurred while executing 'INSERT INTO \"oc_file_locks\" (\"key\", \"lock\", \"ttl\") VALUES(?, ?, ?) ON CONFLICT DO NOTHING' with params [\"files\\/bc95cece8ec382e86defaa1ca64de62c\", -1, 1561031047]:

SQLSTATE[42601]: Syntax error: 7 FEHLER:  Syntaxfehler bei »ON«
LINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...
                                                             ^",
    "Code": 0,
    "Trace": [
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php",
        "line": 184,
        "function": "convertException",
        "class": "Doctrine\\DBAL\\Driver\\AbstractPostgreSQLDriver",
        "type": "->",
        "args": [
          "An exception occurred while executing 'INSERT INTO \"oc_file_locks\" (\"key\", \"lock\", \"ttl\") VALUES(?, ?, ?) ON CONFLICT DO NOTHING' with params [\"files\\/bc95cece8ec382e86defaa1ca64de62c\", -1, 1561031047]:

SQLSTATE[42601]: Syntax error: 7 FEHLER:  Syntaxfehler bei »ON«
LINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...
                                                             ^",
          {
            "errorInfo": [
              "42601",
              7,
              "FEHLER:  Syntaxfehler bei »ON«
LINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...
                                                             ^"
            ],
            "__class__": "Doctrine\\DBAL\\Driver\\PDOException"
          }
        ]
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php",
        "line": 158,
        "function": "wrapException",
        "class": "Doctrine\\DBAL\\DBALException",
        "type": "::",
        "args": [
          {
            "__class__": "Doctrine\\DBAL\\Driver\\PDOPgSql\\Driver"
          },
          {
            "errorInfo": [
              "42601",
              7,
              "FEHLER:  Syntaxfehler bei »ON«
LINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...
                                                             ^"
            ],
            "__class__": "Doctrine\\DBAL\\Driver\\PDOException"
          },
          "An exception occurred while executing 'INSERT INTO \"oc_file_locks\" (\"key\", \"lock\", \"ttl\") VALUES(?, ?, ?) ON CONFLICT DO NOTHING' with params [\"files\\/bc95cece8ec382e86defaa1ca64de62c\", -1, 1561031047]:

SQLSTATE[42601]: Syntax error: 7 FEHLER:  Syntaxfehler bei »ON«
LINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...
                                                             ^"
        ]
      },
      {
        "file": "/var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php",
        "line": 1088,
        "function": "driverExceptionDuringQuery",
        "class": "Doctrine\\DBAL\\DBALException",
        "type": "::",
        "args": [
          {
            "__class__": "Doctrine\\DBAL\\Driver\\PDOPgSql\\Driver"
          },
          {
            "errorInfo": [
              "42601",
              7,
              "FEHLER:  Syntaxfehler bei »ON«
LINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...
                                                             ^"
            ],
            "__class__": "Doctrine\\DBAL\\Driver\\PDOException"
          },
          "INSERT INTO \"oc_file_locks\" (\"key\", \"lock\", \"ttl\") VALUES(?, ?, ?) ON CONFLICT DO NOTHING",
          {
            "1": "files/bc95cece8ec382e86defaa1ca64de62c",
            "2": -1,
            "3": 1561031047
          }
        ]
      },
      {
        "file": "/var/www/nextcloud/lib/private/DB/Connection.php",
        "line": 216,
        "function": "executeUpdate",
        "class": "Doctrine\\DBAL\\Connection",
        "type": "->",
        "args": [
          "INSERT INTO \"oc_file_locks\" (\"key\", \"lock\", \"ttl\") VALUES(?, ?, ?) ON CONFLICT DO NOTHING",
          [
            "files/bc95cece8ec382e86defaa1ca64de62c",
            -1,
            1561031047
          ],
          [
            2,
            2,
            2
          ]
        ]
      },

(Stacktrace cut, i hope i didn't cut essential information - please tell if i did...)

Already tried to remove and re-create the user.

I didn't try to use MySQL yet - i would be glad if i could use PostgreSQL instead because it's already configured and running on the server, serving different web applications.

Answer 1

Found it myself:

The Error was caused by some SQL statements (ON CONFLICT) that requires PostgreSLQ version >= 9.5 (see https://stackoverflow.com/a/40327552 and https://github.com/nextcloud/server/issues/15441). On Centos 7, PostgreSQL 9.2.24 is installed. So, i suppose Nextcloud 16 won't install on a Centos 7 box with default postgres as db backend.

Solutions: - Use MariaDB / MySQL (i assume this should work, didn't test it myself); - Upgrade PostgresSQL to a newer version (tested, worked for me).

To upgrade PostgreSQL to 9.6, i've followed the steps proposed by Altin Ukshini on his blog. Please note: If you've used a different locale for the old postgres cluster (as i did), another step is required (see fifth comment - by Larry O'Neill - to Altins blog entry)!

Before step 4: /usr/pgsql-9.6/bin/postgresql96-setup initdb, you need to set the required locale using the env variable PGSETUP_INITDB_OPTIONS. In my case:

export PGSETUP_INITDB_OPTIONS=' --locale=de_DE.UTF-8'