Page MenuHomePhabricator

Create oauth_registered_consumer.oarc_owner_only column on metawiki
Closed, ResolvedPublic

Description

https://gerrit.wikimedia.org/r/#/c/255488 requires a schema change:

ALTER TABLE /*_*/oauth_registered_consumer ADD COLUMN `oarc_owner_only` tinyint NOT NULL DEFAULT 0 AFTER `oarc_developer_agreement`;

This should go out before Tuesday as I forgot a schema change is involved and merged the patch. Sorry about that.

Should be trivial, metawiki only, about 350 rows.

Event Timeline

Tgr created this task.Dec 13 2015, 4:27 AM
Tgr raised the priority of this task from to Normal.
Tgr updated the task description. (Show Details)
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 13 2015, 4:27 AM
Tgr added subscribers: Tgr, jcrespo.Dec 14 2015, 11:06 AM

This should go out before Tuesday as I forgot a schema change is involved and merged the patch. Sorry about that.

That is unacceptable, I ask for 2 weeks on advance notice on the ticket description: https://phabricator.wikimedia.org/project/profile/1494/ You cannot expect to have my time right away when other people took the time to schedule my time some weeks in advance, and it would be unfair to them to prioritize this over them.

This could be done at the earliest on Wednesday, and I cannot guarantee it.

jcrespo set Security to None.
Tgr added a comment.Dec 14 2015, 7:22 PM

You cannot expect to have my time right away when other people took the time to schedule my time some weeks in advance, and it would be unfair to them to prioritize this over them.

Honestly I did not expect to have your time at all, apart from the 30 seconds it takes to look at this task, see that it affects a table with a couple hundred rows, and say "feel free to do it". I'm not sure a one size fits all workflow for schema changes makes sense when we have tables with millions of rows that are written all the time, and tables with hundreds of rows that are written once a week.

This could be done at the earliest on Wednesday, and I cannot guarantee it.

No rush; the next time code can be deployed after Tuesday is the third week of January, due to the fundraiser code freeze.

Anomie added a subscriber: Anomie.Dec 15 2015, 6:43 PM

This is the final structure, please confirm everything is ok:

mysql -A -BN -h s7-master metawiki -e "SHOW CREATE TABLE oauth_registered_consumer\G"
*************************** 1. row ***************************
oauth_registered_consumer
CREATE TABLE `oauth_registered_consumer` (
  `oarc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `oarc_consumer_key` varbinary(32) NOT NULL,
  `oarc_name` varbinary(128) NOT NULL,
  `oarc_user_id` int(10) unsigned NOT NULL,
  `oarc_version` varbinary(32) NOT NULL,
  `oarc_callback_url` blob NOT NULL,
  `oarc_callback_is_prefix` tinyblob,
  `oarc_description` blob NOT NULL,
  `oarc_email` varbinary(255) NOT NULL,
  `oarc_email_authenticated` varbinary(14) DEFAULT NULL,
  `oarc_developer_agreement` tinyint(4) NOT NULL DEFAULT '0',
  `oarc_owner_only` tinyint(4) NOT NULL DEFAULT '0',
  `oarc_wiki` varbinary(32) NOT NULL,
  `oarc_grants` blob NOT NULL,
  `oarc_registration` varbinary(14) NOT NULL,
  `oarc_secret_key` varbinary(32) DEFAULT NULL,
  `oarc_rsa_key` blob,
  `oarc_restrictions` blob NOT NULL,
  `oarc_stage` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `oarc_stage_timestamp` varbinary(14) NOT NULL,
  `oarc_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`oarc_id`),
  UNIQUE KEY `oarc_consumer_key` (`oarc_consumer_key`),
  UNIQUE KEY `oarc_name_version_user` (`oarc_name`,`oarc_user_id`,`oarc_version`),
  KEY `oarc_user_id` (`oarc_user_id`),
  KEY `oarc_stage_timestamp` (`oarc_stage`,`oarc_stage_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=357 DEFAULT CHARSET=binary

see that it affects a table with a couple hundred rows, and say "feel free to do it"

That is a mistake. Problems do not depend on table size (only), but on traffic. There are other issues, too. See: https://wikitech.wikimedia.org/wiki/Schema_changes

jcrespo moved this task from Triage to Done on the DBA board.Dec 17 2015, 11:10 AM
jcrespo moved this task from Backlog to Done on the Blocked-on-schema-change board.
jcrespo closed this task as Resolved.Dec 17 2015, 11:48 AM
jcrespo claimed this task.
Tgr added a comment.Dec 17 2015, 11:24 PM

This is the final structure, please confirm everything is ok:

Looks good, thanks. For the record, the updated table schema (as defined in code) is OAuth.sql.

That is a mistake. Problems do not depend on table size (only), but on traffic.

True, although small tables usually don't get much traffic either. In this specific case, there are a few writes a day, and the number of reads is largely equal to the number of OAuth-authenticated API requests (which I did not look up but is probably pretty small).

In any case, I started a separate conversation about this in T121491: Define minimum criteria for using the "official" schema change process.

I will give you T121857 instead.

mark added a subscriber: mark.Dec 28 2015, 12:45 PM