Page MenuHomePhabricator

Add abuse_filter_log.afl_filter_id and afl_global columns
Closed, ResolvedPublic

Description

This is for T220791

ALTERs to run: https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/AbuseFilter/+/537361/4/db_patches/patch-split-afl_filter.sql

ALTER TABLE /*_*/abuse_filter_log
	ADD COLUMN afl_global tinyint(1) NOT NULL DEFAULT 0 AFTER afl_filter,
	ADD COLUMN afl_filter_id BIGINT unsigned NOT NULL DEFAULT 0 AFTER afl_global,
	ALTER COLUMN afl_filter SET DEFAULT '';

CREATE INDEX /*i*/filter_timestamp_full ON /*_*/abuse_filter_log (afl_global,afl_filter_id,afl_timestamp);
  1. Where to run those changes: all.dblist
  2. When to run those changes: After deployment of wmf.25, next week
  3. If the schema change is backwards compatible: Yes
  4. If the schema change has been tested already on some of the test/beta wikis: Tested locally
  5. if the data should be made available on the labs replicas and/or dumps: Same as afl_filter for both columns.

Progress:

Details

Related Gerrit Patches:

Event Timeline

Daimona created this task.Sep 27 2019, 3:35 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 27 2019, 3:35 PM
Marostegui changed the task status from Open to Stalled.Sep 30 2019, 7:14 AM
Marostegui triaged this task as Medium priority.
Marostegui moved this task from Triage to Backlog on the DBA board.
Marostegui added a subscriber: Marostegui.

Stalling until wmf.25 is out: https://tools.wmflabs.org/versions/

Daimona changed the task status from Stalled to Open.Oct 4 2019, 12:31 PM

Change 540855 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] filtered_tables.txt: Add two new columns for abuse_filter_log

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

Change 540855 merged by Marostegui:
[operations/puppet@production] filtered_tables.txt: Add two new columns for abuse_filter_log

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

Marostegui updated the task description. (Show Details)Oct 4 2019, 12:45 PM
Marostegui moved this task from Backlog to Next on the DBA board.
Marostegui moved this task from Backlog to In progress on the Blocked-on-schema-change board.
Marostegui moved this task from Next to In progress on the DBA board.

Mentioned in SAL (#wikimedia-operations) [2020-01-02T13:23:44Z] <marostegui> Deploy schema change on s5 codfw master (db2123) with replication - T234052

I have altered s5 codfw wikis and this is how the table looks like now:

mysql.py -hdb2123 dewiki -e "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_global` tinyint(1) NOT NULL DEFAULT '0',
  `afl_filter_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `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 `filter_timestamp` (`afl_filter`,`afl_timestamp`),
  KEY `user_timestamp` (`afl_user`,`afl_user_text`,`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`),
  KEY `filter_timestamp_full` (`afl_global`,`afl_filter_id`,`afl_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=651043 DEFAULT CHARSET=binary

@Daimona ^ looks good to you?

Marostegui updated the task description. (Show Details)Jan 2 2020, 1:27 PM

@Daimona ^ looks good to you?

Yes, thanks!

@Daimona ^ looks good to you?

Thanks for confirming!

Marostegui added a comment.EditedJan 2 2020, 1:57 PM

s5 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1130
  • db1124
  • db1113
  • db1110
  • db1102
  • db1100
  • db1097
  • db1096
  • db1082

Mentioned in SAL (#wikimedia-operations) [2020-01-02T14:22:11Z] <marostegui> Deploy schema change on s5 eqiad hosts - T234052

Marostegui updated the task description. (Show Details)Jan 2 2020, 2:23 PM

Mentioned in SAL (#wikimedia-operations) [2020-01-02T14:27:34Z] <marostegui> Deploy schema change on s6 codfw master (db2129) with replication - T234052

Marostegui updated the task description. (Show Details)Jan 2 2020, 2:34 PM
Marostegui added a comment.EditedJan 2 2020, 2:36 PM

s6 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1139
  • db1131
  • db1125
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085
Marostegui updated the task description. (Show Details)Jan 2 2020, 2:59 PM

Mentioned in SAL (#wikimedia-operations) [2020-01-03T06:57:21Z] <marostegui> Deploy schema change on s6 eqiad hosts - T234052

Marostegui updated the task description. (Show Details)Jan 3 2020, 7:08 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-03T07:09:59Z] <marostegui> Deploy schema change on s2 codfw master, lag will appear on codfw - T234052

Marostegui updated the task description. (Show Details)Jan 3 2020, 7:33 AM
Marostegui added a comment.EditedJan 3 2020, 7:49 AM

s2 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1129
  • db1125
  • db1122
  • db1105
  • db1103
  • db1095
  • db1090
  • db1076
  • db1074

Mentioned in SAL (#wikimedia-operations) [2020-01-03T08:17:21Z] <marostegui> Deploy schema change on labswiki (wikitech) T234052

Marostegui updated the task description. (Show Details)Jan 3 2020, 8:17 AM
Marostegui added a subscriber: Andrew.

@Andrew can you check if this schema change applies also to labtestwiki and if so, run it?
Thanks!

Marostegui updated the task description. (Show Details)Jan 3 2020, 8:26 AM
Marostegui updated the task description. (Show Details)Jan 3 2020, 1:49 PM

Mentioned in SAL (#wikimedia-operations) [2020-01-03T13:50:48Z] <marostegui> Deploy schema change on s4 codfw (lag will appear on codfw s4) - T234052

Marostegui updated the task description. (Show Details)Jan 3 2020, 2:01 PM
Marostegui added a comment.EditedJan 3 2020, 2:09 PM

s4 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1138
  • db1125
  • db1121
  • db1103
  • db1102
  • db1097
  • db1091
  • db1084
  • db1081

Mentioned in SAL (#wikimedia-operations) [2020-01-03T14:58:08Z] <marostegui> Deploy schema changes on s2 and s4 eqiad hosts T234052

Marostegui updated the task description. (Show Details)Jan 3 2020, 3:02 PM

Mentioned in SAL (#wikimedia-operations) [2020-01-07T07:03:03Z] <marostegui> Deploy schema change on s8 codfw (this will generate lag on s8 codfw) - T234052

Marostegui updated the task description. (Show Details)Jan 7 2020, 7:24 AM
Marostegui added a comment.EditedJan 7 2020, 7:34 AM

s8 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1126
  • db1124
  • db1116
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087
Marostegui updated the task description. (Show Details)Jan 7 2020, 2:21 PM

Mentioned in SAL (#wikimedia-operations) [2020-01-07T14:21:29Z] <marostegui> Deploy schema change on s2 codfw master, this will generate lag on s2 codfw - T234052

Mentioned in SAL (#wikimedia-operations) [2020-01-07T14:22:16Z] <marostegui> Deploy schema change on s7 codfw master, this will generate lag on s7 codfw - T234052

Marostegui updated the task description. (Show Details)Jan 7 2020, 2:44 PM
Marostegui added a comment.EditedJan 7 2020, 3:01 PM

s7 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1136
  • db1125
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079

@Andrew I have executed this alter on labtestwiki on clouddb2001-dev

Mentioned in SAL (#wikimedia-operations) [2020-01-08T07:57:59Z] <marostegui> Deploy schema change on clouddb2001-dev.labtestwiki - T234052

Marostegui updated the task description. (Show Details)Jan 8 2020, 7:58 AM
Marostegui updated the task description. (Show Details)Jan 8 2020, 8:05 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-08T08:07:11Z] <marostegui> Deploy schema change on s1 codfw, there will be lag on s1 codfw - T234052

Marostegui updated the task description. (Show Details)Jan 8 2020, 8:18 AM
Marostegui added a comment.EditedJan 8 2020, 8:52 AM

s1 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1107
  • db1114
  • db1139
  • db1134
  • db1124
  • db1119
  • db1118
  • db1106
  • db1105
  • db1099
  • db1089
  • db1083
  • db1080
Marostegui updated the task description. (Show Details)Jan 9 2020, 8:19 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-09T12:41:43Z] <marostegui> Deploy schema change on s3 codfw, lag will appear on s3 codfw - T234052

Marostegui updated the task description. (Show Details)Jan 9 2020, 12:56 PM
Marostegui added a comment.EditedJan 9 2020, 1:53 PM

s3 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1124
  • db1123
  • db1112
  • db1095
  • db1078
  • db1075
Marostegui updated the task description. (Show Details)Jan 9 2020, 1:54 PM

@Andrew I have executed this alter on labtestwiki on clouddb2001-dev

Thank you! I was on vacation :)

Mentioned in SAL (#wikimedia-operations) [2020-01-13T06:00:13Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db1075 T234052', diff saved to https://phabricator.wikimedia.org/P10123 and previous config saved to /var/cache/conftool/dbconfig/20200113-060012-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-13T06:10:26Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repool db1075 T234052', diff saved to https://phabricator.wikimedia.org/P10126 and previous config saved to /var/cache/conftool/dbconfig/20200113-061025-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-01-13T06:11:39Z] <marostegui> Deploy schema change on s1 master (db1083) - T234052

Marostegui updated the task description. (Show Details)Jan 13 2020, 6:33 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-13T06:36:03Z] <marostegui> Deploy schema change on db1112 with replication (lag will appear on s3 on labs) - T234052

Marostegui closed this task as Resolved.Jan 13 2020, 9:38 AM
Marostegui updated the task description. (Show Details)
Marostegui added a subscriber: jcrespo.

All done