Upgrade from 15.07 to 16 Stable issue

[geokh]

Nextcloud Version 16.
when you click on sharing option it keeps cycling with below issue in logs :

[files] Error: Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing 'INSERT INTO "oc_file_locks" ("key", "lock", "ttl") VALUES(?, ?, ?) ON CONFLICT DO NOTHING' with params ["files/e3d2428aae43133e6bb09452baedc26b", -1, 1557312380]:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "ON"
LINE 1: ..._locks" ("key", "lock", "ttl") VALUES($1, $2, $3) ON CONFLIC...
^ at <>

0. /data/www/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 184
   Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException("An exception oc ... ^", Doctrine\DBAL\Dr ... ]})
1. /data/www/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 158
   Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOPgSql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... ^")
2. /data/www/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 1088
   Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOPgSql\Driver {}, Doctrine\DBAL\Dr ... ]}, "INSERT INTO "o ... G", {1: "files/e3d24 ... 0})
3. /data/www/lib/private/DB/Connection.php line 216
   Doctrine\DBAL\Connection->executeUpdate("INSERT INTO "o ... G", ["files/e3d2428a ... 0], [2,2,2])
4. /data/www/lib/private/DB/AdapterPgSql.php line 49
   OC\DB\Connection->executeUpdate("INSERT INTO "o ... G", {dcValue1: "file ... 0}, {dcValue1: 2,dcValue2: 2,dcValue3: 2})
5. /data/www/lib/private/DB/Connection.php line 261
   OC\DB\AdapterPgSql->insertIgnoreConflict("file_locks", {key: "files/e3d ... 0})
6. /data/www/lib/private/Lock/DBLockingProvider.php line 139
   OC\DB\Connection->insertIgnoreConflict("file_locks", {key: "files/e3d ... 0})
7. /data/www/lib/private/Lock/DBLockingProvider.php line 200
   OC\Lock\DBLockingProvider->initLockField("files/e3d2428aae43133e6bb09452baedc26b", -1)
8. /data/www/lib/private/Files/Storage/Common.php line 715
   OC\Lock\DBLockingProvider->acquireLock("files/e3d2428aae43133e6bb09452baedc26b", 2)
9. /data/www/lib/private/Files/Storage/Wrapper/Wrapper.php line 593
   OC\Files\Storage\Common->acquireLock("scanner::appdat ... e", 2, OC\Lock\DBLockingProvider {})
10. /data/www/lib/private/Files/Cache/Scanner.php line 331
    OC\Files\Storage\Wrapper\Wrapper->acquireLock("scanner::appdat ... e", 2, OC\Lock\DBLockingProvider {})
11. /data/www/lib/private/Files/Cache/Scanner.php line 521
    OC\Files\Cache\Scanner->scan("appdata_ocmw6abd062r/dav-photocache", 2, 3)
12. /data/www/lib/private/Files/Cache/Scanner.php line 532
    OC\Files\Cache\Scanner->OC\Files\Cache{closure}("*** sensitive parameters replaced ***")
13. /data/www/lib/private/Files/Cache/Scanner.php line 522
    OC\Files\Cache\Scanner->runBackgroundScanJob(Closure {}, "appdata_ocmw6abd062r/dav-photocache")
14. /data/www/lib/private/Files/Utils/Scanner.php line 173
    OC\Files\Cache\Scanner->backgroundScan()
15. /data/www/apps/files/lib/BackgroundJob/ScanFiles.php line 88
    OC\Files\Utils\Scanner->backgroundScan("")
16. /data/www/apps/files/lib/BackgroundJob/ScanFiles.php line 112
    OCA\Files\BackgroundJob\ScanFiles->runScanner(OC\User\User {})
17. /data/www/lib/private/BackgroundJob/Job.php line 61
    OCA\Files\BackgroundJob\ScanFiles->run(null)
18. /data/www/lib/private/BackgroundJob/TimedJob.php line 55
    OC\BackgroundJob\Job->execute(OC\BackgroundJob\JobList {}, OC\Log {})
19. /data/www/cron.php line 146
    OC\BackgroundJob\TimedJob->execute(OC\BackgroundJob\JobList {}, OC\Log {})

GET /cron.php
from 10.1.11.176 at 2019-05-08T09:46:20+00:00

[kesselb]

Please provide the information from the issue template: https://github.com/nextcloud/server/blob/master/.github/ISSUE_TEMPLATE/Bug_report.md

From your stack trace i would guess you are using postgresql. We use ON CONFLICT DO NOTHING now to suppress some warnings on pqsql instances. This feature is available from pqsql 9.5. Are you using a older pqsql version?

Ref #13721.

[geokh]

Yes 9.2.24 and i went through the other page and checked all the files change and all of them i have them applied on my setup already. What do you suggest?

[kesselb]

9.2.24 is EOL anyway (https://www.postgresql.org/support/versioning/). Upgrade to a version >= 9.5.

A quick and dirty workaround:

Change:
$queryString = $builder->getSQL() . ' ON CONFLICT DO NOTHING';
To:
$queryString = $builder->getSQL();

[ShuffleBox]

I've also bumped into this using Postgresql 9.4 which is still under support.
Thank you for the lead on the issue and work around though.

[ShuffleBox]

TL;DR; - Database upgrade fixed the issue.
A followup on this - I encountered this issue on second instance of Nextcloud migrating to 16.
In addition to the file lock error messages, users observed that playing videos in the browser would not work, sharing tab resulted in spinny wheel, and attempting to download a video would result in an Internal Server error being displayed.

The CentOS 7 database server was running Postgresql 9.2 from the distribution repositories. The application server is Fedora 29 w/ php 7.2.

Upgrading from Postgresql 9.2 to Postgresql 11 from the Postgres repositories corrected the issue. I'll be migrating the 9.4 server (in support of another instance) mentioned in my previous post sometime next week.

A note that could be helpful for others was this issue with the pg_upgrade utility where it specifies a parameter of 'unix_socket_directory' rather than 'unix_socket_directories' which results in the utility failing to work. The modification to both 9.2 & 11 instances of pg_ctl specified in this stackexchange question got my migration to run correctly.

[lvlts]

Hi,

Can confirm this is also happening for PostgreSQL 9.4, which is the default in Debian Jessie (which is not an EOL distro). Same goes for the 9.4 version of PostgreSQL. Obviously, for these operating systems, there is no easy path of upgrading PostgreSQL.

Nextcloud documentation also states that PostgreSQL 9/10 is supported.

Edit:
This workaround, only applies to Debian Jessie and requires adding an additional apt repository, seems to upgrade the cluster to a newer version:

https://gist.github.com/dmitrykustov/27c673ec4f7abd716912e4c830910019

[esantoro]

Hi all,
I just wanted to say that I just wasted a good two-three hours troubleshooting a fresh install of nextcloud using the latest docker image on a fresh CentOS 7.6 install.

You might want to add a check or something and just let the user know at install time that PostgreSQL version below 9.5 are not supported.

That would have saved me a lot of time.

Thank you anyway for all the good work so far.

[kesselb]

cc @MorrisJobke do you have any figures how many people using pqsql 9.4? I think it's not the best upgrade experience if people needs to upgrade pqsql after nextcloud. Is it possible to block 16+ if pqsql 9.4 is used?

[MorrisJobke]

Is it possible to block 16+ if pqsql 9.4 is used?

Not as of now. Currently we only check the PHP version. 😢

cc @MorrisJobke do you have any figures how many people using pqsql 9.4? I think it's not the best upgrade experience if people needs to upgrade pqsql after nextcloud.

Not as of now.

[skjnldsv]

Closing in favour of #15613