Database Cleanup Operations: tbllog_register and tblactivitylog

[linux4me]

  On 10/12/2020 at 6:36 PM, 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!

Expand  

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]

  On 10/15/2020 at 4:54 PM, Md Rasel Khan said:

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

Expand  

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

[Md Rasel Khan]

  On 10/15/2020 at 4:55 PM, 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.

Expand  

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]

  On 10/15/2020 at 5:15 PM, Md Rasel Khan said:

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

Expand  

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]

  On 10/15/2020 at 6:32 PM, 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.

Expand  

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]

  On 10/22/2020 at 12:33 PM, zimbahost said:

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

Obrigado

Expand  

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

[Maison]

  On 10/22/2020 at 12:35 PM, Md Rasel Khan said:

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

Expand  

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]

  On 2/1/2023 at 10:47 PM, 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.

 

Expand  

Yah just update the SQL command in the script.

[Faizal1]

  On 2/1/2023 at 11:01 PM, shoelaced said:

Yah just update the SQL command in the script.

Expand  

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]

  On 2/1/2023 at 11:06 PM, 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.

Expand  

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]

  On 2/1/2023 at 11:17 PM, 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";

 

Expand  

😀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