Page MenuHomePhabricator

Schema change to make rc_id unsigned and rc_timestamp BINARY
Open, Stalled, MediumPublic

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

Event Timeline

Note: I have two other schema changes for recentchanges table. Will create tickets for those soon.

I will group this one with the other two.
But I will start this schema change as soon as possible as I want to get rid of the nightmares of rc_id being signed :)

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.Tue, Apr 13, 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.