[sumo] Please run this SQL query to delete some garbage votes.

RESOLVED FIXED

Status

Data & BI Services Team
DB: MySQL
RESOLVED FIXED
5 years ago
4 years ago

People

(Reporter: rrosario, Assigned: mpressman)

Tracking

Details

(Reporter)

Description

5 years ago
For some reason, Netsparker was run on production and it generate a lot of garbage data that messes up our metrics. We'd like to delete the votes it generated. I was able to do so with the following SQL:

DELETE FROM `wiki_helpfulvotemetadata`
WHERE `wiki_helpfulvotemetadata`.`vote_id` in (
    SELECT `wiki_helpfulvote`.`id` FROM `wiki_helpfulvote`
    WHERE `wiki_helpfulvote`.`user_agent` LIKE '%Netsparker%'
    AND `created` BETWEEN '2013-02-17 0' AND '2013-02-19 0');

DELETE FROM `wiki_helpfulvote`
WHERE `wiki_helpfulvote`.`user_agent` LIKE '%Netsparker%'
AND `created` BETWEEN '2013-02-17 0' AND '2013-02-19 0';
(Reporter)

Comment 1

5 years ago
This is just for production. Thanks!
(Reporter)

Updated

5 years ago
Blocks: 841095
:r1cky - i am going to defer this bug to the database team since i am hesitant to run update/deletes directly against a production database without testing in stage (i know we can't do that in this case).

@dba's - feel free to ping me if you want any assistance with this bug.
Assignee: server-ops-webops → server-ops-database
Component: Server Operations: Web Operations → Server Operations: Database
QA Contact: nmaul → cshields
(Assignee)

Comment 3

5 years ago
I ran a quick test to see how many records would be deleted and it came back with 327,073. If you can confirm that seems about right, I will run the delete on production sumo.
(Reporter)

Comment 4

5 years ago
(In reply to Matt Pressman [:mpressman] from comment #3)
> I ran a quick test to see how many records would be deleted and it came back
> with 327,073. If you can confirm that seems about right, I will run the
> delete on production sumo.

Yep, that's about what I was expecting. Thanks!
(Assignee)

Comment 5

5 years ago
Ok, I'll run it now.
Assignee: server-ops-database → mpressman
(Assignee)

Comment 6

5 years ago
Done.
mysql> DELETE FROM `wiki_helpfulvotemetadata`
    -> WHERE `wiki_helpfulvotemetadata`.`vote_id` in (
    ->     SELECT `wiki_helpfulvote`.`id` FROM `wiki_helpfulvote`
    ->     WHERE `wiki_helpfulvote`.`user_agent` LIKE '%Netsparker%'
    ->     AND `created` BETWEEN '2013-02-17 0' AND '2013-02-19 0');
Query OK, 327073 rows affected (31.86 sec)

And to verify
mysql> select count(*) from `wiki_helpfulvotemetadata` WHERE `wiki_helpfulvotemetadata`.`vote_id` in (     SELECT `wiki_helpfulvote`.`id` FROM `wiki_helpfulvote`     WHERE `wiki_helpfulvote`.`user_agent` LIKE '%Netsparker%'     AND `created` BETWEEN '2013-02-17 0' AND '2013-02-19 0')
    -> ;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (42.99 sec)
Status: NEW → RESOLVED
Last Resolved: 5 years ago
Resolution: --- → FIXED
(Assignee)

Comment 7

5 years ago
oh - just in case, I copied the data off on backup into /tmp/bug-846399.txt.xz

It won't survive long there, but just to make sure, I wanted an easy restore.
Hi Matt, from the log it looks like only the first query was executed (for the helpfulvotemetadata). Did you also execute the second one for the votes themselves?

DELETE FROM `wiki_helpfulvote`
WHERE `wiki_helpfulvote`.`user_agent` LIKE '%Netsparker%'
AND `created` BETWEEN '2013-02-17 0' AND '2013-02-19 0';


I was querying a slave of the production database and those votes are still there, but I'm not sure how the replication works.
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
FYI:

mysql> select count(*) from 
    -> `wiki_helpfulvote`
    -> WHERE `wiki_helpfulvote`.`user_agent` LIKE '%Netsparker%'
    -> AND `created` BETWEEN '2013-02-17 0' AND '2013-02-19 0';
+----------+
| count(*) |
+----------+
|   186730 |
+----------+
1 row in set (0.95 sec)

Archived just in case, to /root/archive on support1:

root@support1 archive]# mysqldump support_mozilla_com wiki_helpfulvote --where "user_agent LIKE '%Netsparker%'  AND created BETWEEN '2013-02-17 0' AND '2013-02-19 0'" > bug846399backup.sql


mysql> DELETE FROM `wiki_helpfulvote`
    -> WHERE `wiki_helpfulvote`.`user_agent` LIKE '%Netsparker%'
    -> AND `created` BETWEEN '2013-02-17 0' AND '2013-02-19 0';
Query OK, 186730 rows affected (23.41 sec)
Status: REOPENED → RESOLVED
Last Resolved: 5 years ago5 years ago
Resolution: --- → FIXED
Thanks, Sheeri
Product: mozilla.org → Data & BI Services Team
You need to log in before you can comment on or make changes to this bug.