Jump to content
linux4me

Database Cleanup Operations: tbllog_register and tblactivitylog

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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 by linux4me

Share this post


Link to post
Share on other sites

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? naughty.gif

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. :957_heavy_check_mark:

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.

Share this post


Link to post
Share on other sites

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. :)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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 ?

Share this post


Link to post
Share on other sites

@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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites
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 by yggdrasil

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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 by yggdrasil

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By whattheserver
      First off thanks to the original thread for the inspiration for the first script. I was unable to reply there with my bash version of this. I also had another script so I figured id share all it here.
      #!/bin/bash ## Author: Michael Ramsey ## Objective clean 2 weeks prior to tbllog_register ## Please ensure that the database user used for this has both Select and Delete permissions for the whmcs database ## save as whmcs-register-clean.sh chmod it to 700 ## example cronjob ## 0 0 * * 0 /bin/bash /home/username/scripts/whmcs-register-clean.sh >/dev/null 2>&1 date2w=$(date --date='2 week ago' +"%Y-%m-%d") /usr/bin/mysql -h "localhost" -u "database_username" "-pPasswordhere" -e "DELETE FROM tbllog_register WHERE created_at < $date2w" database_name  
      The second one is more for privacy based companies that want to prevent any client IP's from being unnecessarily saved and prevent DB growth for really large companies with active clients logins. We run privacy based logless VPN service and wanted to ensure there is no IP's ever saved of any of our clients. This included the client and support portal in addition to the VPN nodes and freeradius auth server. More information about this setup is in our blog post here > https://whattheserver.com/whmcs-client-last-login-ip-log-clearing-script/
      #!/bin/bash ## Author: Michael Ramsey ## Objective clear last login IP and host from tblclients ## Please ensure that the database user used for this has both Select and Delete permissions for the whmcs database ## save as whmcs-client-clear-ip.sh chmod it to 700 ## example cronjob ## * * * * * /bin/bash /home/username/scripts/whmcs-client-clear-ip.sh >/dev/null 2>&1 #Clear clients last login IP address in table tblclients > ip,host /usr/bin/mysql -h "localhost" -u "database_username" "-pPasswordhere" -e "UPDATE tblclients SET ip = '', host = ''" database_name #Clear Order Ipaddress in table tblorders > ipaddress /usr/bin/mysql -h "localhost" -u "database_username" "-pPasswordhere" -e "UPDATE tblorders SET ipaddress = ''" database_name  
      whmcs-register-clean.sh
      whmcs-client-clear-ip.sh
    • By web2008
      Does anyone use MariaDB 10.3 on version 7.7.1 without any problems?
    • By ZoXx
      Hello,
      our cronjob made a dataabse backup. 
      The cronjob works fine, but databse backup needs round about 45minutes.
      11.04.2018 08:44    Cron Job: Backup Complete
      11.04.2018 08:44    Cron Job: FTP Backup - Completed Successfully
      11.04.2018 08:44    Cron Job: Backup Generation Completed
      11.04.2018 08:43    Cron Job: Starting Backup Zip Creation
      11.04.2018 08:43    Cron Job: Backup Database Dump Complete
      11.04.2018 08:00    Cron Job: Starting Backup Database Dump
      11.04.2018 08:00    Cron Job: Starting Backup Generation
      11.04.2018 08:00    Cron Job: Completed Daily Automation Tasks
      11.04.2018 08:00    Domain Sync Cron: Completed
      11.04.2018 08:00    Automated Task: Starting WHMCS Updates
      11.04.2018 08:00    Automated Task: Starting Domain Expiry
      11.04.2018 08:00    Automated Task: Starting Client Status Update
      11.04.2018 08:00    Automated Task: Starting Server Usage Stats
      11.04.2018 08:00    Automated Task: Starting Email Marketer Rules
      11.04.2018 08:00    Automated Task: Starting Delayed Affiliate Commissions
      11.04.2018 08:00    Automated Task: Starting Inactive Tickets
      11.04.2018 08:00    Automated Task: Starting Fixed Term Terminations
      11.04.2018 08:00    Automated Task: Starting Overdue Terminations
      11.04.2018 08:00    Automated Task: Starting Overdue Suspensions
      11.04.2018 08:00    Automated Task: Starting Cancellation Requests
      11.04.2018 08:00    Automated Task: Starting Domain Renewal Notices
      11.04.2018 08:00    Automated Task: Starting Invoice & Overdue Reminders
      11.04.2018 08:00    Automated Task: Starting Credit Card Charges
      11.04.2018 08:00    Cron Job: Late Invoice Fees added to 0 Invoices
      11.04.2018 08:00    Automated Task: Starting Late Fees
      11.04.2018 08:00    Automated Task: Starting Invoices
      11.04.2018 08:00    Automated Task: Starting Product Pricing Updates
      11.04.2018 08:00    Automated Task: Starting Currency Exchange Rates
      11.04.2018 08:00    Cron Job: Starting Daily Automation Tasks
      11.04.2018 08:00    Domain Sync Cron: Starting
       
      Is it normal?
      Is it possible to seperate the cronjob databse to another cronjob that will make a backup maybe at 3 o'clock?
    • By michael24
      Hi,
      i think that "Classes" should get it´s own menu item under developers.whmcs.com. it is somewhat hidden in the footer which is an underrepresentation of something that makes life really easier.
      Michael
  • Recently Browsing   0 members

    No registered users viewing this page.

×

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated