Page MenuHomePhabricator

ipb_address_unique has an extra column in production but not in the code
Closed, ResolvedPublicJun 2 2020

Description

ipb_address_unique UNIQUE index on the ipblocks table has 4 columns in production but 3 lines in tables.sql.
The following wikis needs to be ALTERED to get that extra column, named ipb_anon_only removed per: https://gerrit.wikimedia.org/r/#/c/mediawiki/core/+/594292/

The following wikis need the schema change:

  • s3
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
etwikimedia
euwikisource
execwiki
extwiki
fawikinews
fawikivoyage
fdcwiki
fiwikimedia
fiwikinews
fiwikiversity
fiwikivoyage
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
  • s8 

wikidatawiki

  • wikitech
    • labswiki
    •  labtestwiki
  • mwmaint1002:/home/niharika29/P11137.txt must be removed before closing this task.

alter table would be something like this:

ALTER TABLE /*_*/ipblocks DROP INDEX IF EXISTS /*i*/ipb_address_unique; ALTER TABLE ipblocks ADD UNIQUE INDEX IF NOT EXISTS /*i*/ipb_address_unique (ipb_address(255), ipb_user, ipb_auto);

OLD DESCRIPTION BELOW - DO NOT USE

Here's an example:

wikiadmin@10.64.32.198(fawiki)> show index from ipblocks;
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ipblocks |          0 | PRIMARY             |            1 | ipb_id              | A         |       19209 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address_unique  |            1 | ipb_address         | A         |       19209 |      255 | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address_unique  |            2 | ipb_user            | A         |       19209 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address_unique  |            3 | ipb_auto            | A         |       19209 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_user            |            1 | ipb_user            | A         |       19209 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_timestamp       |            1 | ipb_timestamp       | A         |       19209 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_expiry          |            1 | ipb_expiry          | A         |        9604 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_range           |            1 | ipb_range_start     | A         |       19209 |       20 | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_range           |            2 | ipb_range_end       | A         |       19209 |       20 | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_parent_block_id |            1 | ipb_parent_block_id | A         |          20 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)

But as of now, the ipb_address_unique has four columns tables.sql in core (HEAD of master) and the fourth one is missing:

CREATE UNIQUE INDEX /*i*/ipb_address_unique ON /*_*/ipblocks (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only);

I guess the alter table would be something like this:

ALTER TABLE /*_*/ipblocks DROP INDEX IF EXISTS /*i*/ipb_address_unique; ALTER TABLE ipblocks ADD UNIQUE INDEX IF NOT EXISTS /*i*/ipb_address_unique (ipb_address(255), ipb_user, ipb_auto, ipb_anon_only);

Details

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Marostegui moved this task from Backlog to In progress on the DBA board.May 4 2020, 8:13 AM

Mentioned in SAL (#wikimedia-operations) [2020-05-04T08:17:23Z] <marostegui> Deploy schema change on s5 codfw - T251188

Marostegui updated the task description. (Show Details)May 4 2020, 8:22 AM
jcrespo added a subscriber: jcrespo.May 4 2020, 8:31 AM

Question: are block functionality maintainers (https://www.mediawiki.org/wiki/Developers/Maintainers#MediaWiki_core) aware of these issues, fixes? Anti-Harassment seems to be actively maintaining this. Sometimes columns are added and not used and they should be dropped on code. Apologies if that has been done or attempted.

Heh, this needs to be made in different transactions as we were hit (again) by: https://jira.mariadb.org/browse/MDEV-8351 (I reported it too a few months ago: https://jira.mariadb.org/browse/MDEV-21176) - I have pinged them, again, to see what's going on.

This is how the table looks like after the schema change on cebwiki codfw:

*************************** 1. row ***************************
       Table: ipblocks
Create Table: CREATE TABLE `ipblocks` (
  `ipb_id` int(8) NOT NULL AUTO_INCREMENT,
  `ipb_address` tinyblob NOT NULL,
  `ipb_user` int(8) unsigned NOT NULL DEFAULT '0',
  `ipb_reason_id` bigint(20) unsigned NOT NULL,
  `ipb_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `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_expiry` varbinary(14) NOT NULL DEFAULT '',
  `ipb_range_start` tinyblob NOT NULL,
  `ipb_range_end` tinyblob NOT NULL,
  `ipb_enable_autoblock` tinyint(1) NOT NULL DEFAULT '1',
  `ipb_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `ipb_block_email` tinyint(1) NOT NULL DEFAULT '0',
  `ipb_by_actor` bigint(20) unsigned NOT NULL,
  `ipb_allow_usertalk` tinyint(1) NOT NULL DEFAULT '1',
  `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_range` (`ipb_range_start`(20),`ipb_range_end`(20)),
  KEY `ipb_parent_block_id` (`ipb_parent_block_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1153 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED

@Ladsgroup looks good?
I will proceed slowly on eqiad, as we are changing an index, I need to double check the queries carefully just in case this is a regression.

Marostegui updated the task description. (Show Details)May 4 2020, 8:32 AM

Good question @jcrespo - on the other hand, wikidata (and all the new s3 wikis) are being created with this 4 columns into the UNIQUE index.
Anti-Harassment any thoughts? Is tables.sql indeed to be the right source of truth and the 4 columns in the UNIQUE index is to be expected, or on the other hand, the version that runs in production, for example on enwiki is?

dmaza added a subscriber: dmaza.May 4 2020, 3:06 PM
This comment was removed by dmaza.

Disregard my last comment. I was still organizing my thoughts and pressed submit by mistake.

I think we don't need the 4th column as part of the index. From the product perspective there will never be two blocks to the same target differing on ipb_anon_only. @Tchanders @dbarratt am I missing something?

Disregard my last comment. I was still organizing my thoughts and pressed submit by mistake.

I think we don't need the 4th column as part of the index. From the product perspective there will never be two blocks to the same target differing on ipb_anon_only. @Tchanders @dbarratt am I missing something?

Thank you.
If that is the case, tables.sql needs fixing to get rid of that column so the new wikis don't get it upon creation.
Also, the following wikis would need to be altered in production to get rid of it and have consistency there:

  • wikidatawiki
  • wikitech (and labstestwiki)
  • All the s3 ones that have been created lately (I can get a list of it).

I can take care of these 3 items, but I would need someone to fix tables.sql.

Thank you again

It might be a covering index though meaning it works better with four columns instead of three.

dmaza added a comment.May 4 2020, 3:39 PM

Extra info: Seems to have been added back in 2009 git show 4124558d7b4 maintenance/tables.sql.

@Marostegui let me double check with the team today. I can take care of tables.sql after that. Thanks for working on this.

dbarratt added a comment.EditedMay 4 2020, 4:35 PM

I think we don't need the 4th column as part of the index. From the product perspective there will never be two blocks to the same target differing on ipb_anon_only. @Tchanders @dbarratt am I missing something?

The index appears to be a representation of the "target" which is either a user, an IP address, or an IP range. The IP addresses may be in there with or without being an autoblock.

User ipb_userIP ipb_addressAutoblock ipb_auto
Apples0
Oranges0
127.0.0.10
127.0.0.11
127.0.0.1/160

Ideally Apples, Oranges, and 127.0.0.1/16 would be in the database no more than one time. While the 127.0.0.1 may be in there more than once, but only if the Autoblock flag is varied.

You're correct, a "Hardblock" (which is the inverse of ipb_anon_only) is an option on the block, rather than an identifier of the target. I agree that the column should be removed from the index.

I hope this helps!

Tchanders added a comment.EditedMay 4 2020, 4:57 PM

@jcrespo @Marostegui - thanks for pinging AHT. This would explain T46657: Change of IP block settings produces duplicate block, where the example given is duplicate IP blocks with different anon-only settings.

I agree with @dmaza and @dbarratt that ipb_anon_only should be removed from the unique index, but it is possible that duplicate blocks already exist, as per T46657. That means we could encounter problems updating the index if there are any duplicate blocks hanging around, so it would be helpful to have a strategy for dealing with them.

Change 594292 had a related patch set uploaded (by Dmaza; owner: Dmaza):
[mediawiki/core@master] Remove ipb_anon_only from ipb_address_unique UNIQUE INDEX

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

Would it be possible to tell ahead of time if there are duplicate blocks on our projects already? If duplicate blocks pose a problem then we can give our admins a heads up about them. Knowing how many and which wikis would help.

@jcrespo @Marostegui - thanks for pinging AHT. This would explain T46657: Change of IP block settings produces duplicate block, where the example given is duplicate IP blocks with different anon-only settings.

I agree with @dmaza and @dbarratt that ipb_anon_only should be removed from the unique index, but it is possible that duplicate blocks already exist, as per T46657. That means we could encounter problems updating the index if there are any duplicate blocks hanging around, so it would be helpful to have a strategy for dealing with them.

Keep in mind that this unique index only exists on wikidatawiki and on a bunch of s3 wikis I have listed below:

I have checked on wikidatawiki (which just has 4700 rows) and we have no duplicates for those rows:

root@db1087.eqiad.wmnet[wikidatawiki]> select ipb_address,ipb_user,ipb_auto,ipb_anon_only from ipblocks group by ipb_address,ipb_user,ipb_auto having count(ipb_address) > 1 and count(ipb_user)>1 and count(ipb_auto)>1;
Empty set (0.04 sec)

The rest of wikis that are running with the current 4 column UNIQUE index are:

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
etwikimedia
euwikisource
execwiki
extwiki
fawikinews
fawikivoyage
fdcwiki
fiwikimedia
fiwikinews
fiwikiversity
fiwikivoyage
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

On those wikis I have found some issues on 5 of them that you might want to look at:
{P11137}
If you select by IP you'll see they do have more than 1 entry.

So the ALTER needed to change the UNIQUE from 4 to 3 rows would be needed on wikidatawiki and the above s3 wikis only.

@Marostegui Thanks for looking into those wikis - even though we know it only affects a few, it's helpful to know which ones in advance. I don't seem to have access to P11137 - any idea what to do?

@Marostegui Thanks for looking into those wikis - even though we know it only affects a few, it's helpful to know which ones in advance. I don't seem to have access to P11137 - any idea what to do?

As it contains IPs, I have created it as a WMF-NDA paste. I would assume you have an NDA signed, no? However I don't see you on the NDA group https://phabricator.wikimedia.org/project/view/61/ maybe ask your manager about it.
Maybe ask

Err, @Marostegui Are you sure P11137 is the right link to the paste? I am in WMF-NDA group but I don't see it either.

@Niharika try now, access was very restricted before. I have made it a bit less (but still private).

@Niharika try now, access was very restricted before. I have made it a bit less (but still private).

No luck. Can you add me, @Tchanders, @dbarratt and @dmaza to the paste please?

I've added you to the paste manually, if that doesn't work either, I am as confused as marostegui!

@jcrespo Hmm, doesn't work.

last try! Otherwise I will just leave it on production.

last try! Otherwise I will just leave it on production.

Got it now! Thanks @jcrespo.

That's weird....I created the past with WMF-NDA as usual :-/
Did something change on the paste creations?

Just in case:

root@mwmaint1002:/home/niharika29$ ls -lhsa P11137.txt 
4.0K -rw-r--r-- 1 niharika29 wikidev 1.5K May  5 15:44 P11137.txt

@Marostegui No idea, but something look weird on how to "open" a paste. I wasn't able to either.

@Niharika Apologies about this, not sure what happened exactly.

jcrespo updated the task description. (Show Details)May 5 2020, 3:47 PM

Err, @Marostegui Are you sure P11137 is the right link to the paste? I am in WMF-NDA group but I don't see it either.

I don't see you on the WMF-NDA group either: https://phabricator.wikimedia.org/project/?member=PHID-USER-qztkawkvfnwvkspdr46l&status=active

Err, @Marostegui Are you sure P11137 is the right link to the paste? I am in WMF-NDA group but I don't see it either.

I don't see you on the WMF-NDA group either: https://phabricator.wikimedia.org/project/?member=PHID-USER-qztkawkvfnwvkspdr46l&status=active

That's weird. I used to be on it. Maybe I got removed as part of some cleanup.

That would explain why you were not able to see it until you were CC'ed. Only WMF-NDA members on the phab group would be able to see that paste (if not added as subscribers).
Problem solved I think.
Thanks @jcrespo for quickly adding them so they are not blocked on that group issue!

Niharika added a subscriber: SPoore.May 5 2020, 8:23 PM

With @SPoore's help we managed to clear all the duplicate blocks.
CC @Tchanders @dmaza @Marostegui

Thank you - I am going to rename the task as the task is no longer to add a column to production, but to remove it where it exists :-)

Marostegui renamed this task from ipb_address_unique has an extra column in the code but not in production to ipb_address_unique has an extra column in production but not in the code (WAS: ipb_address_unique has an extra column in the code but not in production).May 6 2020, 5:16 AM
Marostegui updated the task description. (Show Details)
Restricted Application added subscribers: Strainu, jeblad, Cosine02 and 2 others. · View Herald TranscriptMay 6 2020, 5:16 AM
Marostegui added a comment.EditedMay 6 2020, 5:17 AM

I have updated the table description but leaving the old one. Basically to reflect the reality of this task, as it has drifted from the original aiming.
This task is now blocked on merging: https://gerrit.wikimedia.org/r/#/c/mediawiki/core/+/594292/

Marostegui changed the task status from Open to Stalled.May 18 2020, 9:11 AM

Stalling per the above comment.

ARamirez_WMF set Due Date to Jun 2 2020, 4:00 AM.May 21 2020, 3:11 AM
ARamirez_WMF changed the subtype of this task from "Task" to "Deadline".

Change 594292 merged by jenkins-bot:
[mediawiki/core@master] Remove ipb_anon_only from ipb_address_unique UNIQUE INDEX

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

dmaza changed the task status from Stalled to Open.Jun 8 2020, 3:28 AM

Updating status to Open now that https://gerrit.wikimedia.org/r/594292 is merged

Marostegui updated the task description. (Show Details)Jun 8 2020, 7:18 AM

@Niharika @Tchanders I have updated P11137 with duplicates we have on labswiki (wikitech), can you please take a look and fix those so I can go ahead and alter the UNIQUE index there?
Thank you!

Mentioned in SAL (#wikimedia-operations) [2020-06-08T11:53:07Z] <marostegui> Deploy schema change on s3 - T251188

@Niharika @Tchanders I have updated P11137 with duplicates we have on labswiki (wikitech), can you please take a look and fix those so I can go ahead and alter the UNIQUE index there?

Pinging @SPoore too - thanks @Marostegui.

@Niharika @Tchanders I have updated P11137 with duplicates we have on labswiki (wikitech), can you please take a look and fix those so I can go ahead and alter the UNIQUE index there?

Pinging @SPoore too - thanks @Marostegui.

@Tchanders @Marostegui Is this something we can handle automatically? That list of duplicates is way too big to unblock manually.

Maybe I can help? I did most of the blocks with an automated script (mirroring global blocks from meta). I don't have access to the paste.

Amir to the rescue! :-) I gave you access to that paste

Reedy renamed this task from ipb_address_unique has an extra column in production but not in the code (WAS: ipb_address_unique has an extra column in the code but not in production) to ipb_address_unique has an extra column in production but not in the code.Jun 8 2020, 7:13 PM
Marostegui updated the task description. (Show Details)Jun 8 2020, 7:29 PM

Woohoo. Thanks @Ladsgroup! That was a big help.

Mentioned in SAL (#wikimedia-operations) [2020-06-08T20:52:02Z] <Amir1> applying the sql alter table on [[gerrit:594292|ipblocks]] on labswiki (T251188)

Unblock is done and I took the liberty to apply the change on wikitech as well \o/

Ladsgroup updated the task description. (Show Details)Jun 8 2020, 8:52 PM
TK-999 added a subscriber: TK-999.Jun 9 2020, 2:18 AM

This will probably address T157508 as well.

Marostegui closed this task as Resolved.Jun 9 2020, 4:57 AM
Marostegui updated the task description. (Show Details)
Marostegui updated the task description. (Show Details)

This is all done, thanks everyone for the help!

Ladsgroup reopened this task as Open.Aug 5 2020, 10:34 PM

Sorry :( The drift report gave this:

{
    "ipblocks ipb_address index-mismatch-prod-extra": {
        "s4": [
            "db1081.eqiad.wmnet",
            "db1149.eqiad.wmnet",
            "db1138.eqiad.wmnet",
            "db1148.eqiad.wmnet",
            "db1146.eqiad.wmnet",
            "db1147.eqiad.wmnet",
            "db1142.eqiad.wmnet",
            "db1144.eqiad.wmnet",
            "db1121.eqiad.wmnet",
            "db1141.eqiad.wmnet",
            "db1143.eqiad.wmnet"
        ]
    },
    "ipblocks ipb_address_unique index-mismatch-code-extra": {
        "s4": [
            "db1081.eqiad.wmnet",
            "db1149.eqiad.wmnet",
            "db1138.eqiad.wmnet",
            "db1148.eqiad.wmnet",
            "db1146.eqiad.wmnet",
            "db1147.eqiad.wmnet",
            "db1142.eqiad.wmnet",
            "db1144.eqiad.wmnet",
            "db1121.eqiad.wmnet",
            "db1141.eqiad.wmnet",
            "db1143.eqiad.wmnet"
        ]
    }
}

Thanks @Ladsgroup, however I am not seeing the difference on that table, compared for instance with a new wiki created a few days ago.
This is commonswiki (db1138):

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

This is avkwiki:

PRIMARY KEY (`ipb_id`),
UNIQUE KEY `ipb_address_unique` (`ipb_address`(255),`ipb_user`,`ipb_auto`),
KEY `ipb_user` (`ipb_user`),
KEY `ipb_range` (`ipb_range_start`(8),`ipb_range_end`(8)),
KEY `ipb_timestamp` (`ipb_timestamp`),
KEY `ipb_expiry` (`ipb_expiry`),
KEY `ipb_parent_block_id` (`ipb_parent_block_id`)

The only difference I see is:

KEY `ipb_range` (`ipb_range_start`(20),`ipb_range_end`(20)),

vs

KEY `ipb_range` (`ipb_range_start`(8),`ipb_range_end`(8)),

But that is not what this original task is about, am I missing anything here?

I have a feeling it's not commonswiki but it's testcommonswiki

Yup:

wikiadmin@10.64.48.232(testcommonswiki)> show index from ipblocks;
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ipblocks |          0 | PRIMARY             |            1 | ipb_id              | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address         |            1 | ipb_address         | A         |           0 |      255 | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address         |            2 | ipb_user            | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address         |            3 | ipb_auto            | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address         |            4 | ipb_anon_only       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_user            |            1 | ipb_user            | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_range           |            1 | ipb_range_start     | A         |           0 |        8 | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_range           |            2 | ipb_range_end       | A         |           0 |        8 | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_timestamp       |            1 | ipb_timestamp       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_expiry          |            1 | ipb_expiry          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_parent_block_id |            1 | ipb_parent_block_id | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
11 rows in set (0.01 sec)

wikiadmin@10.64.48.232(testcommonswiki)> Bye
ladsgroup@mwmaint1002:~$ sql commonswiki

wikiadmin@10.64.0.219(commonswiki)> show index from ipblocks;
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ipblocks |          0 | PRIMARY             |            1 | ipb_id              | A         |       81159 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address_unique  |            1 | ipb_address         | A         |       81159 |      255 | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address_unique  |            2 | ipb_user            | A         |       81159 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          0 | ipb_address_unique  |            3 | ipb_auto            | A         |       81159 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_user            |            1 | ipb_user            | A         |       81159 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_timestamp       |            1 | ipb_timestamp       | A         |       81159 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_expiry          |            1 | ipb_expiry          | A         |        1591 |     NULL | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_range           |            1 | ipb_range_start     | A         |        1449 |       20 | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_range           |            2 | ipb_range_end       | A         |        1449 |       20 | NULL   |      | BTREE      |         |               |
| ipblocks |          1 | ipb_parent_block_id |            1 | ipb_parent_block_id | A         |          72 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

codfw testcommonswiki 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`),
  KEY `ipb_user` (`ipb_user`),
  KEY `ipb_range` (`ipb_range_start`(8),`ipb_range_end`(8)),
  KEY `ipb_timestamp` (`ipb_timestamp`),
  KEY `ipb_expiry` (`ipb_expiry`),
  KEY `ipb_parent_block_id` (`ipb_parent_block_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
Marostegui added a comment.EditedAug 6 2020, 2:22 PM

s4 eqiad for testcommonswiki

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

Thanks! The wiki is tiny so I hope you don't need to depool repool the whole s4 again.

testcommonswiki is done!
Thanks for reporting it!

Marostegui closed this task as Resolved.Aug 7 2020, 4:56 AM