Page MenuHomePhabricator
Create Task
Maniphest T54924

Drop PovWatch extension-related database tables from Wikimedia wikis
Closed, ResolvedPublic
Actions

Description

Many moons ago, the MediaWiki extension [[mw:Extension:PovWatch]] was installed on a few Wikimedia wikis. It's long since been disabled.

The PovWatch MediaWiki extension has two database tables that appear at least on testwiki, commonswiki and enwiki:

  • povwatch_log
  • povwatch_subscribers

These database tables should be dropped from all Wikimedia wiki databases.

Version: wmf-deployment
Severity: enhancement

Details

Reference
bz52924

Related Objects
Search...

Event Timeline

• bzimport raised the priority of this task from to Medium.Nov 22 2014, 1:50 AM
• bzimport added a project: Wikimedia-Site-requests.
• bzimport set Reference to bz52924.
• bzimport added a subscriber: Unknown Object (MLST).
• MZMcBride created this task.Aug 16 2013, 4:52 PM
valhallasw mentioned this in T85867: Remove unused views from labsdb.Jan 10 2015, 5:25 PM
Krenair edited projects, added acl*sre-team; removed Wikimedia-Site-requests.Apr 20 2015, 12:58 AM
Krenair set Security to None.
Krenair subscribed.
Krenair added a project: DBA.Jun 9 2015, 3:00 PM
jcrespo moved this task from Triage to Backlog on the DBA board.Nov 16 2015, 4:22 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 16 2015, 4:22 PM
Reedy updated the task description. (Show Details)Nov 16 2015, 4:38 PM
Reedy subscribed.

This is good to go. No data on enwiki or commonswiki, and just minimal on testwiki

Peachey88 mentioned this in T54921: Database tables to be dropped on Wikimedia wikis and other WMF databases (tracking).Jul 7 2016, 12:33 PM
Marostegui claimed this task.Sep 13 2016, 10:05 AM
Marostegui added a comment.Sep 13 2016, 1:53 PM

enwiki -> s1
testwiki ->s3
commonswiki -> s4

non pooled slaves to do some testing before dropping the table for good

s1 -> db1073
s3 -> db1044
s4 -> db1019

In db1073 (s1- enwiki) a traffic capture doesn't show any reads or writes to povwatch_log and/or povwatch_subscribers
They are not written (they are in fact empty) since:

-rw-rw---- 1 mysql mysql 1.0M Mar 22  2013 povwatch_log.ibd
-rw-rw---- 1 mysql mysql 1.0M Mar 22  2013 povwatch_subscribers.ibd

In db1044 (s3 - testwiki) there are also no reads as per the traffic capture and the tables are not written for a long time indeed:

-rw-rw---- 1 mysql mysql 112K Nov 18  2015 povwatch_log.ibd
-rw-rw---- 1 mysql mysql  96K Nov 18  2015 povwatch_subscribers.ibd

Tables aren't empty but with just a few rows

MariaDB PRODUCTION s3 localhost testwiki > select count(*) from povwatch_log; select count(*) from povwatch_subscribers;
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|       52 |
+----------+
1 row in set (0.01 sec)

In db1019 (s4 - commonswiki) no reads and tables untouched for long too (tables are empty):

-rw-rw---- 1 mysql mysql 1.0M May 16  2014 povwatch_log.ibd
-rw-rw---- 1 mysql mysql 1.0M May 16  2014 povwatch_subscribers.ibd

I have taken a backup of those tables (even the empty ones, to keep the structure just in case) and left them at:

root@dbstore1001:/srv/tmp/povwatch_tables# pwd
/srv/tmp/povwatch_tables
root@dbstore1001:/srv/tmp/povwatch_tables# ls -lh
total 12K
-rw-r--r-- 1 root root  822 Sep 13 13:47 s1_povwatch_tables_20160913134741.sql.gz
-rw-r--r-- 1 root root 1.3K Sep 13 13:48 s3_povwatch_tables_20160913134810.sql.gz
-rw-r--r-- 1 root root  824 Sep 13 13:48 s4_povwatch_tables_20160913134850.sql.gz

I will be renaming the tables soon and leave them with the new name for a few days to see if there is any errors arising before dropping them for good.

Marostegui moved this task from Backlog to In progress on the DBA board.Sep 13 2016, 1:54 PM
Stashbot subscribed.Sep 14 2016, 9:47 AM

Mentioned in SAL (#wikimedia-operations) [2016-09-14T09:47:32Z] <marostegui> Renaming tables before dropping them T54924

Marostegui added a comment.Sep 14 2016, 9:51 AM

s1, enwiki, db1073

MariaDB  db1073 enwiki > rename table povwatch_log to TO_DROP_povwatch_log;
Query OK, 0 rows affected (0.10 sec)

MariaDB  db1073 enwiki > rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;
Query OK, 0 rows affected (0.02 sec)

s3, testwiki, db1044

MariaDB  db1044 testwiki > rename table povwatch_log to TO_DROP_povwatch_log;
Query OK, 0 rows affected (0.13 sec)

MariaDB  db1044 testwiki > rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;
Query OK, 0 rows affected (0.00 sec)

s4, commonswiki, db1019

MariaDB  db1019 commonswiki > rename table povwatch_log to TO_DROP_povwatch_log;
Query OK, 0 rows affected (0.09 sec)

MariaDB  db1019 commonswiki > rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;
Query OK, 0 rows affected (0.00 sec)

To revert any of the changes in any of the servers:

rename table TO_DROP_povwatch_log to povwatch_log;
rename table TO_DROP_povwatch_subscribers to povwatch_subscribers;
Stashbot added a comment.Sep 16 2016, 10:21 AM

Mentioned in SAL (#wikimedia-operations) [2016-09-16T10:21:48Z] <marostegui> renaming tables before dropping them in codfw S1,S3,S4 - T54924

Marostegui added a comment.Sep 16 2016, 10:35 AM

Tables have been renamed in codfw

S1

root@neodymium:/home/marostegui/git/software/dbtools#for i in `cat s1.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i enwiki -e "SET SESSION sql_log_bin=0;rename table povwatch_log to TO_DROP_povwatch_log;";done
***db2034.codfw.wmnet***
***db2042.codfw.wmnet***
***db2048.codfw.wmnet***
***db2055.codfw.wmnet***
***db2062.codfw.wmnet***
***db2069.codfw.wmnet***
***db2070.codfw.wmnet***
***dbstore2001.codfw.wmnet***
***dbstore2002.codfw.wmnet***
***db2016.codfw.wmnet***


root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s1.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i enwiki -e "SET SESSION sql_log_bin=0;rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;";done
***db2034.codfw.wmnet***
***db2042.codfw.wmnet***
***db2048.codfw.wmnet***
***db2055.codfw.wmnet***
***db2062.codfw.wmnet***
***db2069.codfw.wmnet***
***db2070.codfw.wmnet***
***dbstore2001.codfw.wmnet***
***dbstore2002.codfw.wmnet***
***db2016.codfw.wmnet***

To revert this change

root@neodymium:/home/marostegui/git/software/dbtools#for i in `cat s1.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i enwiki -e "SET SESSION sql_log_bin=0;rename table TO_DROP_povwatch_log to povwatch_log;";done

root@neodymium:/home/marostegui/git/software/dbtools#for i in `cat s1.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i enwiki -e "SET SESSION sql_log_bin=0;rename table TO_DROP_povwatch_subscribers to povwatch_subscribers;";done

S3

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s3.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i testwiki -e "SET SESSION sql_log_bin=0;rename table povwatch_log to TO_DROP_povwatch_log;";done
***dbstore2001.codfw.wmnet***
***dbstore2002.codfw.wmnet***
***db2036.codfw.wmnet***
***db2043.codfw.wmnet***
***db2050.codfw.wmnet***
***db2057.codfw.wmnet***
***db2018.codfw.wmnet***

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s3.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i testwiki -e "SET SESSION sql_log_bin=0;rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;";done
***dbstore2001.codfw.wmnet***
***dbstore2002.codfw.wmnet***
***db2036.codfw.wmnet***
***db2043.codfw.wmnet***
***db2050.codfw.wmnet***
***db2057.codfw.wmnet***
***db2018.codfw.wmnet***

To revert this change

root@neodymium:/home/marostegui/git/software/dbtools#for i in `cat s3.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i testwiki -e "SET SESSION sql_log_bin=0;rename table TO_DROP_povwatch_log to povwatch_log;";done

root@neodymium:/home/marostegui/git/software/dbtools#for i in `cat s3.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i testwiki -e "SET SESSION sql_log_bin=0;rename table TO_DROP_povwatch_subscribers to povwatch_subscribers;";done

S4

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s4.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i commonswiki -e "SET SESSION sql_log_bin=0;rename table povwatch_log to TO_DROP_povwatch_log;";done
***dbstore2002.codfw.wmnet***
***dbstore2001.codfw.wmnet***
***db2065.codfw.wmnet***
***db2058.codfw.wmnet***
***db2051.codfw.wmnet***
***db2044.codfw.wmnet***
***db2037.codfw.wmnet***
***db2019.codfw.wmnet***


root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s4.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i commonswiki -e "SET SESSION sql_log_bin=0;rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;";done
***dbstore2002.codfw.wmnet***
***dbstore2001.codfw.wmnet***
***db2065.codfw.wmnet***
***db2058.codfw.wmnet***
***db2051.codfw.wmnet***
***db2044.codfw.wmnet***
***db2037.codfw.wmnet***
***db2019.codfw.wmnet***

To revert this change

root@neodymium:/home/marostegui/git/software/dbtools#for i in `cat s4.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i commonswiki -e "SET SESSION sql_log_bin=0;rename table TO_DROP_povwatch_log to povwatch_log;";done

root@neodymium:/home/marostegui/git/software/dbtools#for i in `cat s4.hosts | grep cod | cut -f 1 -d " "`; do echo "***$i***"; mysql -h$i commonswiki -e "SET SESSION sql_log_bin=0;rename table TO_DROP_povwatch_subscribers to povwatch_subscribers;";done
Stashbot added a comment.Sep 19 2016, 8:04 AM

Mentioned in SAL (#wikimedia-operations) [2016-09-19T08:04:27Z] <marostegui> renaming tables in S1, S4 and S4 in eqiad before dropping them T54924

Marostegui added a comment.Sep 19 2016, 8:23 AM

I have renamed the table in eqiad hosts (the already exists errors are because those hosts were used as canary:

S1 - enwiki:

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s1.hosts | grep eqiad | cut -f 1 -d " " | grep -v "db1069"`; do echo "***$i***"; mysql -h$i enwiki -e "SET SESSION sql_log_bin=0;rename table povwatch_log to TO_DROP_povwatch_log;";done
***labsdb1008.eqiad.wmnet***
***labsdb1003.eqiad.wmnet***
***labsdb1001.eqiad.wmnet***
***dbstore1001.eqiad.wmnet***
***dbstore1002.eqiad.wmnet***
***db1080.eqiad.wmnet***
***db1083.eqiad.wmnet***
***db1089.eqiad.wmnet***
***db1073.eqiad.wmnet***
ERROR 1050 (42S01) at line 1: Table 'TO_DROP_povwatch_log' already exists
***db1072.eqiad.wmnet***
***db1066.eqiad.wmnet***
***db1065.eqiad.wmnet***
***db1055.eqiad.wmnet***
***db1053.eqiad.wmnet***
***db1052.eqiad.wmnet***
***db1051.eqiad.wmnet***
***db1047.eqiad.wmnet***
***db1057.eqiad.wmnet***


for i in `cat s1.hosts | grep eqiad | cut -f 1 -d " " | grep -v "db1069"`; do echo "***$i***"; mysql -h$i enwiki -e "SET SESSION sql_log_bin=0;rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;";done
***labsdb1008.eqiad.wmnet***
***labsdb1003.eqiad.wmnet***
***labsdb1001.eqiad.wmnet***
***dbstore1001.eqiad.wmnet***
***dbstore1002.eqiad.wmnet***
***db1080.eqiad.wmnet***
***db1083.eqiad.wmnet***
***db1089.eqiad.wmnet***
***db1073.eqiad.wmnet***
ERROR 1050 (42S01) at line 1: Table 'TO_DROP_povwatch_subscribers' already exists
***db1072.eqiad.wmnet***
***db1066.eqiad.wmnet***
***db1065.eqiad.wmnet***
***db1055.eqiad.wmnet***
***db1053.eqiad.wmnet***
***db1052.eqiad.wmnet***
***db1051.eqiad.wmnet***
***db1047.eqiad.wmnet***
***db1057.eqiad.wmnet***

As db1069 has multiple instances, s1.enwiki runs at port 3311

MariaDB  db1069 enwiki > SET SESSION sql_log_bin=0;rename table povwatch_log to TO_DROP_povwatch_log;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.08 sec)

MariaDB  db1069 enwiki > SET SESSION sql_log_bin=0;rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.07 sec)

S3 - testwiki

for i in `cat s3.hosts | grep eqiad | cut -f 1 -d " " | grep -v "db1069"`; do echo "***$i***"; mysql -h$i testwiki -e "SET SESSION sql_log_bin=0;rename table povwatch_log to TO_DROP_povwatch_log;";done
***dbstore1001.eqiad.wmnet***
***dbstore1002.eqiad.wmnet***
***labsdb1001.eqiad.wmnet***
***labsdb1003.eqiad.wmnet***
***db1015.eqiad.wmnet***
***db1035.eqiad.wmnet***
***db1038.eqiad.wmnet***
***db1044.eqiad.wmnet***
ERROR 1050 (42S01) at line 1: Table 'TO_DROP_povwatch_log' already exists
***db1077.eqiad.wmnet***
***db1078.eqiad.wmnet***
***db1075.eqiad.wmnet***

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s3.hosts | grep eqiad | cut -f 1 -d " " | grep -v "db1069"`; do echo "***$i***"; mysql -h$i testwiki -e "SET SESSION sql_log_bin=0;rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;";done
***dbstore1001.eqiad.wmnet***
***dbstore1002.eqiad.wmnet***
***labsdb1001.eqiad.wmnet***
***labsdb1003.eqiad.wmnet***
***db1015.eqiad.wmnet***
***db1035.eqiad.wmnet***
***db1038.eqiad.wmnet***
***db1044.eqiad.wmnet***
ERROR 1050 (42S01) at line 1: Table 'TO_DROP_povwatch_subscribers' already exists
***db1077.eqiad.wmnet***
***db1078.eqiad.wmnet***
***db1075.eqiad.wmnet***

Port 3313 for db1069

MariaDB  db1069.eqiad.wmnet testwiki > SET SESSION sql_log_bin=0;rename table povwatch_log to TO_DROP_povwatch_log;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.04 sec)

MariaDB  db1069.eqiad.wmnet testwiki > SET SESSION sql_log_bin=0;rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

S4 - commonswiki

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s4.hosts | grep eqiad | cut -f 1 -d " " | grep -v "db1069"`; do echo "***$i***"; mysql -h$i commonswiki -e "SET SESSION sql_log_bin=0;rename table povwatch_log to TO_DROP_povwatch_log;";done
***dbstore1001.eqiad.wmnet***
***dbstore1002.eqiad.wmnet***
***labsdb1001.eqiad.wmnet***
***labsdb1003.eqiad.wmnet***
***db1019.eqiad.wmnet***
ERROR 1050 (42S01) at line 1: Table 'TO_DROP_povwatch_log' already exists
***db1056.eqiad.wmnet***
***db1059.eqiad.wmnet***
***db1064.eqiad.wmnet***
***db1068.eqiad.wmnet***
***db1081.eqiad.wmnet***
***db1084.eqiad.wmnet***
***db1091.eqiad.wmnet***
***db1040.eqiad.wmnet***

root@neodymium:/home/marostegui/git/software/dbtools# for i in `cat s4.hosts | grep eqiad | cut -f 1 -d " " | grep -v "db1069"`; do echo "***$i***"; mysql -h$i commonswiki -e "SET SESSION sql_log_bin=0;rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;";done
***dbstore1001.eqiad.wmnet***
***dbstore1002.eqiad.wmnet***
***labsdb1001.eqiad.wmnet***
***labsdb1003.eqiad.wmnet***
***db1019.eqiad.wmnet***
ERROR 1050 (42S01) at line 1: Table 'TO_DROP_povwatch_subscribers' already exists
***db1056.eqiad.wmnet***
***db1059.eqiad.wmnet***
***db1064.eqiad.wmnet***
***db1068.eqiad.wmnet***
***db1081.eqiad.wmnet***
***db1084.eqiad.wmnet***
***db1091.eqiad.wmnet***
***db1040.eqiad.wmnet***

Port 3314 por db1069

MariaDB  db1069.eqiad.wmnet commonswiki > SET SESSION sql_log_bin=0;rename table povwatch_log to TO_DROP_povwatch_log;
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.05 sec)

MariaDB  db1069.eqiad.wmnet commonswiki > SET SESSION sql_log_bin=0;rename table povwatch_subscribers to TO_DROP_povwatch_subscribers;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

MariaDB  db1069.eqiad.wmnet commonswiki >
Marostegui added a comment.Sep 21 2016, 6:36 AM

There is a backup for these tables at:

root@dbstore1001:/srv/tmp/povwatch_tables# ls -lh
total 12K
-rw-r--r-- 1 root root  860 Sep 13 13:47 s1_povwatch_tables_20160913134741.sql.gz
-rw-r--r-- 1 root root 1.3K Sep 13 13:48 s3_povwatch_tables_20160913134810.sql.gz
-rw-r--r-- 1 root root  824 Sep 13 13:48 s4_povwatch_tables_20160913134850.sql.gz
Stashbot added a comment.Sep 21 2016, 6:59 AM

Mentioned in SAL (#wikimedia-operations) [2016-09-21T06:59:11Z] <marostegui> dropping tables in S1,S3,S4 - T54924

Marostegui added a comment.Sep 21 2016, 7:15 AM

The renamed tables have been removed from all the hosts across S1, S3 and S4

Marostegui closed this task as Resolved.Sep 21 2016, 7:15 AM
Krenair mentioned this in T153213: Fix broken views in labs DB "ERROR 1356 -- references invalid table(s) or column(s)".Dec 14 2016, 6:12 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL