#106568Duplicate sys_file entries in database due to interfering file operations
Description
There are a few other issues that might be related but the error scenario is usually different, hence this new issue.
While this ticket defines TYPO3 v11 as it’s the version we discovered this issue, it happens in v13, too and it’s safe to assume 12 to be affected as well as many if not all previous versions of TYPO3.
Scenario
A client of ours wants to re-organize their fileadmin content that has been grown very big over the years in a TYPO3 v11 system. This client has multiple editors who move single files or entire folders from one location to another within the TYPO3 Backend module filelist. Everything is purely done inside the backend. In this client’s website there is a TYPO3\CMS\Scheduler\Task\FileStorageIndexingTask defined and runs every so often.
After starting the re-organization our client reported issues with references of files that have been moved being broken in the website (think PDF-Downloads, or news images). These files are displayed in the filelist as having 0 references as well. Not all of the moved files but many.
Observed problem
The problem we observed is that the file storage indexing task is running while editors are actively moving files around inside the filelist module. This leads to the indexing task to create new sys_file record entries in the database for the files that are being moved around but haven’t been added/updated yet by the move process.
These duplicated entries usually get new metadata records as well instead of keeping previous ones as a side effect. Neither sys_files are set as missing in this context but if they were we’d lose references and metadata in the progress/long run.
Attempted solution
First thing is to check the sys_refindex as it’s usually the first recommendation on dealing with sys_file issues but updating it changes nothing. The issue persists.
The other obvious solution would be to disable the file storage indexing task while editors are moving files around or let it run at times the editors are not working. We attempted to do this and the issue decreased in frequency but didn’t stop.
Further investigation
We dug through the TYPO3 core code to understand what’s happening and found out that the file storage indexing task is not aware of any running filelist module operations. That’s why it creates sys_file database records with new sys_file_metadata records. Similarly the move operations aren’t aware of any other filelist operations like listing folder contents. That’s why the issue persisted as multiple editors click around in the same folder(s) at the same time while one or more of them are moving files around.
Test system
To be sure that this issue has nothing to do with the project itself we created a fresh TYPO3 v13 installation and tried to reproduce this issue.
For simplicity we didn’t use the storage indexing task as race conditions that way are hard to provoke. But manually moving files around in the filelist module while listing folder contents in another tab will lead to duplicated sys_file records.
This is the test scenario that we could reproduce issues with in a WSL2+Docker+ddev environment:
1. Open two tabs in the backend file list module
2. In one tab move a folder to another location (must be sufficiently big to take a few seconds for you to be able to click around)
3. In the other tab list the contents of either while the move is in progress:
3a) the source folder you’re moving files from (ie. you move locationA/testfolder -> locationB/testfolder, you open locationA/testfolder) or
3b) the target folder you’re moving files into (you open locationB/testfolder)
4. Wait for everything to be loaded/moved
5. Check sys_file records in the database for duplicates
Depending on whether you look into the source or target folder the result is different:
*Source* : You end up with sys_file duplicates, that share sha1, creation date, folder_hash but have different identifiers (one with locationA/testfolder/... and one with locationB/testfolder/...).
*Target* : Duplicates are the exact same, including the identifier.
This leads to broken references despite a seemingly up-to-date sys_refindex table and incorrect display of references in the filelist module (- isntead of Number X).
We tried this setup in a MacOS environment as well, but those move files instantly around. So we weren’t able to reproduce the issue there.
Conclusion
It seems that TYPO3 needs some kind of locking mechanism to prevent interference for moving/copying filelist operations from running at the same time.
We were able to find similar issues with other clients as well but to a lot lesser extent. The issue does not lead to exceptions and is therefore not an obvious issue but it seems to be a common problem within TYPO3 and we are at a loss on how to best solve or prevent this ourselves.
Reviews 0
No change resolves or names this issue in its commit message, and no comment links one.
Fix this issue
Start the commit message of your patch with this. Add what the patch does, and the versions it goes to in "Releases:". How to create a patch
- Commit message
-
[BUGFIX] Duplicate sys_file entries in database due to interfering file operations Resolves: #106568 Releases: main