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
Description
Description
Details
Details
| Subject | Repo | Branch | Lines +/- | |
|---|---|---|---|---|
| Rename ipb_address to ipb_address_unique | mediawiki/core | master | +8 -1 |
| Status | Subtype | Assigned | Task | |
|---|---|---|---|---|
| · · · | ||||
| Resolved | Ladsgroup | T104459 Detect object, schema and data drifts between mediawiki HEAD, production masters and replicas | ||
| Resolved | Ladsgroup | T250071 Rename ipb_address index on ipb_address to ipb_address_unique | ||
| · · · |
Event Timeline
Comment Actions
+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.
Comment Actions
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=
Comment Actions
Cool - thank you @daniel
I will standby to change the above wikis till @Ladsgroup (or someone else) has made the changes on tables.sql
Comment Actions
Change 591500 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/core@master] Rename ipb_address to ipb_address_unique
Comment Actions
Saves stuff breaking if someone ends up with using a FORCE/USE INDEX hint at some point in the future in the code
Comment Actions
Change 591500 merged by jenkins-bot:
[mediawiki/core@master] Rename ipb_address to ipb_address_unique
Comment Actions
Mentioned in SAL (#wikimedia-operations) [2020-04-24T11:13:46Z] <Amir1> apply T250071 on s10 (labswiki)
Comment Actions
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
Comment Actions
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
Comment Actions
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)
Comment Actions
Mentioned in SAL (#wikimedia-operations) [2020-04-28T11:45:02Z] <marostegui> Deploy schema change on s8 eqiad master with replication T250071
Comment Actions
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
Comment Actions
I hate reopening tickets so I won't but my check on codfw hosts says s5 has this too (maybe it's also in eqiad of s5 too not sure):
[ "ipblocks ipb_address_unique index-columns-mismatch": { "s5": [ "db2123:srwiki", "db2075:srwiki", "db2113:srwiki", "db2111:srwiki", "db2128:srwiki" ] }, ]
Comment Actions
This is useful Amir! Don't worry! You are helping a lot get all the drifts fixed :)
I will take a look next week
Comment Actions
The following wikis are also affected on those s5 hosts:
cebwiki enwikivoyage (this needs extra alter for the `ipb_address` UNIQUE mgwiktionary shwiki srwiki
- db2123
- db2075
- db2113
- db2111
- db2128
Comment Actions
I have fixed those hosts for all s5 wikis.
# for i in db2123 db2075 db2113 db2111 db2128; do echo $i; mysql.py -h$i -e "show create table cebwiki.ipblocks\G show create table enwikivoyage.ipblocks\G show create table mgwiktionary.ipblocks\G show create table shwiki.ipblocks\G show create table srwiki.ipblocks\G" | grep KEY ; done db2123 PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), 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)) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) db2075 PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), 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)) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) db2113 PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), 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)) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) db2111 PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), 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)) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) db2128 PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), 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)) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`) PRIMARY KEY (`ipb_id`), UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`), KEY `ipb_user` (`ipb_user`), KEY `ipb_timestamp` (`ipb_timestamp`), KEY `ipb_expiry` (`ipb_expiry`), KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)), KEY `ipb_parent_block_id` (`ipb_parent_block_id`)