Statistics is splitting & doubling the stats - getting w

Private Joined: Dec 27, 2006 Posts: 35

the statistics module had been working fine for the first 18 months of use, but about a month ago the statistics started doubling up and splitting the info.

Bascially what i see is:

Yearly Stats:
2008 (1521085)
2008 (286346)

Monthly Stats seem normal

Daily stats:
1 1.151% (7376)
1 0% (0)
2 0% (0)
2 1.529% (9796)
3 1.692% (10845)
3 0% (0)
4 2.251% (14422)
4 0% (0)
5 2.566% (16443)
5 0% (0)
6 0.699% (4479)
6 2.108% (13508)
7 1.748% (11199)
7 1.748% (11199)

that's just a sample so that you can see sometimes it doubles the stats and other times it splits the stats...on the first and second you see it's 0 for half, but not consistant.

Hourly stats seem normal:
00:00 - 00:59 6.031% (324)
01:00 - 01:59 4.672% (251)
02:00 - 02:59 4.206% (226)
03:00 - 03:59 3.797% (204)
04:00 - 04:59 4.169% (224)
05:00 - 05:59 6.589% (354)
06:00 - 06:59 7.557% (406)
07:00 - 07:59 11.39% (612)
08:00 - 08:59 12.90% (693)
09:00 - 09:59 11.76% (632)
10:00 - 10:59 11.55% (621)
11:00 - 11:59 12.23% (657)

Any help would be greatly appreciated!
Thanks!!!

Site Admin Joined: Aug 17, 2003 Posts: 12368

Basically there are duplicate rows in your stats table, thus the duplicated data. There really isn't a simple way to fix besides manually going through those tables

Private Joined: Dec 27, 2006 Posts: 35

how did this happen? the program was working perfectly for so long and then it got corrupted somehow. do you think it will happen again?

which fields in which file do i have to delete?

Private Joined: Dec 27, 2006 Posts: 35

it's also weird that the data gets split up in different ways each time. sometimes the data gets duplicated perfectly and others it gets split in weird ways.

Private Joined: Dec 27, 2006 Posts: 35

Now the stats page is showing 4 lines for each day of the month. it's multiplying! has anyone ever seen this problem?

3 x 2008

Year Page Views
2006 (18606)
2007 (1772611)
2008 (1616036)
2008 (381297)
2008 (16875)

4 januaries, 4 febs, etc

4 days

Date Page Views
1 8.705% (12671)
1 0% (0)
1 8.705% (12671)
1 0% (0)
2 7.712% (11226)
2 0% (0)
2 7.712% (11226)
2 0% (0)
3 0% (0)
3 0% (0)
3 6.998% (10186)
3 6.998% (10186)
4 4.810% (7001)
4 8.206% (11945)
4 8.206% (11945)
4 4.810% (7001)
5 6.783% (9873)
5 6.783% (9873)
5 6.783% (9873)
5 6.783% (9873)
6 0% (0)
6 0% (0)
6 0% (0)
6 0% (0)
7 0% (0)
7 0% (0)
7 0% (0)
7 0% (0)
8 0% (0)
8 0% (0)
8 0% (0)
8 0% (0)

Can anyone help me on this one?

Nuke Cadet Joined: Jul 26, 2004 Posts: 7

I have developed a fix program to at least delete these duplicate daily stats records, but for some reason this forum isn't let me post the code. I suspect it doesn't like me placing PHP code in here. Please email or PM me for the code.

I'm still working on a fix for preventing the duplicates in the first place.

Private Joined: Dec 27, 2006 Posts: 35

that would be a fantastic start! pmotw.

Private Joined: Dec 27, 2006 Posts: 35

this has gotten way out of hand...here are my stats of july:

Yearly Stats

Year Page Views
2006 (18606)
2007 (1772611)
2008 (1861638)
2008 (626899)
2008 (262477)

Montly Stats for 2008

Month Page Views
January (290049)
February (337728)
March (315925)
April (223125)
May (393307)
June (262439)
July (39063)
August (0)
September (0)
October (0)
November (0)
December (0)
January (0)
February (0)
March (0)
April (0)
May (325396)
June (262439)
July (39063)
August (0)
September (0)
October (0)
November (0)
December (0)
February (0)
January (0)
February (0)
April (0)
March (0)
May (0)
June (223412)
April (0)
July (39063)
May (0)
August (0)
September (0)
June (223412)
October (0)
July (39063)
November (0)
August (0)
December (0)
September (0)
October (0)
November (0)
December (0)

Daily Stats for July, 2008

Date Page Views
1 3.570% (5579)
1 3.570% (5579)
1 3.570% (5579)
1 3.570% (5579)
2 3.546% (5542)
2 3.546% (5542)
2 3.546% (5542)
2 3.546% (5542)
3 3.841% (6002)
3 3.841% (6002)
3 3.841% (6002)
3 3.841% (6002)
4 5.140% (8032)
4 5.140% (8032)
4 5.140% (8032)
4 5.140% (8032)
5 3.846% (6011)
5 3.846% (6011)
5 3.846% (6011)
5 3.846% (6011)
6 3.423% (5350)
6 3.423% (5350)
6 3.423% (5350)
6 3.423% (5350)
7 1.630% (2547)
7 1.630% (2547)
7 1.630% (2547)
7 1.630% (2547)
8 0% (0)
8 0% (0)
8 0% (0)
8 0% (0)
9 0% (0)
9 0% (0)
9 0% (0)
9 0% (0)
10 0% (0)
10 0% (0)
10 0% (0)
10 0% (0)
11 0% (0)
11 0% (0)
11 0% (0)
11 0% (0)
12 0% (0)
12 0% (0)
12 0% (0)
12 0% (0)
13 0% (0)
13 0% (0)
13 0% (0)
13 0% (0)
14 0% (0)
14 0% (0)
14 0% (0)
14 0% (0)
15 0% (0)
15 0% (0)
15 0% (0)
15 0% (0)
16 0% (0)
16 0% (0)
16 0% (0)
16 0% (0)
17 0% (0)
17 0% (0)
17 0% (0)
17 0% (0)
18 0% (0)
18 0% (0)
18 0% (0)
18 0% (0)
19 0% (0)
19 0% (0)
19 0% (0)
19 0% (0)
20 0% (0)
20 0% (0)
20 0% (0)
20 0% (0)
21 0% (0)
21 0% (0)
21 0% (0)
21 0% (0)
22 0% (0)
22 0% (0)
22 0% (0)
22 0% (0)
23 0% (0)
23 0% (0)
23 0% (0)
23 0% (0)
24 0% (0)
24 0% (0)
24 0% (0)
24 0% (0)
25 0% (0)
25 0% (0)
25 0% (0)
25 0% (0)
26 0% (0)
26 0% (0)
26 0% (0)
26 0% (0)
27 0% (0)
27 0% (0)
27 0% (0)
27 0% (0)
28 0% (0)
28 0% (0)
28 0% (0)
28 0% (0)
29 0% (0)
29 0% (0)
29 0% (0)
29 0% (0)
30 0% (0)
30 0% (0)
30 0% (0)
30 0% (0)
31 0% (0)
31 0% (0)
31 0% (0)
31 0% (0)

Hourly Stats for July 07, 2008

Hour Page Views
00:00 - 00:59 6.124% (156)
01:00 - 01:59 3.965% (101)
02:00 - 02:59 4.436% (113)
03:00 - 03:59 3.023% (77)
04:00 - 04:59 3.572% (91)
05:00 - 05:59 4.829% (123)
06:00 - 06:59 6.360% (162)
07:00 - 07:59 9.893% (252)
08:00 - 08:59 9.854% (251)
09:00 - 09:59 14.17% (361)
10:00 - 10:59 19.35% (493)
11:00 - 11:59 14.40% (367)
12:00 - 12:59 0% (0)
13:00 - 13:59 0% (0)
14:00 - 14:59 0% (0)
15:00 - 15:59 0% (0)
16:00 - 16:59 0% (0)
17:00 - 17:59 0% (0)
18:00 - 18:59 0% (0)
19:00 - 19:59 0% (0)
20:00 - 20:59 0% (0)
21:00 - 21:59 0% (0)
22:00 - 22:59 0% (0)
23:00 - 23:59 0% (0)

Private Joined: Dec 27, 2006 Posts: 35

now, it seems that sometimes the stats double and sometimes they split. if they are duplicate stats i believe them to be a reprint, but if they are split i believe they need to be combined...just my belief.

Nuke Cadet Joined: Jul 26, 2004 Posts: 7

In looking at the code in the /includes/counter.php file I don't believe that it is both splitting and doubling. What its doing is this...

When its time to add new records for the year, it writes them all out. A period of time goes by where the hitcounts are being updated properly. Then a random db read error causes new daily records to be written out again. The code that updates the daily records updates ALL the records for that day.

So when things are first working fine, lets say you have 5,000 hits for a particular day. Then a db read error causes a second daily record to be written out. Let's say that happens roughly 3/4 of the way through the day. The original record starts at 5,000 and gets updated with the other, say, 2,500 hits. But the 2,500 hits also get updated to the 2nd (duplicate) daily stat record. Your hit count for the day is 7,500 and thats reflected on the first daily record. The 2nd daily record hit count should NOT be added to your totals. It simply needs to be deleted.

This becomes more clear when dealing with a situation where there are multiple daily records for the same day. In my example you would see:

7,500
2,500
2,500
2,500
etc.

In one of the examples posted here it looks like every single record for the day has the same value. So they would all be 7,500 in my example. That just means that the db read error was on a previous date such that every single duplicate daily record got updated for every hit count. So you should actually be able to look at your data and find the furthest back day that has one record with a higher hit count than the other records for that day. That should indicate which day the db read error happened sometime during that day.

I know it works this way from looking at the code. There are no key fields in the file (only year, month, date, hits) and there is no LIMIT clause on the SQL UPDATE command. So it will update the hit count on ALL records for that day, both the original one that is correct and all the erroneous ones that were added throughout the day.

I have the fixit program for deleting the duplicates. What I don't have worked through is the fix for the original problem itself. I need to research the mysql error codes. If the query for the current year record is successful, but not found, then write out the daily records. If the query for the current year record returns an error (db not found, record locked or WHATEVER), then don't write out new daily records. You'll miss a hit count in that case, but it should only happen very infrequently.

Private Joined: Dec 27, 2006 Posts: 35

WOW! Thank you for the indepth analysis! I really appreciate it. Do you suggest that i add your code that you wrote now or do you think you can actually find the db error?