TYPO3: deleting page with huge number of records

I have problems removing a page via TYPO3 backend. It is most likely because it contains huge number of recordes stored on it. For example there is almost half milion of sys_file_reference stored on that page. Is that possible to remove a TYPO3 page via CLI or so ?

In this case I would write a CommandController, which makes use of DataHandler - expecting one parameter (id of a page) and deleting the page. Then you can call a CommandController via CLI. And watch new records in a sys_log table. The delete command of DataHandler triggers deleting of all subpages, content elements and filereferences (and hopefully also the versioning - didn't test this case yet). Make DB-backup before calling the command. And it might take quite a long time till everything is deleted.

TYPO3: deleting page with huge number of records, TYPO3: deleting page with huge number of records. I have problems removing a page via TYPO3 backend. It is most likely because it contains huge number of recordes stored on it. For example there is almost half milion of sys_file_reference stored on that page. Problem. Several years ago, I blogged about how you can reduce the impact on the transaction log by breaking delete operations up into chunks.Instead of deleting 100,000 rows in one large transaction, you can delete 100 or 1,000 or some arbitrary number of rows at a time, in several smaller transactions, in a loop.

I don't know about a CLI delete.

I would help TYPO3 in such a case with manual DB interactions as I assume manual deletion of this references by hand is impossible.

Do an update (= set deleted= 1) (or a real delete) on the table sys_file_references where you select on tablenames, fieldname and uid_foreign. In case of records in that table, which contain the sys_file_references you must build a bigger query with joins.

You also can try to go wild: just 'delete' (= set deleted= 1) the page or the records with the references in a DB-query and let the scheduler task clean up.

as usual: do a DB-backup before you work with records in this way.


  1. identify the records select uid from ???table??? where pid in (???page-uid-list???)
  2. identify the sys_file_references: select * from sys_file_references where tablenames=???table??? and uid_foreign in ( "first select" )
  3. update/delete those identified records update sys_file_references set deleted=0 where tablenames=???table??? and uid_foreign in ( "first select" ) / delete from sys_file_references where tablenames=???table??? and uid_foreign in ( "first select" ) [1]
  4. delete those records (from 1) in case of big numbers (otherwise use BackEnd) update ???table??? set deleted=1 where pid in (???page-uid-list???) / delete from ???table??? where pid in (???page-uid-list???)
  5. delete those pages update pages set deleted=1 where uid in (???page-uid-list???) / delete from pages where uid in (???page-uid-list???) or delete from BackEnd

[1]

UPDATE sys_file_references 
   SET deleted=0 
   WHERE tablenames=???table??? 
     AND uid_foreign IN (SELECT uid 
                           FROM ???table??? 
                          WHERE pid IN (???page-uid-list???)
                        )

RestrictionBuilder, The ctrl section of a tables TCA array specifies optional framework internal handling if a row in the backend is deleted using the page or list module, many tables are We quickly figured this ends up in a huge mess: The distinction between  While deleting a page the entrie in "sys_category_record_mm" should be deleted or for page in the trash the record should be marked as deleted or similar. Related issues Related to TYPO3 Core - Bug #76222 : sys_category_record_mm doesn't get updated when deleting pages and records

You may find https://github.com/NamelessCoder/asynchronous_reference_indexing useful - it's a small package I created which delegates the indexing of references, which will happen when you delete pages and sys_file_references, so it doesn't happen in the same request.

While it doesn't truly solve the problem, it can usually bring the time it takes to execute the deletion request, below whichever threshold your server allows.

Database: DataHandler Basics (Formerly Known as TCEmain , Start · TCE (TYPO3 Core Engine) & DataHandler; Database: DataHandler Basics (Formerly Known as TCEmain) “Commands” is when you want to move, copy or delete a record in the system. [treeLevels]: (Only pages) Integer, -1 to 4, indicating the number of levels of the page tree to These are the most significant:  Turning of the mainmenu reduced the number significantly to about 715 queries, .. still quite a lot. At first I turned off the setting "expAll = 1" in TMENU. Without it typo3 generated about 800 queries for the homepage. when only the first level has "expAll = 1" set, typo3 generated between 2000 and 4000 queries per page.

Bug #80875: Multiple problems with relation handler if too many , Multiple problems with relation handler if too many records exist on a page handler if i modify (edit, delete) a record that is stored on a page where many records of that type exist. Core: Exception handler (WEB): Uncaught TYPO3 Exception: An exception occurred while Uid lists in the relation handler can be very big. Scroll to reveal the ending cell. Hold Shift while you click the ending cell. Then format, delete, copy, or whatever. PS: Depending on how large the range to be deleted is it might help to Filter the list on a common criteria, then select & delete the found set of records.

Database: DataHandler basics (formerly known as TCEmain , Start · TCE (TYPO3 Core Engine) & DataHandler; Database: DataHandler basics (formerly known as TCEmain) “Commands” is when you want to move, copy or delete a record in the system. [treeLevels]: (Only pages) Integer, -1 to 4, indicating the number of levels of the page tree to These are the most significant:  Suppose we need to delete rows from PurchaseOrderDetail for any record that has a DueDate prior to January 1, 2014. At first you may be tempted to execute the following statement. Since there is so much data to delete, this will cause a lot of blocking and also take a long time to complete.

TYPO3 Extension Development, fact that the List module will use the table name when a new record is created. In our case, it does not make a big difference because our tables will be There areno hidden, deleted, starttime, endtime, or Frontend groupfields in the table. itemin the control. page_uid Visited page: Database relation to the pages table with  hit delete. where 1000 marks the first row you believe is empty. make 65536 to be the last row you want deleted . You can get this number by do F5 then special and choosing last cell and noting the row. reference entering the row range in the namebox. this will select all the designated rows and then you delete them. Now save the file.

Comments
  • That would be useful but it would have approximatelly same complexity as executing delete button via TYPO3 list or page module (process of deleting that page started via list/page module will take about 19 days - as I can see it is removing ~1 record per second - to finish so it would be probably same via command). Anyway thank you for proposal.
  • I was thinkg about doing it that way but then when analyze what really happens when a page is beeing deleted I found out that for each record stored on the page there are actually three things happening: "copyMovedRecordToNewLocation" "deleteVersionsForRecord" "deleteRecord" (git.typo3.org/Packages/TYPO3.CMS.git/blob/…) So I am not sure if there won't be any side effects if I just remove the records by mysql query ?
  • are the files linked to pages or to records on that page? do you use translations or workspaces/versions?
  • I can see that the relations are mostly to records on that page and on one other page. There are no workspaces/versions so it seems to be safe just to remove those records via sql - at least those records which are linked to records on same page, correct ?
  • see examples in my answer