786981 - Delete spam comments from bug 693076

Status: RESOLVED FIXED

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

Description

[Dave Townsend [:mossop]]

Can we remove comments 7-12 from bug 693076.

Comment 1

[Sheeri Cabral [:sheeri]]

tl;dr, I think I know what to do, but I want a sanity check by Dave Miller.

Hrm, I know I've done this before, but I can't seem to find it doc'd anywhere.

User looks to be user_id=441810 (from the profiles table), and the tables in question are longdescs, bugs_activity, and bugs_fulltext - bugs_fulltext only has 1 row, and it's not 

mysql> select * from bugs_activity where who=441810;
Empty set (0.00 sec)

mysql> select * from longdescs where bug_id=693076 and who=441810\G
*************************** 1. row ***************************
         bug_id: 693076
            who: 441810
       bug_when: 2012-05-10 05:31:04
        thetext: 1
      work_time: 0.00
      isprivate: 0
already_wrapped: 0
     comment_id: 6296335
           type: 6
     extra_data: 565744
*************************** 2. row ***************************
         bug_id: 693076
            who: 441810
       bug_when: 2012-05-10 05:31:04
        thetext: 1
      work_time: 0.00
      isprivate: 0
already_wrapped: 0
     comment_id: 6296336
           type: 6
     extra_data: 565744
*************************** 3. row ***************************
         bug_id: 693076
            who: 441810
       bug_when: 2012-05-10 05:31:04
        thetext: 1
      work_time: 0.00
      isprivate: 0
already_wrapped: 0
     comment_id: 6296337
           type: 6
     extra_data: 565744
*************************** 4. row ***************************
         bug_id: 693076
            who: 441810
       bug_when: 2012-05-10 05:31:04
        thetext: 1
      work_time: 0.00
      isprivate: 0
already_wrapped: 0
     comment_id: 6296338
           type: 6
     extra_data: 576075
4 rows in set (0.00 sec)

Comment_id is the primary key on longdescs, and there's a referring field from bugs_activity:


mysql> select * from bugs_activity where comment_id between 6296335 and 6296338;
Empty set (0.00 sec)

We should definitely delete from longdescs, and I think that's the right thing to do here, but I'm unsure if there's more than that, so I'll ask Dave Miller for a sanity check.

Comment 2

[David Lawrence [:dkl]]

Looks correct to me since the ids are sequential and as long as BETWEEN includes the min and max ids which it seems to from the MySQL docs.

DELETE FROM longdescs WHERE comment_id BETWEEN 6296335 and 6296338;

dkl

Comment 3

[Sheeri Cabral [:sheeri]]

Here's the backup idea just in case:

[root@tp-bugs01-slave01 ~]# mysqldump bugs longdescs --where "comment_id BETWEEN 6296335 and 6296338;" | grep INSERT
INSERT INTO `longdescs` VALUES (693076,441810,'2012-05-10 05:31:04','1','0.00',0,0,6296335,6,'565744'),(693076,441810,'2012-05-10 05:31:04','1','0.00',0,0,6296336,6,'565744'),(693076,441810,'2012-05-10 05:31:04','1','0.00',0,0,6296337,6,'565744'),(693076,441810,'2012-05-10 05:31:04','1','0.00',0,0,6296338,6,'576075');
[root@tp-bugs01-slave01 ~]# mysql bugs
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11802677
Server version: 5.1.63-rel13.4-log Percona Server (GPL), 13.4, Revision 443

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELETE FROM longdescs WHERE comment_id BETWEEN 6296335 and 6296338;
Query OK, 4 rows affected (0.00 sec)

Comment 4

[Sheeri Cabral [:sheeri]]

Resolving.