[Solved] Fresh install - user login fails - Doctrine\\DBAL\\DBALException - Error 42601

🚧 Installation
, ,
1

Hi all, 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 i 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]:\n\nSQLSTATE[42601]: Syntax error: 7 FEHLER:  Syntaxfehler bei »ON«\nLINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...\n                                                             ^",
    "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]:\n\nSQLSTATE[42601]: Syntax error: 7 FEHLER:  Syntaxfehler bei »ON«\nLINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...\n                                                             ^",
          {
            "errorInfo": [
              "42601",
              7,
              "FEHLER:  Syntaxfehler bei »ON«\nLINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...\n                                                             ^"
            ],
            "__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«\nLINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...\n                                                             ^"
            ],
            "__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]:\n\nSQLSTATE[42601]: Syntax error: 7 FEHLER:  Syntaxfehler bei »ON«\nLINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...\n                                                             ^"
        ]
      },
      {
        "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«\nLINE 1: ..._locks\" (\"key\", \"lock\", \"ttl\") VALUES($1, $2, $3) ON CONFLIC...\n                                                             ^"
            ],
            "__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.

What’s going on?

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.

2

See also https://stackoverflow.com/q/56689560/342546 …

3

As i wrote on StackExchange, the problem is caused by a postgres version that has reached EOL but is nevertheless part of Centos 7. It seems that NC16 uses some SQL features that require postgres >= 9.5. It’s quite easy to upgrade postgres manually (see https://altinukshini.wordpress.com/2018/05/03/upgrade-postgres-9-2-to-9-6-in-centos-7-bold-lines/). Upgrading PostgreSQL to 9.6 solved the issue i’ve encountered.

1 Like