How can we improve WHMCS?

Share, discuss and vote for what you would like to see added to WHMCS

  1. Home
  2. Ideas
  3. Other
  4. Cleanup tool to prune tbllog_register table

Cleanup tool to prune tbllog_register table



102 Votes
Login to Vote
This table gets EXTREMELY large in row size.

When performing certain clean up tasks by going to Utilities -> System -> System Cleanup we have tools that can clean up the following:

FileSystem - Prune Old Attachments
FileSystem - Empty Template Cache
tblactivitylog - Prune Client Activity Logs
tblemails - Prune Saved Emails
tblgatewaylog - Empty Gateway Log
tblticketmaillog - Empty Ticket Mail Import Log
tblwhoislog - Empty WHOIS Lookup Log

but there is nothing for the tbllog_register table. On a clean install of WHMCS, the table already starts out at +-2300 records and over time this grows immensely. My own table is sitting on 206 782 rows as we speak and it's clear that this table needs to be included in the list of cleanup tools as it can hinder restores when it needs to insert hundreds of thousands of rows. having a large table can also reduce performance and thus we should have the abilitiy to clean it up.
Merged Ideas
    DB tbllog_register, this function requires automatic cleaning (Needs a cleanup feature.).
    HiDB, tbllog_registerThis function requires automatic cleaning (Needs a cleanup feature.).It only takes a few months for it to become big data/size.Thanks

18 Comments

Login to post a comment.

Lillian commented 3 days ago
To address the issue of the tbllog_register table growing excessively large in WHMCS, a cleanup tool is suggested to allow users to prune this table along with other logs. The tbllog_register table can cause performance issues and hinder restores due to its large size. While other tables (such as tblactivitylog, tblgatewaylog, etc.) have cleanup options, tbllog_register currently lacks an automated pruning function. Ideally, a cleanup feature for this table should be integrated to help manage its size and maintain performance. https://avatarworldgame.com
Chris Danks commented 2 months ago
I would like to see a configurable option in WHMCS so when the daily cron runs it cleans up tables like this.

Personally I want to keep emails for clients for the last 4 years.

I also noticed WHMCS keeps all admin logins and IPs with no way to flush them from the GUI, i can see i first installed WHMCS in 2008 and all logins since then are logged! As nice as it was to see i'm a loyal long term WHMCS user i'd like to maybe only keep 1 year of admin login logs too.
magnus.219 commented 10th August
@ https://bitlifeonline.io/home The tbllog_register table's unchecked growth highlights a critical gap in WHMCS's cleanup tools, as it can rapidly swell to hundreds of thousands of rows. This unchecked expansion not only poses challenges during database restores but also significantly impacts system performance. Including tbllog_register in the cleanup utility is essential to ensure smooth operation and efficient data management. Implementing automatic cleaning for this table would alleviate potential performance issues and restore optimal functionality, making it a vital update for maintaining the overall health of the system.
bekeanloinse commented 5th June
FileSystem - Prune Old Attachments: Removes outdated file attachments to free up space.
FileSystem - Empty Template Cache: Clears cached template files to ensure that template changes are reflected immediately.
tblactivitylog - Prune Client Activity Logs: Deletes old entries from the activity log table to keep it manageable.
tblemails - Prune Saved Emails: Removes saved email records to reduce the size of the email table.
https://subwaysurfersgame.io
nforde commented 8th May
WAKE UP WHMCS STAFF!!!

This is a very simple fix for you and it's not good enough to ignore it.

This has now been a problem for *4 YEARS*.

As a result, the WHMCS upgrade process just failed when trying to create a backup - "Request Timeout This request takes too long to process, it is timed out by the server. If it should not be timed out, please contact administrator of this web site to increase 'Connection Timeout'."
Websavers commented 26th April
Would like to see this implemented as well.
Edi commented 25th April
500MB in my DB for about 1.5 years.
nforde commented 18th November 22
My installation is pretty 'standard' and is currently generating around 30,000 lines, 5.5MB per month.

A lot of the billing in WHMCS is monthly, so I recommend setting it to 3 months.

That means that if a monthly problem is noticed after 2 or 3 months there are still plenty of log entries to fully investigate.

This should keep the log under 20MB for most typical installations, which I think is acceptable to most people.

Alternatives or extra features -
1) Add it to the section Utilities -> System -> System Cleanup -> Advanced
2) Add an automatic database size check to the upgrade script (and anywhere else that's relevant) before it does a database update, then automatically prune the database, or tell the user exactly how to prune the database.
WHMCSServices commented 18th November 22
This why I developer a module to take care all his log
Official Response
WHMCS commented 18th November 22
Hi all,
Thanks for your feedback and commentary. This table contains the data for the Automation Status page's history.
For how long would you like this data to be retained by default?
nforde commented 18th November 22
My installation is pretty 'standard' and is currently generating around 30,000 lines, 5.5MB per month.

A lot of the billing in WHMCS is monthly, so I recommend setting it to 3 months.

That means that if a monthly problem is noticed after 2 or 3 months there are still plenty of log entries to fully investigate.

This should keep the log under 20MB for most typical installations, which I think is acceptable to most people.

Alternatives or extra features -
1) Add it to the section Utilities -> System -> System Cleanup -> Advanced
2) Add an automatic database size check to the upgrade script (and anywhere else that's relevant) before it does a database update, then automatically prune the database, or tell the user exactly how to prune the database.
nforde commented 18th November 22
Sorry, when I said "before it does a database update" I meant "before it does a database BACKUP"
Websavers commented 26th April
I agree with the 3 months suggested by @nforde
nforde commented 14th November 22
This is a BUG and WHMCS should give it some urgency.

tbllog_register 582269 98464 KB
Total Rows: 708294 - Total Size: 137308.29 KB

As you can see, my database is HUGE because of that table. It means I can no longer backup the database or upgrade due to timeouts because the database is so huge.

I should not be forced to manually go in an look at databases and manually delete stuff from databases.
biovolt commented 14th November 22
I 100% agree with you nforde.
It is the biggest table filled with info that is not useful beyond a few days.
This is causing timeouts on backups and should be easy to fix
fwls commented 10th June 23
We clear rows older than 90 days using MySQL scheduler as follows:

Enable scheduler:
SET GLOBAL event_scheduler = ON;

Create the event, we clear all events > 90 days:
CREATE EVENT whmcs_prune_tbllog ON SCHEDULE EVERY 1 DAY STARTS '2023-01-01 01:00:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Clear WHMCS tbllog_register over 90 days' DO DELETE FROM tbllog_register WHERE created_at <= ( CURDATE() - INTERVAL 90 DAY );

We also made a similar schedule for clearing tblactivitylog:
CREATE EVENT whmcs_prune_tblactivitylog ON SCHEDULE EVERY 1 DAY STARTS '2023-01-01 01:05:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Clear WHMCS tblactivitylog over 90 days' DO DELETE FROM tblactivitylog WHERE date <= ( CURDATE() - INTERVAL 90 DAY );

Verify your events with:
SHOW EVENTS;
biovolt commented 12th June 23
Thank you fwls!
This is a nice solution
Peter L commented 6th January 21
I vote for this! Our tbllog_register table is almost at 500,000.

Created a WHMCS support ticket and they said:
"This is used for the Automation Status page in the admin area. We do not recommend to manually edit to this table. However, you could use a MySQL query to delete entries older than a specific date like so:

DELETE FROM tbllog_register WHERE created_at <= ( CURDATE() - INTERVAL 365 DAY );

We need to be able to control our database from getting too large, so pruning of this table inside WHMCS would be ideal.