An exception occurred while executing 'UPDATE "oc_filecache" when mtime of file is (too far) in the future

[gbeekmans]

Nextcloud: 14.0.4
PostgreSQL: 10.5

Users have external CIFS storage added. Unsure if that makes a difference. I suspect it might not to be honest.

Running "php occ files:scan" fails with the following error:

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(330): 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(338): OC\Files\Cache\Scanner->scanChildren('Movies/Coco (20...', true, 3, 34831, true)
#6 /var/www/nextcloud/lib/private/Files/Utils/Scanner.php(245): OC\Files\Cache\Scanner->scan('Movies/Coco (20...', true, 3)
#7 /var/www/nextcloud/apps/files/lib/Command/Scan.php(172): OC\Files\Utils\Scanner->scan('/gbeekmans/file...', true, NULL)
#8 /var/www/nextcloud/apps/files/lib/Command/Scan.php(249): OCA\Files\Command\Scan->scanFiles('gbeekmans', '/gbeekmans/file...', true, Object(Symfony\Component\Console\Output\ConsoleOutput), false, true, false)
#9 /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))
#10 /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))
#11 /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))
#12 /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))
#13 /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))
#14 /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))
#15 /var/www/nextcloud/console.php(96): OC\Console\Application->run()
#16 /var/www/nextcloud/occ(11): require_once('/var/www/nextcl...')
#17 {main}

At the same time this is logged by PostgreSQL:

2018-12-08 20:16:13.391 MST [4850] nextcloud@nextcloud ERROR: value "4039369200" is out of range for type integer

2018-12-08 20:16:13.391 MST [4850] nextcloud@nextcloud STATEMENT: INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "parent", "checksum", "path_hash", "path", "name", "storage") VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)

2018-12-08 20:16:13.394 MST [4850] nextcloud@nextcloud ERROR: current transaction is aborted, commands ignored until end of transaction block

2018-12-08 20:16:13.394 MST [4850] nextcloud@nextcloud STATEMENT: UPDATE "oc_filecache" SET "size"=$1 WHERE ("size" <> $2 OR "size" IS NULL) AND "fileid" = $3

The only columns in the UPDATE statement that are of type integer are "mtime", "storage_mtime" and "permissions".

The value that it has an issue with (4039369200) is either mtime or storage_mtime. This number converts to date "December 31 2097" which is also the actual date the file in question has. Obviously that file has its date set wrong. This shouldn't make the scanners crash, though.

An integer's max value is 2147483647 which converts to a date of January 19 2038. In 20 years Nextcloud is going to have actual issues? Maybe now is the time to fix this. Perhaps "bigint" is more appropriate and would fix these issues.

After I fixed the file's date stamp the files:scan ran properly.

[kesselb]

An integer's max value is 2147483647 which converts to a date of January 19 2038. In 20 years Nextcloud is going to have actual issues? Maybe now is the time to fix this. Perhaps "bigint" is more appropriate and would fix these issues.

https://docs.nextcloud.com/server/14/admin_manual/configuration_database/bigint_identifiers.html

Obviously that file has its date set wrong. This shouldn't make the scanners crash, though.

True

[gbeekmans]

https://docs.nextcloud.com/server/14/admin_manual/configuration_database/bigint_identifiers.html

This issue occurs on a fresh install of Nextcloud 14 as well.

To be 100% sure, I just installed Nextcloud 14 again on a new VM using instructions found at:

https://docs.nextcloud.com/server/14/admin_manual/installation/source_installation.html

I tried both the GUI installation as well as using an occ command like:

sudo -u www-data php occ maintenance:install --database pgsql --database-name "nextcloud" --database-user "nextcloud"
--database-pass "nextcloudpassword" --admin-user "admin" --admin-pass "adminpassword"

In both cases, the oc_filecache table is created with integer type columns.

I believe the issue is located in the file core/Migrations/Version13000Date20170718121200.php -- the mtime and storage_mtime columns are created when that migration step is run.

The manual page you referenced implies that the bigint issue would only occur if one upgraded from an older Nextcloud version. However, I would think that this should already be setup when a database is initialized from scratch?

If that isn't practical to change as a default maybe the installation page can be updated to mention that the db:convert-filecache-bigint can/should be run during initial installation as the database is still empty at that time so it won't take any time at all to apply the change.

[kesselb]

Ref #8412 mtime and storage_mtime has been added to bigint migration with this pr.

I would think that this should already be setup when a database is initialized from scratch?

Sounds good to me. Would you mind to open a pull request for this?

[gbeekmans]

I'm not familiar enough with the Nextcloud code to figure out where to add that kind of logic and which conventions to use. I suppose a new file should be added to core/Migrations and that file could do a check for existing data in the 'filecache' table and if empty, apply the correction to change from integer to bigint? That seems a little hackish.

Maybe the original file (Version13000Date20170718121200.php) that sets it to 'integer' to begin with should be modified or replaced with an updated file altogether? This is where I'm not sure how Nextcloud works in that regard and how the developers intended those migrations to function.

[bertmelis]

While converting my NC15 installation from MySQL (MariaDB) to PostgreSQL I also encountered this issue. columns mtime and storage_mtime were defined as int4. As I had 3 files with timestamps that didn't fit in these fields (which is another -perhaps the real- issue), the convert was halted.

I just deleted the entries from the MariaDB instance, ran the convert command again and completed the migration.

After the migration I ran the bigint command with following output:

$ sudo -u www-data php occ db:convert-filecache-bigint
Following columns will be updated:

* filecache.mtime
* filecache.storage_mtime

So now they're int8.