Database Cleanup Operations: tbllog_register and tblactivitylog

[linux4me]

On 10/12/2020 at 11:36 AM, Md Rasel Khan said:

This code is working to  clean only tbllog_register database table. But how can I clean tblactivitylog database table? Anyone please share here the code to clean tblactivitylog database table like as the tbllog_register. Thanks!

I'm using WHMCS v. 8.0.2, and with System Settings -> General Settings -> General -> Limit Activity Log set to a reasonable number, I don't have to run an independent script to keep the activity log at a reasonable size.

You can verify that Limit Activity Log is working by going Utilities -> System -> System Cleanup, and clicking the Advanced Tab. The "Prune Client Activity Logs" section will show you how many records are in the Activity Log.

[linux4me]

1 minute ago, Md Rasel Khan said:

If I use another table, why it's not working? https://prnt.sc/v00m81

Maybe the other table doesn't have a "created_at" field that the query uses in the "WHERE" clause?

[Md Rasel Khan]

8 minutes ago, linux4me said:

I'm using WHMCS v. 8.0.2, and with System Settings -> General Settings -> General -> Limit Activity Log set to a reasonable number, I don't have to run an independent script to keep the activity log at a reasonable size.

You can verify that Limit Activity Log is working by going Utilities -> System -> System Cleanup, and clicking the Advanced Tab. The "Prune Client Activity Logs" section will show you how many records are in the Activity Log.

Thanks, previously I was thought It's not working for me. I was set my limit to 100 in general settings. But after a few hours when I have checked the System Logs, there were many lakhs logs. So that I was thought it's not working. But when I check all logs, I saw many same logs multiple times. Then I was unmarked mail debug configuration. Then these same logs are not being recorded. And now my current activity logs are under 100.

[Md Rasel Khan]

And one more thing, is there nay way to check this "tblerrorlog" logs in details? https://prnt.sc/v010vi

[linux4me]

1 hour ago, Md Rasel Khan said:

And one more thing, is there nay way to check this "tblerrorlog" logs in details? https://prnt.sc/v010vi

I'm not sure I understand what you're asking, but if you're asking if there is a way to view the contents of the tblerrorlog table within WHMCS, I think you can do so by going Configuration -> System Logs, and it will go to the Activity Log by default. (You can change the log you're viewing using the links in the left sidebar.) If there have been errors, I believe in v. 8 they are still recorded in the Activity Log if you have "Log Errors" checked as described here.

[Md Rasel Khan]

5 hours ago, linux4me said:

I'm not sure I understand what you're asking, but if you're asking if there is a way to view the contents of the tblerrorlog table within WHMCS, I think you can do so by going Configuration -> System Logs, and it will go to the Activity Log by default. (You can change the log you're viewing using the links in the left sidebar.) If there have been errors, I believe in v. 8 they are still recorded in the Activity Log if you have "Log Errors" checked as described here.

I'm really sorry for the typo mistake. And thanks a lot for your time.

[Maison]

Ainda preciso do script php para limpar as tabelas ou whmcs 8.0.3 resolveram o problema?

Obrigado

[Md Rasel Khan]

1 minute ago, zimbahost said:

Ainda preciso do script php para limpar as tabelas ou whmcs 8.0.3 resolveram o problema?

Obrigado

NO! Still, they didn't release this problem's solution.

[Maison]

Just now, Md Rasel Khan said:

Não! Ainda assim, eles não liberaram a solução deste problema.

Thanks Update

[tkalfaoglu]

Nyah nyah.. it worked great.. THANK YOU!!

Clean WHMCS Logs ran successfully, deleting 945442 records.

[shoelaced]

I did some minor refactoring of this and it still seems to work, thanks @linux4me

Here's my gist if anyone's interested.

Weird that WHMCS doesn't include a native solution for this. There must be databases that are bloated out of their minds. I only had records going back to 2018 and it had like half a million rows in there and was getting to the point where the database download during the WHMCS upgrade process was not working at all. This probably explains it, ha.

[Faizal1]

Thanks @linux4me @shoelaced. Is it possible to make it so it only deletes the cron entries. Using SELECT * FROM `tbllog_register` WHERE namespace = 'RunJobsQueue.executed' showed 174592 entries.

 

[shoelaced]

12 minutes ago, Faizal1 said:

Thanks @linux4me @shoelaced. Is it possible to make it so it only deletes the cron entries. Using SELECT * FROM `tbllog_register` WHERE namespace = 'RunJobsQueue.executed' showed 174592 entries.

 

Yah just update the SQL command in the script.

[Faizal1]

1 minute ago, shoelaced said:

Yah just update the SQL command in the script.

Thanks for the quick response. I've been looking at your script and assume the line:

$sql = 'DELETE FROM tbllog_register WHERE created_at < :date';

has to be updated but wasn't sure how to format the command. But i'm on it and trying to figure it out.

[shoelaced]

9 minutes ago, Faizal1 said:

Thanks for the quick response. I've been looking at your script and assume the line:

$sql = 'DELETE FROM tbllog_register WHERE created_at < :date';

has to be updated but wasn't sure how to format the command. But i'm on it and trying to figure it out.

I'm not really an SQL expert but I think this should do it:

$sql = "DELETE FROM tbllog_register WHERE namespace = 'RunJobsQueue.executed' AND created_at < :date";

 

[Faizal1]

30 minutes ago, shoelaced said:

I'm not really an SQL expert but I think this should do it:

$sql = "DELETE FROM tbllog_register WHERE namespace = 'RunJobsQueue.executed' AND created_at < :date";

 

😀I think that will do it. Thank you very much. I've been running simulations in phpmyadmin and was just missing the AND part. Thanks again.

• Edit: Worked perfectly 👍👍

Edited February 1, 2023 by Faizal1