Page MenuHomePhabricator

Remove abuse_filter_log.afl_filter column and adjust schema consequently from Wikimedia production
Closed, ResolvedPublic

Description

This is for T220791. We need to remove the old column (not used by the code anymore) and drop defaults from the new fields.

ALTERs to run: https://gerrit.wikimedia.org/r/plugins/gitiles/mediawiki/extensions/AbuseFilter/+/632b39f8ca32927781a3877982ae0a012438e4d4/db_patches/mysql/patch-remove-afl_filter.sql

DROP INDEX /*i*/afl_filter_timestamp ON /*_*/abuse_filter_log;
ALTER TABLE /*_*/abuse_filter_log
	DROP COLUMN afl_filter,
	ALTER COLUMN afl_filter_id DROP DEFAULT,
	ALTER COLUMN afl_global DROP DEFAULT;
  1. Where to run those changes: all.dblist
  2. When to run those changes: After deployment of wmf.2, 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: afl_filter should be removed from the views.

Progress

Event Timeline

This is also tested automatically on the beta cluster, right?

Also, we can't drop afl_filter_timestamp on databases where it is still named filter_timestamp, i.e. waiting for T281058: Rename AbuseFilter indexes for consistency to be done. Or we could change the alters for this to use the old name?

This is also tested automatically on the beta cluster, right?

Yes, in theory. I think I also tested this manually, but it was some time ago...

Also, we can't drop afl_filter_timestamp on databases where it is still named filter_timestamp, i.e. waiting for T281058: Rename AbuseFilter indexes for consistency to be done. Or we could change the alters for this to use the old name?

Good point, I forgot that T281058 is still open. I'm leaving this up to DBAs, but perhaps it would be better to finish T281058 first, since it's already half done.

Marostegui added subscribers: Kormat, Marostegui.

Yes, T281058 will be finished once @Kormat is back from holidays.

Marostegui triaged this task as Medium priority.Sep 27 2021, 4:39 AM
Jdforrester-WMF renamed this task from Remove abuse_filter_log.afl_filter column and adjust schema consequently to Remove abuse_filter_log.afl_filter column and adjust schema consequently from Wikimedia production.Sep 27 2021, 11:45 PM

This is blocked by: T291806

This task is scheduled for 6th October, so after that (and once T281058 is done) I will be able to start with this one.

s6 eqiad:

  • dbstore1005
  • db1180
  • db1173
  • db1168
  • db1165
  • db1155
  • db1140
  • db1131
  • db1113
  • db1098
  • db1096
  • clouddb1021
  • clouddb1019
  • clouddb1015

I have deployed this on db1180 and will leave it for a few days to make sure:

  1. no writes are indeed happening (replication will break if there're)
  2. no regression from that index removal.

The table now looks like:

root@cumin1001:/home/marostegui# mysql.py -hdb1180 ruwiki -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_global` tinyint(1) NOT NULL,
  `afl_filter_id` bigint(20) unsigned NOT NULL,
  `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 `afl_filter_timestamp_full` (`afl_global`,`afl_filter_id`,`afl_timestamp`),
  KEY `afl_user_timestamp` (`afl_user`,`afl_user_text`,`afl_timestamp`),
  KEY `afl_page_timestamp` (`afl_namespace`,`afl_title`,`afl_timestamp`),
  KEY `afl_ip_timestamp` (`afl_ip`,`afl_timestamp`),
  KEY `afl_wiki_timestamp` (`afl_wiki`,`afl_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=3283468 DEFAULT CHARSET=binary

The table now looks like: [...]

Just to confirm, this looks correct, thank you!

Mentioned in SAL (#wikimedia-operations) [2021-10-21T04:37:15Z] <marostegui> Deploy schema change on s6 codfw - T291719

Mentioned in SAL (#wikimedia-operations) [2021-10-21T04:47:14Z] <marostegui> Deploy schema change on s5 codfw - T291719

s5 eqiad

  • dbstore1003
  • db1161
  • db1154
  • db1150
  • db1144
  • db1130
  • db1113
  • db1110
  • db1100
  • db1096
  • clouddb1021
  • clouddb1020
  • clouddb1016

s2 eqiad

  • dbstore1007
  • db1182
  • db1170
  • db1162
  • db1156
  • db1155
  • db1146
  • db1129
  • db1122
  • db1105
  • db1102
  • clouddb1021
  • clouddb1018
  • clouddb1014

s4 eqiad

  • dbstore1007
  • db1160
  • db1155
  • db1150
  • db1149
  • db1148
  • db1147
  • db1146
  • db1145
  • db1144
  • db1143
  • db1142
  • db1141
  • db1138
  • db1121
  • clouddb1021
  • clouddb1019
  • clouddb1015

Mentioned in SAL (#wikimedia-operations) [2021-10-22T04:46:03Z] <marostegui_> Deploy schema change on s8 codfw - T291719

s8 eqiad

  • dbstore1005
  • db1178
  • db1177
  • db1172
  • db1171
  • db1167
  • db1154
  • db1126
  • db1116
  • db1114
  • db1111
  • db1109
  • db1104
  • db1101
  • db1099
  • clouddb1021
  • clouddb1020
  • clouddb1016

s1 eqiad

  • dbstore1003
  • db1184
  • db1169
  • db1164
  • db1163
  • db1154
  • db1140
  • db1139
  • db1135
  • db1134
  • db1133
  • db1119
  • db1118
  • db1106
  • db1105
  • db1099
  • clouddb1021
  • clouddb1017
  • clouddb1013

s7 progress

  • dbstore1003
  • db1181
  • db1174
  • db1171
  • db1170
  • db1158
  • db1155
  • db1136
  • db1127
  • db1101
  • db1098
  • clouddb1021
  • clouddb1018
  • clouddb1014

Mentioned in SAL (#wikimedia-operations) [2021-10-25T14:31:11Z] <marostegui> Deploy schema change on s3 codfw - T291719

s3 eqiad progress

  • dbstore1007
  • db1179
  • db1175
  • db1166
  • db1157
  • db1154
  • db1123
  • db1112
  • db1102
  • clouddb1021
  • clouddb1017
  • clouddb1013
Marostegui updated the task description. (Show Details)

All done