Page MenuHomePhabricator
Create Task
Maniphest T245925

CentralAuth schema changes Feb 2020
Closed, ResolvedPublic
Actions

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

Related Objects
Search...

Event Timeline

Reedy created this task.Feb 22 2020, 5:22 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 22 2020, 5:22 PM
Reedy updated the task description. (Show Details)Feb 22 2020, 5:23 PM
DannyS712 subscribed.Feb 22 2020, 6:24 PM
Melos subscribed.Feb 22 2020, 7:33 PM
Reedy updated the task description. (Show Details)Feb 23 2020, 3:02 PM
Reedy added a subtask: T245413: Prepare CentralAuth db for global groups expiration.Feb 23 2020, 3:05 PM
Reedy added a project: DBA.
Reedy updated the task description. (Show Details)Feb 24 2020, 6:12 AM
• Marostegui changed the task status from Open to Stalled.Feb 24 2020, 6:43 AM
• Marostegui claimed this task.
• Marostegui added a project: Schema-change-in-production.
• Marostegui moved this task from Triage to In progress on the DBA board.
• Marostegui moved this task from Backlog to In progress on the Schema-change-in-production board.

This is not yet ready till https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/CentralAuth/+/572647/ is merged. Stalling till then.
https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/CentralAuth/+/574188/ this is merged and ready.

• Marostegui moved this task from In progress to Unclear/blocked to proceed on the Schema-change-in-production board.Feb 24 2020, 6:45 AM
• Marostegui moved this task from In progress to Blocked external/Not db team on the DBA board.
• Marostegui mentioned this in T245413: Prepare CentralAuth db for global groups expiration.
Reedy added a comment.Feb 24 2020, 6:48 AM
In T245925#5911007, @Marostegui wrote:

This is not yet ready till https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/CentralAuth/+/572647/ is merged. Stalling till then.
https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/CentralAuth/+/574188/ this is merged and ready.

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)

• Marostegui added a comment.Feb 24 2020, 6:50 AM
In T245925#5911015, @Reedy wrote:
In T245925#5911007, @Marostegui wrote:

This is not yet ready till https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/CentralAuth/+/572647/ is merged. Stalling till then.
https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/CentralAuth/+/574188/ this is merged and ready.

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)Feb 24 2020, 6:52 AM
Reedy updated the task description. (Show Details)
Reedy closed subtask T245413: Prepare CentralAuth db for global groups expiration as Resolved.Feb 24 2020, 6:59 AM
• Marostegui changed the task status from Stalled to Open.Feb 24 2020, 7:01 AM
• Marostegui moved this task from Unclear/blocked to proceed to In progress on the Schema-change-in-production board.
• Marostegui moved this task from Blocked external/Not db team to In progress on the DBA board.
MarcoAurelio subscribed.Feb 24 2020, 10:44 AM

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.

• Marostegui added a comment.Feb 24 2020, 3:01 PM
In T245925#5911533, @MarcoAurelio wrote:

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

Stashbot added a comment.Feb 24 2020, 3:05 PM

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

• Marostegui closed this task as Resolved.Feb 24 2020, 3:06 PM

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)
Reedy added a comment.Feb 24 2020, 3:23 PM
In T245925#5912199, @Marostegui wrote:
In T245925#5911533, @MarcoAurelio wrote:

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

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL