Files: Integrity constraint violation: 1062 Duplicate entry

Nextcloud version: 18.0.4
Operating system and version: Apache Docker Image from https://hub.docker.com/_/nextcloud

The issue you are facing:

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing ‘UPDATE oc_filecache SET storage = ?, path = ?, path_hash = ?, name = ?, parent = ? WHERE fileid = ?’ with params [2, “files_encryption/keys/files/\u00c9quipe/abc.pdf”, “517e045a8b3102882bba05159e674bc9”, “abc.pdf”, 201606, 201515]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘2-517e045a8b3102882bba05159e674bc9’ for key ‘fs_storage_path_hash’

As a result the file cannot be deleted and blocks synchronization from Nextcloud client with an message indicating that the file abc.pdf has been deleted from the server. The file shows when browsing with the web interface.

Is this the first time you’ve seen this error? Yes

Steps to replicate it:

I do not know how to replicate the problem

The output of your Nextcloud log in Admin > Logging: (slightly edited to remove sensitive information)

[webdav] Fatal: Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'UPDATE `oc_filecache` SET `storage` = ?, `path` = ?, `path_hash` = ?, `name` = ?, `parent` = ? WHERE `fileid` = ?' with params [2, "files_encryption\/keys\/files\/\u00c9quipe\/abc.pdf", "517e045a8b3102882bba05159e674bc9", "abc.pdf", 201606, 201515]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-517e045a8b3102882bba05159e674bc9' for key 'fs_storage_path_hash'

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-517e045a8b3102882bba05159e674bc9' for key 'fs_storage_path_hash' at <<closure>>

 0. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 169
    Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exception oc ... '", Doctrine\DBAL\Dr ... ]})
 1. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 145
    Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... '")
 2. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 1063
    Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "UPDATE `oc_file ... ?", {1: 2,2: "files_ ... 5})
 3. /var/www/html/lib/private/DB/Connection.php line 220
    Doctrine\DBAL\Connection->executeUpdate("UPDATE `oc_file ... ?", [2,"files_encryp ... 5], [2,2,2,2,1,1])
 4. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php line 203
    OC\DB\Connection->executeUpdate("UPDATE `oc_file ... 6", {dcValue1: 2,dcV ... 5}, {dcValue1: 2,dcV ... 1})
 5. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 215
    Doctrine\DBAL\Query\QueryBuilder->execute()
 6. /var/www/html/lib/private/Files/Cache/Cache.php line 656
    OC\DB\QueryBuilder\QueryBuilder->execute()
 7. /var/www/html/lib/private/Files/Cache/Cache.php line 579
    OC\Files\Cache\Cache->moveFromCache(OC\Files\Cache\HomeCache {}, "files_encryptio ... f", "files_encryptio ... f")
 8. /var/www/html/lib/private/Files/Cache/Updater.php line 196
    OC\Files\Cache\Cache->move("files_encryptio ... f", "files_encryptio ... f")
 9. /var/www/html/lib/private/Files/View.php line 333
    OC\Files\Cache\Updater->renameFromStorage(OCA\Files_Trashb ... }}, "files_encryptio ... f", "files_encryptio ... f")
10. /var/www/html/lib/private/Files/View.php line 828
    OC\Files\View->renameUpdate(OCA\Files_Trashb ... }}, OCA\Files_Trashb ... }}, "files_encryptio ... f", "files_encryptio ... f")
11. /var/www/html/lib/private/Encryption/Keys/Storage.php line 280
    OC\Files\View->rename("/heckel/files_e ... f", "/heckel/files_e ... f")
12. /var/www/html/lib/private/Files/Storage/Wrapper/Encryption.php line 285
    OC\Encryption\Keys\Storage->renameKeys("/heckel/files/É ... f", "/heckel/files/É ... f")
13. /var/www/html/lib/private/Files/Storage/Wrapper/Wrapper.php line 277
    OC\Files\Storage\Wrapper\Encryption->rename("/files/Équipe/E ... f", "/files/Équipe/E ... f")
14. /var/www/html/apps/files_sharing/lib/SharedStorage.php line 322
    OC\Files\Storage\Wrapper\Wrapper->rename("/files/Équipe/E ... f", "/files/Équipe/E ... f")
15. /var/www/html/lib/private/Files/Storage/Wrapper/Wrapper.php line 277
    OCA\Files_Sharing\SharedStorage->rename("Equipe juridiqu ... f", "Equipe juridiqu ... f")
16. /var/www/html/lib/private/Files/View.php line 814
    OC\Files\Storage\Wrapper\Wrapper->rename("Equipe juridiqu ... f", "Equipe juridiqu ... f")
17. /var/www/html/apps/dav/lib/Connector/Sabre/Directory.php line 440
    OC\Files\View->rename("/Équipe/Equipe  ... f", "/Équipe/Equipe  ... f")
18. /var/www/html/3rdparty/sabre/dav/lib/DAV/Tree.php line 158
    OCA\DAV\Connector\Sabre\Directory->moveInto("Cclusions_responsives_n°2.pdf", "files/LT ... f", OCA\DAV\Connector\Sabre\File {})
19. /var/www/html/3rdparty/sabre/dav/lib/DAV/CorePlugin.php line 642
    Sabre\DAV\Tree->move("files/LT ... f", "files/LT ... f")
20. <<closure>>
    Sabre\DAV\CorePlugin->httpMove(Sabre\HTTP\Reque ... "}, Sabre\HTTP\Response {})
21. /var/www/html/3rdparty/sabre/event/lib/EventEmitterTrait.php line 105
    call_user_func_array([Sabre\DAV\CorePlugin {},"httpMove"], [Sabre\HTTP\Requ ... }])
22. /var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php line 479
    Sabre\Event\EventEmitter->emit("method:MOVE", [Sabre\HTTP\Requ ... }])
23. /var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php line 254
    Sabre\DAV\Server->invokeMethod(Sabre\HTTP\Reque ... "}, Sabre\HTTP\Response {})
24. /var/www/html/apps/dav/lib/Server.php line 319
    Sabre\DAV\Server->exec()
25. /var/www/html/apps/dav/appinfo/v2/remote.php line 35
    OCA\DAV\Server->exec()
26. /var/www/html/remote.php line 165
    require_once("/var/www/html/a ... p")

MOVE /remote.php/dav/files/LT/abc.pdf

After running maintenance:repair -vv the problem is fixed: the file is no longer visible in the web interface and the Nextcloud clients synchronise. There was no data loss.

docker-compose exec -T -u www-data app bash -c 'php -f occ maintenance:repair -vv'                                            
Nextcloud is in maintenance mode - no apps have been loaded                                                                                                                  
                                                                                                                                                                             
 - Repair MySQL collation                                                                                                                                                    
     - All tables already have the correct collation -> nothing to do                                                                                                        
 - Repair mime types                                                                                                                                                         
 - Clean tags and favorites                                                                                                                                                  
     - 0 tags of deleted users have been removed.                                                                                                                            
     - 0 tags for delete files have been removed.                                                                                                                            
     - 0 tag entries for deleted tags have been removed.                                                                                                                     
     - 0 tags with no entries have been removed.                                                                                                                             
 - Repair invalid shares                                                                                                                                                     
 - Move .step file of updater to backup location                                                                                                                             
 - Fix potential broken mount points                                                                                                                                         
     - No mounts updated                                                                                                                                                     
 - Add log rotate job                                                                                                                                                        
 - Clear frontend caches                                                                                                                                                     
     - Image cache cleared                                                                                                                                                   
     - SCSS cache cleared                                                                                                                                                    
     - JS cache cleared                                                                                                                                                      
 - Clear every generated avatar on major updates                                                                                                                             
 - Add preview background cleanup job                                                                                                                                        
 - Queue a one-time job to cleanup old backups of the updater                                                                                                                
 - Cleanup invalid photocache files for carddav                                                                                                                              
 - Add background job to cleanup login flow v2 tokens                                                                                                                        
 - Remove potentially over exposing share links                             
     - No need to remove link shares.
 - Clear access cache of projects
 - Switches from deprecated "production" to "stable" update channel
 - Sets the enterprise logo
     - Repair step already executed
 - Reset generated avatar flag
 - Update name of the stored view
 - Fix component of birthday calendars
     - 0 birthday calendars updated.
 - Regenerating birthday calendars to use new icons and fix old birthday events without year
     - Repair step already executed
 - Fix broken values of calendar objects

 - Registering building of calendar search index as background job
     - Repair step already executed
 - Registering background jobs to update cache for webcal calendars
    0 [>---------------------------] < 1 sec     - Added 0 background jobs to update webcal calendars
 - Registering building of calendar reminder index as background job
     - Repair step already executed
 - Clean up orphan event and contact data
     - 0 events without a calendar have been cleaned up
     - 0 properties without an events have been cleaned up
     - 0 changes without a calendar have been cleaned up
     - 0 cached events without a calendar subscription have been cleaned up
     - 0 changes without a calendar subscription have been cleaned up
     - 0 contacts without an addressbook have been cleaned up
     - 0 properties without a contact have been cleaned up
     - 0 changes without an addressbook have been cleaned up
 - Remove activity entries of private events
     - Removed 0 activity entries
 - Write default encryption module configuration to the database
 - Fix the share type of guest shares when migrating from ownCloud
 - Copy the share password into the dedicated column
 - Set existing shares as accepted
 - Update OAuth token expiration times
 - Add background job to check for backup codes
 - Populating added database structures for workflows

I ran into the same problem again today and the solution did not work this time. I found an open issue that seems to match this problem exactly (the subject of the issue does not reflect the actual problem and will hopefully be changed):

After a few days (maybe less) the problem disappeared, i.e. the faulty files disappeared. There must be a background process fixing these issues somewhere… but where ?

On June 24th, 2020 the same problem happened again with a similar stack trace. As of today (June 29th, 2020) the ghost file is still present.

I ran maintenance:repair now. The problem is still present after the run. Let see if it disapears in the next 24h.

$ docker-compose exec -T -u www-data app php -f occ maintenance:repair -vv                         
 - Repair MySQL collation                                                                                                                         
     - All tables already have the correct collation -> nothing to do                                                                             
 - Repair mime types                                                                                                                              
 - Clean tags and favorites                                                                                                                       
     - 0 tags of deleted users have been removed.                                                                                                 
     - 0 tags for delete files have been removed.                                                                                                 
     - 0 tag entries for deleted tags have been removed.                                                                                          
     - 0 tags with no entries have been removed.                                                                                                  
 - Repair invalid shares                                                                                                                          
 - Move .step file of updater to backup location                                                                                                  
 - Fix potential broken mount points                                                                                                              
     - No mounts updated                                                                                                                          
 - Add log rotate job                                                                                                                             
 - Clear frontend caches                                                                                                                          
     - Image cache cleared                                                                                                                        
     - SCSS cache cleared                                                                                                                         
     - JS cache cleared                                                                                                                           
 - Clear every generated avatar on major updates   
 - Add preview background cleanup job
 - Queue a one-time job to cleanup old backups of the updater
 - Cleanup invalid photocache files for carddav
 - Add background job to cleanup login flow v2 tokens
 - Remove potentially over exposing share links
     - No need to remove link shares.
 - Clear access cache of projects
 - Switches from deprecated "production" to "stable" update channel
 - Sets the enterprise logo
     - Repair step already executed
 - Reset generated avatar flag
 - Update name of the stored view
 - Fix component of birthday calendars
     - 0 birthday calendars updated.
 - Regenerating birthday calendars to use new icons and fix old birthday events without year
     - Repair step already executed
 - Fix broken values of calendar objects

 - Registering building of calendar search index as background job
     - Repair step already executed
 - Registering background jobs to update cache for webcal calendars
     - Added 0 background jobs to update webcal calendars
 - Registering building of calendar reminder index as background job
     - Repair step already executed
 - Clean up orphan event and contact data
     - 0 events without a calendar have been cleaned up
    0 [>---------------------------] < 1 sec     - 0 properties without an events have been cleaned up
     - 0 changes without a calendar have been cleaned up
     - 0 cached events without a calendar subscription have been cleaned up
     - 0 changes without a calendar subscription have been cleaned up
     - 0 contacts without an addressbook have been cleaned up
     - 0 properties without a contact have been cleaned up
     - 0 changes without an addressbook have been cleaned up
 - Remove activity entries of private events
     - Removed 0 activity entries
 - Write default encryption module configuration to the database
 - Fix the share type of guest shares when migrating from ownCloud
 - Copy the share password into the dedicated column
 - Set existing shares as accepted
 - Update OAuth token expiration times
 - Add background job to check for backup codes
 - Populating added database structures for workflows

I ran a file:scan of the folder containing the entry to the non existent file and it fixed the issue.

$ docker-compose exec -T -u www-data app php -f occ files:scan -vvv --path='/MLA/files/Communication/Newsletter/Modèle Newsletter MLA'                  
Starting scan for user 1 out of 1 (MLA)                                                                                                                                                                
Path not found: /MLA/files/Communication/Newsletter/Modèle Newsletter MLA                                                                                                                              
                                                                                                                                                                                                       
+---------+-------+--------------+                                                                                                                                                                     
| Folders | Files | Elapsed time |                                                                                                                                                                     
+---------+-------+--------------+                                                                                                                                                                     
| 0       | 0     | 00:00:00     |                                                                                                                                                                     
+---------+-------+--------------+               
$ docker-compose exec -T -u www-data app php -f occ files:scan -vvv --path='/MLA/files/Communication/Newsletter/'                                       
Starting scan for user 1 out of 1 (MLA)                                                                                                                                                                
        Folder  /MLA/files/Communication/Newsletter                                                                                                                                                    
        File    /MLA/files/Communication/Newsletter/HTML modèle définitif                                                                                                                              
        File    /MLA/files/Communication/Newsletter/TUTO Newsletter.odt                                                                                                                                
        Folder  /MLA/files/Communication/Newsletter/Newsletters 2019                                                                                                                                   
        File    /MLA/files/Communication/Newsletter/Newsletters 2019/stats dossiers LA.png                        
...
        File    /MLA/files/Communication/Newsletter/Archives base html/Modèles SC/pied de page page d'abonnement NL .rtf
        File    /MLA/files/Communication/Newsletter/Archives base html/Modèles SC/entête page d'abonnement NL  (modèle SC).rtf

+---------+-------+--------------+
| Folders | Files | Elapsed time |
+---------+-------+--------------+
| 47      | 70    | 00:00:04     |
+---------+-------+--------------+

Assuming file:scan rebuilds the folders based on what is really on disk, it makes sense that it fixed the problem.