UniqueConstraintViolationException during upgrade from 13.0.5 to 13.0.6 - sqlite3 database

[radoeka]

Steps to reproduce

1. Upgrade from 13.0.5 to 13.0.6
2. During the upgrade the error is reported

Expected behaviour

Succesful upgrade (as many times before)

Actual behaviour

Upgrade fails (unusual, the upgade is normally succesfull)

Server configuration

Operating system: linux 4.4 (openSUSE)

Web server: apache-2.4.23

Database: sqlite3 3.8.10

PHP version: 7.0.7

Nextcloud version: 13.0.5 to be upgraded to 13.0.6
Updated from an older Nextcloud/ownCloud or fresh install:

Where did you install Nextcloud from: Nextcloud.com

Nextcloud configuration:

Config report

$CONFIG = array (
  'datadirectory' => '/srv/owncloud',
  'dbtype' => 'sqlite3',
  'defaultapp' => 'bookmarks',
  'version' => '13.0.5.2',
  'installed' => true,
  'logfile' => '/var/log/nextcloud/nextcloud.log',
  'log_rotate_size' => 1048576,
  'loglevel' => 1,
  'theme' => '',
  'trusted_domains' => 
  array (
  ),
  'maintenance' => false,
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'forcessl' => true,
  'forceSSLforSubdomains' => true,
  'htaccess.RewriteBase' => '/',
  'trashbin_retention_obligation' => 'auto, 30',
);

Logs

Web server error log

Web server error log: Nothing special to report

Nextcloud log (data/nextcloud.log)

Nextcloud log

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO oc_filecache (fileid, storage, parent, name, mimetype, mimepart, mtime, encrypted, path, path_hash, etag, storage_mtime, permissions, size, unencrypted_size, checksum) SELECT fileid, storage, parent, name, mimetype, mimepart, mtime, encrypted, path, path_hash, etag, storage_mtime, permissions, size, unencrypted_size, checksum FROM __temp__oc_filecache':                                                                                          

SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: oc_filecache.fileid                                          
Update failed

Browser log

Browser log:

Nothing special to report

Discussed in the forum as well:
https://help.nextcloud.com/t/during-update-from-13-0-5-to-13-06-database-error-sqlstate-23000-integrity-constraint-violation-19-unique-constraint-failed-oc-filecache-fileid/36455/6

Advice was to open a ticket.

[nextcloud-bot]

GitMate.io thinks possibly related issues are #10350 (13.0.5), #10932 (13.0.6), #5515 (Upgrade fails 10.0.5 to 11.0.3 ), #7439 (Failed upgrade from 12.0.4 → 13.0.0.6), and #10885 (NC 14.0.0 beta 4, web login failed after upgrade from 13.0.5.2).

[radoeka]

When I emptied the oc_filecache table the update succeeded succesfully. Edit by @MorrisJobke: DON'T DO THIS - IT WILL CAUSE DAMAGE.

However after logging in, it was very much noticable that the cache had to be build again.
So initially the response from the server is slow.

[MorrisJobke]

When I emptied the oc_filecache table the update succeeded succesfully.

However after logging in, it was very much noticable that the cache had to be build again.
So initially the response from the server is slow.

🙈 This breaks your instance. Even if it says "cache" it is not really a cache, but an index (due to limitations on DB side we also don't want to rename the table as it would mean that the upgrade could take hours if not longer, because some DBs do a copy on rename and this table is the biggest one we have). Thus this table MUST NEVER BE EMPTIED.

If this is emptied all shares, comments, activities, etc are lost due to the fact that the index is dropped.

[MorrisJobke]

Close as the evidence what went wrong was deleted and we can't investigate what went wrong.