#59312No possibility to remove missing indexed files from DB
Description
There currently is no possibility to remove indexed files from DB, when they are missing.
The FileStorageIndexingTask marks the files as missing and the FalStatus-Report shows which files are missing. This report tells the user to restore the files and rerun the indexer to clear the flag.
This means it is not possible to delete files using FTP or otherwise the entries in the DB won’t be cleared. It would be nice if the Indexer could be configured to delete missing files in the database, instead of only marking them as missing.
But I can imagine that it gets quite complicated, if the files are still in use somewhere and were deleted using FTP or some other direct access.
Reviews 1
Related issues 2
History 3041
Show 16 earlier messages
-
Frans Saris commented and changed status
NewAccepted priorityCould haveShould have <span data-processor="relativetime" data-value="2014-06-05T21:36:26+00:00" > 06/05/14, 21:36 </span> -
Marcin Sągol commented <span data-processor="relativetime" data-value="2014-07-03T14:36:12+00:00" > 07/03/14, 14:36 </span>
+1 for some solution for this issue.
-
Wolfgang Wagner commented <span data-processor="relativetime" data-value="2014-08-20T15:05:09+00:00" > 08/20/14, 15:05 </span>
This would be a nice feature indeed!
-
Armin Vieweg commented <span data-processor="relativetime" data-value="2014-10-30T18:36:35+00:00" > 10/30/14, 18:36 </span>
+1
-
Frans Saris commented and changed tracker
FeatureTask target version TYPO3 Version 6.2 Sprint Focus On Location Sprint <span data-processor="relativetime" data-value="2015-01-08T21:49:58+00:00" > 01/08/15, 21:49 </span>A new BE (sub) module needs to be created where a admin can view a list of all missing files with the posibility to remove all/only some records from database.
-
Ingo Schmitt changed Complexity hard <span data-processor="relativetime" data-value="2015-01-30T16:20:29+00:00" > 01/30/15, 16:20 </span>
-
Benni Mack changed target version <span data-processor="relativetime" data-value="2015-06-15T17:45:29+00:00" > 06/15/15, 17:45 </span>
-
Susanne Moog changed Sprint Focus
On Location Sprint<span data-processor="relativetime" data-value="2015-07-17T13:14:57+00:00" > 07/17/15, 13:14 </span> -
Susanne Moog changed target version <span data-processor="relativetime" data-value="2015-08-05T10:32:01+00:00" > 08/05/15, 10:32 </span>
-
Benni Mack changed target version <span data-processor="relativetime" data-value="2015-09-24T20:22:08+00:00" > 09/24/15, 20:22 </span>
-
Clemens Riccabona commented <span data-processor="relativetime" data-value="2016-08-12T08:50:40+00:00" > 08/12/16, 08:50 </span>
Upvote.
It is getting more and more anoying … -
Frank Gerards commented <span data-processor="relativetime" data-value="2016-08-12T12:28:29+00:00" > 08/12/16, 12:28 </span>
It is indeed a dearly missing feature, which shouldnt be that hard to implement.
I would see this as a flag in the scheduler Task for updating the file reference index as this is the point,
where u are sure the “missing” state is most accurate.Anyways you could check for file_exists before deleting the “missing” DB entries.
Clemens Riccabona wrote:
> Upvote.
> It is getting more and more anoying … -
Markus Klein commented <span data-processor="relativetime" data-value="2016-08-12T12:39:56+00:00" > 08/12/16, 12:39 </span>
Keep in mind that you have to do a bit more when cleaning this.
You have to remove all references to that very file as well. -
Jörg Wagner commented <span data-processor="relativetime" data-value="2016-08-12T13:09:04+00:00" > 08/12/16, 13:09 </span>
Markus Klein wrote:
> Keep in mind that you have to do a bit more when cleaning this.
> You have to remove all references to that very file as well.Not really. The major problem are files that are NOT referenced but still listed by the indexer als missing.
Any file that the indexer has seen in his lifetime will create an error when it is removed from the file system using FTP or shell access – even if there is no references at all!Referenced files that are deleted can be considered operator errors and the request to bring them back is correct.
But I would be happy to have an option to remove the error messages for unreferenced files.
IMHO the indexer records for these files could even be removed without further notice. -
Philipp Gampe commented <span data-processor="relativetime" data-value="2016-10-25T15:23:49+00:00" > 10/25/16, 15:23 </span>
Until this is fixed, you can run the following SQL on regular base:
DELETE FROM sys_file WHERE sys_file.missing=1 AND sys_file.uid NOT IN (SELECT sys_file_reference.uid_local FROM sys_file_reference WHERE sys_file_reference.uid_local=sys_file.uid)
-
Jörg Wagner commented <span data-processor="relativetime" data-value="2016-10-26T14:01:58+00:00" > 10/26/16, 14:01 </span>
Steffen Müller has written a TYPO3 scheduler task class that does exactly what Philipp Gampe is proposing:
https://gist.github.com/stmllr/da259f62428cb0b499c48fbaedc932e3
-
Viktor Livakivskyi commented <span data-processor="relativetime" data-value="2016-10-27T10:44:39+00:00" > 10/27/16, 10:44 </span>
I think, it would also make sense to run same query in Fileadmin garbage collection scheduler task, because after a while the Files flagged as missing report is filled with tons of un-useful
/_recycler_/*entries. It makes impossible to see if there are really some problems with missing files, because of such spam entries. -
Philipp Gampe commented <span data-processor="relativetime" data-value="2016-10-27T17:33:05+00:00" > 10/27/16, 17:33 </span>
@Viktor sounds like another bug
-
Viktor Livakivskyi commented <span data-processor="relativetime" data-value="2016-10-27T18:16:21+00:00" > 10/27/16, 18:16 </span>
@Phillip, done #78473 :)
-
Leonie Philine commented and changed TYPO3 Version
6.29 PHP Version 7.2 Complexityhardeasy <span data-processor="relativetime" data-value="2019-02-06T16:51:30+00:00" > 02/06/19, 16:51 </span>This is still an issue in TYPO3 9.
Could https://gist.github.com/stmllr/da259f62428cb0b499c48fbaedc932e3 be included in the core?
-
Susanne Moog changed tracker
TaskFeature <span data-processor="relativetime" data-value="2019-03-03T14:17:55+00:00" > 03/03/19, 14:17 </span> -
Michael Bakonyi commented <span data-processor="relativetime" data-value="2020-08-12T10:10:28+00:00" > 08/12/20, 10:10 </span>
Having 9.5 installed after running the SQL-command provided by Philipp in my case leads to an error when trying to run the Console command “cleanup:missingrelations” afterwards. Error said something like “Missing record with UID XYZ”. After restoring a DB-backup the command doesn’t show an error anymore. So some things seem to have changed regarding relation-handling within the last 4 years. Not surprising I guess :)
-
Jonas Eberle commented <span data-processor="relativetime" data-value="2020-11-26T10:45:43+00:00" > 11/26/20, 10:45 </span>
@Michael Bakonyi I think this is to be expected if the `sys_file` got deleted.
A `cleanup:missingrelation` afterwards can remove the dangling references. If not, you can end up having to check in Fluid if `file.originalResource` is actually not null. That is not nice.
If you are sure that you are not going to restore these files, this should not lose you anything.
I think we should have a clear documentation how to get the reference index, lost files and file references into a 100% clean state.
-
Moritz Ahl commented <span data-processor="relativetime" data-value="2021-02-25T14:17:11+00:00" > 02/25/21, 14:17 </span>
+1. Willing to sponsor.
-
Sybille Peters commented <span data-processor="relativetime" data-value="2023-03-05T11:58:16+00:00" > 03/05/23, 11:58 </span>
Question: This issue is about removing sys_file entries with missing=1 (where the files have been removed, but not the sys_file entries), right?
How did these occur? Was it by a bug? Were the files manipulated on the file storage level without updating the database entries? Why?
It is my understanding, this (sys_file.missing=1) should not happen, if TYPO3 methods are used. E.g. if you remove a file from the backend, the sys_file entry will also be removed.
I would like to understand this better. Also, I have issues with some of the suggestions and I think problem should be addressed on a deeper level.
-
Philipp Gampe commented <span data-processor="relativetime" data-value="2023-03-05T12:46:59+00:00" > 03/05/23, 12:46 </span>
Sybille Peters wrote in #note-25:
> Question: This issue is about removing sys_file entries with missing=1 (where the files have been removed, but not the sys_file entries), right?
>
> How did these occur? Was it by a bug? Were the files manipulated on the file storage level without updating the database entries? Why?Correct. The files have been manipulated outside of TYPO3, e.g. via FTP or some automated process.
Also happens if you restore some database dump and you do not have a copy of the filesystem from the same time. Or if you do not want to restore the file system, because you have new files already in it.
> It is my understanding, this (sys_file.missing=1) should not happen, if TYPO3 methods are used. E.g. if you remove a file from the backend, the sys_file entry will also be removed.
>
> I would like to understand this better. Also, I have issues with some of the suggestions and I think problem should be addressed on a deeper level.The underlaying problem is how to get the real filesystem state and the TYPO3 database state back in sync.
Right now, one would have to restore the file to again delete it within TYPO3. It would be much better if there would be a way to clear the warnings from within TYPO3 (.e.g. delete unused missing files).
If the file is still used in a relation, then the problem should be solved by
a) restoring the file or
b) removing the relation and the file entry -
Clemens Riccabona commented <span data-processor="relativetime" data-value="2023-03-23T08:30:18+00:00" > 03/23/23, 08:30 </span>
So that’s true, what Philipp says.
From time to time it happens, that files get deleted outside of TYPO3, via FTP oder SSH.
These files often do not have a relation, and also they do not need to be restored. But happening that every now and than can fill up these warnings.
As Philipp wrote already 6 years ago, you could do this directly on the database:DELETE FROM sys_file
WHERE sys_file.missing=1
AND sys_file.uid NOT IN (SELECT sys_file_reference.uid_local FROM sys_file_reference WHERE sys_file_reference.uid_local=sys_file.uid)But I do not really like that approach (although I use it, because it’s the only way to go for now).
BTW: for me it happens more often on really old TYPO3 instances, which got upgraded up to now, starting somewhere back in the 3.5/3.6 era.
Back then we had files in fileadmin on a regular base, which should not be stored there anymore, and sometimes it’s nearly impossible to remove them via TYPO3 (eg. some tt_news hook php file, a patched gmenu layers php file etc …). -
Tung Ngo commented and changed PHP Version
7.27.4 <span data-processor="relativetime" data-value="2023-09-14T10:45:31+00:00" > 09/14/23, 10:45 </span>I have created a patch to tackle this problem, feedback appreciated: https://review.typo3.org/c/Packages/TYPO3.CMS/+/80984
-
Gerrit Code Review pushed patch set 4 for main to change 80984 status
AcceptedUnder Review <span data-processor="relativetime" data-value="2023-09-17T11:22:35+00:00" > 09/17/23, 11:22 </span> -
Gerrit Code Review pushed patch set 5 for main to change 80984 <span data-processor="relativetime" data-value="2023-09-18T13:11:49+00:00" > 09/18/23, 13:11 </span>
-
Gerrit Code Review pushed patch set 6 for main to change 80984 <span data-processor="relativetime" data-value="2023-09-18T13:29:01+00:00" > 09/18/23, 13:29 </span>
-
Gerrit Code Review pushed patch set 7 for main to change 80984 <span data-processor="relativetime" data-value="2023-09-20T12:44:24+00:00" > 09/20/23, 12:44 </span>
-
Gerrit Code Review pushed patch set 8 for main to change 80984 <span data-processor="relativetime" data-value="2023-09-20T13:50:47+00:00" > 09/20/23, 13:50 </span>
-
Gerrit Code Review pushed patch set 9 for main to change 80984 <span data-processor="relativetime" data-value="2023-09-21T10:07:24+00:00" > 09/21/23, 10:07 </span>
-
Gerrit Code Review pushed patch set 10 for main to change 80984 <span data-processor="relativetime" data-value="2023-09-22T14:53:24+00:00" > 09/22/23, 14:53 </span>
-
Gerrit Code Review pushed patch set 11 for main to change 80984 <span data-processor="relativetime" data-value="2023-09-26T12:59:43+00:00" > 09/26/23, 12:59 </span>
-
Falko Trojahn commented <span data-processor="relativetime" data-value="2023-09-27T17:29:09+00:00" > 09/27/23, 17:29 </span>
Tung Ngo wrote in #note-28:
> I have created a patch to tackle this problem, feedback appreciated: https://review.typo3.org/c/Packages/TYPO3.CMS/+/80984+1 for the patch. Had to try that by backporting to 10.4, though: worked flawlessly for me, much appreciated.
Feature request: could we please have the possibility to index multiple storages at once?
Like
--update-storage=1,5,7Thanx!
-
Gerrit Code Review pushed patch set 12 for main to change 80984 <span data-processor="relativetime" data-value="2026-10-23T15:01:42+00:00" > 10/23/26, 15:01 </span>
-
Gerrit Code Review pushed patch set 13 for main to change 80984 <span data-processor="relativetime" data-value="2026-10-23T15:11:29+00:00" > 10/23/26, 15:11 </span>
-
Gerrit Code Review pushed patch set 14 for main to change 80984 <span data-processor="relativetime" data-value="2026-10-23T21:36:00+00:00" > 10/23/26, 21:36 </span>
-
Tung Ngo commented and changed status
Under ReviewResolved done0 %100 % <span data-processor="relativetime" data-value="2026-10-23T22:20:16+00:00" > 10/23/26, 22:20 </span>Applied in changeset commit:78ef1026a5c0c628a171d401f1943a28ff34ff2c.
Indeed something we need to add. Maybe something we can add to System -> DB check and can link to from reports module.
And then some overview of all missing files and where they are linked + the possibility to remove some you have selected or all.