Page MenuHomePhabricator

Rename ipb_address index on ipb_address to ipb_address_unique
Closed, ResolvedPublic

Description

This index is named in ipb_address_unique in production causing lots of schema drift reports (and actual issues, like some databases in s3 has the one name and some have the other name) and renaming indexes in pretty hard in production (you need to drop and recreate it). I suggest we rename it to ipb_address_unique in core. Using unique as prefix is not uncommon either. We already have ar_revid_uniq

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 13 2020, 12:35 PM
Marostegui triaged this task as Medium priority.Apr 13 2020, 12:59 PM
Marostegui moved this task from Triage to Blocked external/Not db team on the DBA board.

+1 to rename this on table.sql.

The wikis that were created and would need an alter table to get the index renamed as ipb_address_unique are the following:

acewiki
advisorswiki
advisorywiki
adywiki
amwikimedia
arbcom_cswiki
arbcom_dewiki
arbcom_enwiki
arbcom_fiwiki
arbcom_nlwiki
arwikimedia
arwikiversity
arzwiki
aswikisource
atjwiki
auditcomwiki
azbwiki
banwiki
barwiki
bclwiki
bdwikimedia
betawikiversity
bewiki
bewikimedia
bewikisource
bjnwiki
bnwikisource
bnwikivoyage
boardgovcomwiki
bpywiki
brwikimedia
brwikiquote
brwikisource
bswikinews
bxrwiki
cawikimedia
cbk_zamwiki
cdowiki
chairwiki
checkuserwiki
ckbwiki
cnwikimedia
collabwiki
cowikimedia
crhwiki
cswikinews
cswikiversity
cuwiki
dewikivoyage
dinwiki
diqwiki
dkwikimedia
donatewiki
dsbwiki
dtywiki
ecwikimedia
electcomwiki
elwikinews
elwikiversity
elwikivoyage
emlwiki
enwikiversity
eowikinews
eowikisource
eswikiversity
eswikivoyage
euwikisource
execwiki
extwiki
fawikinews
fawikivoyage
fdcwiki
fiwikimedia
fiwikinews
fiwikisource
fiwikiversity
fiwikivoyage
fiwiktionary
frrwiki
frwikiversity
frwikivoyage
gagwiki
ganwiki
gcrwiki
gewikimedia
glkwiki
gomwiki
gorwiki
grwikimedia
guwikisource
hakwiki
hewikivoyage
hifwiki
hifwiktionary
hiwikimedia
hiwikisource
hiwikiversity
hiwikivoyage
hsbwiki
hsbwiktionary
huwikinews
hywikisource
hywwiki
id_internalwikimedia
idwikimedia
iegcomwiki
ilwikimedia
inhwiki
itwikiversity
itwikivoyage
jamwiki
jawikiversity
kaawiki
kabwiki
kbdwiki
kbpwiki
koiwiki
kowikinews
kowikiversity
krcwiki
lbewiki
legalteamwiki
lezwiki
lfnwiki
liwikibooks
liwikinews
liwikiquote
liwikisource
loginwiki
lrcwiki
ltgwiki
maiwiki
maiwikimedia
mdfwiki
mhrwiki
minwiki
minwiktionary
mkwikimedia
mkwikisource
mnwwiki
movementroleswiki
mrjwiki
mrwikisource
mwlwiki
mxwikimedia
myvwiki
mznwiki
napwikisource
newwiki
ngwikimedia
nlwikivoyage
noboard_chapterswikimedia
novwiki
nowikimedia
nqowiki
nsowiki
nycwikimedia
officewiki
olowiki
ombudsmenwiki
orwikisource
otrs_wikiwiki
outreachwiki
pa_uswikimedia
pagwiki
pawikisource
pcdwiki
pflwiki
plwikivoyage
pmswikisource
pnbwiki
pnbwiktionary
pntwiki
projectcomwiki
pswikivoyage
ptwikimedia
ptwikiversity
ptwikivoyage
punjabiwikimedia
qualitywiki
roa_tarawiki
romdwikimedia
rowikivoyage
rswikimedia
ruewiki
ruwikimedia
ruwikiversity
ruwikivoyage
sahwiki
sahwikiquote
sahwikisource
satwiki
sawikiquote
sawikisource
sdwikinews
searchcomwiki
sewikimedia
shnwiki
shywiktionary
slwikiversity
sqwikinews
srnwiki
stewardwiki
stqwiki
strategywiki
svwikiversity
svwikivoyage
szlwiki
szywiki
tawikinews
tawikisource
tcywiki
techconductwiki
tenwiki
test2wiki
testwikidatawiki
tgwikibooks
transitionteamwiki
trwikimedia
trwikinews
tyvwiki
uawikimedia
ukwikivoyage
usabilitywiki
vecwikisource
vecwiktionary
vepwiki
viwikivoyage
votewiki
wbwikimedia
wg_enwiki
wikimania2007wiki
wikimania2008wiki
wikimania2009wiki
wikimania2010wiki
wikimania2011wiki
wikimania2012wiki
wikimania2013wiki
wikimania2014wiki
wikimania2015wiki
wikimania2016wiki
wikimania2017wiki
wikimania2018wiki
wikimaniawiki
wuuwiki
xmfwiki
yuewiktionary
zeawiki
zh_classicalwiki
zhwikiversity
zhwikivoyage

Once this is fully renamed on tables.sql, we can go ahead and do the alter.

daniel added a subscriber: daniel.Apr 14 2020, 7:59 PM

Codesearch shows some references to the index in updater code, but none in production code or extensions. We should be able to just rename the index for new installs, and keep the old name for existing installs.

https://codesearch.wmflabs.org/search/?q=ipb_address_unique&i=nope&files=&repos=

Cool - thank you @daniel
I will standby to change the above wikis till @Ladsgroup (or someone else) has made the changes on tables.sql

Fun fact: This index is already named ipb_address_unique in Postgres.

Another fun fact: I absolutely hate doing schema changes in Sqlite.

Change 591500 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Rename ipb_address to ipb_address_unique

https://gerrit.wikimedia.org/r/591500

Fun fact: This index is already named ipb_address_unique in Postgres.

I see no need to rename the index for existing installs. Do you?

Reedy added a subscriber: Reedy.Apr 22 2020, 1:13 PM

Fun fact: This index is already named ipb_address_unique in Postgres.

I see no need to rename the index for existing installs. Do you?

Saves stuff breaking if someone ends up with using a FORCE/USE INDEX hint at some point in the future in the code

Change 591500 merged by jenkins-bot:
[mediawiki/core@master] Rename ipb_address to ipb_address_unique

https://gerrit.wikimedia.org/r/591500

Ladsgroup closed this task as Resolved.Apr 22 2020, 2:05 PM
Ladsgroup claimed this task.
Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptApr 22 2020, 2:05 PM
Maintenance_bot moved this task from Incoming to Done on the User-Ladsgroup board.Apr 22 2020, 2:15 PM

Mentioned in SAL (#wikimedia-operations) [2020-04-24T11:13:46Z] <Amir1> apply T250071 on s10 (labswiki)

Marostegui reopened this task as Open.EditedApr 27 2020, 5:41 AM

We have to alter the following wikis: T250071#6051598 + wikidatawiki
They were created with the "new" name, so they need to go back to ipb_address_unique

Mentioned in SAL (#wikimedia-operations) [2020-04-28T06:35:19Z] <marostegui> Deploy schema change on s3 master with replication for the wikis at T250071#6051598 - T250071

+1 to rename this on table.sql.

The wikis that were created and would need an alter table to get the index renamed as ipb_address_unique are the following:

acewiki
advisorswiki
advisorywiki
adywiki
amwikimedia
arbcom_cswiki
arbcom_dewiki
arbcom_enwiki
arbcom_fiwiki
arbcom_nlwiki
arwikimedia
arwikiversity
arzwiki
aswikisource
atjwiki
auditcomwiki
azbwiki
banwiki
barwiki
bclwiki
bdwikimedia
betawikiversity
bewiki
bewikimedia
bewikisource
bjnwiki
bnwikisource
bnwikivoyage
boardgovcomwiki
bpywiki
brwikimedia
brwikiquote
brwikisource
bswikinews
bxrwiki
cawikimedia
cbk_zamwiki
cdowiki
chairwiki
checkuserwiki
ckbwiki
cnwikimedia
collabwiki
cowikimedia
crhwiki
cswikinews
cswikiversity
cuwiki
dewikivoyage
dinwiki
diqwiki
dkwikimedia
donatewiki
dsbwiki
dtywiki
ecwikimedia
electcomwiki
elwikinews
elwikiversity
elwikivoyage
emlwiki
enwikiversity
eowikinews
eowikisource
eswikiversity
eswikivoyage
euwikisource
execwiki
extwiki
fawikinews
fawikivoyage
fdcwiki
fiwikimedia
fiwikinews
fiwikisource
fiwikiversity
fiwikivoyage
fiwiktionary
frrwiki
frwikiversity
frwikivoyage
gagwiki
ganwiki
gcrwiki
gewikimedia
glkwiki
gomwiki
gorwiki
grwikimedia
guwikisource
hakwiki
hewikivoyage
hifwiki
hifwiktionary
hiwikimedia
hiwikisource
hiwikiversity
hiwikivoyage
hsbwiki
hsbwiktionary
huwikinews
hywikisource
hywwiki
id_internalwikimedia
idwikimedia
iegcomwiki
ilwikimedia
inhwiki
itwikiversity
itwikivoyage
jamwiki
jawikiversity
kaawiki
kabwiki
kbdwiki
kbpwiki
koiwiki
kowikinews
kowikiversity
krcwiki
lbewiki
legalteamwiki
lezwiki
lfnwiki
liwikibooks
liwikinews
liwikiquote
liwikisource
loginwiki
lrcwiki
ltgwiki
maiwiki
maiwikimedia
mdfwiki
mhrwiki
minwiki
minwiktionary
mkwikimedia
mkwikisource
mnwwiki
movementroleswiki
mrjwiki
mrwikisource
mwlwiki
mxwikimedia
myvwiki
mznwiki
napwikisource
newwiki
ngwikimedia
nlwikivoyage
noboard_chapterswikimedia
novwiki
nowikimedia
nqowiki
nsowiki
nycwikimedia
officewiki
olowiki
ombudsmenwiki
orwikisource
otrs_wikiwiki
outreachwiki
pa_uswikimedia
pagwiki
pawikisource
pcdwiki
pflwiki
plwikivoyage
pmswikisource
pnbwiki
pnbwiktionary
pntwiki
projectcomwiki
pswikivoyage
ptwikimedia
ptwikiversity
ptwikivoyage
punjabiwikimedia
qualitywiki
roa_tarawiki
romdwikimedia
rowikivoyage
rswikimedia
ruewiki
ruwikimedia
ruwikiversity
ruwikivoyage
sahwiki
sahwikiquote
sahwikisource
satwiki
sawikiquote
sawikisource
sdwikinews
searchcomwiki
sewikimedia
shnwiki
shywiktionary
slwikiversity
sqwikinews
srnwiki
stewardwiki
stqwiki
strategywiki
svwikiversity
svwikivoyage
szlwiki
szywiki
tawikinews
tawikisource
tcywiki
techconductwiki
tenwiki
test2wiki
testwikidatawiki
tgwikibooks
transitionteamwiki
trwikimedia
trwikinews
tyvwiki
uawikimedia
ukwikivoyage
usabilitywiki
vecwikisource
vecwiktionary
vepwiki
viwikivoyage
votewiki
wbwikimedia
wg_enwiki
wikimania2007wiki
wikimania2008wiki
wikimania2009wiki
wikimania2010wiki
wikimania2011wiki
wikimania2012wiki
wikimania2013wiki
wikimania2014wiki
wikimania2015wiki
wikimania2016wiki
wikimania2017wiki
wikimania2018wiki
wikimaniawiki
wuuwiki
xmfwiki
yuewiktionary
zeawiki
zh_classicalwiki
zhwikiversity
zhwikivoyage

Once this is fully renamed on tables.sql, we can go ahead and do the alter.

I am altering this list of wikis directly on the master with a 60 seconds sleep. The tables are tiny.
This is what I am running:

alter table ipblocks drop index if exists ipb_address, ADD UNIQUE INDEX IF NOT EXISTS /*i*/ipb_address_unique (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only)

+1 to rename this on table.sql.

The wikis that were created and would need an alter table to get the index renamed as ipb_address_unique are the following:

acewiki
advisorswiki
advisorywiki
adywiki
amwikimedia
arbcom_cswiki
arbcom_dewiki
arbcom_enwiki
arbcom_fiwiki
arbcom_nlwiki
arwikimedia
arwikiversity
arzwiki
aswikisource
atjwiki
auditcomwiki
azbwiki
banwiki
barwiki
bclwiki
bdwikimedia
betawikiversity
bewiki
bewikimedia
bewikisource
bjnwiki
bnwikisource
bnwikivoyage
boardgovcomwiki
bpywiki
brwikimedia
brwikiquote
brwikisource
bswikinews
bxrwiki
cawikimedia
cbk_zamwiki
cdowiki
chairwiki
checkuserwiki
ckbwiki
cnwikimedia
collabwiki
cowikimedia
crhwiki
cswikinews
cswikiversity
cuwiki
dewikivoyage
dinwiki
diqwiki
dkwikimedia
donatewiki
dsbwiki
dtywiki
ecwikimedia
electcomwiki
elwikinews
elwikiversity
elwikivoyage
emlwiki
enwikiversity
eowikinews
eowikisource
eswikiversity
eswikivoyage
euwikisource
execwiki
extwiki
fawikinews
fawikivoyage
fdcwiki
fiwikimedia
fiwikinews
fiwikisource
fiwikiversity
fiwikivoyage
fiwiktionary
frrwiki
frwikiversity
frwikivoyage
gagwiki
ganwiki
gcrwiki
gewikimedia
glkwiki
gomwiki
gorwiki
grwikimedia
guwikisource
hakwiki
hewikivoyage
hifwiki
hifwiktionary
hiwikimedia
hiwikisource
hiwikiversity
hiwikivoyage
hsbwiki
hsbwiktionary
huwikinews
hywikisource
hywwiki
id_internalwikimedia
idwikimedia
iegcomwiki
ilwikimedia
inhwiki
itwikiversity
itwikivoyage
jamwiki
jawikiversity
kaawiki
kabwiki
kbdwiki
kbpwiki
koiwiki
kowikinews
kowikiversity
krcwiki
lbewiki
legalteamwiki
lezwiki
lfnwiki
liwikibooks
liwikinews
liwikiquote
liwikisource
loginwiki
lrcwiki
ltgwiki
maiwiki
maiwikimedia
mdfwiki
mhrwiki
minwiki
minwiktionary
mkwikimedia
mkwikisource
mnwwiki
movementroleswiki
mrjwiki
mrwikisource
mwlwiki
mxwikimedia
myvwiki
mznwiki
napwikisource
newwiki
ngwikimedia
nlwikivoyage
noboard_chapterswikimedia
novwiki
nowikimedia
nqowiki
nsowiki
nycwikimedia
officewiki
olowiki
ombudsmenwiki
orwikisource
otrs_wikiwiki
outreachwiki
pa_uswikimedia
pagwiki
pawikisource
pcdwiki
pflwiki
plwikivoyage
pmswikisource
pnbwiki
pnbwiktionary
pntwiki
projectcomwiki
pswikivoyage
ptwikimedia
ptwikiversity
ptwikivoyage
punjabiwikimedia
qualitywiki
roa_tarawiki
romdwikimedia
rowikivoyage
rswikimedia
ruewiki
ruwikimedia
ruwikiversity
ruwikivoyage
sahwiki
sahwikiquote
sahwikisource
satwiki
sawikiquote
sawikisource
sdwikinews
searchcomwiki
sewikimedia
shnwiki
shywiktionary
slwikiversity
sqwikinews
srnwiki
stewardwiki
stqwiki
strategywiki
svwikiversity
svwikivoyage
szlwiki
szywiki
tawikinews
tawikisource
tcywiki
techconductwiki
tenwiki
test2wiki
testwikidatawiki
tgwikibooks
transitionteamwiki
trwikimedia
trwikinews
tyvwiki
uawikimedia
ukwikivoyage
usabilitywiki
vecwikisource
vecwiktionary
vepwiki
viwikivoyage
votewiki
wbwikimedia
wg_enwiki
wikimania2007wiki
wikimania2008wiki
wikimania2009wiki
wikimania2010wiki
wikimania2011wiki
wikimania2012wiki
wikimania2013wiki
wikimania2014wiki
wikimania2015wiki
wikimania2016wiki
wikimania2017wiki
wikimania2018wiki
wikimaniawiki
wuuwiki
xmfwiki
yuewiktionary
zeawiki
zh_classicalwiki
zhwikiversity
zhwikivoyage

Once this is fully renamed on tables.sql, we can go ahead and do the alter.

I am altering this list of wikis directly on the master with a 60 seconds sleep. The tables are tiny.
This is what I am running:

alter table ipblocks drop index if exists ipb_address, ADD UNIQUE INDEX IF NOT EXISTS /*i*/ipb_address_unique (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only)

s3 wikis done.
Going for s8 now.

Mentioned in SAL (#wikimedia-operations) [2020-04-28T11:45:02Z] <marostegui> Deploy schema change on s8 eqiad master with replication T250071

Marostegui closed this task as Resolved.Apr 28 2020, 11:46 AM

s8 done:

CREATE TABLE `ipblocks` (
  `ipb_id` int(11) NOT NULL AUTO_INCREMENT,
  `ipb_address` tinyblob NOT NULL,
  `ipb_user` int(10) unsigned NOT NULL DEFAULT '0',
  `ipb_by_actor` bigint(20) unsigned NOT NULL,
  `ipb_reason_id` bigint(20) unsigned NOT NULL,
  `ipb_timestamp` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `ipb_auto` tinyint(1) NOT NULL DEFAULT '0',
  `ipb_anon_only` tinyint(1) NOT NULL DEFAULT '0',
  `ipb_create_account` tinyint(1) NOT NULL DEFAULT '1',
  `ipb_enable_autoblock` tinyint(1) NOT NULL DEFAULT '1',
  `ipb_expiry` varbinary(14) NOT NULL DEFAULT '',
  `ipb_range_start` tinyblob NOT NULL,
  `ipb_range_end` tinyblob NOT NULL,
  `ipb_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `ipb_block_email` tinyint(1) NOT NULL DEFAULT '0',
  `ipb_allow_usertalk` tinyint(1) NOT NULL DEFAULT '0',
  `ipb_parent_block_id` int(11) DEFAULT NULL,
  `ipb_sitewide` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ipb_id`),
  UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`,`ipb_anon_only`),
  KEY `ipb_user` (`ipb_user`),
  KEY `ipb_timestamp` (`ipb_timestamp`),
  KEY `ipb_expiry` (`ipb_expiry`),
  KEY `ipb_parent_block_id` (`ipb_parent_block_id`),
  KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20))
) ENGINE=InnoDB AUTO_INCREMENT=17191 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8