Plesk Bandwidth reporting error

I managed to bump my head into this situation a few times, and I think some of you have too, or will in the future.

The DomainsTraffic table in the Plesk psa database gets a weird value, usually quite huge, and the traffic stats for a certain domain will skyrocket overnight from values of a couple hundred MB’s usually to several GB’s. Domains get suspended, customers get pissed and the “techies”  got work to do.

It’s actually easy to find the problem and fix it:

Log into your Plesk server as root, enter mysql and find the domain in question using the psa database:

[root@nl-ams-sp1 ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow`
mysql> use psa;
mysql> select * from domains where name = "wirelessisfun.com";

Find the day where the records got corrupted and are causing the erroneous report:

mysql> select dom_id,date,http_in,http_out from DomainsTraffic where dom_id =(select id from domains where name = "wirelessisfun.com");

This will output quite some data, but the culprit line will be really obvious:

+——–+————+———+————–+
| dom_id | date | http_in | http_out |
+——–+————+———+————–+
[snip]
| 316 | 2010-08-02 | 0 | 472399336 |
| 316 | 2010-08-03 | 0 | 491239251 |
| 316 | 2010-08-04 | 0 | 470982351 |
| 316 | 2010-08-05 | 0 | 470829065 |
| 316 | 2010-08-06 | 0 | 493939844 |
| 316 | 2010-08-07 | 0 | 454701317 |
| 316 | 2010-08-08 | 0 | 100221521161 |
| 316 | 2010-08-09 | 0 | 144318797 |
+——–+————+———+————–+

Now, that you know the corrupted line, just update the http_out value with something similar to the days before, I used the exact value as the previous day.
Make sure you replace “wirelessisfun.com” with the actual domain name, and the date value with the date in question:

mysql> update DomainsTraffic set http_out = "454701317" where dom_id =(select id from domains where name = "wirelessisfun.com") AND date = "2010-08-08";

The MySQL output should be something like:

Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now that the record for the domain traffic has been fixed, you can wait for the statistics script to run during the night, and the clients traffic will be updated automatically, or you can simply re-run the statistics for that domain from your bash prompt like this:

[root@nl-ams-sp1 ~]# /usr/local/psa/admin/sbin/statistics --calculate-one --domain-name=wirelessisfun.com

Simple enough, right? Use the above tip at your own risk, it worked for me everytime.