Schema changes for expiring user groups
Closed, ResolvedPublic

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

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 Blocked-on-schema-change 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)

TTO added a subscriber: Legoktm.

Added Blocked-on-schema-change 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.

TTO added a comment.Feb 24 2017, 10:00 AM

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.

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.

TTO added a comment.Feb 24 2017, 10:08 AM

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?

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`)
TTO added a comment.Feb 28 2017, 10:20 AM

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

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
Reedy added a subscriber: Reedy.Feb 28 2017, 1:17 PM

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:

508
533
784
798
834

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.

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.

Marostegui closed this task as Resolved.Mar 2 2017, 8:15 AM
Marostegui claimed this task.

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.

TTO added a comment.Mar 2 2017, 8:29 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?

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 Untriaged to Archive on the Community-Tech board.Mar 2 2017, 6:37 PM

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