Renaming an index isn't supported on mariadb 10.1 or 10.4. It has been added to 10.5 (https://mariadb.com/kb/en/mariadb-1052-release-notes/) but we are not there yet. So for now, this schema change will need to be a DROP+CREATE index.

In T268004#6626801, @Marostegui wrote:

Renaming an index isn't supported on mariadb 10.1 or 10.4. It has been added to 10.5 (https://mariadb.com/kb/en/mariadb-1052-release-notes/) but we are not there yet. So for now, this schema change will need to be a DROP+CREATE index.

The the linked SQL should be good.

I haven't found anything specifically targeting this index with https://codesearch.wmcloud.org/search/?q=namespace_title&i=nope&files=&repos= (like USE INDEX or FORCE INDEX).

s6 progress

• dbstore1005
• db2141
• db2129
• db2124
• db2117
• db2114
• db2097
• db2089
• db2087
• db2076
• db1140
• db1139
• db1131
• db1113
• db1098
• db1096
• db1093
• db1088
• db1085

Mentioned in SAL (#wikimedia-operations) [2020-11-24T09:23:52Z] <marostegui> Deploy schema change on db2114 and db1096:3316 - T268004

For big wikis this alter requires depooling, as while the index isn't present some queries get full scans:

+------+-------------+------------------+--------+---------------+---------+---------+------------------------+----------+-----------------------------------------------------------+
| id   | select_type | table            | type   | possible_keys | key     | key_len | ref                    | rows     | Extra                                                     |
+------+-------------+------------------+--------+---------------+---------+---------+------------------------+----------+-----------------------------------------------------------+
|    1 | SIMPLE      | watchlist        | index  | NULL          | wl_user | 265     | NULL                   | 34709560 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | watchlist_expiry | eq_ref | PRIMARY       | PRIMARY | 4       | frwiki.watchlist.wl_id | 1        | Using where                                               |
+------+-------------+------------------+--------+---------------+---------+---------+------------------------+----------+-----------------------------------------------------------+

Doing everything on the same transaction fixes it: alter table watchlist drop key namespace_title, add key wl_namespace_title (wl_namespace, wl_title);

s6 is fully done, waiting for db1139 which is down at the moment, but doesn't receive user traffic.
Let's give it 24h to see if we find issues (FORCE INDEX or USE INDEX statements)

Nothing on logs regarding namespace_title index.

s5 progress

• dbstore1003
• db1150
• db1145
• db1144
• db1130
• db1113
• db1110
• db1100
• db1096
• db1082

s2 progress

• dbstore1004
• db1146
• db1129
• db1122
• db1105
• db1095
• db1090
• db1076
• db1074

Mentioned in SAL (#wikimedia-operations) [2020-11-25T13:14:31Z] <marostegui> Deploy schema change on commonswiki.watchlist on s4 codfw - there will be lag on s4 codfw - T268004

s4 progress

• dbstore1004
• db1150
• db1149
• db1148
• db1147
• db1146
• db1145
• db1144
• db1143
• db1142
• db1141
• db1138
• db1121
• db1081

Mentioned in SAL (#wikimedia-operations) [2020-11-26T08:08:27Z] <marostegui> Deploy schema change on s7 codfw - there will be lag on s7 codfw - T268004

s7 eqiad progress

• dbstore1003
• db1136
• db1127
• db1116
• db1101
• db1098
• db1094
• db1090
• db1086
• db1079

Mentioned in SAL (#wikimedia-operations) [2020-11-26T11:40:53Z] <marostegui> Deploy schema change on s8 codfw - there will be lag on s8 codfw - T268004

s8 progress

• dbstore1005
• db1126
• db1116
• db1114
• db1111
• db1109
• db1104
• db1101
• db1099
• db1092
• db1087

Mentioned in SAL (#wikimedia-operations) [2020-11-30T07:11:40Z] <marostegui> Deploy schema change on s1 codfw - T268004

s1 eqiad progress

• dbstore1003
• db1140
• db1133
• db1139 T268004#6655792
• db1135
• db1134
• db1119
• db1118
• db1106
• db1105
• db1099
• db1089
• db1084
• db1083

From s1 only db1139 is pending the schema change - as it is now under maintenance due to HW issues.

Mentioned in SAL (#wikimedia-operations) [2020-11-30T14:23:41Z] <marostegui> Deploy schema change on s3 codfw, lag will show up on s3 codfw T268004

s3 eqiad progress

• dbstore1004
• db1123
• db1112
• db1095
• db1078
• db1075

db1139:3311 done