Page MenuHomePhabricator

Drop abuse_filter_log.afl_log_id in production
Closed, ResolvedPublic

Description

The column abuse_filter_log.afl_log_id was introduced in 2012 with rEABF5e4289ce4e240f5a91e7c6fd68bab32b8fe7074c, and is unused since then. It's unclear how it should be used, and there's no plan to ever use it.
The patch to drop the field has already been merged on master with https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/AbuseFilter/+/499770/.

From wikitech:

  1. ALTERs to run: https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/AbuseFilter/+/499770/2/db_patches/patch-drop_afl_log_id.sql
  2. Where to run those changes: all.dblist
  3. When to run those changes: As soon as wmf.13 reaches production. Since the column is unused there's nothing to be worried about I guess.
  4. If the schema change is backwards compatible: Yes, as long as all uses of the column will be removed in wmf.13
  5. If the schema change has been tested already on some of the test/beta wikis: Just a bit locally

Thanks!

Progress:

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Daimona updated the task description. (Show Details)Jul 1 2019, 8:29 AM

@Daimona as per https://tools.wmflabs.org/versions/ we are on .11 everywhere, this is safe to proceed then?

Actually, I was wrong. The change will be in wmf.13, according to the release tag in T214592 and direct verification on https://phabricator.wikimedia.org/diffusion/EABF/browse/wmf%252F1.34.0-wmf.11/abusefilter.tables.sql.
I amended the task description accordingly. Thanks all the same!

Marostegui changed the task status from Open to Stalled.Jul 1 2019, 8:40 AM

Thanks, I am going to stall this until then.

Daimona changed the task status from Stalled to Open.EditedJul 12 2019, 9:48 AM

wmf.13 everywhere, unstalling

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

Thanks for confirming. I am removing the Blocked-on-schema-change tag as there is nothing blocked on this removal (please correct me if I am wrong). So this is part of our our clean up backlog.
What I will do is rename the column on an enwiki host and leave it for a few days to make sure nothing really uses it.

Thanks for confirming. I am removing the Blocked-on-schema-change tag as there is nothing blocked on this removal (please correct me if I am wrong).

Right, thanks. I just blindly followed the list on wikitech :-)

What I will do is rename the column on an enwiki host and leave it for a few days to make sure nothing really uses it.

Great, thanks. I'll also keep an eye on tendril to see if we really get a better performance without this column per T214592#5065643.

Marostegui updated the task description. (Show Details)Jul 18 2019, 5:48 AM

Change 524173 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-codfw.php: Depool db2116

https://gerrit.wikimedia.org/r/524173

Change 524173 merged by jenkins-bot:
[operations/mediawiki-config@master] db-codfw.php: Depool db2116

https://gerrit.wikimedia.org/r/524173

Mentioned in SAL (#wikimedia-operations) [2019-07-18T08:56:04Z] <marostegui> Drop afl_log_id column from enwiki.abuse_filter_log on db2116 T226851

Marostegui added a comment.EditedJul 18 2019, 10:30 AM

I have altered db2116 for now, to make sure nothing writes to that column (if it does, it will break replication there, but won't impact the users). Will leave it for a few days before altering an active slave on eqiad (which is active and we can monitor if something reads from it for another few days).

root@db2116.codfw.wmnet[enwiki]> show create table abuse_filter_log\G
*************************** 1. row ***************************
       Table: abuse_filter_log
Create Table: CREATE TABLE `abuse_filter_log` (
  `afl_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `afl_filter` varbinary(64) NOT NULL DEFAULT '',
  `afl_user` bigint(20) unsigned NOT NULL DEFAULT '0',
  `afl_user_text` varbinary(255) NOT NULL DEFAULT '',
  `afl_ip` varbinary(255) NOT NULL DEFAULT '',
  `afl_action` varbinary(255) NOT NULL DEFAULT '',
  `afl_actions` varbinary(255) NOT NULL DEFAULT '',
  `afl_var_dump` blob NOT NULL,
  `afl_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `afl_namespace` int(11) NOT NULL,
  `afl_title` varbinary(255) NOT NULL DEFAULT '',
  `afl_wiki` varbinary(64) DEFAULT NULL,
  `afl_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `afl_patrolled_by` int(10) unsigned NOT NULL DEFAULT '0',
  `afl_rev_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`afl_id`),
  KEY `afl_timestamp` (`afl_timestamp`),
  KEY `afl_rev_id` (`afl_rev_id`),
  KEY `user_timestamp` (`afl_user`,`afl_user_text`,`afl_timestamp`),
  KEY `filter_timestamp` (`afl_filter`,`afl_timestamp`),
  KEY `page_timestamp` (`afl_namespace`,`afl_title`,`afl_timestamp`),
  KEY `ip_timestamp` (`afl_ip`,`afl_timestamp`),
  KEY `wiki_timestamp` (`afl_wiki`,`afl_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=24431867 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.04 sec)
Marostegui updated the task description. (Show Details)Jul 18 2019, 10:30 AM
Marostegui updated the task description. (Show Details)Jul 22 2019, 7:18 AM

Change 524716 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1134

https://gerrit.wikimedia.org/r/524716

Change 524716 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1134

https://gerrit.wikimedia.org/r/524716

Mentioned in SAL (#wikimedia-operations) [2019-07-22T07:27:27Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Depool db1134 for schema change T226851 (duration: 00m 56s)

Mentioned in SAL (#wikimedia-operations) [2019-07-22T07:27:43Z] <marostegui> Drop afl_log_id column from enwiki.abuse_filter_log on db1134 T226851

I have altered db1134, and will monitor the errors to make sure nothing complains about this column being gone.

root@db1134.eqiad.wmnet[enwiki]> show create table abuse_filter_log\G
*************************** 1. row ***************************
       Table: abuse_filter_log
Create Table: CREATE TABLE `abuse_filter_log` (
  `afl_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `afl_filter` varbinary(64) NOT NULL DEFAULT '',
  `afl_user` bigint(20) unsigned NOT NULL DEFAULT '0',
  `afl_user_text` varbinary(255) NOT NULL DEFAULT '',
  `afl_ip` varbinary(255) NOT NULL DEFAULT '',
  `afl_action` varbinary(255) NOT NULL DEFAULT '',
  `afl_actions` varbinary(255) NOT NULL DEFAULT '',
  `afl_var_dump` blob NOT NULL,
  `afl_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `afl_namespace` int(11) NOT NULL,
  `afl_title` varbinary(255) NOT NULL DEFAULT '',
  `afl_wiki` varbinary(64) DEFAULT NULL,
  `afl_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `afl_patrolled_by` int(10) unsigned NOT NULL DEFAULT '0',
  `afl_rev_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`afl_id`),
  KEY `afl_timestamp` (`afl_timestamp`),
  KEY `afl_rev_id` (`afl_rev_id`),
  KEY `user_timestamp` (`afl_user`,`afl_user_text`,`afl_timestamp`),
  KEY `filter_timestamp` (`afl_filter`,`afl_timestamp`),
  KEY `page_timestamp` (`afl_namespace`,`afl_title`,`afl_timestamp`),
  KEY `ip_timestamp` (`afl_ip`,`afl_timestamp`),
  KEY `wiki_timestamp` (`afl_wiki`,`afl_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=24453155 DEFAULT CHARSET=binary
1 row in set (0.00 sec)
Marostegui updated the task description. (Show Details)Jul 22 2019, 7:52 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-22T07:56:59Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Repool db1134 after schema change T226851 (duration: 00m 51s)

I am going to start dropping this column on s6 (starting with codfw first).
I have double checked that there are rows where afl_log_id isn't NULL on all the s6 wikis (frwiki, jawiki and ruwiki).

Marostegui updated the task description. (Show Details)Jul 23 2019, 9:52 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-23T09:53:57Z] <marostegui> Drop abuse_filter_log.afl_log_id from s6 codfw with replication (this will cause lag in s6 codfw) - T226851

Marostegui updated the task description. (Show Details)Jul 23 2019, 10:00 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-23T10:17:21Z] <marostegui> Drop abuse_filter_log.afl_log_id from db1096:3316, db1139:3316 and dbstore1005:3316 T226851

Marostegui added a comment.EditedJul 23 2019, 10:23 AM

s6 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1139
  • db1131
  • db1125
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085
  • db1061
Marostegui updated the task description. (Show Details)Jul 23 2019, 10:23 AM
Marostegui updated the task description. (Show Details)Jul 23 2019, 1:01 PM

Mentioned in SAL (#wikimedia-operations) [2019-07-23T13:06:17Z] <marostegui> Drop abuse_filter_log.afl_log_id from s8 codfw (lag will happen on codfw s8) - T226851

Marostegui updated the task description. (Show Details)Jul 23 2019, 1:16 PM
Marostegui added a comment.EditedJul 23 2019, 1:47 PM

s8 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1126
  • db1124
  • db1116
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087
  • db1071
Marostegui updated the task description. (Show Details)Jul 23 2019, 1:47 PM

Mentioned in SAL (#wikimedia-operations) [2019-07-24T07:57:59Z] <marostegui> Drop abuse_filter_log.afl_log_id from wikidata in eqiad - T226851

Marostegui updated the task description. (Show Details)Jul 24 2019, 8:30 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-24T08:34:59Z] <marostegui> Drop abuse_filter_log.afl_log_id in s2 codfw (lag will appear on codfw) - T226851

Marostegui updated the task description. (Show Details)Jul 24 2019, 8:50 AM
Marostegui added a comment.EditedJul 24 2019, 9:56 AM

s2 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1129
  • db1125
  • db1122
  • db1105
  • db1103
  • db1095
  • db1090
  • db1076
  • db1074
  • db1066
Marostegui updated the task description. (Show Details)Jul 24 2019, 9:56 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-24T10:04:23Z] <marostegui> Drop abuse_filter_log.afl_log_id from labswiki (wikitech) and labtestwiki - T226851

Marostegui updated the task description. (Show Details)Jul 24 2019, 10:05 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-24T13:31:33Z] <marostegui> Drop abuse_filter_log.afl_log_id in s2 eqiad - T226851

Marostegui updated the task description. (Show Details)Jul 24 2019, 2:33 PM

Mentioned in SAL (#wikimedia-operations) [2019-07-24T14:40:46Z] <marostegui> Drop abuse_filter_log.afl_log_id in s5 codfw (lag will appear on codfw) - T226851

Marostegui updated the task description. (Show Details)Jul 24 2019, 2:41 PM

Mentioned in SAL (#wikimedia-operations) [2019-07-24T14:43:24Z] <marostegui> Drop abuse_filter_log.afl_log_id in s5 eqiad - T226851

Marostegui added a comment.EditedJul 24 2019, 2:44 PM

s5 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1130
  • db1124
  • db1113
  • db1110
  • db1102
  • db1100
  • db1097
  • db1096
  • db1082
  • db1070
Marostegui updated the task description. (Show Details)Jul 24 2019, 2:53 PM
Marostegui updated the task description. (Show Details)Jul 25 2019, 12:57 PM

Mentioned in SAL (#wikimedia-operations) [2019-07-25T13:09:20Z] <marostegui> Drop abuse_filter_log.afl_log_id in s5 eqiad - T226851

Marostegui added a comment.EditedJul 25 2019, 1:19 PM

s4 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1138
  • db1125
  • db1121
  • db1103
  • db1102
  • db1097
  • db1091
  • db1084
  • db1081
Marostegui updated the task description. (Show Details)Jul 25 2019, 1:20 PM
Marostegui updated the task description. (Show Details)Jul 25 2019, 1:40 PM

The abuse_filter_log table is apparently missing on the replicas, presumably because of this. Maybe the view definition needs to be updated?

MariaDB [enwiki_p]> DESCRIBE itwiki_p.abuse_filter_log;
ERROR 1356 (HY000): View 'itwiki_p.abuse_filter_log' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Change 525713 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] maintain-views: Remove afl_log_id

https://gerrit.wikimedia.org/r/525713

The abuse_filter_log table is apparently missing on the replicas, presumably because of this. Maybe the view definition needs to be updated?

MariaDB [enwiki_p]> DESCRIBE itwiki_p.abuse_filter_log;
ERROR 1356 (HY000): View 'itwiki_p.abuse_filter_log' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Thanks for letting me know. It is indeed because of this change.
I have sent a patch for review to the cloud-services-team, once merged I will re-create the views for the table on those wikis where it has already been deleted.

Change 525713 merged by Marostegui:
[operations/puppet@production] maintain-views: Remove afl_log_id

https://gerrit.wikimedia.org/r/525713

The abuse_filter_log table is apparently missing on the replicas, presumably because of this. Maybe the view definition needs to be updated?

MariaDB [enwiki_p]> DESCRIBE itwiki_p.abuse_filter_log;
ERROR 1356 (HY000): View 'itwiki_p.abuse_filter_log' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Thanks for letting me know. It is indeed because of this change.
I have sent a patch for review to the cloud-services-team, once merged I will re-create the views for the table on those wikis where it has already been deleted.

This should now be fixed

root@cumin1001:~# for i in labsdb1009 labsdb1010 labsdb1011; do echo $i; mysql.py -h$i itwiki -e "describe abuse_filter_log";done
labsdb1009
+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| afl_id           | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| afl_filter       | varbinary(64)       | NO   | MUL |         |                |
| afl_user         | bigint(20) unsigned | NO   | MUL | 0       |                |
| afl_user_text    | varbinary(255)      | NO   |     |         |                |
| afl_ip           | varbinary(255)      | NO   | MUL |         |                |
| afl_action       | varbinary(255)      | NO   |     |         |                |
| afl_actions      | varbinary(255)      | NO   |     |         |                |
| afl_var_dump     | blob                | NO   |     | NULL    |                |
| afl_timestamp    | varbinary(14)       | NO   | MUL |         |                |
| afl_namespace    | int(11)             | NO   | MUL | NULL    |                |
| afl_title        | varbinary(255)      | NO   |     |         |                |
| afl_wiki         | varbinary(64)       | YES  | MUL | NULL    |                |
| afl_deleted      | tinyint(1)          | NO   |     | 0       |                |
| afl_patrolled_by | int(10) unsigned    | NO   |     | 0       |                |
| afl_rev_id       | int(10) unsigned    | YES  | MUL | NULL    |                |
+------------------+---------------------+------+-----+---------+----------------+
labsdb1010
+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| afl_id           | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| afl_filter       | varbinary(64)       | NO   | MUL |         |                |
| afl_user         | bigint(20) unsigned | NO   | MUL | 0       |                |
| afl_user_text    | varbinary(255)      | NO   |     |         |                |
| afl_ip           | varbinary(255)      | NO   | MUL |         |                |
| afl_action       | varbinary(255)      | NO   |     |         |                |
| afl_actions      | varbinary(255)      | NO   |     |         |                |
| afl_var_dump     | blob                | NO   |     | NULL    |                |
| afl_timestamp    | varbinary(14)       | NO   | MUL |         |                |
| afl_namespace    | int(11)             | NO   | MUL | NULL    |                |
| afl_title        | varbinary(255)      | NO   |     |         |                |
| afl_wiki         | varbinary(64)       | YES  | MUL | NULL    |                |
| afl_deleted      | tinyint(1)          | NO   |     | 0       |                |
| afl_patrolled_by | int(10) unsigned    | NO   |     | 0       |                |
| afl_rev_id       | int(10) unsigned    | YES  | MUL | NULL    |                |
+------------------+---------------------+------+-----+---------+----------------+
labsdb1011
+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| afl_id           | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| afl_filter       | varbinary(64)       | NO   | MUL |         |                |
| afl_user         | bigint(20) unsigned | NO   | MUL | 0       |                |
| afl_user_text    | varbinary(255)      | NO   |     |         |                |
| afl_ip           | varbinary(255)      | NO   | MUL |         |                |
| afl_action       | varbinary(255)      | NO   |     |         |                |
| afl_actions      | varbinary(255)      | NO   |     |         |                |
| afl_var_dump     | blob                | NO   |     | NULL    |                |
| afl_timestamp    | varbinary(14)       | NO   | MUL |         |                |
| afl_namespace    | int(11)             | NO   | MUL | NULL    |                |
| afl_title        | varbinary(255)      | NO   |     |         |                |
| afl_wiki         | varbinary(64)       | YES  | MUL | NULL    |                |
| afl_deleted      | tinyint(1)          | NO   |     | 0       |                |
| afl_patrolled_by | int(10) unsigned    | NO   |     | 0       |                |
| afl_rev_id       | int(10) unsigned    | YES  | MUL | NULL    |                |
+------------------+---------------------+------+-----+---------+----------------+

Thanks for reporting it

Marostegui updated the task description. (Show Details)Jul 29 2019, 5:03 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-29T05:18:11Z] <marostegui> Drop Drop abuse_filter_log.afl_log_id from s7 codfw with replication (this will cause lag in s7 codfw) - T226851

Marostegui added a comment.EditedJul 29 2019, 6:25 AM

s7 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1136
  • db1125
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079
  • db1062
Marostegui updated the task description. (Show Details)Jul 29 2019, 6:26 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-29T08:16:25Z] <marostegui> Drop abuse_filter_log.afl_log_id in s7 eqiad - T226851

Marostegui updated the task description. (Show Details)Jul 29 2019, 9:24 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-31T05:44:18Z] <marostegui> Drop abuse_filter_log.afl_log_id from s1 codfw with replication (this will cause lag in s1 codfw) - T226851

Marostegui updated the task description. (Show Details)EditedJul 31 2019, 6:15 AM

s1 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1139
  • db1134
  • db1124
  • db1119
  • db1118
  • db1106
  • db1105
  • db1099
  • db1089
  • db1083
  • db1080
  • db1067
Marostegui updated the task description. (Show Details)Jul 31 2019, 6:16 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-31T07:39:59Z] <marostegui> Drop abuse_filter_log.afl_log_id in s1 eqiad - T226851

Marostegui updated the task description. (Show Details)Jul 31 2019, 12:52 PM

Mentioned in SAL (#wikimedia-operations) [2019-07-31T12:53:35Z] <marostegui> Drop abuse_filter_log.afl_log_id from s3 codfw with replication (this will cause lag in s3 codfw) - T226851

Marostegui updated the task description. (Show Details)Jul 31 2019, 1:04 PM
Marostegui added a comment.EditedJul 31 2019, 1:15 PM

s3 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1124
  • db1123
  • db1112
  • db1095
  • db1078
  • db1075
Marostegui updated the task description. (Show Details)Jul 31 2019, 1:15 PM

Mentioned in SAL (#wikimedia-operations) [2019-07-31T13:15:36Z] <marostegui> Drop abuse_filter_log.afl_log_id in s3 eqiad - T226851

Marostegui updated the task description. (Show Details)Aug 1 2019, 5:01 AM

Change 526837 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] filtered_tables: Remove abuse_filter_log.afl_log_id

https://gerrit.wikimedia.org/r/526837

Change 526837 merged by Marostegui:
[operations/puppet@production] filtered_tables: Remove abuse_filter_log.afl_log_id

https://gerrit.wikimedia.org/r/526837

Marostegui closed this task as Resolved.Aug 1 2019, 5:15 AM
Marostegui updated the task description. (Show Details)

All done!

Change 539710 had a related patch set uploaded (by Daimona Eaytoy; owner: Daimona Eaytoy):
[operations/puppet@production] Remove afl_log_id from maintain-views

https://gerrit.wikimedia.org/r/539710

Change 539710 abandoned by Daimona Eaytoy:
Remove afl_log_id from maintain-views

Reason:
Uh, already done with I81f285fe5c57facb661ebb2270e6cc2f7bd03dca.

Codesearch was showing it though...

https://gerrit.wikimedia.org/r/539710