can i clean tbllog_register ?

[lims]

in mysql tbllog_register  we have big data row can i clean this from mysql ?

is will effect to my billing system ?

thanks before

Regards

Edited December 15, 2021 by lims

[pRieStaKos]

I think you can clean it but leave the latest entries (lets say last year), because it will mess with your reports (and daily emails)

[WHMCS John]

Hi @lims,

Clearing this table will lose historical data from the Utilities > Automation Status page, so is not recommended.

But if you are OK with that data loss, please proceed with caution, and as always take a backup beforehand.

[lims]

i found 2 options here

DELETE FROM `tbllog_register` WHERE created_at < "2021-01-01";

Matched rows: 12158

DELETE FROM `tbllog_register` WHERE namespace = 'RunJobsQueue.executed'

Matched rows: 15586

 

Edited December 22, 2021 by lims
Fixed, only clean Queue

[Dallin121]

Thanks or sharing this but i still have some issue in it can anyone explain me in detail ?

[shoelaced]

@Dallin121 I created a gist of the solution I'm using based on @linux4me's solution in the above mention, but yeah just bear in mind that it'll delete the historical cron records. I don't really care about the cron records and I haven't noticed any other issues but it's possible I might regret it later, ha. Anyway, be sure to create a backup first.

[whm_usr]

Rather than a PHP script running with cron, you can use 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 use a similar schedule for 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;