[SOLVED] Not able to resolve db records with missing relations via CLI

TYPO3 Questions
1

Hi all,

I’m getting the following output on System / DB Check / Database Relations:

grafik
grafik791×333 14.6 KB

Getting into this was because of a very old secured file store, created with/for use with the fal_securedownload extension. Used this for small “parent’s website”, parents as crossing guards, when our son was in the elementary school… loooong ago :slight_smile:

AFAI remember there’ve been some sponsoring issues for this IMHO pretty nice extension that time time, meaning that the extension have been for a while far behind the maintained TYPO3 releases. Just seen that they’re now up to 12 LTS, means that they’ve found their funding, cool!

I deactivated the site in Backend when not needed anymore long ago, but kept it for maybe later look up something. And I’ve deinstalled the extension, to be able to upgrade TYPO3 that time.

As I’ve within last days cleaned up everything, I’ve seen that I’ve got an error when trying to access (by clicking on it) the secured file store in Backend, File / Filelist. RMB click on “settings” worked, showing me the file system location, outside the TYPO3 structure.
Trying to delete the filestore gave me a warning about 151 “records” (guessed files) which will be “relationless” when doing the delete. Made probably a mistake then by deleting the 151 files on the file system, at least this delete doesn’t affect the DB records, as deleting a file on the physical fileadmin location does.

So I finally deleted the filestore, “accepting” the 151 records warning.

Thanks to @ghi and @mabolek I was able to solve an issue with the CLI utility.
But surprising for me typo3 cleanup:orphanrecords says “no orphaned records”.
Nevertheless typo3 cleanup:missingrelations finds them, but saying (similar for each of the 151 files):

Removing reference in record "sys_file:121:storage" (Hash: ff935b483e7e34472e061c4930430abd)

                                                                                                                        
 [ERROR] ReferenceIndex::setReferenceValue() reported "                                                                 
         DataHandler:[1.2.1]: Attempt to set legacy storage directly is disallowed"

Can’t find hints reg. this error.

Questions:
Is there any chance to allow “set legacy storage directly”, temporay? Maybe a not known to me CLI flag?
Or do I have to “fix” this on the database level?

Thanks,
Michael

2

Maybe the EXT:dbdoctor can help you, have a look?

Otherwise, did you execute all migration wizards when you upgraded, and only went from one major version to the next? Only then all wizards will be able to perform migrations. I’m not sure exactly but I think some wizards took care of sys_file.storage cleanups?!

HTH!

3

@ghi Based on the error message, I wonder if this could be a bug in the TYPO3\CMS\Core\Database\ReferenceIndex class in TYPO3 v12. It looks like it’s trying to modify the storage field in the sys_file table using DataHandler. That is no longer allowed, following this security issue:

It should be using the FAL APIs or (if that’s not possible) direct database queries.

4

[SOLVED] :slight_smile:

Thanks again to @mabolek and @ghi!

Installed dbdoctor. Tried to do some health checks. Which also ran into some "unexpected error"s for the sys_file entries. Seeing this, I decided (as I have a recent backup, of course :smiley: ) to use the “sharpest sword”… phpMyAdmin…

  • looked into structure of sys_file, has a storage column, with values of 1 (or maybe 0) for the probably always ex. fileadmin. And 8 for the deleted “culprit” file storage.
  • sys_file.uids of all records having storage=8 have been the one listed in Backend for DB Check / Database Relations.
  • So I decided to delete one of them, rebuilt the ref index, and check DB Check / Database Relations. Uid seemed to have gone… “seemed” → explained further below
  • deleted then all sys_file records where storage=8, to delete the rest of them. Rebuilt the ref index, and check DB Check / Database Relations. New missing relations, mostly with same uids, but now shown as missing ref to sys_file, not sys_file_storage
  • means that I’ve missed/haven’t been aware of additional constraints/relations to other tables. Typical risk when doing things directly in the database! Unfortunately I’m not aware of any ER diagram or similar for TYPO3 DB?
  • Good luck not a that difficult place inside the TYPO3 database, missing constraint was relation between sys_file and sys_file_metadata.
  • As sys_file_metadata has NO direct human readable information to know at a glance for which file it refers, for good reason, would be a redundant information, I’ve made a select for all sys_file_metadata.uid which do NOT exist in/as sys_file.uid.
  • Found more than the expected number, means full stop for me to continue trying to delete whatever record of them without further information
  • Bug: Good luck now, as not sys_file anymore, dbdoctor was able to finde and delete the currect ones!
  • Rebuilt the ref index, and check DB Check / Database Relations. Nothing shown anymore, voila!

Final remark:

IMHO it would be pretty helpful if the DB Check / Database Relations would show the source table(s) of all records that are pointing to a missing target record, too!

To generate a line like shown on my screenshot in the initial post - “There are 151 records pointing to this missing or deleted record: …” - means that this job/task/function must know the source tables (and records) at that time, of course…

Cheers,
Michael

1 Like
5

Great to see that you resolved your issue!

Feel free submit a feature request for making DB Check / Database Relations show the source tables.

6

Good idea, done :slight_smile: Feature #106720: Would be very helpful if System/DB Check/Database Relations would show the source tables - TYPO3 Core - TYPO3 Forge

1 Like