Schema change for refactored comment storage
Closed, ResolvedPublic

Description

From the checklist at https://wikitech.wikimedia.org/wiki/Schema_changes:

  1. ALTERs to run: https://gerrit.wikimedia.org/r/#/c/357892/14/maintenance/archives/patch-comment-table.sql (note there are some CREATEs in there too)
  2. Where to run those changes: all.dblist
  3. When to run those changes: No time constraint.
  4. If the schema change is backwards compatible: Yes. The new columns and tables won't be used until a feature flag is enabled.
  5. If the schema change has been tested already on some of the test/beta wikis: It appears to have been auto-deployed to Beta. Things seem to be working in some quick testing, and if anyone has complained about things breaking I haven't heard it.
  6. if the data should be made available on the labs replicas and/or dumps: Treatment of replicas and dumps are the same.
  • All columns in the comment table can be made available without restriction. Rows must only be available if they are publicly referenced from one of the _id columns being added in this patch, as mentioned in T153333#3238701 and T153333#3238821. Extensions converted to have _id columns will eventually need to be added to that list. Note: From the MediaWiki side, this table should receive manly INSERTs, no UPDATEs, and DELETEs only due to potential maintenance.
  • All columns in revision_comment_temp can be made available without restriction. Rows must only be available if the corresponding revision table row exists (join on revcomment_rev = rev_id) and has (rev_deleted & 2) = 0. Note: From the MediaWiki side, this table should receive INSERTs and DELETEs but no UPDATEs.
  • All columns in image_comment_temp can be made available without restriction. Rows must only be available if the corresponding image table row exists (join on imgcomment_name = img_name). Note: From the MediaWiki side, this table should receive INSERTs and DELETEs but no UPDATEs.
  • All the _id columns added to existing tables should be available under the same conditions as the corresponding column without the _id suffix. These seem to be:
    • archive.ar_comment_id: Never available.
    • ipblocks.ipb_reason_id: Always available (whenever the row itself is).
    • oldimage.oi_description_id: Available when (oi_deleted & 2) = 0.
    • filearchive.fa_description_id: Available when (fa_deleted & 2) = 0.
    • filearchive.fa_deleted_reason_id: Always available.
    • recentchanges.rc_comment_id: Available when (rc_deleted & 2) = 0.
    • logging.log_comment_id: Available when (log_deleted & 2) = 0.
    • protectedtitles.pt_reason_id: Always available.
There are a very large number of changes, so older changes are hidden. Show Older Changes

Mentioned in SAL (#wikimedia-operations) [2018-01-11T06:25:11Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1096:3315 - T174569 (duration: 01m 03s)

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

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

Change 403588 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1082

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

Mentioned in SAL (#wikimedia-operations) [2018-01-11T06:32:32Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1082 - T174569 (duration: 01m 02s)

Mentioned in SAL (#wikimedia-operations) [2018-01-11T06:32:43Z] <marostegui> Deploy schema change on db1082.s5 with replication (this will generate lag on labs) - T174569

Mentioned in SAL (#wikimedia-operations) [2018-01-11T07:44:43Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1082 - T174569 (duration: 01m 03s)

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

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

Change 403604 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1110

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

Mentioned in SAL (#wikimedia-operations) [2018-01-11T07:50:36Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1110 - T174569 (duration: 01m 03s)

Mentioned in SAL (#wikimedia-operations) [2018-01-11T07:50:47Z] <marostegui> Deploy schema change on db1110 - T174569

Change 403606 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Repool db1110 with low weight

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

Change 403606 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Repool db1110 with low weight

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

Mentioned in SAL (#wikimedia-operations) [2018-01-11T09:31:03Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1110 with low weight - T174569 (duration: 01m 08s)

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

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

Change 403884 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1100

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

Mentioned in SAL (#wikimedia-operations) [2018-01-12T06:24:31Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1100 - T174569 (duration: 01m 22s)

Mentioned in SAL (#wikimedia-operations) [2018-01-12T06:24:45Z] <marostegui> Deploy schema change on db1100 - T174569

s5 is almost done, only pending the master, which I will do on Monday

Marostegui updated the task description. (Show Details)Jan 12 2018, 7:56 AM

Mentioned in SAL (#wikimedia-operations) [2018-01-15T06:13:08Z] <marostegui> Deploy schema change on db1070 (s5 master) - T174569

Mentioned in SAL (#wikimedia-operations) [2018-01-15T07:11:52Z] <marostegui> Deploy schema change on silver (labswiki) and labtestweb2001 (labtestwiki) - T174569

Marostegui updated the task description. (Show Details)Jan 15 2018, 7:18 AM

s5 master is done

Marostegui updated the task description. (Show Details)Jan 15 2018, 8:51 AM

Mentioned in SAL (#wikimedia-operations) [2018-01-15T09:45:15Z] <marostegui> Deploy schema change on s8 codfw master (db2045) with replication (this will generate lag on s8 codfw) - T174569

Marostegui updated the task description. (Show Details)Jan 16 2018, 6:12 AM

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

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

Change 404405 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1092

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

Mentioned in SAL (#wikimedia-operations) [2018-01-16T06:23:48Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1092 - T174569 (duration: 01m 32s)

Mentioned in SAL (#wikimedia-operations) [2018-01-16T06:32:02Z] <marostegui> Deploy schema change on db1092 - T174569

Change 404407 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/software@master] s8.hosts: Add a dbstore and labs servers

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

Change 404407 merged by jenkins-bot:
[operations/software@master] s8.hosts: Add a dbstore and labs servers

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

Marostegui added a comment.EditedJan 16 2018, 7:01 AM

s8 codfw has been done.
Let's track here s8 eqiad progress:

  • dbstore1001
  • dbstore1002
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • db1095 (sanitarium)
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087 (sanitarium master)
  • db1063 (won't be done, this host will be moved to s6)
  • db1071 (master)

Mentioned in SAL (#wikimedia-operations) [2018-01-16T07:03:32Z] <marostegui> Deploy schema change on dbstore1002 (s8) - T174569

Mentioned in SAL (#wikimedia-operations) [2018-01-16T07:34:30Z] <marostegui> Deploy schema change on dbstore1001 (s8) - T174569

Marostegui updated the task description. (Show Details)Jan 16 2018, 8:27 AM

Change 404466 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1101:3317,3318

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

Change 404466 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1101:3317,3318

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

Mentioned in SAL (#wikimedia-operations) [2018-01-16T15:30:39Z] <marostegui> Deploy schema change on db1101:3318 - T174569

Change 404487 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Slowly repool db1092

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

Change 404487 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Slowly repool db1092

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

Mentioned in SAL (#wikimedia-operations) [2018-01-16T16:29:31Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Slowly repool db1092 - T174569 (duration: 01m 08s)

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

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

Change 404629 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1104

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

Mentioned in SAL (#wikimedia-operations) [2018-01-17T06:20:42Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1104 - T174569 (duration: 01m 14s)

Mentioned in SAL (#wikimedia-operations) [2018-01-17T06:28:02Z] <marostegui> Deploy schema change on db1104 - T174569

Change 404635 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Slowly repool db1101

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

Change 404635 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Slowly repool db1101

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

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

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

Change 404661 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1099:3318

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

Mentioned in SAL (#wikimedia-operations) [2018-01-17T13:22:13Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1099:3318 - T174569 (duration: 01m 12s)

Mentioned in SAL (#wikimedia-operations) [2018-01-17T13:22:25Z] <marostegui> Deploy schema change on db1099:3318 - https://phabricator.wikimedia.org/T174569

Mentioned in SAL (#wikimedia-operations) [2018-01-18T06:18:42Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1099:3318 - T174569 (duration: 01m 13s)

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

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

Change 404913 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1087

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

Mentioned in SAL (#wikimedia-operations) [2018-01-18T06:27:02Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1087 - T174569 (duration: 01m 12s)

Mentioned in SAL (#wikimedia-operations) [2018-01-18T06:27:27Z] <marostegui> Deploy schema change on s8 db1087 (sanitarium master) with replication (this will generate lag on labs servers) - T174569

Mentioned in SAL (#wikimedia-operations) [2018-01-18T14:30:22Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1087 - T174569 (duration: 01m 12s)

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

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

Change 405245 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool db1109

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

Mentioned in SAL (#wikimedia-operations) [2018-01-19T06:20:36Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Depool db1109 - T174569 (duration: 00m 57s)

Mentioned in SAL (#wikimedia-operations) [2018-01-19T06:22:39Z] <marostegui> Deploy schema change on db1109 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-01-19T16:01:16Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Repool db1109 - T174569 (duration: 00m 56s)

s8 is only pending the master. And once it gets done, we will need to sanitize the tables on sanitarium+labs and this task will be finished.

I am not sure I will do it on Monday, on powerful servers it takes around 8h to complete. The master isn't a powerful server so I expect it to take even more, and given that there will be already people traveling for the All-hands, not sure it is the best time to leave a long running alter on a master.
So I might run it after the all hands.

Mentioned in SAL (#wikimedia-operations) [2018-02-05T07:45:06Z] <marostegui> Deploy schema change on s8 primary master (db1071) - T174569

Marostegui updated the task description. (Show Details)Feb 6 2018, 6:34 AM

The last host, s8 master finished the alter table.
I am going to start sanitizing sanitarium and labsdb hosts.

Change 394254 merged by Marostegui:
[operations/puppet@production] filtered_tables: Add new columns

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

Mentioned in SAL (#wikimedia-operations) [2018-02-06T09:38:19Z] <marostegui> Sanitizing s2 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-02-06T11:54:05Z] <marostegui> Sanitize s4 - T174569

Anomie added a comment.Feb 6 2018, 2:22 PM

The last host, s8 master finished the alter table.

\o/

The last host, s8 master finished the alter table.

\o/

I am still placing all the triggers on sanitariums, I will probably ask you soon to see if it is possible to generate some writes on a given wiki to make sure the filters are correctly in place for the ar_comment_id column which is the only one filtered.
Is that something doable from your side?
Keep in mind that the views are still not available (T181650) - I will ping Cloud team once the triggers are in place (hopefully between today and tomorrow)

Mentioned in SAL (#wikimedia-operations) [2018-02-06T14:28:57Z] <marostegui> Changing triggers on s2 - T174569

Anomie added a comment.Feb 6 2018, 2:57 PM

I am still placing all the triggers on sanitariums, I will probably ask you soon to see if it is possible to generate some writes on a given wiki to make sure the filters are correctly in place for the ar_comment_id column which is the only one filtered.
Is that something doable from your side?

Assuming the given wiki is testwiki or test2wiki, sure. Ping me on IRC with what you need.

I am still placing all the triggers on sanitariums, I will probably ask you soon to see if it is possible to generate some writes on a given wiki to make sure the filters are correctly in place for the ar_comment_id column which is the only one filtered.
Is that something doable from your side?

Assuming the given wiki is testwiki or test2wiki, sure. Ping me on IRC with what you need.

Will do, thanks!

Mentioned in SAL (#wikimedia-operations) [2018-02-07T07:05:30Z] <marostegui> Change triggers for s6 on db1102 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-02-07T07:17:33Z] <marostegui> Change triggers for s7 on db1102 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-02-07T07:53:36Z] <marostegui> Change triggers for s8 on db1095 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-02-07T08:11:10Z] <marostegui> Change triggers for s5 on db1095 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-02-07T08:21:28Z] <marostegui> Change triggers for s1 on db1095 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-02-07T08:43:04Z] <marostegui> Change triggers for s3 on db1095 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-02-07T09:05:33Z] <marostegui> Failover labsdb1011 to labsdb1010 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-02-07T09:16:45Z] <marostegui> Failover back labsdb1010 to labsdb1011 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-02-07T09:18:38Z] <marostegui> Failover labsdb1009 to labsdb1010 - T174569

Mentioned in SAL (#wikimedia-operations) [2018-02-07T09:38:07Z] <marostegui> Failover back labsdb1010 to labsdb1009 - T174569

@Anomie can you generate some more writes on testwiki? I have sanitized existing records and now ar_comment_id looks good. I would like to see if the new ones arriving get sanitized properly with the triggers.
Thanks!

Anomie added a comment.Feb 7 2018, 2:15 PM

I just deleted two revisions on testwiki, ar_id 90260 and 90261. Let me know if you need more.

I just deleted two revisions on testwiki, ar_id 90260 and 90261. Let me know if you need more.

And those were sanitized properly on labs hosts :-)

mysql:root@localhost [testwiki]> select ar_comment_id from archive where ar_id=90260;
+---------------+
| ar_comment_id |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql:root@localhost [testwiki]> select ar_comment_id from archive where ar_id=90261;
+---------------+
| ar_comment_id |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

Also the test done on test2wiki was also fine:

mysql:root@localhost [test2wiki]> select ar_comment_id from archive where ar_id=231162;
+---------------+
| ar_comment_id |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

So triggers are working properly.
As a last step I am going to check all the hosts again to make sure no host/wiki was forgotten. There were so many hosts and so many wikis that something might have slipped thru the cracks.

Marostegui updated the task description. (Show Details)Feb 7 2018, 4:15 PM

The following shards, on all the servers, are looking good: s1, s2, s4,s5,s6,s7,s8.
Pending to check s3 which is a big one, so will do it tomorrow.

Marostegui closed this task as Resolved.Feb 8 2018, 10:06 AM

I have finished checking s3 and it also looks good.

Thank you so much for the huge amount of work.

ArielGlenn moved this task from Up Next to Done on the Dumps-Generation board.Mar 2 2018, 10:40 AM