Purging Click Tracking database [solved]

Solutions for other advanced phplisters

Purging Click Tracking database [solved]

Postby nolfclvr » 5:40pm, Fri 20 Mar, 2009

Hi all,

Recently my click tracking has been acting kind of funky. The links would not redirect properly.

I have since turned the tracking off so that I am able to send out a message until it is fixed.

In the mean time, I have been informed by my site host that the click tracking database is taking up quite a bit of my disk space on the server. I would like to purge the data that is a year old or more.

How would I go about purging old click track data?

Thanks!
nolfclvr
phpLister
 
Posts: 5
Joined: 5:37pm, Fri 20 Mar, 2009

Postby nolfclvr » 3:27pm, Mon 23 Mar, 2009

Ok... if I were to remove the click tracking table from the database, would that negatively affect the rest of the program?
nolfclvr
phpLister
 
Posts: 5
Joined: 5:37pm, Fri 20 Mar, 2009

Postby CS2 » 5:32pm, Mon 23 Mar, 2009

Yes, deleting the table would cause SQL errors. To delete entries older than one year, try this:
Code: Select all
DELETE FROM phplist_linktrack WHERE DATEDIFF(NOW(), latestclick) > 365;
and
Code: Select all
DELETE FROM phplist_linktrack_userclick WHERE DATEDIFF(NOW(), date) > 365;


Its a known issue that the click tracking functionality tends to create massive tables that can negatively impact performance and disk space. The click tracking system has been redesigned for 2.11.x to correct this issue.
Last edited by CS2 on 4:04am, Sun 29 Mar, 2009, edited 2 times in total.
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Postby nolfclvr » 6:21pm, Mon 23 Mar, 2009

Thanks a lot for the reply! :D
nolfclvr
phpLister
 
Posts: 5
Joined: 5:37pm, Fri 20 Mar, 2009

cleaning db

Postby Nans » 7:55pm, Fri 27 Mar, 2009

I tried that sql command too, because my table is 6GB big!!

But I get this:
#1305 - FUNCTION femistyle.DATE_DIFF does not exist
Nans
phpList newbie
 
Posts: 4
Joined: 6:09pm, Fri 27 Mar, 2009

Postby CS2 » 9:59pm, Fri 27 Mar, 2009

I apologize for the error, it should be DATEDIFF, not DATE_DIFF. I've corrected my other post.
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Postby Nans » 8:41pm, Sat 28 Mar, 2009

Thanks.. the first one did it now...
I removed 2 million records :-)
Nans
phpList newbie
 
Posts: 4
Joined: 6:09pm, Fri 27 Mar, 2009

Re: Purging Click Tracking database [solved]

Postby gerold » 7:07pm, Sat 04 Sep, 2010

is it possible to delete the clicks belonging to special messages instead of days passed (there is a field Message-Id in mysql, i discovered :));
Thank you for your help
gerold
phpLister
 
Posts: 6
Joined: 4:54pm, Thu 02 Sep, 2010

Re: Purging Click Tracking database [solved]

Postby CS2 » 1:31pm, Tue 07 Sep, 2010

Yes, to delete all linktracking data for message 10, change the "WHERE" portion of the previous queries to "WHERE messageid = 10"
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Re: Purging Click Tracking database [solved]

Postby gerold » 9:35pm, Wed 08 Sep, 2010

Thank you, CS2. I will try it.
gerold
phpLister
 
Posts: 6
Joined: 4:54pm, Thu 02 Sep, 2010

Re: Purging Click Tracking database [solved]

Postby gerold » 8:40pm, Fri 10 Sep, 2010

now i did delete the message with "messageid=10"; the data in the database is gone (i looked it up with SELECT * FROM).
But the size of the database didn't shrink (the difference could be to marginal, because the amount of the deleted data isn't huge). And - more astonishing - the statistic data in PHPList is still there.
Is there another thing i have to do, to make it final? Thanks for your patience and help.

[Edit 11. Sep.] checked once more and discovered that in PHPList-Statistic the data is half gone and half there; in statistic/overview i can still see the number of clicks and who clicked (the number of clicks is still linked); in statistic/clicks-per-message the data is gone. It is also (half) gone in the message itself. That is: i still see how many clicks the message had but the link "click statistic" is gone. Strange :roll:

I think i will try a work-around in the future. When sending out messages where i don't need statistics (and don't want because i fear database overload), i will set CLICKTRACK and CLICKTRACK_SHOWDETAIL = 0 in config.php, i will change this in 1 when i want stats. If you suggest another way to handle this i would appreciate to read about it. Thank you.
gerold
phpLister
 
Posts: 6
Joined: 4:54pm, Thu 02 Sep, 2010

Re: Purging Click Tracking database [solved]

Postby CS2 » 1:07pm, Wed 15 Sep, 2010

Did you run both queries? There are two tables that need to be purged.
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Re: Purging Click Tracking database [solved]

Postby gerold » 8:57am, Thu 23 Sep, 2010

yes, i cleaned both phplist_linktrack and phplist_linktrack_userclick
gerold
phpLister
 
Posts: 6
Joined: 4:54pm, Thu 02 Sep, 2010


Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 2 guests

cron