Page MenuHomePhabricator

Schema change for renaming user_properties_property index
Closed, ResolvedPublic

Description

Part of T270033: Fix and enforce table prefix usage in columns and indexes in core

  1. ALTERs to run: https://gerrit.wikimedia.org/r/c/mediawiki/core/+/649414/3/maintenance/archives/patch-user_properties-rename-index.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

Progress

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 15 2020, 3:01 PM
Marostegui triaged this task as Medium priority.
Marostegui moved this task from Triage to Ready on the DBA board.
Marostegui added a subscriber: Marostegui.

I haven't found anything on codesearch that could have user_properties_property index forced

Marostegui moved this task from Ready to In progress on the DBA board.
Marostegui updated the task description. (Show Details)
Marostegui updated the task description. (Show Details)Mon, Jan 4, 8:52 AM

@Ladsgroup this looks good?

mysql:root@localhost [labtestwiki]> show create table user_properties\G
*************************** 1. row ***************************
       Table: user_properties
Create Table: CREATE TABLE `user_properties` (
  `up_user` int(10) unsigned NOT NULL,
  `up_property` varbinary(255) NOT NULL,
  `up_value` blob,
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)
Marostegui updated the task description. (Show Details)Mon, Jan 4, 8:55 AM
Marostegui updated the task description. (Show Details)

LGTM. Thanks!

Thanks I will deploy it to s6 and let it run for a couple of days to make sure nothing strange comes up.

Mentioned in SAL (#wikimedia-operations) [2021-01-04T09:48:51Z] <marostegui> Deploy schema change on s6 codfw master (lag will appear on codfw) - T270187

s6 codfw is done:

# /home/marostegui/section s6 | grep codfw | while read host port; do echo "$host:$port"; mysql.py -h$host:$port ruwiki -e "show create table user_properties\G" | grep "up_property" | grep "KEY"; done
db2141.codfw.wmnet:3316
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
db2129.codfw.wmnet:3306
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
db2124.codfw.wmnet:3306
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
db2117.codfw.wmnet:3306
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
db2114.codfw.wmnet:3306
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
db2097.codfw.wmnet:3316
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
db2095.codfw.wmnet:3316
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
db2089.codfw.wmnet:3316
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
db2087.codfw.wmnet:3316
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
db2076.codfw.wmnet:3306
  PRIMARY KEY (`up_user`,`up_property`),
  KEY `up_property` (`up_property`)
Marostegui updated the task description. (Show Details)EditedMon, Jan 4, 9:58 AM

s6 eqiad:

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1140
  • db1139
  • db1131
  • db1125
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085
  • clouddb1019
  • clouddb1015

db1096 on eqiad is altered, I am going to leave it for a couple of days to make sure no queries are forcing that index:

# for i in frwiki jawiki ruwiki; do echo $i; mysql.py -hdb1096:3316 $i -e "show create table user_properties\G" | grep -v "PRIMARY"| grep KEY | grep "up_property" ; done
frwiki
  KEY `up_property` (`up_property`)
jawiki
  KEY `up_property` (`up_property`)
ruwiki
  KEY `up_property` (`up_property`)
Marostegui updated the task description. (Show Details)Mon, Jan 4, 10:03 AM
Marostegui updated the task description. (Show Details)Thu, Jan 7, 12:20 PM

Mentioned in SAL (#wikimedia-operations) [2021-01-08T06:18:43Z] <marostegui> Deploy schema change on s2 codfw master - T270187

Marostegui updated the task description. (Show Details)Fri, Jan 8, 6:24 AM
Marostegui added a comment.EditedFri, Jan 8, 6:33 AM

s2 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1155
  • db1146
  • db1129
  • db1125
  • db1122
  • db1105
  • db1095
  • db1090
  • db1076
  • db1074
  • clouddb1018
  • clouddb1014
Marostegui updated the task description. (Show Details)Fri, Jan 8, 6:38 AM
Marostegui updated the task description. (Show Details)Fri, Jan 8, 7:21 AM

Mentioned in SAL (#wikimedia-operations) [2021-01-08T07:23:13Z] <marostegui> Deploy schema change on s5 codfw master - T270187

Marostegui updated the task description. (Show Details)Fri, Jan 8, 7:29 AM

s5 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1154
  • db1150
  • db1145
  • db1144
  • db1130
  • db1124
  • db1113
  • db1110
  • db1100
  • db1096
  • db1082
  • clouddb1020
  • clouddb1016
Marostegui updated the task description. (Show Details)Fri, Jan 8, 8:08 AM
Marostegui updated the task description. (Show Details)Fri, Jan 8, 8:12 AM

Mentioned in SAL (#wikimedia-operations) [2021-01-08T08:12:56Z] <marostegui> Deploy schema change on s4 codfw master - T270187

Marostegui added a comment.EditedFri, Jan 8, 8:20 AM

s4 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1150
  • db1149
  • db1148
  • db1147
  • db1146
  • db1145
  • db1144
  • db1143
  • db1142
  • db1141
  • db1138
  • db1125
  • db1121
  • db1081
  • clouddb1019
  • clouddb1015
Marostegui updated the task description. (Show Details)Fri, Jan 8, 12:11 PM

Mentioned in SAL (#wikimedia-operations) [2021-01-11T06:04:14Z] <marostegui> Deploy schema change on s7 codfw master - T270187

Marostegui updated the task description. (Show Details)Mon, Jan 11, 6:12 AM
Marostegui added a comment.EditedMon, Jan 11, 6:31 AM

s7 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1136
  • db1127
  • db1125
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079
  • clouddb1018
  • clouddb1014
Marostegui updated the task description. (Show Details)Mon, Jan 11, 7:12 AM

Mentioned in SAL (#wikimedia-operations) [2021-01-11T07:12:50Z] <marostegui> Deploy schema change on s8 codfw master - T270187

Marostegui updated the task description. (Show Details)Mon, Jan 11, 7:53 AM
Marostegui added a comment.EditedMon, Jan 11, 8:18 AM

s8 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1154
  • db1126
  • db1124
  • db1116
  • db1114
  • db1111
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087
  • clouddb1020
  • clouddb1016
Marostegui updated the task description. (Show Details)Mon, Jan 11, 9:31 AM

Mentioned in SAL (#wikimedia-operations) [2021-01-11T09:31:35Z] <marostegui> Deploy schema change on s1 codfw master - T270187

Marostegui updated the task description. (Show Details)Mon, Jan 11, 9:56 AM
Marostegui added a comment.EditedMon, Jan 11, 10:23 AM

s1 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1154
  • db1140
  • db1139
  • db1135
  • db1134
  • db1133
  • db1124
  • db1119
  • db1118
  • db1106
  • db1105
  • db1099
  • db1089
  • db1084
  • db1083
  • clouddb1017
  • clouddb1013
Marostegui updated the task description. (Show Details)Mon, Jan 11, 2:12 PM

Mentioned in SAL (#wikimedia-operations) [2021-01-11T14:13:12Z] <marostegui> Deploy schema change on s3 codfw master - T270187

Marostegui updated the task description. (Show Details)Mon, Jan 11, 2:33 PM
Marostegui added a comment.EditedMon, Jan 11, 2:52 PM

s3 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1154
  • db1124
  • db1123
  • db1112
  • db1095
  • db1078
  • db1075
  • clouddb1017
  • clouddb1013

Mentioned in SAL (#wikimedia-operations) [2021-01-12T08:21:42Z] <marostegui> Deploy schema change on s3 eqiad master - T270187

Started the schema change on s3 master with NO replication. It will take around 15 hours.

Marostegui updated the task description. (Show Details)Tue, Jan 12, 8:22 AM
Marostegui closed this task as Resolved.Wed, Jan 13, 6:11 AM
Marostegui updated the task description. (Show Details)

All done