sys_history broken because of sys_log task
we have activated scheduler task to clean up sys_log, so only last 90 days are in log.
Now we have found out that the history of some older records were deleted, but still available in sys_history.
I have searched the core and found method getHistoryData in RecordHistory.php. There you build following Query:
$res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('sys_history.*, sys_log.userid', 'sys_history, sys_log', 'sys_history.sys_log_uid = sys_log.uid AND sys_history.tablename = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr($table, 'sys_history') . ' AND sys_history.recuid = ' . intval($uid), '', 'sys_log.uid DESC', $this->maxSteps);
As you can see, this query can only build entries from sys_history where a relation to a sys_log entry exists. The entry from sys_log was deleted, so output of module history is incomplete.
So what to do?
- When deleting sys_log entries also delete related sys_history
- Modify task to only delete non related entries.
[!!!][BUGFIX] Separate sys_history from sys_log db entries
Before, the history module fetched info about "modified records" from
sys_history+the authoritive user from a coupled sys_log entry.
Info about "insert" and "delete" was fetched from sys_log solely.
However, when using a scheduled cleanup task to truncate sys_log
then all history information is useless (see bug report).
The patch introduces a new RecordHistoryStore as an abstraction
for adding history entries (currently done solely within DataHandler).
It adds some additional, necessary SQL fields to sys_history to
store all information in there and creates an update wizard
to migrate all coupled sys_history/sys_log entries to a
new sys_history entry itself.
Additionally, the whole existing "RecordHistory" class is
now only necessary for fetching the so-called ChangeLog,
for a page or a specific record, and to do rollbacks, preparing
the history records so they can be worked on.
The whole logic for fetching the GET/POST parameters is moved
into the "ElementHistoryController", everything that is only possible
via Fluid is moved from the RecordHistory object and the
ElementHistoryController into the view.
Referencing from sys_log (Log module) into sys_history is
now done the other way around, storing information about
the corresponding history entry inside sys_log.
As a side-effect, sys_log should load faster.
- sys_history is the only source of truth about the history of a record
- sys_log contains a reference to an history entry now
(inside sys_log.log_data) to link from the backend log module
- RecordHistoryStore exists for tracking changes to records
- RecordHistory is for retrieving, compiling the history/changelog and rollbacks
- ElementHistoryController is doing PSR-7 style request/response
handling and preparing data for the view
- Fluid is handling more view functionality now, removing
the need for doing <f:format.raw> everywhere in the templates.
- Data within sys_history is now stored as JSON, not serialized anymore
- Adding/deleting was previously stored in sys_log only, is now within sys_history
- Moving records is now tracked (but not evaluated yet)
- Highlight/Snapshot functionality within the Backend Module
This functionality is built so it can also be used within Extbase
persistence and in FE in general in a future iteration.
Reviewed-by: Andreas Fernandez <email@example.com>
Tested-by: Andreas Fernandez <firstname.lastname@example.org>
Tested-by: TYPO3com <email@example.com>
Reviewed-by: Joerg Kummer <firstname.lastname@example.org>
Tested-by: Joerg Kummer <email@example.com>
Reviewed-by: Benni Mack <firstname.lastname@example.org>
Tested-by: Benni Mack <email@example.com>
#1 Updated by Steffen Müller over 5 years ago
sys_history and sys_log are tightly coupled. I would love to see this decoupled.
Nevertheless this story will not be solved with 6.2.
To solve this for 6.2, I would vote for
a) When deleting sys_log entries also delete related sys_history
and add a note to the task. Not sure how to handle this for 6.1.
What do others think?
#2 Updated by Steffen Müller over 5 years ago
I had a quick look at the backend. It looks like no history record is shown without a corresponding sys_log entry.
If that's true, I would suggest to leave things like they are for now. Once we have decoupled sys_history from sys_log, this can be be solved in a more clean way.
Would it work for you if you set up two tasks, one to clear sys_log, the other to clear sys_history?
#4 Updated by Georg Kühnberger over 5 years ago
I highly support Stefan Froemken's suggestion to decouple the two tables; as the current situation is a) Double-entry bookkeeping and b) sys_log is somehow illnamed asuming that all tables with _log in the name might be deleted without loosing the BE-functionality to see the changes & diffs & being able to restore a change.
#6 Updated by Andreas Allacher over 3 years ago
Sys Log also has an icon to sys history which I think should be kept.
That one, most likely still would to have use the sys_log_uid but as mentioned in #71950 that could actually be easily fixed by retrieving the latest entry for the sys_log_uid in HistoryEntryViewHelper