In a Discourse forum, the user_auth_token_logs table grew to 61 million rows despite only 25k active user_auth_tokens, with 54 million referencing non-existent tokens and 58 million older than 2 months. RGJ raised concerns about data integrity and cleanup, noting that logs are retained even when verbose_auth_token_logging is disabled — a bug introduced by commit 8fb823c, which made logging unconditional but forgot to make cleanup unconditional too RGJ. pmusaraj confirmed logs are for debugging and can be safely purged, though some (e.g., suspicious, generate, rotate) are needed for security reports Osama. JonahAragon1 questioned whether logs for non-staff users need to be retained indefinitely, suggesting a 6-month retention window for privacy. RGJ clarified the bug: cleanup only runs if verbose logging is enabled, which is incorrect. A PR was submitted RGJ and later merged by Osama, partially fixing the issue by cleaning most log types — except generate entries, which will be addressed in a follow-up. The topic remains open until full cleanup is implemented.
We hebben een forum waar user_auth_token_logs 61 miljoen rijen heeft (en groeiende).
Er zijn slechts 25k user_auth_tokens.
Van de 61 miljoen rijen verwijzen 54 miljoen rijen naar een user_auth_token die niet meer bestaat (d.w.z. een database-integriteitsprobleem). En van de 61 miljoen rijen zijn ongeveer 58 miljoen ouder dan 2 maanden (d.w.z. schijnbaar nutteloos?)
Vragen:
Kunnen we dit gewoon opschonen zonder verdere integriteitsproblemen te riskeren?
Zou het een idee zijn om hier automatisch een taak voor te laten lopen?
db=# select count(*) from user_auth_tokens;
count
-------
25648
db=# select count(*) from user_auth_token_logs;
count
----------
61415352
db=# select count(*) from user_auth_token_logs where user_auth_token_id not in (select id from user_auth_tokens);
count
----------
54558442
db=# select count(*) from user_auth_token_logs where created_at < '2024-07-13';
count
----------
58565943
Yes, user_auth_token_logs are there only for debugging purposes. All rows can be emptied, the only consequence will be that you won’t have any logs to debug.
Ah yes, good catch. Looks like lines 214 to 217 need fixing as well.
I would be comfortable with a global cleanup after a certain timeframe. @osama (since you’re the author of the commit linked above), do you think we can clean up all of these logs after some time (and if so, after how long)? It sounds like we need to keep some of them for detecting suspicious logins.
Why does it need fixing? That piece of code is about cleaning up rotated UserAuthTokens, not about the log records?
Update: after enabling SiteSetting.verbose_auth_token_logging, triggering the weekly job and running VACUUM FULL user_auth_token_logs the table went from 16GB to 687MB
Yes, I think we can clean up most of the logs, but some of them have to stay. Specifically, I think any records that have suspicious, generate, or rotate for action will need to be kept around because they’re used for detecting and generating reports for suspicious logins.
The original implementation only logged when SiteSetting.verbose_auth_token_logging is true. Which still had the problem that after disabling it, the most recent remaining logs would stay, but that’s a small thing.
But this change made the logging unconditional (“The generate, rotate and suspicious auth token logs are now always logged regardless of the verbose_auth_token_logging setting”).
TLDR; That change forgot to make the removal unconditional as well.
Zeker, we zullen het de komende weken oplossen. Als er haast bij is, stuur gerust een pull request (die getest is en bevestigt dat dit naar verwachting werkt).
Inderdaad, die PR is nu samengevoegd dankzij @Osama. Het pakt de meeste soorten user_auth_token_logs aan, maar niet allemaal. We zullen binnenkort een oplossing voor de generate-vermeldingen volgen. (Zie de discussie in de PR-link hierboven voor meer context).
Ik houd dit onderwerp open terwijl we de follow-up aanpakken.