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

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)

@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)

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`)

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`)

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

s2 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1155
  • db1146
  • db1129
  • db1125
  • db1122
  • db1105
  • db1095
  • db1090
  • db1076
  • db1074
  • clouddb1018
  • clouddb1014

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

s5 eqiad

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

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

s4 eqiad

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

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

s7 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1136
  • db1127
  • db1125
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079
  • clouddb1018
  • clouddb1014

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

s8 eqiad

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

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

s1 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1154
  • db1140
  • db1139
  • db1135
  • db1134
  • db1133
  • db1124
  • db1119
  • db1118
  • db1106
  • db1105
  • db1099
  • db1089
  • db1084
  • db1083
  • clouddb1017
  • clouddb1013

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

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)

All done