Page MenuHomePhabricator

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

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

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 22 2014, 1:50 AM
bzimport set Reference to bz52924.
bzimport added a subscriber: Unknown Object (MLST).
Krenair set Security to None.
Krenair added a subscriber: Krenair.
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 added a subscriber: Reedy.

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

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

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

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;

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

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

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

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 >

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

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

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