Page MenuHomePhabricator

Drop FlaggedRevs tables in database for srwikinews
Closed, ResolvedPublic

Description

Hi,
srwikinews community, per request in T209251 wants to MediaWiki-extensions-FlaggedRevs be disabled because a simple simplified patrol mechanism is enough for such a small project..

Per comment of @Marostegui at T209251#4741279, we want to tables related to FlaggedRevs be dropped.

Thanks!

Event Timeline

Zoranzoki21 removed Zoranzoki21 as the assignee of this task.Nov 17 2018, 1:32 PM
Marostegui changed the task status from Open to Stalled.Nov 17 2018, 2:34 PM
Marostegui moved this task from Triage to Blocked external/Not db team on the DBA board.

I assume this is not ready to go until T209251 is completed, right?

I assume this is not ready to go until T209251 is completed, right?

Yes. You are right.

@Zoranzoki21 just to be 100% sure everybody is aware of the consequences, dropping the tables means if at a later time a change of opinion happens, it can be reenabled with no problems, but previous usage (data) of the extension will not be available- usage from 2013 until now, approximately 370K flags. May I ask you transmit that (or make sure that is clear already by community members) and they agree with that?

Zoranzoki21 changed the task status from Stalled to Open.Nov 21 2018, 12:51 AM

@Zoranzoki21 just to be 100% sure everybody is aware of the consequences, dropping the tables means if at a later time a change of opinion happens, it can be reenabled with no problems, but previous usage (data) of the extension will not be available- usage from 2013 until now, approximately 370K flags. May I ask you transmit that (or make sure that is clear already by community members) and they agree with that?

They already know it.

(changing status to open, as patch for disabling is merged)

Marostegui triaged this task as Medium priority.Dec 10 2018, 6:37 AM
Marostegui added a subscriber: Reedy.

@Zoranzoki21 can you confirm if this is good to go and if these are the tables that need to be dropped?

root@db1077.eqiad.wmnet[srwikinews]> show tables like '%flaggedre%';
+------------------------------------+
| Tables_in_srwikinews (%flaggedre%) |
+------------------------------------+
| flaggedrevs                        |
| flaggedrevs_promote                |
| flaggedrevs_statistics             |
| flaggedrevs_stats                  |
| flaggedrevs_stats2                 |
| flaggedrevs_tracking               |
+------------------------------------+
6 rows in set (0.00 sec)

//cc @Reedy

@Zoranzoki21 can you confirm if this is good to go and if these are the tables that need to be dropped?

root@db1077.eqiad.wmnet[srwikinews]> show tables like '%flaggedre%';
+------------------------------------+
| Tables_in_srwikinews (%flaggedre%) |
+------------------------------------+
| flaggedrevs                        |
| flaggedrevs_promote                |
| flaggedrevs_statistics             |
| flaggedrevs_stats                  |
| flaggedrevs_stats2                 |
| flaggedrevs_tracking               |
+------------------------------------+
6 rows in set (0.00 sec)

//cc @Reedy

Sure, I can confirm.

Mentioned in SAL (#wikimedia-operations) [2018-12-11T06:45:57Z] <marostegui> Rename flaggedrevs tables on srwikinews on db1078 - T209761

Marostegui moved this task from Next to In progress on the DBA board.

I have renamed the tables on db1078 for now, to make sure nothing really breaks.
Renamed then on db1078 only:

root@cumin1001:/home/marostegui# for i in flaggedrevs flaggedrevs_promote flaggedrevs_statistics flaggedrevs_stats flaggedrevs_stats2 flaggedrevs_tracking; do mysql.py -hdb1078 srwikinews -e "set session sql_log_bin=0; rename table $i to T209761_$i;";done

root@cumin1001:/home/marostegui# mysql.py -hdb1078 srwikinews -e "show tables like 'T209761%';"
+---------------------------------+
| Tables_in_srwikinews (T209761%) |
+---------------------------------+
| T209761_flaggedrevs             |
| T209761_flaggedrevs_promote     |
| T209761_flaggedrevs_statistics  |
| T209761_flaggedrevs_stats       |
| T209761_flaggedrevs_stats2      |
| T209761_flaggedrevs_tracking    |
+---------------------------------+

Let's leave them for a few days before removing them for good.

Everything looks good..

Reedy added a comment.Dec 11 2018, 3:33 PM

There's possibly flaggedpage_config, flaggedimages, flaggedtemplates, flaggedpage_pending too based on https://github.com/wikimedia/mediawiki-extensions-FlaggedRevs/blob/master/backend/schema/mysql/FlaggedRevs.sql

MariaDB [srwikinews]> show tables like 'flagged%';
+---------------------------------+
| Tables_in_srwikinews (flagged%) |
+---------------------------------+
| flaggedimages                   |
| flaggedpage_config              |
| flaggedpage_pending             |
| flaggedpages                    |
| flaggedrevs                     |
| flaggedrevs_promote             |
| flaggedrevs_statistics          |
| flaggedrevs_stats               |
| flaggedrevs_stats2              |
| flaggedrevs_tracking            |
| flaggedtemplates                |
+---------------------------------+
11 rows in set (0.00 sec)

Thanks @Reedy!

root@cumin1001:~# for i in flaggedtemplates flaggedpage_config flaggedimages flaggedpage_pending flaggedpages ; do mysql.py -hdb1078 srwikinews -e "set session sql_log_bin=0; rename table $i to T209761_$i;";done
root@db1078.eqiad.wmnet[srwikinews]> show tables like '%flagged%';
+----------------------------------+
| Tables_in_srwikinews (%flagged%) |
+----------------------------------+
| T209761_flaggedimages            |
| T209761_flaggedpage_config       |
| T209761_flaggedpage_pending      |
| T209761_flaggedpages             |
| T209761_flaggedrevs              |
| T209761_flaggedrevs_promote      |
| T209761_flaggedrevs_statistics   |
| T209761_flaggedrevs_stats        |
| T209761_flaggedrevs_stats2       |
| T209761_flaggedrevs_tracking     |
| T209761_flaggedtemplates         |
+----------------------------------+
11 rows in set (0.00 sec)

No trace of errors on logtash for this wiki since 11th Dec. So I am going to proceed dropping these tables

Mentioned in SAL (#wikimedia-operations) [2018-12-13T08:15:20Z] <marostegui> Drop unused flaggedrevs tables from srwikinews - T209761

I have left a temporary backup at:

root@db1078:/srv/tmp/T209761
Marostegui closed this task as Resolved.Dec 13 2018, 8:33 AM

Tables are gone:

root@db1075.eqiad.wmnet[srwikinews]> show tables like '%flagged%';
Empty set (0.00 sec)
This comment was removed by Zoranzoki21.
Zoranzoki21 reopened this task as Open.EditedDec 13 2018, 9:46 PM

I still can find these tables at srwikinews.analytics.db.svc.eqiad.wmflabs:

zoranzoki21@tools-bastion-03:~$ mysql --defaults-file=$HOME/replica.my.cnf -h srwikinews.analytics.db.svc.eqiad.wmflabs srwikinews_p
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 MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32863599
Server version: 10.1.37-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [srwikinews_p]> show tables like '%flagged%';
+------------------------------------+
| Tables_in_srwikinews_p (%flagged%) |
+------------------------------------+
| flaggedimages                      |
| flaggedpage_config                 |
| flaggedpage_pending                |
| flaggedpages                       |
| flaggedrevs                        |
| flaggedrevs_promote                |
| flaggedrevs_statistics             |
| flaggedrevs_stats                  |
| flaggedrevs_stats2                 |
| flaggedrevs_tracking               |
| flaggedtemplates                   |
+------------------------------------+
11 rows in set (0.00 sec)

MariaDB [srwikinews_p]>

Looks like the tables are gone, the views just need dropping:

MariaDB [srwikinews_p]> select 1 from flaggedimages limit 1;
ERROR 1356 (HY000): View 'srwikinews_p.flaggedimages' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [srwikinews_p]> select 1 from flaggedpage_config limit 1;
ERROR 1356 (HY000): View 'srwikinews_p.flaggedpage_config' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [srwikinews_p]> select 1 from flaggedpage_pending limit 1;
ERROR 1356 (HY000): View 'srwikinews_p.flaggedpage_pending' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [srwikinews_p]> select 1 from flaggedpages limit 1;
ERROR 1356 (HY000): View 'srwikinews_p.flaggedpages' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [srwikinews_p]> select 1 from flaggedrevs_promote limit 1;
ERROR 1356 (HY000): View 'srwikinews_p.flaggedrevs_promote' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [srwikinews_p]> select 1 from flaggedrevs_statistics limit 1;
ERROR 1356 (HY000): View 'srwikinews_p.flaggedrevs_statistics' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [srwikinews_p]> select 1 from flaggedrevs_stats limit 1;
ERROR 1356 (HY000): View 'srwikinews_p.flaggedrevs_stats' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [srwikinews_p]> select 1 from flaggedrevs_stats2 limit 1;
ERROR 1356 (HY000): View 'srwikinews_p.flaggedrevs_stats2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [srwikinews_p]> select 1 from flaggedrevs_tracking limit 1;
ERROR 1356 (HY000): View 'srwikinews_p.flaggedrevs_tracking' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
MariaDB [srwikinews_p]> select 1 from flaggedtemplates limit 1;
ERROR 1356 (HY000): View 'srwikinews_p.flaggedtemplates' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Zoranzoki21 added a comment.EditedDec 13 2018, 9:58 PM

I still see tables at analytics.db.svc.eqiad.wmflabs

UPDATE: And still I see these tables, but when I use select 1 from flaggedimages; or simular, I get same output as up which provided @Krenair

What you are seeing there is views.

Zoranzoki21 closed this task as Resolved.Dec 13 2018, 10:05 PM

What you are seeing there is views.

I rollbacking status to resolved, let`s resume disscusion on IRC #wikimedia-tech.

Well, ideally relevant views would be dropped within such tasks.

i feel prod and cloud is quite relevant, created T211939, let's do it there.