Deleting many files with the same name causes trashbin database error (integrity contstraint violation, duplicate entry)

[MarcelWaldvogel]

Steps to reproduce

1. Have the desktop sync client on a machine and the trashbin app on the server
2. In the synced directory, have many files with the same name but in different directories (e.g. using find . -type d -exec touch {}/_TEST_FILE_ \;)
3. Wait for these files to be synced to the server
4. Delete all of these files on the client within a short period of time (e.g. using find . -name _TEST_FILE_ -exec rm {} \;)

Expected behaviour

All the files are moved to the trashbin with no errors

Actual behaviour

• Several errors appear in the sync client activity log: Server replied "500 Server Error" to "DELETE https://[…]/_TEST_FILE_"
• Nextcloud server log shows many of these messages:

[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 [1, "files_trashbin\/files\/_TEST_FILE_.d1568906728", "2483968b8cffabc109c268695206d8c2", "_TEST_FILE_.d1568906728", 1451947, 1476273]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-2483968b8cffabc109c268695206d8c2' for key 'fs_storage_path_hash' at <<closure>>

 0. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 184
    Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException("An exception oc ... '", Doctrine\DBAL\Dr ... ]})
 1. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 158
    Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... '")
 2. /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 938
    Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOMySql\Driver {}, Doctrine\DBAL\Dr ... ]}, "UPDATE `oc_file ... ?", {1: 1,2: "files_ ... 3})
 3. /var/www/nextcloud/lib/private/DB/Connection.php line 195
    Doctrine\DBAL\Connection->executeQuery("UPDATE `oc_file ... ?", [1,"files_trashb ... 3], [], null)
 4. /var/www/nextcloud/lib/private/Files/Cache/Cache.php line 579
    OC\DB\Connection->executeQuery("UPDATE `oc_file ... ?", [1,"files_trashb ... 3])
 5. /var/www/nextcloud/lib/private/Files/Cache/Cache.php line 509
    OC\Files\Cache\Cache->moveFromCache(OC\Files\Cache\HomeCache {}, "files/[…]/_TEST_FILE_", "files_trashbin/ ... 8")
 6. /var/www/nextcloud/lib/private/Files/Cache/Updater.php line 196
    OC\Files\Cache\Cache->move("files/[…]/_TEST_FILE_", "files_trashbin/ ... 8")
 7. /var/www/nextcloud/apps/files_trashbin/lib/Trashbin.php line 273
    OC\Files\Cache\Updater->renameFromStorage(OCA\Files_Trashb ... }}, "files/[…]/_TEST_FILE_", "files_trashbin/ ... 8")
 8. /var/www/nextcloud/apps/files_trashbin/lib/Trash/LegacyTrashBackend.php line 107
    OCA\Files_Trashbin\Trashbin::move2trash("[…]/_TEST_FILE_")
 9. /var/www/nextcloud/apps/files_trashbin/lib/Trash/TrashManager.php line 103
    OCA\Files_Trashbin\Trash\LegacyTrashBackend->moveToTrash(OCA\Files_Trashb ... }}, "files/[…]/_TEST_FILE_")
10. /var/www/nextcloud/apps/files_trashbin/lib/Storage.php line 186
    OCA\Files_Trashbin\Trash\TrashManager->moveToTrash(OCA\Files_Trashb ... }}, "files/[…]/_TEST_FILE_")
11. /var/www/nextcloud/apps/files_trashbin/lib/Storage.php line 98
    OCA\Files_Trashbin\Storage->doDelete("files/[…]/_TEST_FILE_", "unlink")
12. /var/www/nextcloud/lib/private/Files/View.php line 1160
    OCA\Files_Trashbin\Storage->unlink("files/[…]/_TEST_FILE_")
13. /var/www/nextcloud/lib/private/Files/View.php line 716
    OC\Files\View->basicOperation("unlink", "/[…]/_TEST_FILE_", ["delete"])
14. /var/www/nextcloud/apps/dav/lib/Connector/Sabre/File.php line 422
    OC\Files\View->unlink("/[…]/_TEST_FILE_")
15. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Tree.php line 179
    OCA\DAV\Connector\Sabre\File->delete()
16. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php line 287
    Sabre\DAV\Tree->delete("files/marcel/Me ... _")
17. <<closure>>
    Sabre\DAV\CorePlugin->httpDelete(Sabre\HTTP\Reque ... "}, Sabre\HTTP\Response {})
18. /var/www/nextcloud/3rdparty/sabre/event/lib/EventEmitterTrait.php line 105
    undefinedundefinedcall_user_func_array([Sabre\DAV\CorePlugin {},"httpDelete"], [Sabre\HTTP\Requ ... }])
19. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php line 479
    Sabre\Event\EventEmitter->emit("method:DELETE", [Sabre\HTTP\Requ ... }])
20. /var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php line 254
    Sabre\DAV\Server->invokeMethod(Sabre\HTTP\Reque ... "}, Sabre\HTTP\Response {})
21. /var/www/nextcloud/apps/dav/lib/Server.php line 316
    Sabre\DAV\Server->exec()
22. /var/www/nextcloud/apps/dav/appinfo/v2/remote.php line 35
    OCA\DAV\Server->exec()
23. /var/www/nextcloud/remote.php line 163
    undefinedundefinedrequire_once("/var/www/nextcl ... p")

DELETE /remote.php/dav/files/marcel/[…]/_TEST_FILE_
from 1.2.3.4 by marcel at 2019-09-19T15:25:28+00:00

It seems that — within one second — only one file with the same name may be moved into the trashbin. Appending .d<current second> to the file name does not provide enough uniqueness.

Server configuration

Nextcloud version: 16.04

[kesselb]

cc @rullzer @icewind1991

[MarcelWaldvogel]

If you do not already have many directories on your client in the sync directory before running find […] touch […] above, create them e.g. as follows:

mkdir manydirs
for i in a b c d e f g h i j k l m n o p q r s t u v w x y z; do
  mkdir manydirs/$i
  for j in a b c d e f g h i j k l m n o p q r s t u v w x y z; do
    mkdir manydirs/$i/$j
  done
done

[J0WI]

I don't think that this is limited to the trashbin feature. I've also seen this fs_storage_path_hash issue with other bulk actions with parallel connected clients, like mass thumbnail image generation or rapid file changes.

[papimla]

I ran into this issue today. The file in question still shows when using the web interface but cannot be opened (throwing a failed to open stream: No such file or directory at /var/www/html/lib/private/Files/Storage/Local.php#302 error when trying). It also triggers an error when the Nextcloud client tries to sync. Here is the (redacted for sensitive information) stack trace. Running Nextcloud 18.04

[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\/*SENSITIVE*", "3146c5db70e331a9dc2c92f04bccda98", "*SENSITIVE*", 207242, 82801]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-3146c5db70e331a9dc2c92f04bccda98' 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_ ... 1})
 3. /var/www/html/lib/private/DB/Connection.php line 220
    Doctrine\DBAL\Connection->executeUpdate("UPDATE `oc_file ... ?", [2,"files_encryp ... 1], [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 ... 1}, {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("/*SENSITIVE*/files_e ... f", "/*SENSITIVE*/files_e ... f")
12. /var/www/html/lib/private/Files/Storage/Wrapper/Encryption.php line 285
    OC\Encryption\Keys\Storage->renameKeys("/*SENSITIVE*/files/É ... f", "/*SENSITIVE*/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("*SENSITIVE* ... f", "*SENSITIVE* ... f")
16. /var/www/html/lib/private/Files/View.php line 814
    OC\Files\Storage\Wrapper\Wrapper->rename("*SENSITIVE* ... f", "*SENSITIVE* ... 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("*SENSITIVE* ... f", "files/*SENSITIVE* ... f", OCA\DAV\Connector\Sabre\File {})
19. /var/www/html/3rdparty/sabre/dav/lib/DAV/CorePlugin.php line 642
    Sabre\DAV\Tree->move("files/*SENSITIVE* ... f", "files/*SENSITIVE* ... 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/*SENSITIVE* from 172.19.0.1 by *SENSITIVE* at 2020-06-16T22:12:32+00:00

[papimla]

Dare I suggest the title of the issue is renamed to better reflect the problem? Something like Integrity constraint violation after moving files maybe?

[papimla]

See also the forum entry matching this bug, with another identical report that is a month old https://help.nextcloud.com/t/files-integrity-constraint-violation-1062-duplicate-entry/81425

[MarcelWaldvogel]

@papimla: Can you explain what you did? In my case, I believe it is actually caused by the trashbin operation, not by a general move. Before changing the title to include a "move", I would like to understand what happens and whether it should go into this issue or into a new issue.

When a file called ExampleFile.txt is moved into the trashbin, it is moved to the Trashbin folder and renamed to ExampleFile.txt.d1593075967, where 1593075967 is the current second since 1970. So if two files named ExampleFile.txt are trashed at the same time, they both get the same file name in the trashbin.

Does your operation (probably a move) cause the same name duplication? Can you construct an example case and how it differs from my example?

[papimla]

@MarcelWaldvogel I don't remember exactly what I did, which is part of the problem. Checking the stack trace I see renameFromStorage(OCA\Files_Trashb ... }} which suggests the trashbin is indeed involved. Please forget my suggestion to rename the title of the issue.

[papimla]

On June 24th, 2020 the same problem happened with another file. This time with another user. They did not remove the file, they moved it to another folder. I guess the move operation involves copying the file and then removing it which involves the trashbin. In the previous error, I was unable to fix it and the ghost persisted. However, a few days later (not sure how many) they disappeared spontaneously.

In case you're interested in some more forensic evidence, the current ghost file is still present for the bug that showed on June 24th, 2020. Maybe there is a weekly job cleaning up those errors and it will happen in the next few days.

[papimla]

Happened again today.