Schema changes for expiring user groups
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	TTO
	Jan 18 2017, 3:09 AM

Description

Once https://gerrit.wikimedia.org/r/328377 and its various dependencies are merged, there will need to be schema changes on Wikimedia wikis.

The ALTER TABLES to run: https://gerrit.wikimedia.org/r/#/c/328377/19/maintenance/archives/patch-user_groups-ug_expiry.sql (most wikis will not require lines 4 and 5 - see Gerrit comments)
Where to run those changes: All wikis
When to run those changes: Ideally before MW 1.29 release in May or June.
If the schema change is backwards compatible: Yes. The feature is temporarily disabled by a feature flag.
If the schema change has been tested already on some of the test/beta wikis: Yes
If it involves new columns or tables, if the data should be made available on the labs replicas: Yes, the new ug_expiry column is public data

Details

	Subject	Repo	Branch	Lines +/-
	Set wgDisableUserGroupExpiry to true on production, false on labs	operations/mediawiki-config	master	+8 -0

Customize query in gerrit

Related Objects
Search...

		Status	Subtype	Assigned	Task
		Resolved		TTO	T12493 Setting a temporary usergroup (allow expiry of user rights via Special:UserRights form)
		Resolved		Marostegui	T155605 Schema changes for expiring user groups

Event Timeline

TTO created this task.Jan 18 2017, 3:09 AM

Change 332721 had a related patch set uploaded (by TTO):
Set wgDisableUserGroupExpiry to true on production, false on labs

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

gerritbot added a project: Patch-For-Review.Jan 18 2017, 3:12 AM

Hi!

Thanks for the detailed summary. Can you please add me as a reviewer to the SQL patch so I can take a look?
Once this ticket is marked for Schema-change-in-production we will take care of it.

Marostegui moved this task from Triage to Backlog on the DBA board.Jan 18 2017, 1:03 PM

Change 332721 merged by jenkins-bot:
Set wgDisableUserGroupExpiry to true on production, false on labs

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

Mentioned in SAL (#wikimedia-operations) [2017-01-18T14:30:07Z] <zfilipin@tin> Synchronized wmf-config/InitialiseSettings-labs.php: SWAT: [[gerrit:332721|Set wgDisableUserGroupExpiry to true on production, false on labs (T155605)]] (duration: 00m 40s)

Mentioned in SAL (#wikimedia-operations) [2017-01-18T14:31:05Z] <zfilipin@tin> Synchronized wmf-config/InitialiseSettings.php: SWAT: [[gerrit:332721|Set wgDisableUserGroupExpiry to true on production, false on labs (T155605)]] (duration: 00m 40s)

BethNaught subscribed.Jan 18 2017, 4:40 PM

MarcoAurelio edited projects, added Stewards-and-global-tools (Temporary-UserRights); removed Stewards-and-global-tools.Jan 18 2017, 9:45 PM

Raymond subscribed.Jan 28 2017, 9:59 AM

TTO mentioned this in T12493: Setting a temporary usergroup (allow expiry of user rights via Special:UserRights form).Jan 31 2017, 1:19 AM

tom29739 subscribed.Jan 31 2017, 1:57 AM

MusikAnimal subscribed.Jan 31 2017, 3:16 AM

Added Schema-change-in-production tag. I think we are satisfied that the database aspect of this change is suitable for deployment.

@jcrespo, @Marostegui: Do you think you could give a rough ETA for this change? We'd like to know whether the feature flag will be able to be removed from MW core before MW 1.29 is released in May or June.

TTO updated the task description. (Show Details)Feb 3 2017, 2:35 AM

Hey @TTO!

We will take a look, given that it needs to be done in all the wikis it will take a while, but I think we can try to have it done before May. The good news is that those changes can be done ONLINE so the slaves will not get delayed while doing it, so we'd need less time to depool/repool them as they don't have to catch up.
Also, if most of the big wikis do not require the PK to be added (as I mentioned here: https://gerrit.wikimedia.org/r/#/c/328377/19/maintenance/archives/patch-user_groups-ug_expiry.sql) , that is even faster.

We have some other schema changes going on now, but this one should not be one of the most difficult ones I believe (last famous words :-) )

I have taken a look at all the hosts in s1 (enwiki) and the table there already has the PK, so it should be easier to get the index removed and the column+index added. Will start with s1 next week.

Note that if the PK already exists, the unique index may not exist, so it would just be a matter of adding the new column and index. The majority of wikis will probably be like this.

In T155605#3052574, @TTO wrote:

Note that if the PK already exists, the unique index may not exist, so it would just be a matter of adding the new column and index. The majority of wikis will probably be like this.

Yes, but the patch also drops the existing index on the table:

KEY `ug_group` (`ug_group`)

And adds the one for the new column: https://gerrit.wikimedia.org/r/#/c/328377/22/maintenance/archives/patch-user_groups-ug_expiry.sql

And yes, there is not UNIQUE key as the PK is there, at least on s1.

Eh? Where are we dropping the ug_group index? From https://gerrit.wikimedia.org/r/#/c/328377/22/maintenance/tables.sql we look to be keeping it. Are you saying it is now redundant, as part of the primary key?

In T155605#3052579, @TTO wrote:

Eh? Where are we dropping the ug_group index? From https://gerrit.wikimedia.org/r/#/c/328377/22/maintenance/tables.sql we look to be keeping it. Are you saying it is now redundant, as part of the primary key?

Sorry, you are right, I misread:

DROP INDEX ug_user_group,

And read confused it with the existing one: KEY ug_group (ug_group)

Apart from s1:

s2 all databases there have the PK on their user_groups table
s4, commonswiki has the PK
s5, dewiki has the PK. wikidatawiki doesn't, it has the UNIQUE
s6, ruwiki,jawiki and frwiki, they all have the PK.

And the list of wikis of s3 that DO NOT HAVE the PK but an UNIQUE key instead (this is mostly for myself to remember before doing all the alters :-) ):

acewiki
adywiki
arbcom_cswiki
arbcom_fiwiki
arwikimedia
arwikiversity
aswikisource
azbwiki
bdwikimedia
bewikimedia
bewikisource
bjnwiki
boardgovcomwiki
brwikimedia
brwikisource
cawikimedia
checkuserwiki
ckbwiki
cnwikimedia
cowikimedia
dkwikimedia
donatewiki
ecwikimedia
elwikinews
elwikivoyage
enwikivoyage
eowikinews
eowikisource
eswikivoyage
etwikimedia
fawikinews
fawikivoyage
fdcwiki
fiwikimedia
fiwikiversity
fiwikivoyage
flaggedrevs_labswikimedia
frrwiki
frwikivoyage
gagwiki
gomwiki
guwikisource
hewikivoyage
iegcomwiki
jamwiki
kbdwiki
koiwiki
kowikinews
kowikiversity
krcwiki
legalteamwiki
lezwiki
liquidthreads_labswikimedia
liwikibooks
loginwiki
lrcwiki
ltgwiki
maiwiki
mhrwiki
minwiki
mkwikimedia
movementroleswiki
mrjwiki
mrwikisource
mwlwiki
mxwikimedia
nlwikivoyage
noboard_chapterswikimedia
noboardwiki
nsowiki
nycwikimedia
olowiki
ombudsmenwiki
orwikisource
outreachwiki
pcdwiki
pflwiki
plwikivoyage
pnbwiki
pnbwiktionary
pntwiki
projectcomwiki
ptwikimedia
ptwikivoyage
readerfeedback_labswikimedia
rowikivoyage
ruewiki
ruwikimedia
ruwikiversity
ruwikivoyage
sahwikisource
sawikiquote
sawikisource
slwikiversity
sqwikinews
steward
stewardwiki
strategyappswiki
strategywiki
svwikiversity
svwikivoyage
tcywiki
tenwiki
test2wiki
testwikidatawiki
transitionteamwiki
trwikimedia
trwikinews
tyvwiki
uawikimedia
ukwikivoyage
usabilitywiki
vecwikisource
vecwiktionary
vepwiki
vewikimedia
viwikivoyage
votewiki
wikimania2010wiki
wikimania2011wiki
wikimania2012wiki
wikimania2013wiki
wikimania2014wiki
wikimania2015wiki
wikimania2016wiki
wikimania2017wiki
xmfwiki
zerowiki
zhwikivoyage

Mentioned in SAL (#wikimedia-operations) [2017-02-28T07:41:09Z] <marostegui> Deploy alter table s4.user_groups - T155605

s4 is done.
As it had the primary key, this is what was run (on the master - db1040):

./osc_host.sh --host=db1040.eqiad.wmnet --port=3306 --db=commonswiki --table=user_groups --method=ddl "ADD COLUMN ug_expiry varbinary(14) NULL default NULL,ADD INDEX ug_expiry (ug_expiry);"

And it got replicated to all the hosts:

root@PRODUCTION s4[commonswiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db2065     |
+------------+
1 row in set (0.00 sec)

root@PRODUCTION s4[commonswiki]> show create table user_groups\G
*************************** 1. row ***************************
       Table: user_groups
Create Table: CREATE TABLE `user_groups` (
  `ug_user` int(5) unsigned NOT NULL DEFAULT '0',
  `ug_group` varbinary(255) NOT NULL DEFAULT '',
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_group` (`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

Mentioned in SAL (#wikimedia-operations) [2017-02-28T08:15:01Z] <marostegui> Deploy alter table s5 dewiki.user_groups - T155605

Mentioned in SAL (#wikimedia-operations) [2017-02-28T08:18:58Z] <marostegui> Deploy alter table s5 wikidatawiki.user_groups - T155605

s5 (dewiki and wikidatawiki) are done.
dewiki only needed the column and the index as the PK was there:

./osc_host.sh --host=db1049.eqiad.wmnet --port=3306 --db=dewiki --table=user_groups --method=ddl "ADD COLUMN ug_expiry varbinary(14) NULL default NULL,ADD INDEX ug_expiry (ug_expiry);"

And this is how a slave looks like now:

root@PRODUCTION s5[dewiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db2066     |
+------------+
1 row in set (0.00 sec)

root@PRODUCTION s5[dewiki]> show create table user_groups;
+-------------+------------------------------------------------------------------------------------------------------------------------
| Table       | Create Table
+-------------+------------------------------------------------------------------------------------------------------------------------
| user_groups | CREATE TABLE `user_groups` (
  `ug_user` int(5) unsigned NOT NULL DEFAULT '0',
  `ug_group` varbinary(255) NOT NULL DEFAULT '',
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_group` (`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

wikidatawiki needed the PK to be added as it has the UNIQUE key:

./osc_host.sh --host=db1049.eqiad.wmnet --port=3306 --db=wikidatawiki --table=user_groups --method=ddl "drop key ug_user_group, ADD PRIMARY KEY (ug_user, ug_group), ADD COLUMN ug_expiry varbinary(14) NULL default NULL,ADD INDEX ug_expiry (ug_expiry);"

root@PRODUCTION s5[wikidatawiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db2066     |
+------------+
1 row in set (0.00 sec)

root@PRODUCTION s5[wikidatawiki]> show create table user_groups\G
*************************** 1. row ***************************
       Table: user_groups
Create Table: CREATE TABLE `user_groups` (
  `ug_user` int(10) unsigned NOT NULL DEFAULT '0',
  `ug_group` varbinary(255) NOT NULL DEFAULT '',
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_group` (`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

Mentioned in SAL (#wikimedia-operations) [2017-02-28T08:35:04Z] <marostegui> Deploy alter table s6 (frwiki,jawiki,ruwiki).user_groups - T155605

s6 (frwiki,jawiki and ruwiki) done, only needed the column and the key, the PK was already there

./osc_host.sh --host=db1050.eqiad.wmnet --port=3306 --db={frwiki,jawiki,ruwiki} --table=user_groups --method=ddl "ADD COLUMN ug_expiry varbinary(14) NULL default NULL,ADD INDEX ug_expiry (ug_expiry);"

Slave:

root@PRODUCTION s6[(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db2067     |
+------------+
1 row in set (0.00 sec)

root@PRODUCTION s6[(none)]> show create table frwiki.user_groups\G
*************************** 1. row ***************************
       Table: user_groups
Create Table: CREATE TABLE `user_groups` (
  `ug_user` int(5) unsigned NOT NULL DEFAULT '0',
  `ug_group` varbinary(255) NOT NULL DEFAULT '',
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_group` (`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

root@PRODUCTION s6[(none)]> show create table jawiki.user_groups\G
*************************** 1. row ***************************
       Table: user_groups
Create Table: CREATE TABLE `user_groups` (
  `ug_user` int(5) unsigned NOT NULL DEFAULT '0',
  `ug_group` varbinary(255) NOT NULL DEFAULT '',
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_group` (`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

root@PRODUCTION s6[(none)]> show create table ruwiki.user_groups\G
*************************** 1. row ***************************
       Table: user_groups
Create Table: CREATE TABLE `user_groups` (
  `ug_user` int(5) unsigned NOT NULL DEFAULT '0',
  `ug_group` varbinary(255) NOT NULL DEFAULT '',
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_group` (`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

Mentioned in SAL (#wikimedia-operations) [2017-02-28T09:03:39Z] <marostegui> Deploy alter table s1 (enwiki).user_groups - T155605

s1 (enwiki) done, only needed the column and the index:

./osc_host.sh --host=db1052.eqiad.wmnet --port=3306 --db=enwiki --table=user_groups --method=ddl "ADD COLUMN ug_expiry varbinary(14) NULL default NULL,ADD INDEX ug_expiry (ug_expiry);"

Slave:

root@PRODUCTION s1[enwiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db2070     |
+------------+
1 row in set (0.00 sec)

root@PRODUCTION s1[enwiki]> show create table user_groups\G
*************************** 1. row ***************************
       Table: user_groups
Create Table: CREATE TABLE `user_groups` (
  `ug_user` int(5) unsigned NOT NULL DEFAULT '0',
  `ug_group` varbinary(255) NOT NULL DEFAULT '',
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_group` (`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

Mentioned in SAL (#wikimedia-operations) [2017-02-28T09:38:54Z] <marostegui> Deploy alter table s7 on all wikis for table user_groups - T155605

s7 done for all wikis (centralauth doesn't have that table). Only the column and index was needed, the PK was there:

./osc_host.sh --host=db1041.eqiad.wmnet --port=3306 --db=viwiki --table=user_groups --method=ddl "ADD COLUMN ug_expiry varbinary(14) NULL default NULL,ADD INDEX ug_expiry (ug_expiry);"

arwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
cawiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
eswiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
fawiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
frwiktionary
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
hewiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
huwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
kowiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
metawiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
rowiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
ukwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
viwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)

Mentioned in SAL (#wikimedia-operations) [2017-02-28T10:09:44Z] <marostegui> Deploy alter table s2 on all wikis for table user_groups - T155605

s2 is done for all wikis except l10nwiki, which doesn't have that table. The PK was there in all of them, so only the column and the index was needed:

./osc_host.sh --host=db1018.eqiad.wmnet --port=3306 --db=$i --table=user_groups --method=ddl "ADD COLUMN ug_expiry varbinary(14) NULL default NULL,ADD INDEX ug_expiry (ug_expiry);"

Slave:

root@db2063:~# for i in `mysql --skip-ssl -e "show databases;" | egrep -v "sys|percona|test|ops|information_schema|performance_schema|heartbeat|mysql|10nwiki"`; do echo $i; mysql --skip-ssl $i -e "show create table user_groups\G"| grep expiry ;done
Database
bgwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
bgwiktionary
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
cswiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
enwikiquote
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
enwiktionary
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
eowiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
fiwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
idwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
itwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
nlwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
nowiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
plwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
ptwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
svwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
thwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
trwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)
zhwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  KEY `ug_expiry` (`ug_expiry`)

In T155605#3060614, @Marostegui wrote:

s2 is done for all wikis except l10nwiki, which doesn't have that table.

For l10nwiki see T119811: Drop localisation and localisation_file_hash tables, l10nwiki databases too

In T155605#3060617, @TTO wrote:

In T155605#3060614, @Marostegui wrote:

s2 is done for all wikis except l10nwiki, which doesn't have that table.

For l10nwiki see T119811: Drop localisation and localisation_file_hash tables, l10nwiki databases too

Thanks! I was not aware of that ticket :)

So, to sum up:

s1 done
s2 done
s3 pending
s4 done
s5 done
s6 done
s7 done

I will start with s3, but will require more time as the list of wikis is larger and I need to double check all of them, PKs, sizes etc

For s3 I am doing batches of wikis, starting with those which don't have the PK (129 wikis in total) now

All the 129 wikis in s3 that didn't have the PK have been modified, so they now have the new PK, the new column and the new index:

root@db1075:~# for i in `cat non_pk`; do echo $i; mysql --skip-ssl $i -e "show create table user_groups\G" | egrep "PRIMARY|expiry";done
acewiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
adywiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
arbcom_cswiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
arbcom_fiwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)


<snip>


wikimania2016wiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
wikimania2017wiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
xmfwiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
zerowiki
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)
zhwikivoyage
  `ug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`ug_user`,`ug_group`),
  KEY `ug_expiry` (`ug_expiry`)

Will start to double check all the other wikis, PK, sizes etc and start altering in batches too.

I am also filtering the wikis that do not have that table in s3:

zh_twwiki|zh_cnwiki|wikimania|wikiconfig|webshop|test|rel13testwiki|oai|katesdb|l10nwiki|jamestemp|devwikiinternal|defoundation|boardvote2006|boardvote2005|boardvote|boards|blocker|affcomwiki

In T155605#3060901, @Marostegui wrote:
I am also filtering the wikis that do not have that table in s3:
zh_twwiki|zh_cnwiki|wikimania|wikiconfig|webshop|test|rel13testwiki|oai|katesdb|l10nwiki|jamestemp|devwikiinternal|defoundation|boardvote2006|boardvote2005|boardvote|boards|blocker|affcomwiki

At least a couple of those look archiveable/droppable :)... oai was never a wiki either

We have 753 wikis (yay) to alter that already have the PK on s3. I will do that in batches.
The sizes of the tables are not worrying as these are the top5 in number of rows:

From those 753 pending wikis, 308 have already been altered in different batches.
We have seen errors on some servers, and we believe they were being generated while the alter was being done (race condition), these are some examples: https://logstash.wikimedia.org/goto/373a2cbcbbd98fed90a20e9188344405

message	  	CentralAuthUser::localUserData		Table definition has changed, please retry transaction ()	SELECT  ug_group  FROM `user_groups`    WHERE ug_user = 'xxx' AND (1) 



@timestamp	  	2017-02-28T14:13:58
t@version	  	1
#_score	  	
t_type	  	mediawiki
tchannel	  	DBQuery
tdb_name	  	bnwiki
tdb_user	  	wikiuser
?errno	  	  1412
terror	  	Table definition has changed, please retry transaction ()
?fname	  	  CentralAuthUser::localUserData
thost	  	mw1182
tmessage	  	CentralAuthUser::localUserData		Table definition has changed, please retry transaction ()	SELECT  ug_group  FROM `user_groups`    WHERE ug_user = 'xxx' AND (1)  
tmethod	  	Database::reportQueryError
tmwversion	  	1.29.0-wmf.13
tserver	  	www.wikidata.org
?sql1line	  	  SELECT  ug_group  FROM `user_groups`    WHERE ug_user = 'xxxx' AND (1)  
ttags	  	syslog, es, es
ttype	  	mediawiki
twiki	  	wikidatawiki

@TTO, I have stopped for now to see if they also stop or they keep being generated. There is not a big amount, around 37 errors in the last 12 hours (I started today with the ALTERs) but let's see if we have no more generated until tomorrow morning.

No errors in the last hour since I stopped the ALTERs - looks good.

The errors stopped right after I stopped the ALTERs and there has been no more. So I am going to resume the alters and keep advancing on the list of wikis from s3.

Mentioned in SAL (#wikimedia-operations) [2017-03-01T09:14:16Z] <marostegui> Deploy alter table s3 (all wikis) user_groups table - T155605

All the wikis in s3 have been finished. I believe all the shards are now done. I am going to recheck them to make sure we have the new column everywhere where the table exists.

Marostegui mentioned this in T132838: Certain wiki databases missing from replicas?.Mar 1 2017, 3:17 PM

So I have done the following checks.
Every shard master + a slave in codfw. Checked all the wikis per shard with that table, and if the table existed, check the column and the PK.
s1, s2, s4, s5, s6 and s7 look good
s3 will take a bit more time to check if it looks good too, I will close this ticket once checked out.

s3 is missing the column on: closed_zh_twwiki which I assume isn't used.
Please reopen if you think I should add it there too.

The rest of the wikis, where the table exists, are done.

jcrespo awarded a token.Mar 2 2017, 8:26 AM

Thank you, Manuel, for your work on this!

Will these schema changes automatically percolate to the Labs views, or does some action need to be taken so that the new column is visible there?

In T155605#3066863, @TTO wrote:

Thank you, Manuel, for your work on this!

You are welcome! Another table with PK :-)

Will these schema changes automatically percolate to the Labs views, or does some action need to be taken so that the new column is visible there?

No, I believe we will need to create the views to include this column, @chasemp @yuvipanda can you help out here?
This is the current view have in labs (took labsdb1009 as an example):

mysql:root@localhost [enwiki_p]> show create table user_groups\G
*************************** 1. row ***************************
                View: user_groups
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER VIEW `user_groups` AS select `enwiki`.`user_groups`.`ug_user` AS `ug_user`,`enwiki`.`user_groups`.`ug_group` AS `ug_group` from `enwiki`.`user_groups`
character_set_client: utf8
collation_connection: utf8_general_ci

Thanks!

@TTO the db changes should have included labs physical table change already, but to make them visible, if they are not already there, you may need to file a new ticket for the labs team so that their views are updated. That may require legal and/or security approval. Sending a patch to their yaml normally speeds the process for them.

• DannyH moved this task from New & TBD Tickets to Archive on the Community-Tech board.Mar 2 2017, 6:37 PM

TTO mentioned this in T154355: page_lang column of the page table is not replicated to Labs.Mar 3 2017, 11:21 AM

Marostegui mentioned this in T160686: ug_expiry column of the user_groups table is not present on Labs.Apr 24 2017, 1:46 PM

Mentioned in SAL (#wikimedia-operations) [2017-04-24T17:46:50Z] <marostegui> Alter table labtestwiki.user_groups on labtestweb2001 - T155605

Marostegui mentioned this in T130067: Add wl_id to watchlist tables on production dbs.Apr 25 2017, 1:19 PM

• Phabricator_maintenance removed a subscriber: yuvipanda.Jun 7 2017, 6:39 PM

Marostegui mentioned this in T200140: Ensure labswiki and labtestwiki are up to date with MW schema changes.Jul 22 2018, 6:19 AM

Aklapper removed a subscriber: Anomie.Oct 16 2020, 5:43 PM