Page MenuHomePhabricator
Create Task
Maniphest T270187

Schema change for renaming user_properties_property index
Closed, ResolvedPublic
Actions

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

Related Objects
Search...

Event Timeline

Ladsgroup created this task.Dec 15 2020, 3:01 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 15 2020, 3:01 PM
• Marostegui claimed this task.Dec 16 2020, 10:20 AM
• Marostegui triaged this task as Medium priority.
• Marostegui moved this task from Triage to Ready on the DBA board.
• Marostegui subscribed.

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

• Marostegui moved this task from Backlog to In progress on the Schema-change-in-production board.Jan 4 2021, 8:50 AM
• 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)Jan 4 2021, 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)Jan 4 2021, 8:55 AM
• Marostegui updated the task description. (Show Details)
Ladsgroup added a comment.Jan 4 2021, 9:27 AM

LGTM. Thanks!

• Marostegui added a comment.Jan 4 2021, 9:30 AM

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

Stashbot added a comment.Jan 4 2021, 9:48 AM

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

• Marostegui added a comment.Jan 4 2021, 9:57 AM

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)EditedJan 4 2021, 9:58 AM

s6 eqiad:

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1140
  • db1139
  • db1131
  • db1125
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085
  • clouddb1019
  • clouddb1015
• Marostegui added a comment.Jan 4 2021, 10:02 AM

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)Jan 4 2021, 10:03 AM
• Marostegui updated the task description. (Show Details)Jan 7 2021, 12:20 PM
Stashbot added a comment.Jan 8 2021, 6:18 AM

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)Jan 8 2021, 6:24 AM
• Marostegui added a comment.EditedJan 8 2021, 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)Jan 8 2021, 6:38 AM
• Marostegui updated the task description. (Show Details)Jan 8 2021, 7:21 AM
Stashbot added a comment.Jan 8 2021, 7:23 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)Jan 8 2021, 7:29 AM
• Marostegui added a comment.Jan 8 2021, 8:08 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)Jan 8 2021, 8:08 AM
• Marostegui updated the task description. (Show Details)Jan 8 2021, 8:12 AM
Stashbot added a comment.Jan 8 2021, 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.EditedJan 8 2021, 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)Jan 8 2021, 12:11 PM
Stashbot added a comment.Jan 11 2021, 6:04 AM

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)Jan 11 2021, 6:12 AM
• Marostegui added a comment.EditedJan 11 2021, 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)Jan 11 2021, 7:12 AM
Stashbot added a comment.Jan 11 2021, 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)Jan 11 2021, 7:53 AM
• Marostegui added a comment.EditedJan 11 2021, 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)Jan 11 2021, 9:31 AM
Stashbot added a comment.Jan 11 2021, 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)Jan 11 2021, 9:56 AM
• Marostegui added a comment.EditedJan 11 2021, 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)Jan 11 2021, 2:12 PM
Stashbot added a comment.Jan 11 2021, 2:13 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)Jan 11 2021, 2:33 PM
• Marostegui added a comment.EditedJan 11 2021, 2:52 PM

s3 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1154
  • db1124
  • db1123
  • db1112
  • db1095
  • db1078
  • db1075
  • clouddb1017
  • clouddb1013
Stashbot added a comment.Jan 12 2021, 8:21 AM

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

• Marostegui added a comment.Jan 12 2021, 8:22 AM

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

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

All done

• Marostegui mentioned this in T345509: db1128 mariadb crashed.Sep 14 2023, 12:41 PM
Maintenance_bot moved this task from In progress to Done on the DBA board.Sep 14 2023, 1:15 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL