CODEWITHSUNDEEP
typo3
yunzen
yunzen

Reputation: 33439

Best way to keep the TYPO3 sys_log nice & clean?

I have this MySQL

DELETE FROM sys_log 
WHERE sys_log.tstamp < UNIX_TIMESTAMP(ADDDATE(NOW(), INTERVAL -2 MONTH)) 
ORDER BY sys_log.tstamp ASC 
LIMIT 10000

Is this good for keeping the sys_log small, if I cronjob it?

Upvotes: 12

Views: 16826

Answers (5)

Sybille Peters
Sybille Peters

Reputation: 3229

Since TYPO3 9 the history is no longer stored referencing the database table sys_log. You can safely delete records from sys_log. The history is stored in sys_history.

See Breaking Change #55298.

For TYPO3 version < 9, sys_history referenced sys_log, so:

  • if you delete records from sys_log, you should make sure sys_history is not referencing the records you want to delete or delete these as well, if intended.

For versions before v9 (to delete only records in sys_log which are not referenced by sys_history):

DELETE FROM sys_log WHERE NOT EXISTS 
(SELECT * FROM sys_history WHERE sys_history.sys_log_uid=sys_log.uid) 
AND recuid=0 AND tstamp < $timestamp LIMIT $limit

Feel free to optimize this for your requirements.

What you can also do safely (without affecting sys_history) is deleting records with sys_log.error != 0.

Some more recommendations:

  • Set your debugging level to verbose (Warnings) on development but errors-only in production
  • Regularly look at the sys log and eliminate problems. You can delete the specific error from the sys_log once you have taken care of the problem (see sys_log.error != 0, sys_log.details). You can do this with a database command or on newer TYPO3 versions use the "SYSTEM: log" in the backend and use the "Delete similar errors" button:

enter image description here

  • You can also consider, doing a truncate sys_log and truncate sys_history together with using the lowlevel cleaner and delete records with deleted=1 on a major version upgrade. Be sure to talk with someone in close vicinity to the editors first though, as this will remove the entire history. Be sure that you will want to do that.

For the scheduler task "Table garbage collection" see the documentation:

Upvotes: 9

Rem.co
Rem.co

Reputation: 3831

Another common cause for large sys_log tables are issues/errors in one of the extensions used in the TYPO3 installation.

A common example when an old version of tx_solr is used:

Core: Error handler (FE): PHP Warning: Invalid argument supplied for foreach() in typo3conf/ext/solr/classes/class.tx_solr_util.php
Core: Error handler (FE): PHP Warning: array_reverse() expects parameter 1 to be array, null given in typo3conf/ext/solr/classes/class.tx_solr_util.php line 280

This set of records will pop up in sys_log every minute or so which leads to millions of records in a short period of time.

Luckily, these kind of records don't have any effect on the record history in sys_history and the associated rollback functionality, so it's safe to delete them.

If you have a large sys_log this will likely cause issues with LOCK timeouts, so you'll have to limit the delete query:

delete from sys_log where details LIKE 'Core:%' LIMIT 200000;

Upvotes: 1

Michiel Roos
Michiel Roos

Reputation: 883

Yes and No

It IS NOT if you care about your record history. You can revert changes to records (content, pages etc.) using the sys_history table. The sys_history tables and sys_log tables are related. When you truncate sys_log, you also loose the ability to rollback any changes to the system. Your clients may not like that.

It IS if you only care about the sys_log size. Truncating the table via cron is fine.

In TYPO3 4.6 and up you can use the Table garbage collection scheduler task als pgampe says. For TYPO3 versions below 4.5 you can use the tablecleaner extension. If you remove all records from sys_log older than [N] days, you will also retain your record history for [N] days. That seems to be the best solution to me.

And please try to fix what is filling your sys_log in the first place ;-)

Upvotes: 12

pgampe
pgampe

Reputation: 4578

There is a scheduler task for this.

It is called Table garbage collection (scheduler).

In TYPO3 4.7, it can only clean the sys_log table. Starting from TYPO3 6.0, it can also clean the sys_history table. You can configure the number of days and what tables to clean.

Extensions may register further tables to clean.

Upvotes: 9

biesior
biesior

Reputation: 55798

Yes, it is.

See also other suggestions by Jochen Weiland about keeping TYPO3 installation clean and small

Upvotes: 7

Related Questions