Page MenuHomePhabricator

Schema change for watchlist.wl_notificationtimestamp going binary(14) from varbinary(14)
Closed, ResolvedPublic

Description

(Part of T42626: Standardise type of timestamp database fields (MySQL) and T230428: Migrate tables.sql to abstract schema)

  1. ALTERs to run: https://gerrit.wikimedia.org/r/c/mediawiki/core/+/642685/1/maintenance/archives/patch-watchlist-wl_notificationtimestamp.sql
  2. Where to run those changes: all.dblist
  3. When to run those changes: At any time
  4. If the schema change is backwards compatible: Yes
  5. If the schema change has been tested already on some of the test/beta wikis: Tested in beta cluster.
  6. if the data should be made available on the labs replicas and/or dumps: Yes, data in this table is public

The field is all NULL in enwiki.

Progress:

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Marostegui triaged this task as Medium priority.Nov 23 2020, 7:19 AM
Marostegui added a project: DBA.
Marostegui moved this task from Triage to Ready 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 Ready to In progress on the DBA board.

Will alter a host in s6 codfw and eqiad and leave it till next week before going for all of them

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db2141
  • db2129
  • db2124
  • db2117
  • db2114
  • db2097
  • db2095
  • db2089
  • db2087
  • db2076
  • db1180
  • db1173
  • db1168
  • db1165
  • db1155
  • db1140
  • db1139
  • db1131
  • db1113
  • db1098
  • db1096
  • db1085
  • clouddb1021
  • clouddb1019
  • clouddb1015

Mentioned in SAL (#wikimedia-operations) [2021-04-28T07:03:40Z] <marostegui> Deploy schema change on db2089:3316 and db1098:3316 T266486 T268392 T273360

Mentioned in SAL (#wikimedia-operations) [2021-04-28T07:40:47Z] <marostegui> Deploy schema change on db1098:3316 and db1098:3316 T266486 T268392 T273360

@Ladsgroup this looks good?

# mysql.py -hdb2089:3316 frwiki -e "show create table watchlist\G"
*************************** 1. row ***************************
       Table: watchlist
Create Table: CREATE TABLE `watchlist` (
  `wl_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `wl_user` int(5) unsigned NOT NULL DEFAULT 0,
  `wl_namespace` int(11) NOT NULL DEFAULT 0,
  `wl_title` varbinary(255) NOT NULL DEFAULT '',
  `wl_notificationtimestamp` binary(14) DEFAULT NULL,
  PRIMARY KEY (`wl_id`),
  UNIQUE KEY `wl_user` (`wl_user`,`wl_namespace`,`wl_title`),
  KEY `wl_user_notificationtimestamp` (`wl_user`,`wl_notificationtimestamp`),
  KEY `wl_namespace_title` (`wl_namespace`,`wl_title`)
) ENGINE=InnoDB AUTO_INCREMENT=42264822 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

WRT this patch it looks okay while wl_user shouldn't have a default (at least comparing with https://github.com/wikimedia/mediawiki/blob/master/maintenance/tables-generated.sql#L317). I'll check that further. It's unrelated to this schema change.

Thanks, removing a default is an online DDL so it can be done in a different task and it will be easier than changing a data type

It seems it's a drift back from 2007. I will create a separate ticket for that later (it'll just show up in the drift report).

Mentioned in SAL (#wikimedia-operations) [2021-05-04T05:36:56Z] <marostegui> Deploy schema change on s6 codfw, lag will appear - T266486 T268392 T273360

s6 is done, pending the master. It will be finished once we've completed the migration to 10.4 on T280751

s5 progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1161
  • db1154
  • db1150
  • db1145
  • db1144
  • db1130
  • db1113
  • db1110
  • db1100
  • db1096
  • db1082
  • clouddb1021
  • clouddb1020
  • clouddb1016

s5 is done, pending the master.

Mentioned in SAL (#wikimedia-operations) [2021-05-07T06:17:28Z] <marostegui> Deploy schema change on s2 codfw, lag will appear T266486 T268392 T273360

s2 eqiad

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1182
  • db1171
  • db1170
  • db1162
  • db1156
  • db1155
  • db1146
  • db1129
  • db1122
  • db1105
  • db1102
  • db1074
  • clouddb1021
  • clouddb1018
  • clouddb1014

s7 eqiad

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1181
  • db1174
  • db1170
  • db1158
  • db1155
  • db1136
  • db1127
  • db1124
  • db1116
  • db1101
  • db1098
  • db1079
  • clouddb1021
  • clouddb1018
  • clouddb1014

Mentioned in SAL (#wikimedia-operations) [2021-05-17T11:55:23Z] <marostegui> Deploy schema change on s8 codfw, lag will appear in codfw T266486 T268392 T273360

s8 eqiad

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1178
  • db1177
  • db1172
  • db1167
  • db1154
  • db1126
  • db1116
  • db1114
  • db1111
  • db1109
  • db1104
  • db1101
  • db1099
  • db1087
  • clouddb1021
  • clouddb1020
  • clouddb1016

Mentioned in SAL (#wikimedia-operations) [2021-05-18T07:05:59Z] <marostegui> Deploy schema change on s4 codfw, lag will appear in codfw T266486 T268392 T273360

s4 eqiad

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

Mentioned in SAL (#wikimedia-operations) [2021-05-19T07:31:44Z] <marostegui> Deploy schema change on s3 codfw, lag will appear in codfw T266486 T268392 T273360

s3 eqiad

  • dbstore1004
  • db1179
  • db1175
  • db1171
  • db1166
  • db1157
  • db1154
  • db1123
  • db1112
  • db1102
  • clouddb1021
  • clouddb1017
  • clouddb1013

Mentioned in SAL (#wikimedia-operations) [2021-05-20T10:15:00Z] <marostegui> Deploy schema change on s1 codfw, lag will appear in codfw T266486 T268392 T273360

s3 is done, only the master pending, to be done once the switchover is completed (T283131)

s1 eqiad

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

Mentioned in SAL (#wikimedia-operations) [2021-05-26T04:35:32Z] <marostegui> Deploy schema change on db1106, this will generate lag on s1 (enwiki) on wiki replicas T266486 T268392 T273360

s1 is fully done, only pending the master

Mentioned in SAL (#wikimedia-operations) [2021-06-03T05:20:04Z] <marostegui> Deploy schema change on db1121, lag will appear on s4 (commonswiki) wiki replicas - T266486 T268392 T273360

Change 697898 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] db1121,db1155,clouddb*s4: Disable notifications

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

Change 697898 merged by Marostegui:

[operations/puppet@production] db1121,db1155,clouddb*s4: Disable notifications

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

Marostegui changed the task status from Open to Stalled.Jun 4 2021, 5:13 AM
Marostegui updated the task description. (Show Details)
Marostegui moved this task from In progress to Blocked on the DBA board.

All done, pending master swap or master DC switch to complete all the masters that are pending

s8 eqiad master is done

root@db1104.eqiad.wmnet[wikidatawiki]> set session sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)

root@db1104.eqiad.wmnet[wikidatawiki]> ALTER TABLE /*_*/watchlist
    ->   MODIFY wl_notificationtimestamp BINARY(14) DEFAULT NULL;
Query OK, 48690834 rows affected (37 min 29.572 sec)
Records: 48690834  Duplicates: 0  Warnings: 0
Marostegui changed the task status from Stalled to Open.Jul 1 2021, 2:22 PM
Marostegui moved this task from Blocked to In progress on the DBA board.
Marostegui updated the task description. (Show Details)

All done