Page MenuHomePhabricator

Extend flow_wiki_ref.ref_src_wiki
Closed, ResolvedPublic

Description

What:

ALTER TABLE /*_*/flow_wiki_ref MODIFY ref_src_wiki varchar(64) binary not null;

Where: Everywhere that has the flow_wiki_ref table. private wikis have it as part of their database. everywhere else is extension1/flowdb

When: At DBA's leisure (patch is merged, no code dependancy)

Is backwards compatible: Yes

Has been tested already: On Miraheze per subtask. Live on beta

Schema change progress:

s3:

  • officewiki
  • techconductwiki

x1

  • flowdb

s10

  • labswiki

Event Timeline

Reedy created this task.Dec 23 2019, 9:03 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 23 2019, 9:03 PM

No rush, mostly here to keep the schema in sync with changes in the repo

RhinosF1 updated the task description. (Show Details)Dec 23 2019, 10:12 PM
Reedy updated the task description. (Show Details)Dec 23 2019, 10:39 PM
Reedy updated the task description. (Show Details)Dec 23 2019, 11:12 PM

This being not an emergency, and due to deployment freeze + team vacations, I will schedule to be done in the year 2020 CC @Marostegui

jcrespo triaged this task as Medium priority.Dec 24 2019, 9:30 AM
jcrespo moved this task from Triage to Next on the DBA board.

List of wikis that have this table:

s1 - none
s2 - none
s3:

  • officewiki
  • techconductwiki

s4; none
s5: none
s6: none
s7: none
s8: none
x1: flowdb
s10: labswiki

@Andrew I guess if labswiki has this table, labtestwiki probably has it too.

Marostegui updated the task description. (Show Details)Dec 31 2019, 7:24 AM

That table isn't present on labtestwiki -- I must not have ever installed flow there.

Andrew updated the task description. (Show Details)Dec 31 2019, 11:51 AM

Cool, thanks for confirming!

Marostegui moved this task from Next to In progress on the DBA board.
Marostegui moved this task from Backlog to In progress on the Blocked-on-schema-change board.
Marostegui added a comment.EditedJan 2 2020, 6:43 AM

x1 progress:

  • dbstore1005
  • db2131
  • db2115
  • db2101
  • db2096
  • db1140
  • db1137
  • db1127
  • db1120

Mentioned in SAL (#wikimedia-operations) [2020-01-02T06:46:48Z] <marostegui> Deploy schema change on db2131 - T241387

db2131 flowdb.flow_wiki_ref has been altered and this is how it looks like now:

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(64) NOT NULL,
  `ref_id` binary(11) NOT NULL,
  PRIMARY KEY (`ref_id`),
  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=binar

Mentioned in SAL (#wikimedia-operations) [2020-01-02T07:18:37Z] <marostegui> Deploy schema change on labswiki.flow_wiki_ref (empty table) T241387

Wikitech has been altered, it was empty anyways:

root@db1133.eqiad.wmnet[labswiki]> select count(*) from flow_wiki_ref;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

root@db1133.eqiad.wmnet[labswiki]> ALTER TABLE /*_*/flow_wiki_ref MODIFY ref_src_wiki varchar(64) binary not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
Marostegui updated the task description. (Show Details)Jan 2 2020, 7:19 AM

As I was expecting, as soon as we got a write to that table (it doesn't have many, so it took a while), replication broke on db2131 x1 (we use ROW):

Last_SQL_Error: Column 8 of table 'flowdb.flow_wiki_ref' cannot be converted from type 'varchar(16)' to type 'varbinary(64)'

I have reverted the change and replication is working again on that unused host.
So this needs to be done directly on the master with replication. Luckily the table doesn't have many writes and the alter is relatively fast (10 seconds). So I think I will perform this early in the morning and getting the master to serve reads.

Mentioned in SAL (#wikimedia-operations) [2020-01-02T07:49:19Z] <marostegui> Deploy schema change on techconductwiki.flow_wiki_ref (empty table) on s3 master (db1123) T241387

Marostegui updated the task description. (Show Details)Jan 2 2020, 7:49 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-02T08:10:21Z] <marostegui> Deploy schema change on officewiki.flow_wiki_ref on s3 master (db1123) T241387

Marostegui updated the task description. (Show Details)Jan 2 2020, 8:10 AM

Mentioned in SAL (#wikimedia-operations) [2020-01-13T05:51:42Z] <marostegui> Deploy schema change on x1 master on flowdb with replication - T241387

Restricted Application added a project: User-RhinosF1. · View Herald TranscriptJan 13 2020, 5:51 AM
Marostegui closed this task as Resolved.Jan 13 2020, 5:56 AM
Marostegui updated the task description. (Show Details)

All done

RhinosF1 moved this task from Incoming to Done on the User-RhinosF1 board.Jan 13 2020, 6:33 AM