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

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Ricky Rosario [:rrosario, :r1cky]]

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';

Comment 1

[Ricky Rosario [:rrosario, :r1cky]]

This is just for production. Thanks!

Comment 2

[Chris Turra [:cturra]]

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

Comment 3

[Matt Pressman [:mpressman]]

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.

Comment 4

[Ricky Rosario [:rrosario, :r1cky]]

(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!

Comment 5

[Matt Pressman [:mpressman]]

Ok, I'll run it now.

Comment 6

[Matt Pressman [:mpressman]]

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)

Comment 7

[Matt Pressman [:mpressman]]

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.

Comment 8

[Kadir Topal [:atopal]]

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.

Comment 9

[Sheeri Cabral [:sheeri]]

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)

Comment 10

[Kadir Topal [:atopal]]

Thanks, Sheeri