Database Cleanup Operations: tbllog_register and tblactivitylog

[linux4me]

I noticed in the database that the table tbllog_register has over 89,000 records in it, and tblactivitylog has over 12,000. I have Settings -> General Settings -> Limit Activity Log set to 1000, which apparently doesn't appear to be working for tblactivitylog, although the documentation does say, "Non client related entries will remain."

I took a look at Utilities -> System -> System Cleanup, and none of the tools there appear to apply to the table tbllog_register. On that page, it shows only 2094 log entries in the Activity Log, so does that mean there are 10,000 non-client-related entries in the table? The Activity Log takes a long time to load even on my dedicated server, so it seems like a good idea to get control of those tables.

What's the best way to keep the tables tblactivitylog and tbllog_register from getting huge?

[wsa]

you can delete manual by going to phpmyadmin or search a module at https://marketplace.whmcs.com/

[linux4me]

On 6/11/2018 at 1:49 PM, wsa said:

you can delete manual by going to phpmyadmin or search a module at https://marketplace.whmcs.com/

So you're saying there is no way built into WHMCS to keep those tables from getting huge, and the best thing to do is to manually delete some of the records in those tables periodically? Or maybe set up a cron job to do it?

Edited June 13, 2018 by linux4me

[brian!]

what he's really saying is that he has a product in Marketplace that he thinks can deal with your issue - however, he's been here for over 10 years and knows that it's against the community guidelines to self-promote (outside of specified places)... I suspect he believes that he's getting around that by posting a vague "search in Marketplace" reply - yet, he only ever seems to do that in threads where he has an applicable product in Marketplace for sale - strange that, isn't it?

to my knowledge, he's done it at least four times recently and has been reported to the authorities for them to deal with.... if he continues to do it, they'll receive further reports.

15 hours ago, linux4me said:

there is no way built into WHMCS to keep those tables from getting huge, and the best thing to do is to manually delete some of the records in those tables periodically? Or maybe set up a cron job to do it?

there's no method built in - it only trims the tables shown in the system cleanup page - so yes, you'd have to delete the records periodically... I wouldn't have thought it would be worth a cron job, just trim when required.

[linux4me]

Ah ha! Now I get it. Thanks @brian!

I was just thinking I could write a quick PHP script to delete anything from those two tables that's over a month old, and run it once a month via cron, then not have to think about it again. I just wanted to make sure that was the best thing to do, and that I wouldn't be doing any harm.

[brian!]

33 minutes ago, linux4me said:

I was just thinking I could write a quick PHP script to delete anything from those two tables that's over a month old, and run it once a month via cron, then not have to think about it again. I just wanted to make sure that was the best thing to do, and that I wouldn't be doing any harm

it wouldn't do any harm - and if you're backing up the db daily anyway, then you'll have a copy in case of emergencies.

[yggdrasil]

Deleting tables in your MySQL database is something easily done with any DB management tool, from your hosting company, server, or one in your desktop that connects remotely. Or why not, even from the command line. Yes, you can automate this, and a cron would probably a good idea if this is something you need to do all the time (clean up). I personally prefer to export old logs before deleting them as you never know when you will need them for auditing purposes.

[linux4me]

I looked into what was in tbllog_register this morning to get an idea how often I'd need to clean the table out. It may be that the rate of entries in that table has been increased since the latest update. There are 90,997 records in the table dating back to 2016-12-15. If I look at the number of records just since the beginning of this year, there are 52,871, so it seems like more records are being added now than in the past. There have been 4,712 since the first of this month. If I had increased the number of clients since 2016, I could see the increase. But the number of clients hasn't changed, nor have the number of products/services associated with those clients changed. We've got a stable client base. Maybe more data is being added now than in the past?

[brian!]

3 hours ago, linux4me said:

Maybe more data is being added now than in the past?

perhaps linked to the increased running of the cron ?

[linux4me]

@brian! I think you're correct! There's a new record every five minutes with the name "Jobs Executed" and the namespace "RunJobsQueue.executed."

I ran the query:

SELECT * FROM `tbllog_register` WHERE namespace = 'RunJobsQueue.executed'

and got back 73,155 records. Maybe I'll just set up a cron job to delete all the records over a month old and those with that namespace...

[bear]

Has me wondering why there's no facility within WHMCS to prune at least some system logs on a schedule. Seems to me allowing a DB to grow every 5 minutes day in and day out will at some point break things as tables become huge.

[linux4me]

30 minutes ago, bear said:

Has me wondering why there's no facility within WHMCS to prune at least some system logs on a schedule. Seems to me allowing a DB to grow every 5 minutes day in and day out will at some point break things as tables become huge.

That's exactly what I'm thinking, and why I posed the question originally. I thought I must be missing a setting somewhere that would limit the tbllog_register table, but if there is one, I haven't located it.

[yggdrasil]

2 hours ago, bear said:

Has me wondering why there's no facility within WHMCS to prune at least some system logs on a schedule. Seems to me allowing a DB to grow every 5 minutes day in and day out will at some point break things as tables become huge.

Isn't this what the clean up tools in the utilities section are supposed to do? Maybe this is a bug if its not cleaning that table.

[bear]

The cleanup tools don't address system logs for things like the cron, and haven't changed since v5 of WHMCS. I'm guessing they didn't contemplate this log in particular growing with 5 minute crons as it does? This should be automated, like keeping only the past 30 days or something along those lines, maybe. 

[yggdrasil]

2 hours ago, bear said:

The cleanup tools don't address system logs for things like the cron, and haven't changed since v5 of WHMCS. I'm guessing they didn't contemplate this log in particular growing with 5 minute crons as it does? This should be automated, like keeping only the past 30 days or something along those lines, maybe. 

Sounds bad. This should be cleaned up from the utilities manually or with the cron automatically defined by a setting. Did someone reported this as a bug already?

Edited June 16, 2018 by yggdrasil

[brian!]

9 hours ago, bear said:

Has me wondering why there's no facility within WHMCS to prune at least some system logs on a schedule. Seems to me allowing a DB to grow every 5 minutes day in and day out will at some point break things as tables become huge.

because I think WHMCS development often tends to be short-sighted, e.g we'll make this change now (increased frequency of cron jobs), but deal with the logical consequences of it down the road... happens time and time again.

6 hours ago, bear said:

The cleanup tools don't address system logs for things like the cron, and haven't changed since v5 of WHMCS. I'm guessing they didn't contemplate this log in particular growing with 5 minute crons as it does? This should be automated, like keeping only the past 30 days or something along those lines, maybe. 

there is the "Limit Activity Log" setting in General Settings, which I think by default is set to 10,000 entries...

Quote

The maximum number of System related entries that will be stored in Utilities > Activity Log. System related entries are those where the user shows as System and associated to UserID 0. Entries attributed to specific clients will be kept indefinitely for auditing purposes. Client specific log entries can be pruned via Utilities > System > System Cleanup.

but just checking my v7.5 dev tblactivitylog, there are 176,000+ records in there - of which, only 1,244 would meet the above condition.

when a cron runs, it's using an admin username, not "System" - so those entries would never be caught by this check...

4 hours ago, yggdrasil said:

Did someone reported this as a bug already?

would they even see it as a bug though? is the software doing something that it isn't designed/supposed to do?? (that tends to be their bug definition)... it's the opposite and needs to start doing something that it currently can't.. which whiffs more of a feature request to me - therefore, time to post an obligatory 5-year old feature request...

https://requests.whmcs.com/topic/ability-to-prune-system-activity-log

[yggdrasil]

Yes, the default is 10,000. What types of records do you see there? I have 7.1 and I only see records related to actions, like sending someone an email, new order, modified product, failed logins, etc. No cron logs on my table and so its small.

Edited June 16, 2018 by yggdrasil

[brian!]

most of them are hooks debugs and smarty template errors... nothing I need to keep on a long-term basis

[linux4me]

I waited what seemed like a couple of years for WHMCS to come out with a built-in module for Stripe, which was another thing I thought was a logical move, but they eventually did so. I suspect they'll do something about the logs sometime too, but my tbllog_register is already nearly up to 100,000 records, so I just wrote a PHP script to delete all the records older than two weeks, and added a cron job to run it once a week. That looks like it will keep the table down to about 4000 records or so, which is still a lot for a table that doesn't seem to contain a very high ratio of valuable to unnecessary information, but it's tolerable for now.

[linux4me]

The one I use won't work for most people because it uses PHPMailer with SMTP, but here's a simple version using the PHP mail() function:

<?php
	/*
		This script is designed to keep the table tbllog_register in WHMCS from getting huge from creating a record 
		every five minutes when cron runs.
		
		Fill out the database variables for your WHMCS installation and the $to email address where you want 
		the script to send you notices.
		
		I recommend naming the script "clean_whmcs_logs.php"  and putting it in a folder outside of your 
		/public_html folder, then giving it 600 permissions since it will contain a database username and 
		password as well as an email address.
		
		If you name it as above you can run the script in cron once a week with the command:
		
		php -q /<path to your scripts folder>/clean_whmcs_logs.php >/dev/null 2>&1
		
		The script will send you an email if there is a connection problem, and on completion.
	*/
	// ******************************** DATABASE VARIABLES *********************************************
	$host = 'localhost';
	$dbname = '';	// The name of your WHMCS database.
	$dbuser = '';	// The username for your WHMCS database. Only DELETE capability is necessary for this script.
	$dbpass = '';	// The password for your WHMCS database user.
	// ***************************** END DATABASE VARIABLES *******************************************
	
	// ******************************* EMAIL SETTING(S) *************************************************
	$to = '';	// The email address where you want notices sent.
	// ***************************** END EMAIL SETTINGS ************************************************
		
	function MailNotice($subject, $message) {
		global $to;
		$message = wordwrap($message, 70, "\r\n");
		if (!mail($to, $subject, $message)) {
			error_log('The script clean_whmcs_logs.php was unable to send a notice email: ' . $message);
		}
	}
	
	// Set the cutoff date for selecting/deleting records to two weeks ago.
	$date = date('Y-m-d', strtotime('-2 weeks'));
	
	// Delete the records prior to the $date.
	$sql = "DELETE FROM tbllog_register WHERE created_at < :date";
	try {
		$dbh = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $dbuser, $dbpass);
		$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$sth = $dbh->prepare($sql);
		$sth->bindParam(':date', $date);
		$sth->execute();
		$deleted = $sth->rowCount();
		MailNotice('Clean WHMCS Logs Notice', 'Clean WHMCS Logs ran successfully, deleting ' . $deleted . ' records.');
	} catch(PDOException $e) {
		MailNotice('PDO Error on Line 52', $e->getMessage() . ' with sql = ' . $sql);
	}
?>

 

clean_whmcs_logs.php

[isixhosting]

On 6/16/2018 at 10:39 AM, brian! said:

there is the "Limit Activity Log" setting in General Settings

This doesn't work and never did.

[Md Rasel Khan]

On 6/18/2018 at 12:06 AM, linux4me said:

The one I use won't work for most people because it uses PHPMailer with SMTP, but here's a simple version using the PHP mail() function:

<?php
	/*
		This script is designed to keep the table tbllog_register in WHMCS from getting huge from creating a record 
		every five minutes when cron runs.
		
		Fill out the database variables for your WHMCS installation and the $to email address where you want 
		the script to send you notices.
		
		I recommend naming the script "clean_whmcs_logs.php"  and putting it in a folder outside of your 
		/public_html folder, then giving it 600 permissions since it will contain a database username and 
		password as well as an email address.
		
		If you name it as above you can run the script in cron once a week with the command:
		
		php -q /<path to your scripts folder>/clean_whmcs_logs.php >/dev/null 2>&1
		
		The script will send you an email if there is a connection problem, and on completion.
	*/
	// ******************************** DATABASE VARIABLES *********************************************
	$host = 'localhost';
	$dbname = '';	// The name of your WHMCS database.
	$dbuser = '';	// The username for your WHMCS database. Only DELETE capability is necessary for this script.
	$dbpass = '';	// The password for your WHMCS database user.
	// ***************************** END DATABASE VARIABLES *******************************************
	
	// ******************************* EMAIL SETTING(S) *************************************************
	$to = '';	// The email address where you want notices sent.
	// ***************************** END EMAIL SETTINGS ************************************************
		
	function MailNotice($subject, $message) {
		global $to;
		$message = wordwrap($message, 70, "\r\n");
		if (!mail($to, $subject, $message)) {
			error_log('The script clean_whmcs_logs.php was unable to send a notice email: ' . $message);
		}
	}
	
	// Set the cutoff date for selecting/deleting records to two weeks ago.
	$date = date('Y-m-d', strtotime('-2 weeks'));
	
	// Delete the records prior to the $date.
	$sql = "DELETE FROM tbllog_register WHERE created_at < :date";
	try {
		$dbh = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $dbuser, $dbpass);
		$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$sth = $dbh->prepare($sql);
		$sth->bindParam(':date', $date);
		$sth->execute();
		$deleted = $sth->rowCount();
		MailNotice('Clean WHMCS Logs Notice', 'Clean WHMCS Logs ran successfully, deleting ' . $deleted . ' records.');
	} catch(PDOException $e) {
		MailNotice('PDO Error on Line 52', $e->getMessage() . ' with sql = ' . $sql);
	}
?>

 

clean_whmcs_logs.php

This one worked for me. But I have little bit confusion about two lines https://prnt.sc/uvnsb9

What does mean by this two lines?

[Md Rasel Khan]

On 10/9/2020 at 12:08 AM, Md Rasel Khan said:

This one worked for me. But I have little bit confusion about two lines https://prnt.sc/uvnsb9

What does mean by this two lines?

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!

[linux4me]

On 10/8/2020 at 11:08 AM, Md Rasel Khan said:

This one worked for me. But I have little bit confusion about two lines https://prnt.sc/uvnsb9

What does mean by this two lines?

The first line:

// Set the cutoff date for selecting/deleting records to two weeks ago.
$date = date('Y-m-d', strtotime('-2 weeks'));

sets a variable to a timestamp for two weeks prior to the current date, so that only records two weeks old and older will be deleted.

the second line creates the database query to be executed to do the deleting.

You don't need to change those to use the script.

[Md Rasel Khan]

4 minutes ago, linux4me said:

The first line:

// Set the cutoff date for selecting/deleting records to two weeks ago.
$date = date('Y-m-d', strtotime('-2 weeks'));

sets a variable to a timestamp for two weeks prior to the current date, so that only records two weeks old and older will be deleted.

the second line creates the database query to be executed to do the deleting.

You don't need to change those to use the script.

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