Page MenuHomePhabricator

Schema change to make rc_id unsigned and rc_timestamp BINARY
Closed, ResolvedPublic

Description

Implementing T62962: The primary key of recentchanges (rc_id) table should be unsigned

  1. ALTERs to run: https://gerrit.wikimedia.org/r/c/mediawiki/core/+/667356/4/maintenance/archives/patch-recentchanges-rc_id.sql and and https://gerrit.wikimedia.org/r/c/mediawiki/core/+/667158/2/maintenance/archives/patch-recentchanges-rc_timestamp.sql
  2. Where to run those changes: all.dblist
  3. When to run those changes: At any time (preferably before it runs out :D)
  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.

Progress

Related Objects

StatusSubtypeAssignedTask
ResolvedMarostegui
DeclinedNone
ResolvedMarostegui
ResolvedJclark-ctr
ResolvedMarostegui
ResolvedMarostegui
ResolvedRequestwiki_willy
ResolvedLegoktm
ResolvedMarostegui
ResolvedMarostegui
ResolvedKormat
ResolvedMarostegui
ResolvedTrizek-WMF
ResolvedKormat
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
Resolvedsgrabarczuk
ResolvedMarostegui
Resolved Cmjohnson
ResolvedMarostegui
ResolvedMarostegui
ResolvedMarostegui
ResolvedRequestwiki_willy
ResolvedRequest Cmjohnson
ResolvedRequest Cmjohnson
ResolvedRequest Cmjohnson
ResolvedRequest Cmjohnson

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.
Marostegui moved this task from Triage to Ready on the DBA board.

Yeah, I will keep an eye on it. I will probably start with s6 (frwiki, jawiki and ruwiki).

Note: This requires a master failover for each section as we are changing the data type and that cannot be done online

I have altered db2089:3316 and will leave it running for a few days before going for a host in eqiad.
@Ladsgroup does this look good?

# mysql.py -hdb2089:3316 frwiki -e "show create table recentchanges\G" | grep -v KEY
*************************** 1. row ***************************
       Table: recentchanges
Create Table: CREATE TABLE `recentchanges` (
  `rc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rc_timestamp` varbinary(14) NOT NULL,
  `rc_actor` bigint(20) unsigned NOT NULL,
  `rc_namespace` int(11) NOT NULL DEFAULT 0,
  `rc_title` varbinary(255) NOT NULL DEFAULT '',
  `rc_comment_id` bigint(20) unsigned NOT NULL,
  `rc_minor` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `rc_bot` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `rc_new` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `rc_cur_id` int(10) unsigned NOT NULL DEFAULT 0,
  `rc_this_oldid` int(10) unsigned NOT NULL DEFAULT 0,
  `rc_last_oldid` int(10) unsigned NOT NULL DEFAULT 0,
  `rc_type` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `rc_source` varbinary(16) NOT NULL DEFAULT '',
  `rc_patrolled` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `rc_ip` varbinary(40) NOT NULL DEFAULT '',
  `rc_old_len` int(10) DEFAULT NULL,
  `rc_new_len` int(10) DEFAULT NULL,
  `rc_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `rc_logid` int(10) unsigned NOT NULL DEFAULT 0,
  `rc_log_type` varbinary(255) DEFAULT NULL,
  `rc_log_action` varbinary(255) DEFAULT NULL,
  `rc_params` blob DEFAULT NULL,
  • labsdb1011
  • labsdb1010
  • labsdb1009 (host has crashed T276980 - should get it if replication is ever back)
  • dbstore1005
  • db2141
  • db2129
  • db2124
  • db2117
  • db2114
  • db2097
  • db2095
  • db2089
  • db2087
  • db2076
  • db1173
  • db1168
  • db1155
  • db1140
  • db1139
  • db1131
  • db1125
  • db1113
  • db1098
  • db1096
  • db1088
  • db1085
  • clouddb1019
  • clouddb1015
  • clouddb1021

Excellent - will alter a host in eqiad on Monday

Marostegui renamed this task from Schema change to make rc_id unsigned to Schema change to make rc_id unsigned and rc_timestamp VARBINARY.Mar 4 2021, 2:58 PM
Marostegui renamed this task from Schema change to make rc_id unsigned and rc_timestamp VARBINARY to Schema change to make rc_id unsigned and rc_timestamp BINARY.

Applied also the change about rc_timestamp being binary to already altered host (db2089:3316):

root@db2089.codfw.wmnet[frwiki]> ALTER TABLE /*_*/recentchanges
    ->     MODIFY rc_timestamp BINARY(14) NOT NULL;
Query OK, 2409364 rows affected (3 min 20.112 sec)
Records: 2409364  Duplicates: 0  Warnings: 0

root@db2089.codfw.wmnet[frwiki]> use jawiki
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@db2089.codfw.wmnet[jawiki]> ALTER TABLE  recentchanges     MODIFY rc_timestamp BINARY(14) NOT NULL;
Query OK, 1060786 rows affected (1 min 26.109 sec)
Records: 1060786  Duplicates: 0  Warnings: 0

root@db2089.codfw.wmnet[jawiki]> use ruwiki
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@db2089.codfw.wmnet[ruwiki]> ALTER TABLE  recentchanges     MODIFY rc_timestamp BINARY(14) NOT NULL;
Query OK, 1753580 rows affected (2 min 23.318 sec)
Records: 1753580  Duplicates: 0  Warnings: 0

Mentioned in SAL (#wikimedia-operations) [2021-03-09T06:19:02Z] <marostegui> Deploy schema change on s6 codfw (there will be lag on codfw) T276150 T276156

After s6 I will go for s7 as we need to switch that master in two weeks, we might as well get this schema change fully done on the master too

s6 is done, only pending the master switchover.

Mentioned in SAL (#wikimedia-operations) [2021-03-10T07:52:39Z] <marostegui> Deploy schema change on s7 codfw (lag will appear) T276150 T276156

s7 progress

  • labsdb1011
  • labsdb1010
  • labsdb1009 (host has crashed T276980 - should get it if replication is ever back)
  • dbstore1003
  • db1174
  • db1170
  • db1155
  • db1136
  • db1127
  • db1125
  • db1116
  • db1101
  • db1098
  • db1086
  • db1079
  • clouddb1021
  • clouddb1018
  • clouddb1014

s7 is done, only pending the master. Will be done after the scheduled switchover. T274336

Mentioned in SAL (#wikimedia-operations) [2021-03-11T09:45:30Z] <marostegui> Deploy schema change on s5 codfw master, lag will appear - T276150 T276156

s5 progress

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

Mentioned in SAL (#wikimedia-operations) [2021-03-12T06:30:43Z] <marostegui> Deploy schema change on s2 codfw master, lag will appear - T276150 T276156

s2 eqiad progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1171
  • db1170
  • db1162 (broken - will get it once it is reimaged)
  • db1155
  • db1146
  • db1129
  • db1125
  • db1122
  • db1105
  • db1076
  • db1074
  • clouddb1021
  • clouddb1018
  • clouddb1014

s8 progress

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

Mentioned in SAL (#wikimedia-operations) [2021-03-17T11:49:12Z] <marostegui> Deploy schema change on s8, lag will appear on wiki replicas T276150 T276156

Mentioned in SAL (#wikimedia-operations) [2021-03-18T07:19:32Z] <marostegui> Deploy schema change on s4 codfw master, lag will appear - T276150 T276156

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

Mentioned in SAL (#wikimedia-operations) [2021-04-05T09:50:36Z] <marostegui> Deploy schema change on s1 codfw, lag will appear in codfw - T276150 T276156

s1 eqiad

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

Mentioned in SAL (#wikimedia-operations) [2021-04-07T11:39:53Z] <marostegui> Deploy schema change on s3 codfw, lag will appear T276150 T276156

s3 eqiad

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1175
  • db1171
  • db1166
  • db1157
  • db1154
  • db1124
  • db1123
  • db1112
  • clouddb1021
  • clouddb1017
  • clouddb1013
Marostegui changed the task status from Open to Stalled.Apr 13 2021, 8:04 AM
Marostegui updated the task description. (Show Details)
Marostegui moved this task from In progress to Blocked on the DBA board.

This is all done and only waiting for the masters to be done, either master switchover or DC switchover.

db1100 (old s5 master) done

Mentioned in SAL (#wikimedia-operations) [2021-07-01T11:35:33Z] <marostegui> Deploy schema change on s8 eqiad master T276150

Marostegui changed the task status from Stalled to Open.Jul 1 2021, 1:01 PM
Marostegui updated the task description. (Show Details)

Mentioned in SAL (#wikimedia-operations) [2021-07-01T13:02:59Z] <marostegui> Deploy schema change on s2 eqiad master T276150

Marostegui updated the task description. (Show Details)

This is all done

drift tracker is saying this is not done on db1181 (s7) (This is pre-auto schema :D)

Mentioned in SAL (#wikimedia-operations) [2022-03-07T07:53:41Z] <marostegui> dbmaint on db1181 s7@eqiad T276150

drift tracker is saying this is not done on db1181 (s7) (This is pre-auto schema :D)

Done