How can we improve WHMCS?

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

Cleanup tool to prune tbllog_register table



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.

10 Comments

Login to post a comment.

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.
This why I developer a module to take care all his log
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?
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.
Sorry, when I said "before it does a database update" I meant "before it does a database BACKUP"
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.
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
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;
Thank you fwls!
This is a nice solution
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.