Page MenuHomePhabricator

Add globaluser.gu_hidden_level column to production
Closed, ResolvedPublic

Description

  1. ALTERs to run: https://gerrit.wikimedia.org/r/plugins/gitiles/mediawiki/extensions/CentralAuth/+/464c81a4808b29ee5e05d4613bbbce349837edc6/db_patches/patch-gu_hidden_normalize.sql
  2. Where to run those changes: centralauth database only
  3. When to run those changes: Up to DBA availability
  4. If the schema change is backwards compatible: Yes. All added fields have default values.
  5. If the schema change has been tested already on some of the test/beta wikis: Yes, tested on deployment-prep.
  6. If the data should be made available on the cloud services replicas and/or dumps: Please treat like gu_hidden, rows with gu_hidden_level != 0 MUST NOT be exposed publicly

Progress

Related Objects

Event Timeline

Change 743948 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[operations/puppet@production] wmcs: Change maintain-views to prepare for schema change

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

Change 743948 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[operations/puppet@production] wmcs: Change maintain-views to prepare for schema change

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

hmm, first we need to run the schema change and then merge this I guess

Marostegui moved this task from Triage to Ready on the DBA board.
Marostegui added a subscriber: Marostegui.

Change 743948 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[operations/puppet@production] wmcs: Change maintain-views to prepare for schema change

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

hmm, first we need to run the schema change and then merge this I guess

Yep

@Majavah this change will happen after the end of year holidays, but before. We are wrapping up things before the end of year code freeze and we'll not do big maintenances in between.

Marostegui triaged this task as Medium priority.Dec 7 2021, 5:55 AM

Schema change code

from auto_schema.schema_change import SchemaChange

# Copy this file and make adjustments

section = 's7'
should_depool = 'auto'
downtime_hours = 6
should_downtime = True
ticket = 'T297094'

# Don't add set session sql_log_bin=0;
command = """use centralauth;ALTER TABLE  globaluser ADD COLUMN gu_hidden_level INT NOT NULL DEFAULT 0 AFTER gu_hidden; CREATE INDEX gu_hidden_level ON globaluser (gu_name(255), gu_hidden_level);"""

# Set this to false if you don't want to run on all dbs
# In that case, you have to specify the db in the command.
all_dbs = False

# DO NOT FORGET to set the right port if it's not 3306
# Use None instead of [] to get all pooled replicas
replicas = None

# Should return true if schema change is applied


def check(db):
    if 'globaluser' not in db.run_sql('use centralauth;show tables;'):
        return True
    return 'gu_hidden_level' in db.run_sql('use centralauth;desc globaluser;')


schema_change = SchemaChange(
    replicas=replicas,
    section=section,
    all_dbs=all_dbs,
    check=check,
    command=command,
    ticket=ticket,
    downtime_hours=downtime_hours,
    should_depool=should_depool
)
schema_change.run()

@Ladsgroup please review :)

Mentioned in SAL (#wikimedia-operations) [2021-12-07T16:25:28Z] <Amir1> deleting broken flaggedtemplates rows on dewiki (T297094)

Mentioned in SAL (#wikimedia-operations) [2021-12-07T16:25:28Z] <Amir1> deleting broken flaggedtemplates rows on dewiki (T297094)

wrong ticket :facepalm:

I am executing this on a codfw replica (db2150) and will leave it with that column until January to make sure nothing breaks (we always do this for a couple of days before proceeding, but on Friday it is the last day we'll do maintenance before the code freeze).

@Majavah let me know if this looks correct:

# mysql.py -hdb2150 centralauth -e "show create table globaluser\G"
*************************** 1. row ***************************
       Table: globaluser
Create Table: CREATE TABLE `globaluser` (
  `gu_id` int(11) NOT NULL AUTO_INCREMENT,
  `gu_name` varbinary(255) DEFAULT NULL,
  `gu_enabled` varbinary(14) NOT NULL DEFAULT '',
  `gu_enabled_method` enum('opt-in','batch','auto','admin') DEFAULT NULL,
  `gu_home_db` varbinary(255) DEFAULT NULL,
  `gu_email` varbinary(255) DEFAULT NULL,
  `gu_email_authenticated` varbinary(14) DEFAULT NULL,
  `gu_salt` varbinary(16) DEFAULT NULL,
  `gu_password` tinyblob DEFAULT NULL,
  `gu_locked` tinyint(1) NOT NULL DEFAULT 0,
  `gu_hidden` varbinary(255) NOT NULL DEFAULT '',
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  `gu_registration` varbinary(14) DEFAULT NULL,
  `gu_password_reset_key` tinyblob DEFAULT NULL,
  `gu_password_reset_expiration` varbinary(14) DEFAULT NULL,
  `gu_auth_token` varbinary(32) DEFAULT NULL,
  `gu_cas_token` int(10) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`gu_id`),
  UNIQUE KEY `gu_name` (`gu_name`),
  KEY `gu_email` (`gu_email`),
  KEY `gu_locked` (`gu_name`,`gu_locked`),
  KEY `gu_hidden` (`gu_name`,`gu_hidden`),
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
) ENGINE=InnoDB AUTO_INCREMENT=68409564 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

@Majavah let me know if this looks correct:

# mysql.py -hdb2150 centralauth -e "show create table globaluser\G"
*************************** 1. row ***************************
       Table: globaluser
Create Table: CREATE TABLE `globaluser` (
  `gu_id` int(11) NOT NULL AUTO_INCREMENT,
  `gu_name` varbinary(255) DEFAULT NULL,
  `gu_enabled` varbinary(14) NOT NULL DEFAULT '',
  `gu_enabled_method` enum('opt-in','batch','auto','admin') DEFAULT NULL,
  `gu_home_db` varbinary(255) DEFAULT NULL,
  `gu_email` varbinary(255) DEFAULT NULL,
  `gu_email_authenticated` varbinary(14) DEFAULT NULL,
  `gu_salt` varbinary(16) DEFAULT NULL,
  `gu_password` tinyblob DEFAULT NULL,
  `gu_locked` tinyint(1) NOT NULL DEFAULT 0,
  `gu_hidden` varbinary(255) NOT NULL DEFAULT '',
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  `gu_registration` varbinary(14) DEFAULT NULL,
  `gu_password_reset_key` tinyblob DEFAULT NULL,
  `gu_password_reset_expiration` varbinary(14) DEFAULT NULL,
  `gu_auth_token` varbinary(32) DEFAULT NULL,
  `gu_cas_token` int(10) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`gu_id`),
  UNIQUE KEY `gu_name` (`gu_name`),
  KEY `gu_email` (`gu_email`),
  KEY `gu_locked` (`gu_name`,`gu_locked`),
  KEY `gu_hidden` (`gu_name`,`gu_hidden`),
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
) ENGINE=InnoDB AUTO_INCREMENT=68409564 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

Looks good. Thank you!

Change 743948 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[operations/puppet@production] wmcs: Change maintain-views to prepare for schema change

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

The maintain-views script will probably need to be run as soon as the schema change is done on clouddb* as otherwise it might break the view entirely. Once that is done I will create an specific ticket for WMCS about it.

codfw fully done:

db2150.codfw.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db2122.codfw.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db2121.codfw.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db2120.codfw.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db2118.codfw.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db2108.codfw.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db2098.codfw.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db2095.codfw.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db2087.codfw.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db2086.codfw.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db2077.codfw.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)

Started automatic deployment on eqiad replicas

Mentioned in SAL (#wikimedia-operations) [2022-01-03T10:11:17Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1181 (T297094)', diff saved to https://phabricator.wikimedia.org/P18270 and previous config saved to /var/cache/conftool/dbconfig/20220103-101116-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T10:31:17Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1181 (T297094)', diff saved to https://phabricator.wikimedia.org/P18271 and previous config saved to /var/cache/conftool/dbconfig/20220103-103116-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T11:16:31Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1181 (T297094)', diff saved to https://phabricator.wikimedia.org/P18276 and previous config saved to /var/cache/conftool/dbconfig/20220103-111631-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T11:16:38Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1174 (T297094)', diff saved to https://phabricator.wikimedia.org/P18277 and previous config saved to /var/cache/conftool/dbconfig/20220103-111638-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T11:26:18Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1174 (T297094)', diff saved to https://phabricator.wikimedia.org/P18278 and previous config saved to /var/cache/conftool/dbconfig/20220103-112617-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T12:11:32Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1174 (T297094)', diff saved to https://phabricator.wikimedia.org/P18285 and previous config saved to /var/cache/conftool/dbconfig/20220103-121131-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T12:41:17Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1170:3317 (T297094)', diff saved to https://phabricator.wikimedia.org/P18286 and previous config saved to /var/cache/conftool/dbconfig/20220103-124117-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T13:17:08Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1170:3317 (T297094)', diff saved to https://phabricator.wikimedia.org/P18287 and previous config saved to /var/cache/conftool/dbconfig/20220103-131707-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T14:02:22Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1170:3317 (T297094)', diff saved to https://phabricator.wikimedia.org/P18291 and previous config saved to /var/cache/conftool/dbconfig/20220103-140221-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T14:02:33Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1158 (T297094)', diff saved to https://phabricator.wikimedia.org/P18292 and previous config saved to /var/cache/conftool/dbconfig/20220103-140232-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T14:30:35Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1158 (T297094)', diff saved to https://phabricator.wikimedia.org/P18293 and previous config saved to /var/cache/conftool/dbconfig/20220103-143034-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T15:15:51Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1158 (T297094)', diff saved to https://phabricator.wikimedia.org/P18297 and previous config saved to /var/cache/conftool/dbconfig/20220103-151550-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T15:15:58Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1127 (T297094)', diff saved to https://phabricator.wikimedia.org/P18298 and previous config saved to /var/cache/conftool/dbconfig/20220103-151558-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T16:01:31Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1127 (T297094)', diff saved to https://phabricator.wikimedia.org/P18302 and previous config saved to /var/cache/conftool/dbconfig/20220103-160131-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T16:46:45Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1127 (T297094)', diff saved to https://phabricator.wikimedia.org/P18307 and previous config saved to /var/cache/conftool/dbconfig/20220103-164645-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T16:46:53Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1101:3317 (T297094)', diff saved to https://phabricator.wikimedia.org/P18308 and previous config saved to /var/cache/conftool/dbconfig/20220103-164652-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T17:46:08Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1101:3317 (T297094)', diff saved to https://phabricator.wikimedia.org/P18309 and previous config saved to /var/cache/conftool/dbconfig/20220103-174608-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T18:31:23Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1101:3317 (T297094)', diff saved to https://phabricator.wikimedia.org/P18314 and previous config saved to /var/cache/conftool/dbconfig/20220103-183122-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-03T18:31:30Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1098:3317 (T297094)', diff saved to https://phabricator.wikimedia.org/P18315 and previous config saved to /var/cache/conftool/dbconfig/20220103-183130-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-04T00:39:52Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1098:3317 (T297094)', diff saved to https://phabricator.wikimedia.org/P18328 and previous config saved to /var/cache/conftool/dbconfig/20220104-003951-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2022-01-04T01:25:06Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1098:3317 (T297094)', diff saved to https://phabricator.wikimedia.org/P18331 and previous config saved to /var/cache/conftool/dbconfig/20220104-012506-marostegui.json

All eqiad replicas done - proceeding with the master now

eqiad done:

dbstore1003.eqiad.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db1181.eqiad.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db1174.eqiad.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db1171.eqiad.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db1170.eqiad.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db1158.eqiad.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db1155.eqiad.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db1136.eqiad.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db1127.eqiad.wmnet:3306
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db1101.eqiad.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
db1098.eqiad.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
clouddb1021.eqiad.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
clouddb1018.eqiad.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)
clouddb1014.eqiad.wmnet:3317
  `gu_hidden_level` int(11) NOT NULL DEFAULT 0,
  KEY `gu_hidden_level` (`gu_name`,`gu_hidden_level`)

Change 743948 merged by Razzi:

[operations/puppet@production] wmcs: Change maintain-views to prepare for schema change

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

On upgrading my MediaWiki instance to v1.39, I started getting this error when trying to login:

Error 1054: Unknown column 'gu_hidden_level' in 'field list'
Function: MediaWiki\Extension\CentralAuth\User\CentralAuthUser::loadFromDatabase
Query: SELECT gu_id,gu_name,lu_wiki,gu_salt,gu_password,gu_auth_token,gu_locked,gu_hidden_level,gu_registration,gu_email,gu_email_authenticated,gu_home_db,gu_cas_token FROM globaluser LEFT OUTER JOIN localuser ON ((gu_name=lu_name) AND lu_wiki = 'seminaverbi_en') WHERE gu_name = '151.69.176.101' LIMIT 1

I have run the maintenance update script for all language wikis after the upgrade, but this doesn't seem to fix the missing field. Is there a migration I need to run manually?

@taavi is probably the best person to answer the above comment?

Yes, if you use CentralAuth you are required to run any migrations manually. The extension is maintained purely for Wikimedia wikis where the automatic updater can't be used.

okay I found the SQL patch at the top of this page, I ran that against my central database and now everything is working again. I guess I'll add a comment about this on the extension discussion page...