Page MenuHomePhabricator

CentralAuth schema changes Feb 2020
Closed, ResolvedPublic

Description

Combining two requests into one task (T245413: Prepare CentralAuth db for global groups expiration and T245921: Remove redundant global_user_groups.gug_user and global_group_permissions.ggp_group indexes)

https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/CentralAuth/+/572647/1/db_patches/patch-gug_expiry.sql

ALTER TABLE /*_*/global_user_groups
  ADD COLUMN gug_expiry varbinary(14) NULL default NULL,
  ADD INDEX gug_expiry (gug_expiry);

https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/CentralAuth/+/574188/2/db_patches/patch-remove-dupe-indexes.sql (merged)

ALTER TABLE global_user_groups
  DROP INDEX gug_user;

ALTER TABLE global_group_permissions
  DROP INDEX ggp_group;

Combined SQL:

ALTER TABLE global_group_permissions
  DROP INDEX ggp_group;

ALTER TABLE /*_*/global_user_groups
  ADD COLUMN gug_expiry varbinary(14) NULL default NULL,
  ADD INDEX gug_expiry (gug_expiry),
  DROP INDEX gug_user;

Where: centralauth database (s7)
When: At DBA leisure
Backwards compatible: Yes
Tested already: Index removals applied to beta

Event Timeline

Marostegui changed the task status from Open to Stalled.Feb 24 2020, 6:43 AM
Marostegui claimed this task.
Marostegui moved this task from Triage to In progress on the DBA board.
Marostegui moved this task from Backlog to In progress on the Blocked-on-schema-change board.

Dunno if it's better for you or Jaime to review, but was looking for a quick DBA check on 572647 (I'm happy to C+2 it)

Dunno if it's better for you or Jaime to review, but was looking for a quick DBA check on 572647 (I'm happy to C+2 it)

I just did :)

Reedy updated the task description. (Show Details)
Marostegui changed the task status from Stalled to Open.Feb 24 2020, 7:01 AM
Marostegui moved this task from Blocked external/Not db team to In progress on the DBA board.

We also need some sort of auto purging as we did for local userrights in https://gerrit.wikimedia.org/r/plugins/gitiles/operations/puppet/+/production/modules/mediawiki/manifests/maintenance/purge_expired_userrights.pp

That'd require creating a maintenance script within extension-CentralAuth and later a regular cron added to Wikimedia puppet.

We also need some sort of auto purging as we did for local userrights in https://gerrit.wikimedia.org/r/plugins/gitiles/operations/puppet/+/production/modules/mediawiki/manifests/maintenance/purge_expired_userrights.pp

That'd require creating a maintenance script within extension-CentralAuth and later a regular cron added to Wikimedia puppet.

Would you mind creating a task for it?
Thank you :)

Mentioned in SAL (#wikimedia-operations) [2020-02-24T15:05:58Z] <marostegui> Deploy schema change on db1086 (s7 master) with replication - T245925

Done

root@db1086.eqiad.wmnet[centralauth]> show create table global_group_permissions\G show create table global_user_groups\G
*************************** 1. row ***************************
       Table: global_group_permissions
Create Table: CREATE TABLE `global_group_permissions` (
  `ggp_group` varbinary(255) NOT NULL DEFAULT '',
  `ggp_permission` varbinary(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`ggp_group`,`ggp_permission`),
  KEY `ggp_permission` (`ggp_permission`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.01 sec)

*************************** 1. row ***************************
       Table: global_user_groups
Create Table: CREATE TABLE `global_user_groups` (
  `gug_user` int(11) NOT NULL DEFAULT '0',
  `gug_group` varbinary(255) NOT NULL DEFAULT '',
  `gug_expiry` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`gug_user`,`gug_group`),
  KEY `gug_group` (`gug_group`),
  KEY `gug_expiry` (`gug_expiry`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

We also need some sort of auto purging as we did for local userrights in https://gerrit.wikimedia.org/r/plugins/gitiles/operations/puppet/+/production/modules/mediawiki/manifests/maintenance/purge_expired_userrights.pp

That'd require creating a maintenance script within extension-CentralAuth and later a regular cron added to Wikimedia puppet.

Would you mind creating a task for it?
Thank you :)

We might not, depends on actual solution that ends up in https://gerrit.wikimedia.org/r/#/c/mediawiki/core/+/574230/

No point filing a task just yet IMHO. The code to use the fields isn't finished yet (partially depending on the above), so it's not of any sort of urgency