Exception updating oc_filecache during scan

[gbeekmans]

The setup is Nextcloud 14.0.3 with database PostgreSQL 10.5

When running occ files:scan I get the error below. This only happens when scanning one particular user and there are a number of external SMB/CIFS storages defined for this user.

In total it's scanning about 44,000 files and 3,750 folders.

Hopefully somebody has some insight into this problem?

I am not stuck on using PostgreSQL if using MySQL is a better option for Nextcloud.

The error thrown is:

Exception during scan: An exception occurred while executing 'UPDATE "oc_filecache" SET "size"=? WHERE ("size" <> ? OR "size" IS NULL) AND "fileid" = ? ' with params [0, 0, 34831]:

SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block
#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(128): Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDOException))
#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(855): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDOPgSql\Driver), Object(Doctrine\DBAL\Driver\PDOException), 'UPDATE "oc_file...', Array)
#2 /var/www/nextcloud/lib/private/DB/Connection.php(195): Doctrine\DBAL\Connection->executeQuery('UPDATE "oc_file...', Array, Array, NULL)
#3 /var/www/nextcloud/lib/private/Files/Cache/Cache.php(319): OC\DB\Connection->executeQuery('UPDATE "oc_file...', Array)
#4 /var/www/nextcloud/lib/private/Files/Cache/Scanner.php(417): OC\Files\Cache\Cache->update(34831, Array)
#5 /var/www/nextcloud/lib/private/Files/Cache/Scanner.php(409): OC\Files\Cache\Scanner->scanChildren('Movies/Coco (20...', true, 3, 34831, true)
#6 /var/www/nextcloud/lib/private/Files/Cache/Scanner.php(409): OC\Files\Cache\Scanner->scanChildren('Movies', true, 3, 10425, true)
#7 /var/www/nextcloud/lib/private/Files/Cache/Scanner.php(338): OC\Files\Cache\Scanner->scanChildren('', true, 3, 317, true)
#8 /var/www/nextcloud/lib/private/Files/Utils/Scanner.php(245): OC\Files\Cache\Scanner->scan('', true, 3)
#9 /var/www/nextcloud/apps/files/lib/Command/Scan.php(172): OC\Files\Utils\Scanner->scan('/gbeekmans', true, NULL)
#10 /var/www/nextcloud/apps/files/lib/Command/Scan.php(249): OCA\Files\Command\Scan->scanFiles('gbeekmans', '/gbeekmans', false, Object(Symfony\Component\Console\Output\ConsoleOutput), false, true, false)
#11 /var/www/nextcloud/3rdparty/symfony/console/Command/Command.php(251): OCA\Files\Command\Scan->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#12 /var/www/nextcloud/core/Command/Base.php(162): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#13 /var/www/nextcloud/3rdparty/symfony/console/Application.php(946): OC\Core\Command\Base->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#14 /var/www/nextcloud/3rdparty/symfony/console/Application.php(248): Symfony\Component\Console\Application->doRunCommand(Object(OCA\Files\Command\Scan), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#15 /var/www/nextcloud/3rdparty/symfony/console/Application.php(148): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#16 /var/www/nextcloud/lib/private/Console/Application.php(213): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 /var/www/nextcloud/console.php(96): OC\Console\Application->run()
#18 /var/www/nextcloud/occ(11): require_once('/var/www/nextcl...')
#19 {main}

[nextcloud-bot]

GitMate.io thinks possibly related issues are #9462 (Exception during scan), #6608 (Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'UPDATE oc_filecache), #6160 (Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO "oc_filecache" ), #2813 (File is lock in Nextcloud 11 with Redis filelocking turned on), and #4853 (occ files:scan - an exception occures if folder or file name contains a '+' character.).

[kesselb]

Thanks for reporting 👍 Do you still see this issue? Some problems with transactions on pqsql were fixed with Nextcloud 16.

[gbeekmans]

No more issue. It was tracked down to an issue where certain database columns weren't set as bigint.

This relates to #12927 where you (kesselb) fixed the issue. This ticket should have been closed back then but I forgot to mention it.

[kesselb]

Thanks 👍