Page MenuHomePhabricator

Remove event_page_namespace and event_page_title
Closed, ResolvedPublic

Description

These fields were migrated to event_page_id in production in 2013, but were then never dropped.

SQL:

Where: all x1 databases that have an echo_event table (same set as echo.dblist)
Backwards compatible: yes, now that the patch is merged the software doesn't reference these fields anymore

Event Timeline

Turns out 2.5M out of those 2.8M rows have both a title and an ID, so we don't need to migrate them. The number of rows that need migration (i.e. have a title but no ID, and are not orphans) is 237k:

mysql:research@x1-analytics-slave [enwiki]> select count(*) from echo_event where event_page_title is not null and event_page_id is null and not exists (select * from echo_notification where notification_event=event_id);
+----------+
| count(*) |
+----------+
|   237731 |
+----------+
1 row in set (55.40 sec)

Change 292983 had a related patch set uploaded (by Catrope):
Add maintenance script to backfill event_page_id

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

Change 292983 abandoned by Catrope:
Add maintenance script to backfill event_page_id

Reason:
Turns out we already have this: maintenance/updateEchoSchemaForSuppression.php

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

Turns out we already have a migration script that was written as part of T50059: Echo notifications may display oversighted content back in 2013 (!). https://gerrit.wikimedia.org/r/#/c/63572/ even added SQL patch files to remove the columns, but they were never added to Hooks.php and the migration script was never run. I'm now fixing up the migration script so it even runs at all, and rigging things up in Hooks.php.

Change 293029 had a related patch set uploaded (by Catrope):
Clean up and fix updateEchoSchemaForSuppression.php

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

Change 293036 had a related patch set uploaded (by Catrope):
BatchRowWriter: Allow running in transactionless mode

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

Change 293039 had a related patch set uploaded (by Catrope):
Migrate and remove event_page_namespace and event_page_title

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

Turns out we already have a migration script that was written as part of T50059: Echo notifications may display oversighted content back in 2013 (!). https://gerrit.wikimedia.org/r/#/c/63572/ even added SQL patch files to remove the columns, but they were never added to Hooks.php and the migration script was never run. I'm now fixing up the migration script so it even runs at all, and rigging things up in Hooks.php.

Never mind, it was run, it's just not obvious. We still need to put that script in update.php for 3rd parties though.

Change 293029 merged by jenkins-bot:
Clean up and fix updateEchoSchemaForSuppression.php

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

Change 293036 abandoned by Aaron Schulz:
BatchRowWriter: Allow running in transactionless mode

Reason:
This was fixed in the updater instead.

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

Catrope renamed this task from Migrate event_page_namespace and event_page_title to event_page_id and remove them to Remove event_page_namespace and event_page_title.Aug 26 2016, 12:10 AM
Catrope updated the task description. (Show Details)
Catrope updated the task description. (Show Details)

Untagging for now because the patch is not yet merged.

Catrope updated the task description. (Show Details)

Change 293039 merged by jenkins-bot:
[mediawiki/extensions/Echo@master] Migrate and remove event_page_namespace and event_page_title

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

Catrope updated the task description. (Show Details)
Catrope updated the task description. (Show Details)
Marostegui added a subscriber: Marostegui.

x1 runs row based replication, which means this may break replication if executed first on the slaves and then on the master.
We can probably put the master to serve reads (once we have the new master in place T211613: rack/setup/install db11[26-38].eqiad.wmnet) and execute it with replication.

Marostegui triaged this task as Medium priority.Mar 15 2019, 6:19 AM

This can probably proceed just fine with the same procedure as: T217453 T143763
Given that the biggest table is just 9.5G it should be even faster that the other two.

So I have altered the first 10 wikis:

aawiki
aawikibooks
aawiktionary
abwiki
abwiktionary
acewiki
advisorswiki
advisorywiki
adywiki
afwiki

And it looks like:

*************************** 1. row ***************************
       Table: echo_event
Create Table: CREATE TABLE `echo_event` (
  `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_type` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `event_variant` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `event_agent_id` int(10) unsigned DEFAULT NULL,
  `event_agent_ip` varchar(39) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `event_extra` blob,
  `event_page_id` int(10) unsigned DEFAULT NULL,
  `event_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`event_id`),
  KEY `echo_event_type` (`event_type`),
  KEY `echo_event_page_id` (`event_page_id`)
) ENGINE=InnoDB AUTO_INCREMENT=886307 DEFAULT CHARSET=latin1

Mentioned in SAL (#wikimedia-operations) [2019-04-10T13:19:27Z] <marostegui> Deploy schema change on aawiki aawikibooks aawiktionary abwiki abwiktionary acewiki advisorswiki advisorywiki adywiki afwiki on x1 - T136427

Change 505692 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool all x1 slaves

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

Change 505692 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool all x1 slaves

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

Mentioned in SAL (#wikimedia-operations) [2019-04-23T05:16:32Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Depool all slaves in x1 T136427 (duration: 00m 54s)

Mentioned in SAL (#wikimedia-operations) [2019-04-23T05:16:55Z] <marostegui> Deploy schema change on x1 master - lag will appear on x1 slaves - T136427

Mentioned in SAL (#wikimedia-operations) [2019-04-23T06:20:06Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Repool all slaves in x1 T136427 (duration: 00m 57s)