Add ref_id to flow_wiki_ref and flow_ext_ref (DBA phase 1)
Closed, ResolvedPublic

Description

Review https://gerrit.wikimedia.org/r/#/c/238393/ . If the code and deployment plan is acceptable, then run db_patches/patch-ref_id-phase1.sql from latest patch set of that change.

This has to be done on flowdb on extension1, and officewiki on the standard cluster.

It should be done before https://gerrit.wikimedia.org/r/#/c/238393/ is merged. You can just mark this task resolved and comment on that Gerrit, and we'll take care of merging and deploying.

This is to ensure that all Flow tables have a PK; see T109676. I have tested that Flow works continually if the deployment plan given on that Gerrit is followed.

Restricted Application added a project: Collaboration-Team-Triage. · View Herald TranscriptNov 4 2015, 7:27 PM

I will probably do this tomorrow (Thursday), unless something more urgent happens.

jcrespo moved this task from Triage to Backlog on the DBA board.Nov 4 2015, 7:44 PM
jcrespo moved this task from Backlog to In progress on the DBA board.Nov 5 2015, 5:16 PM
jcrespo closed this task as Resolved.Nov 5 2015, 6:10 PM

Deployed to both x1 and office wiki. Final results:

MariaDB [flowdb]> SHOW CREATE TABLE flow_ext_ref\G
*************************** 1. row ***************************
       Table: flow_ext_ref
Create Table: CREATE TABLE `flow_ext_ref` (
  `ref_src_object_id` binary(11) NOT NULL,
  `ref_src_object_type` varbinary(32) NOT NULL,
  `ref_src_workflow_id` binary(11) NOT NULL,
  `ref_src_namespace` int(11) NOT NULL,
  `ref_src_title` varbinary(255) NOT NULL,
  `ref_target` blob,
  `ref_type` varbinary(16) NOT NULL,
  `ref_src_wiki` varbinary(16) NOT NULL,
  `ref_id` binary(11) DEFAULT NULL,
  KEY `flow_ext_ref_idx_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_type`,`ref_target`(255),`ref_src_object_type`,`ref_src_object_id`),
  KEY `flow_ext_ref_revision_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_src_object_type`,`ref_src_object_id`,`ref_type`,`ref_target`(255))
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

MariaDB [flowdb]> SHOW CREATE TABLE flow_wiki_ref\G
*************************** 1. row ***************************
       Table: flow_wiki_ref
Create Table: CREATE TABLE `flow_wiki_ref` (
  `ref_src_object_id` binary(11) NOT NULL,
  `ref_src_object_type` varbinary(32) NOT NULL,
  `ref_src_workflow_id` binary(11) NOT NULL,
  `ref_src_namespace` int(11) NOT NULL,
  `ref_src_title` varbinary(255) NOT NULL,
  `ref_target_namespace` int(11) NOT NULL,
  `ref_target_title` varbinary(255) NOT NULL,
  `ref_type` varbinary(16) NOT NULL,
  `ref_src_wiki` varbinary(16) NOT NULL,
  `ref_id` binary(11) DEFAULT NULL,
  KEY `flow_wiki_ref_idx_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_type`,`ref_target_namespace`,`ref_target_title`,`ref_src_object_type`,`ref_src_object_id`),
  KEY `flow_wiki_ref_revision_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_src_object_type`,`ref_src_object_id`,`ref_type`,`ref_target_namespace`,`ref_target_title`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)
MariaDB [officewiki]> SHOW CREATE TABLE flow_ext_ref\G
*************************** 1. row ***************************
       Table: flow_ext_ref
Create Table: CREATE TABLE `flow_ext_ref` (
  `ref_src_object_id` binary(11) NOT NULL,
  `ref_src_object_type` varbinary(32) NOT NULL,
  `ref_src_workflow_id` binary(11) NOT NULL,
  `ref_src_namespace` int(11) NOT NULL,
  `ref_src_title` varbinary(255) NOT NULL,
  `ref_target` blob,
  `ref_type` varbinary(16) NOT NULL,
  `ref_src_wiki` varbinary(16) NOT NULL,
  `ref_id` binary(11) DEFAULT NULL,
  KEY `flow_ext_ref_idx_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_type`,`ref_target`(255),`ref_src_object_type`,`ref_src_object_id`),
  KEY `flow_ext_ref_revision_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_src_object_type`,`ref_src_object_id`,`ref_type`,`ref_target`(255))
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

MariaDB [officewiki]> SHOW CREATE TABLE flow_wiki_ref\G
*************************** 1. row ***************************
       Table: flow_wiki_ref
Create Table: CREATE TABLE `flow_wiki_ref` (
  `ref_src_object_id` binary(11) NOT NULL,
  `ref_src_object_type` varbinary(32) NOT NULL,
  `ref_src_workflow_id` binary(11) NOT NULL,
  `ref_src_namespace` int(11) NOT NULL,
  `ref_src_title` varbinary(255) NOT NULL,
  `ref_target_namespace` int(11) NOT NULL,
  `ref_target_title` varbinary(255) NOT NULL,
  `ref_type` varbinary(16) NOT NULL,
  `ref_src_wiki` varbinary(16) NOT NULL,
  `ref_id` binary(11) DEFAULT NULL,
  KEY `flow_wiki_ref_idx_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_type`,`ref_target_namespace`,`ref_target_title`,`ref_src_object_type`,`ref_src_object_id`),
  KEY `flow_wiki_ref_revision_v2` (`ref_src_wiki`,`ref_src_namespace`,`ref_src_title`,`ref_src_object_type`,`ref_src_object_id`,`ref_type`,`ref_target_namespace`,`ref_target_title`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.01 sec)