Page MenuHomePhabricator

CentralNotice: Update DB schema on Meta for new features
Closed, ResolvedPublic

Description

These are changes for the CentralNotice extension. They should be applied only on Meta in the following order:

  1. Alter tables to apply:
  1. Wikis to apply to:
    • metawiki
    • testwiki
  1. No specific time window required.
  2. Change is backwards compatible with current production code.
  3. Change has been tested on the beta cluster.
  4. Data is not sensitive or private, and can be made available on lab replicas and dumps.

Thanks so much!!!

Schema change progress:

  • metawiki
  • testwiki

Event Timeline

Marostegui triaged this task as Medium priority.Jun 4 2020, 5:27 AM
Marostegui moved this task from Triage to Backlog on the DBA board.
Marostegui subscribed.

Thanks for the ticket @AndyRussG - would you mind creating it with the format described at https://wikitech.wikimedia.org/wiki/Schema_changes#Workflow_of_a_schema_change ?
We receive lots of them, and by using a common template we can act faster on them and with less probability of making a mistake in production.

Lastly, keep in mind that the table creation isn't done by DBAs, but done via SWAT/train. Can the schema change go before the table creation or should that wait?

Thank you!

Marostegui changed the task status from Open to Stalled.Jun 9 2020, 10:41 AM
Marostegui moved this task from Backlog to Blocked external/Not db team on the DBA board.

Stalling per the above comment

Marostegui changed the task status from Stalled to Open.Jun 10 2020, 4:55 AM

Thank you!

@AndyRussG the first task description does not match the second edit, so I am unsure about which alters to apply. The first one pointed to:
1- https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CentralNotice/+/552121
2- https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CentralNotice/+/573520
3- https://gerrit.wikimedia.org/r/c/mediawiki/extensions/CentralNotice/+/597827

Which means:
1-
ALTER TABLE /*_*/cn_notice_log ADD notlog_begin_regions text;
ALTER TABLE /*_*/cn_notice_log ADD notlog_end_regions text;

2-
ALTER TABLE /*$wgDBprefix*/cn_templates ADD tmp_is_template bool NOT NULL DEFAULT 0;

3-
ALTER TABLE /*$wgDBprefix*/cn_templates MODIFY tmp_is_template tinyint(1) NOT NULL DEFAULT 0;

After the task edition, it points to:

1- https://gerrit.wikimedia.org/g/mediawiki/extensions/CentralNotice/+/1aff3d33f47affc85d49f31cacb45d3e88da41e2
2- https://gerrit.wikimedia.org/g/mediawiki/extensions/CentralNotice/+/6e6e471f793ca8dccce5af8c13b0e4e477d864f2

Which means:
1-
ALTER TABLE /*$wgDBprefix*/cn_templates ADD tmp_is_template bool NOT NULL DEFAULT 0;

2-
ALTER TABLE /*$wgDBprefix*/cn_templates MODIFY tmp_is_template tinyint(1) NOT NULL DEFAULT 0;

So the schema changes to cn_notice_log aren't there on the second edit, is that intended?

So the schema changes to cn_notice_log aren't there on the second edit, is that intended?

Ooops! No, I'll update the task description. The intention was to include all the ALTER TABLEs and omit only the table creation.

Thanks so much!!

So the schema changes to cn_notice_log aren't there on the second edit, is that intended?

Ooops! No, I'll update the task description. The intention was to include all the ALTER TABLEs and omit only the table creation.

Thanks so much!!

Thank you - it is clear now! :)

Mentioned in SAL (#wikimedia-operations) [2020-06-11T04:50:52Z] <marostegui> Deploy schema change on testwiki - T254371

testwiki is done, this is how the tables looks like:

root@db1123.eqiad.wmnet[testwiki]> show create table cn_templates\G show create table cn_notice_log\G
*************************** 1. row ***************************
       Table: cn_templates
Create Table: CREATE TABLE `cn_templates` (
  `tmp_id` int(11) NOT NULL AUTO_INCREMENT,
  `tmp_name` varbinary(255) DEFAULT NULL,
  `tmp_display_anon` tinyint(1) NOT NULL DEFAULT '1',
  `tmp_display_account` tinyint(1) NOT NULL DEFAULT '1',
  `tmp_fundraising` tinyint(1) NOT NULL DEFAULT '0',
  `tmp_autolink` tinyint(1) NOT NULL DEFAULT '0',
  `tmp_landing_pages` varbinary(255) DEFAULT NULL,
  `tmp_archived` tinyint(1) NOT NULL DEFAULT '0',
  `tmp_category` tinyint(4) NOT NULL DEFAULT '0',
  `tmp_preview_sandbox` tinyint(1) NOT NULL DEFAULT '0',
  `tmp_is_template` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tmp_id`),
  KEY `tmp_name` (`tmp_name`)
) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: cn_notice_log
Create Table: CREATE TABLE `cn_notice_log` (
  `notlog_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `notlog_timestamp` binary(14) NOT NULL,
  `notlog_user_id` int(10) unsigned NOT NULL,
  `notlog_action` enum('created','modified','removed') NOT NULL DEFAULT 'modified',
  `notlog_not_id` int(10) unsigned NOT NULL,
  `notlog_not_name` varbinary(255) DEFAULT NULL,
  `notlog_begin_projects` varbinary(255) DEFAULT NULL,
  `notlog_end_projects` varbinary(255) DEFAULT NULL,
  `notlog_begin_languages` blob,
  `notlog_end_languages` blob,
  `notlog_begin_countries` blob,
  `notlog_end_countries` blob,
  `notlog_begin_start` binary(14) DEFAULT NULL,
  `notlog_end_start` binary(14) DEFAULT NULL,
  `notlog_begin_end` binary(14) DEFAULT NULL,
  `notlog_end_end` binary(14) DEFAULT NULL,
  `notlog_begin_enabled` tinyint(1) DEFAULT NULL,
  `notlog_end_enabled` tinyint(1) DEFAULT NULL,
  `notlog_begin_preferred` tinyint(1) DEFAULT NULL,
  `notlog_end_preferred` tinyint(1) DEFAULT NULL,
  `notlog_begin_locked` tinyint(1) DEFAULT NULL,
  `notlog_end_locked` tinyint(1) DEFAULT NULL,
  `notlog_begin_geo` tinyint(1) DEFAULT NULL,
  `notlog_end_geo` tinyint(1) DEFAULT NULL,
  `notlog_begin_banners` blob,
  `notlog_end_banners` blob,
  `notlog_begin_buckets` tinyint(1) DEFAULT NULL,
  `notlog_end_buckets` tinyint(1) DEFAULT NULL,
  `notlog_begin_mobile_carrier` int(11) DEFAULT NULL,
  `notlog_end_mobile_carrier` int(11) DEFAULT NULL,
  `notlog_begin_weight` int(11) DEFAULT NULL,
  `notlog_end_weight` int(11) DEFAULT NULL,
  `notlog_begin_archived` tinyint(4) DEFAULT NULL,
  `notlog_end_archived` tinyint(4) DEFAULT NULL,
  `notlog_comment` varbinary(255) DEFAULT NULL,
  `notlog_begin_throttle` int(11) DEFAULT NULL,
  `notlog_end_throttle` int(11) DEFAULT NULL,
  `notlog_begin_mixins` blob,
  `notlog_end_mixins` blob,
  `notlog_begin_regions` blob,
  `notlog_end_regions` blob,
  PRIMARY KEY (`notlog_id`),
  KEY `notlog_timestamp` (`notlog_timestamp`),
  KEY `notlog_user_id` (`notlog_user_id`,`notlog_timestamp`),
  KEY `notlog_not_id` (`notlog_not_id`,`notlog_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=282 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

s7 (metawiki) progress

  • codfw
  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1136
  • db1127
  • db1125
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079
Marostegui updated the task description. (Show Details)

Reminder, this columns will be available on labs hosts, but they won't be able to be queried. If you want them to be there, please send a ticket to cloud-services-team with the Data-Services tag to request a view on those new columns.