Page MenuHomePhabricator
Create Task
Maniphest T302658

globaluser table schema changes (March 2022)
Closed, ResolvedPublic
Actions

Description

Apply parts of T300576 to wmf wikis

  1. ALTERs to run:

Combined SQL

DROP  INDEX gu_hidden ON  /*_*/globaluser;
ALTER TABLE  /*_*/globaluser
DROP  gu_hidden,
DROP  gu_enabled,
DROP  gu_enabled_method,
CHANGE  gu_registration gu_registration BINARY(14),
CHANGE  gu_password_reset_expiration gu_password_reset_expiration BINARY(14);
  1. Where to run those changes: centralauth databases (s7)
  2. When to run those changes: any time after T301674
  3. If the schema change is backwards compatible: Yes
  4. If the schema change has been tested already on some of the test/beta wikis: beta cluster is running with the new schema
  5. if the data should be made available on the labs replicas and/or dumps: no change of the existing rules

Details

SubjectRepoBranchLines +/-
drop_gu_hidden_T302658.py: New schema changeoperations/software/schema-changesmaster+38 -0
Customize query in gerrit

Related Objects
Search...

Event Timeline

Umherirrender created this task.Feb 26 2022, 7:24 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 26 2022, 7:24 AM
Umherirrender changed the task status from Open to Stalled.Feb 26 2022, 11:16 AM

Needs to wait for a fix of application code - https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CentralAuth/+/766220

Zabe changed the task status from Stalled to Open.Feb 26 2022, 1:29 PM
Zabe subscribed.
In T302658#7739547, @Umherirrender wrote:

Needs to wait for a fix of application code - https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CentralAuth/+/766220

merged

Umherirrender triaged this task as Lowest priority.Feb 26 2022, 3:34 PM
Stashbot added a comment.Feb 26 2022, 8:05 PM

Mentioned in SAL (#wikimedia-releng) [2022-02-26T20:05:17Z] <zabe> apply T302658 on deployment-prep centralauth databases

Zabe updated the task description. (Show Details)Feb 26 2022, 8:34 PM
Marostegui moved this task from Triage to Ready on the DBA board.Mar 7 2022, 6:09 AM
Marostegui subscribed.Mar 8 2022, 7:16 AM

@Zabe @Umherirrender to confirm, this is now ready to go after the above patch was merged?

Zabe added a comment.Mar 8 2022, 1:08 PM
In T302658#7759203, @Marostegui wrote:

@Zabe @Umherirrender to confirm, this is now ready to go after the above patch was merged?

Yes it is.

Marostegui added a comment.Mar 9 2022, 6:30 AM

So looking at this schema change and the current table definition:

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=69143660 DEFAULT CHARSET=binary

gu_enabled -> we are simply removing the DEFAULT
gu_registration -> we are removing the DEFAULT
gu_password_reset_expiration -> we are removing the DEFAULT

Is this expected?

Zabe changed the task status from Open to Stalled.EditedMar 9 2022, 11:52 AM
In T302658#7762989, @Marostegui wrote:

So looking at this schema change and the current table definition:

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=69143660 DEFAULT CHARSET=binary

gu_enabled -> we are simply removing the DEFAULT
gu_registration -> we are removing the DEFAULT
gu_password_reset_expiration -> we are removing the DEFAULT

Is this expected?

Yes (lemme double check), but I want to change something anyway. Because of T303266, I think that changing gu_enabled is not useful.

Marostegui removed a project: Schema-change-in-production.Mar 9 2022, 11:53 AM

Sure! Please re-add Schema-change-in-production once you consider this ready to be shipped into production

Marostegui moved this task from Ready to Blocked on the DBA board.Mar 9 2022, 11:54 AM
Umherirrender added a comment.Mar 9 2022, 8:32 PM
In T302658#7762989, @Marostegui wrote:

So looking at this schema change and the current table definition:

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=69143660 DEFAULT CHARSET=binary

gu_enabled -> we are simply removing the DEFAULT
gu_registration -> we are removing the DEFAULT
gu_password_reset_expiration -> we are removing the DEFAULT

Is this expected?

This changes from varying-length field to fixed-length fields. The default for gu_registration and gu_password_reset_expiration is also in the new code NULL, its not written explicit

taavi renamed this task from Adjust the field type of globaluser timestamp columns to fixed binary on wmf wikis to Adjust the field type of globaluser timestamp columns to fixed binary and drop gu_hidden on wmf wikis.Mar 16 2022, 7:45 PM
taavi updated the task description. (Show Details)
taavi subscribed.

Added a second change so you can combine alters to the same table - let me know if you prefer I split that to a separate task.

Zabe updated the task description. (Show Details)Mar 16 2022, 7:55 PM
Zabe updated the task description. (Show Details)
Zabe renamed this task from Adjust the field type of globaluser timestamp columns to fixed binary and drop gu_hidden on wmf wikis to globaluser table schema changes (March 2022).Mar 16 2022, 7:59 PM
In T302658#7783552, @Majavah wrote:

Added a second change so you can combine alters to the same table - let me know if you prefer I split that to a separate task.

Added a third one. Same question applies.

Umherirrender added a comment.Mar 16 2022, 8:59 PM
In T302658#7783615, @Zabe wrote:
In T302658#7783552, @Majavah wrote:

Added a second change so you can combine alters to the same table - let me know if you prefer I split that to a separate task.

Added a third one. Same question applies.

I am fine with this and it has happen in the past as well. This seems also useful if some master switchover is needed to alter the table and only one statement needs to be run

taavi added a subtask: T301674: Recreate views for globaluser table.Mar 17 2022, 7:02 AM
Marostegui added a comment.Mar 21 2022, 7:22 AM

Is this good to go?

Zabe added a comment.Mar 22 2022, 3:16 PM
In T302658#7791586, @Marostegui wrote:

Is this good to go?

I think we need to wait for T301674 first.

Marostegui changed the task status from Stalled to Open.Mar 23 2022, 6:29 AM
Marostegui closed subtask T301674: Recreate views for globaluser table as Resolved.
Marostegui moved this task from Blocked to Ready on the DBA board.

In order to unblock this task I have done T301674 myself.

Marostegui claimed this task.Mar 23 2022, 6:31 AM
gerritbot added a comment.Mar 23 2022, 6:36 AM

Change 773134 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/software/schema-changes@master] drop_gu_hidden_T302658.py: New schema change

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

gerritbot added a project: Patch-For-Review.Mar 23 2022, 6:36 AM
taavi reopened subtask T301674: Recreate views for globaluser table as Open.Mar 23 2022, 7:56 AM
Marostegui closed subtask T301674: Recreate views for globaluser table as Resolved.Mar 23 2022, 9:22 AM
gerritbot added a comment.Mar 23 2022, 1:17 PM

Change 773134 merged by jenkins-bot:

[operations/software/schema-changes@master] drop_gu_hidden_T302658.py: New schema change

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

Marostegui mentioned this in rSCHCHfea5c8868c13: drop_gu_hidden_T302658.py: New schema change.Mar 23 2022, 1:17 PM
Maintenance_bot removed a project: Patch-For-Review.Mar 23 2022, 2:10 PM
Marostegui added a project: Schema-change-in-production.Mar 24 2022, 8:09 AM
Marostegui moved this task from Backlog to In progress on the Schema-change-in-production board.
Stashbot added a comment.Mar 24 2022, 8:11 AM

Mentioned in SAL (#wikimedia-operations) [2022-03-24T08:11:43Z] <marostegui> dbmaint s7@codfw T302658

Stashbot added a comment.Mar 24 2022, 8:44 AM

Mentioned in SAL (#wikimedia-operations) [2022-03-24T08:44:39Z] <marostegui> dbmaint s7@eqiad T302658

Marostegui added a comment.Mar 24 2022, 12:00 PM

In order not to leave two schema changes running during the weekend (we have a big one going on s4 at the moment which will take until Monday) I have deployed this change on codfw entirely and on non production hosts (dbstore and backup sources).
On Monday I will do the pending eqiad slaves (the master cannot be done online and requires a master swap).

Stashbot added a comment.Mar 24 2022, 2:31 PM

Mentioned in SAL (#wikimedia-operations) [2022-03-24T14:31:52Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1098:3317 (T302658)', diff saved to https://phabricator.wikimedia.org/P23030 and previous config saved to /var/cache/conftool/dbconfig/20220324-143149-marostegui.json

Marostegui added a comment.Mar 24 2022, 2:35 PM

Deploying on a few more hosts (multi-instance ones)

Stashbot added a comment.Mar 24 2022, 5:34 PM

Mentioned in SAL (#wikimedia-operations) [2022-03-24T17:34:56Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1101:3317 (T302658)', diff saved to https://phabricator.wikimedia.org/P23035 and previous config saved to /var/cache/conftool/dbconfig/20220324-173450-marostegui.json

Stashbot added a comment.Mar 24 2022, 7:27 PM

Mentioned in SAL (#wikimedia-operations) [2022-03-24T19:27:42Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1101:3317 (T302658)', diff saved to https://phabricator.wikimedia.org/P23041 and previous config saved to /var/cache/conftool/dbconfig/20220324-192741-marostegui.json

Stashbot added a comment.Mar 24 2022, 8:12 PM

Mentioned in SAL (#wikimedia-operations) [2022-03-24T20:12:57Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1101:3317 (T302658)', diff saved to https://phabricator.wikimedia.org/P23044 and previous config saved to /var/cache/conftool/dbconfig/20220324-201257-marostegui.json

Stashbot added a comment.Mar 24 2022, 8:13 PM

Mentioned in SAL (#wikimedia-operations) [2022-03-24T20:13:09Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1170:3317 (T302658)', diff saved to https://phabricator.wikimedia.org/P23045 and previous config saved to /var/cache/conftool/dbconfig/20220324-201305-marostegui.json

Stashbot added a comment.Mar 24 2022, 9:45 PM

Mentioned in SAL (#wikimedia-operations) [2022-03-24T21:45:16Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1170:3317 (T302658)', diff saved to https://phabricator.wikimedia.org/P23047 and previous config saved to /var/cache/conftool/dbconfig/20220324-214515-marostegui.json

Stashbot added a comment.Mar 24 2022, 10:30 PM

Mentioned in SAL (#wikimedia-operations) [2022-03-24T22:30:31Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1170:3317 (T302658)', diff saved to https://phabricator.wikimedia.org/P23050 and previous config saved to /var/cache/conftool/dbconfig/20220324-223031-marostegui.json

Stashbot added a comment.Mar 25 2022, 5:47 AM

Mentioned in SAL (#wikimedia-operations) [2022-03-25T05:47:06Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1127 (T302658)', diff saved to https://phabricator.wikimedia.org/P23054 and previous config saved to /var/cache/conftool/dbconfig/20220325-054705-marostegui.json

Stashbot added a comment.Mar 25 2022, 7:18 AM

Mentioned in SAL (#wikimedia-operations) [2022-03-25T07:18:40Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1127 (T302658)', diff saved to https://phabricator.wikimedia.org/P23058 and previous config saved to /var/cache/conftool/dbconfig/20220325-071840-marostegui.json

Stashbot added a comment.Mar 25 2022, 8:03 AM

Mentioned in SAL (#wikimedia-operations) [2022-03-25T08:03:55Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1127 (T302658)', diff saved to https://phabricator.wikimedia.org/P23064 and previous config saved to /var/cache/conftool/dbconfig/20220325-080355-marostegui.json

Stashbot added a comment.Mar 25 2022, 8:04 AM

Mentioned in SAL (#wikimedia-operations) [2022-03-25T08:04:06Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1174 (T302658)', diff saved to https://phabricator.wikimedia.org/P23065 and previous config saved to /var/cache/conftool/dbconfig/20220325-080403-marostegui.json

Stashbot added a comment.Mar 25 2022, 8:55 AM

Mentioned in SAL (#wikimedia-operations) [2022-03-25T08:55:09Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1174 (T302658)', diff saved to https://phabricator.wikimedia.org/P23067 and previous config saved to /var/cache/conftool/dbconfig/20220325-085508-marostegui.json

Stashbot added a comment.Mar 25 2022, 9:40 AM

Mentioned in SAL (#wikimedia-operations) [2022-03-25T09:40:24Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1174 (T302658)', diff saved to https://phabricator.wikimedia.org/P23072 and previous config saved to /var/cache/conftool/dbconfig/20220325-094023-marostegui.json

Stashbot added a comment.Mar 25 2022, 9:40 AM

Mentioned in SAL (#wikimedia-operations) [2022-03-25T09:40:34Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depooling db1181 (T302658)', diff saved to https://phabricator.wikimedia.org/P23073 and previous config saved to /var/cache/conftool/dbconfig/20220325-094031-marostegui.json

Stashbot added a comment.Mar 25 2022, 10:17 AM

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

Stashbot added a comment.Mar 25 2022, 11:02 AM

Mentioned in SAL (#wikimedia-operations) [2022-03-25T11:02:17Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Repooling after maintenance db1181 (T302658)', diff saved to https://phabricator.wikimedia.org/P23080 and previous config saved to /var/cache/conftool/dbconfig/20220325-110217-marostegui.json

Marostegui moved this task from In progress to Unclear/blocked to proceed on the Schema-change-in-production board.Mar 25 2022, 2:08 PM

This is all done - only pending the master that will be done after a switchover.

Marostegui moved this task from Ready to Blocked on the DBA board.Mar 25 2022, 2:08 PM
Stashbot added a comment.Apr 19 2022, 6:11 AM

Mentioned in SAL (#wikimedia-operations) [2022-04-19T06:11:54Z] <marostegui> dbmaint s7@eqiad T302658

Marostegui closed this task as Resolved.Apr 19 2022, 6:55 AM

Old s7 master (db1136) done

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