Page MenuHomePhabricator

Deploy translation_cx_version schema change to production
Closed, ResolvedPublic

Description

The ALTER TABLEs to run

Where to run those changes

  • on the wikishared database

When to run those changes

  • anytime

If the schema change is backwards compatible

  • adds a new column which has a default value, so yes

If the schema change has been tested already on some of the test/beta wikis

  • it has been tested on beta (aka deployment prep) and in cx-testing labs instances with no issues noted

If it involves new columns or tables, if the data should be made available on the labs replicas and/or dumps or not because they contain private or sensitive data:

  • new column does not contain sensitive or private information

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 20 2018, 10:40 AM
jcrespo claimed this task.Mar 20 2018, 10:43 AM
jcrespo moved this task from Triage to Next on the DBA board.
jcrespo added a subscriber: jcrespo.

As a note, we do not replicate x1 data to wikireplicas, as we store mostly on x1 non-public data, but it is interesting to ask it anyway so appropiate filters are added.

I can take this.

jcrespo triaged this task as Medium priority.Mar 20 2018, 12:31 PM
jcrespo moved this task from Next to In progress on the DBA board.

Mentioned in SAL (#wikimedia-operations) [2018-03-20T12:53:56Z] <jynus> applying schema change to wikishared.cx_translations T190133

Please check and resolve when happy:

$ grep -v dbstore1001 x1.hosts | while read host port; do echo "$host:$port"; mysql -h $host -P $port wikishared -e "SHOW CREATE TABLE cx_translations\G"; done
dbstore2001.codfw.wmnet:3320
*************************** 1. row ***************************
       Table: cx_translations
Create Table: CREATE TABLE `cx_translations` (
  `translation_id` int(11) NOT NULL AUTO_INCREMENT,
  `translation_source_title` varbinary(512) NOT NULL,
  `translation_target_title` varbinary(512) NOT NULL,
  `translation_source_language` varbinary(36) NOT NULL,
  `translation_target_language` varbinary(36) NOT NULL,
  `translation_source_url` blob NOT NULL,
  `translation_target_url` blob,
  `translation_status` enum('draft','published','deleted') DEFAULT NULL,
  `translation_start_timestamp` varbinary(14) NOT NULL,
  `translation_last_updated_timestamp` varbinary(14) NOT NULL,
  `translation_progress` tinyblob NOT NULL,
  `translation_started_by` int(11) DEFAULT NULL,
  `translation_last_update_by` int(11) DEFAULT NULL,
  `translation_source_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_target_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_cx_version` tinyint(3) unsigned DEFAULT '1',
  PRIMARY KEY (`translation_id`),
  UNIQUE KEY `cx_translation_ref` (`translation_source_title`,`translation_source_language`,`translation_target_language`,`translation_started_by`),
  KEY `cx_translation_languages` (`translation_source_language`,`translation_target_language`)
) ENGINE=InnoDB AUTO_INCREMENT=445845 DEFAULT CHARSET=binary
dbstore2002.codfw.wmnet:3320
*************************** 1. row ***************************
       Table: cx_translations
Create Table: CREATE TABLE `cx_translations` (
  `translation_id` int(11) NOT NULL AUTO_INCREMENT,
  `translation_source_title` varbinary(512) NOT NULL,
  `translation_target_title` varbinary(512) NOT NULL,
  `translation_source_language` varbinary(36) NOT NULL,
  `translation_target_language` varbinary(36) NOT NULL,
  `translation_source_url` blob NOT NULL,
  `translation_target_url` blob,
  `translation_status` enum('draft','published','deleted') DEFAULT NULL,
  `translation_start_timestamp` varbinary(14) NOT NULL,
  `translation_last_updated_timestamp` varbinary(14) NOT NULL,
  `translation_progress` tinyblob NOT NULL,
  `translation_started_by` int(11) DEFAULT NULL,
  `translation_last_update_by` int(11) DEFAULT NULL,
  `translation_source_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_target_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_cx_version` tinyint(3) unsigned DEFAULT '1',
  PRIMARY KEY (`translation_id`),
  UNIQUE KEY `cx_translation_ref` (`translation_source_title`,`translation_source_language`,`translation_target_language`,`translation_started_by`),
  KEY `cx_translation_languages` (`translation_source_language`,`translation_target_language`)
) ENGINE=InnoDB AUTO_INCREMENT=445845 DEFAULT CHARSET=binary
db2034.codfw.wmnet:3306
*************************** 1. row ***************************
       Table: cx_translations
Create Table: CREATE TABLE `cx_translations` (
  `translation_id` int(11) NOT NULL AUTO_INCREMENT,
  `translation_source_title` varbinary(512) NOT NULL,
  `translation_target_title` varbinary(512) NOT NULL,
  `translation_source_language` varbinary(36) NOT NULL,
  `translation_target_language` varbinary(36) NOT NULL,
  `translation_source_url` blob NOT NULL,
  `translation_target_url` blob,
  `translation_status` enum('draft','published','deleted') DEFAULT NULL,
  `translation_start_timestamp` varbinary(14) NOT NULL,
  `translation_last_updated_timestamp` varbinary(14) NOT NULL,
  `translation_progress` tinyblob NOT NULL,
  `translation_started_by` int(11) DEFAULT NULL,
  `translation_last_update_by` int(11) DEFAULT NULL,
  `translation_source_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_target_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_cx_version` tinyint(3) unsigned DEFAULT '1',
  PRIMARY KEY (`translation_id`),
  UNIQUE KEY `cx_translation_ref` (`translation_source_title`,`translation_source_language`,`translation_target_language`,`translation_started_by`),
  KEY `cx_translation_languages` (`translation_source_language`,`translation_target_language`)
) ENGINE=InnoDB AUTO_INCREMENT=445845 DEFAULT CHARSET=binary
db2033.codfw.wmnet:3306
*************************** 1. row ***************************
       Table: cx_translations
Create Table: CREATE TABLE `cx_translations` (
  `translation_id` int(11) NOT NULL AUTO_INCREMENT,
  `translation_source_title` varbinary(512) NOT NULL,
  `translation_target_title` varbinary(512) NOT NULL,
  `translation_source_language` varbinary(36) NOT NULL,
  `translation_target_language` varbinary(36) NOT NULL,
  `translation_source_url` blob NOT NULL,
  `translation_target_url` blob,
  `translation_status` enum('draft','published','deleted') DEFAULT NULL,
  `translation_start_timestamp` varbinary(14) NOT NULL,
  `translation_last_updated_timestamp` varbinary(14) NOT NULL,
  `translation_progress` tinyblob NOT NULL,
  `translation_started_by` int(11) DEFAULT NULL,
  `translation_last_update_by` int(11) DEFAULT NULL,
  `translation_source_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_target_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_cx_version` tinyint(3) unsigned DEFAULT '1',
  PRIMARY KEY (`translation_id`),
  UNIQUE KEY `cx_translation_ref` (`translation_source_title`,`translation_source_language`,`translation_target_language`,`translation_started_by`),
  KEY `cx_translation_languages` (`translation_source_language`,`translation_target_language`)
) ENGINE=InnoDB AUTO_INCREMENT=445845 DEFAULT CHARSET=binary
dbstore1002.eqiad.wmnet:3306
*************************** 1. row ***************************
       Table: cx_translations
Create Table: CREATE TABLE `cx_translations` (
  `translation_id` int(11) NOT NULL AUTO_INCREMENT,
  `translation_source_title` varbinary(512) NOT NULL,
  `translation_target_title` varbinary(512) NOT NULL,
  `translation_source_language` varbinary(36) NOT NULL,
  `translation_target_language` varbinary(36) NOT NULL,
  `translation_source_url` blob NOT NULL,
  `translation_target_url` blob,
  `translation_status` enum('draft','published','deleted') DEFAULT NULL,
  `translation_start_timestamp` varbinary(14) NOT NULL,
  `translation_last_updated_timestamp` varbinary(14) NOT NULL,
  `translation_progress` tinyblob NOT NULL,
  `translation_started_by` int(11) DEFAULT NULL,
  `translation_last_update_by` int(11) DEFAULT NULL,
  `translation_source_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_target_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_cx_version` tinyint(3) unsigned DEFAULT '1',
  PRIMARY KEY (`translation_id`),
  UNIQUE KEY `cx_translation_ref` (`translation_source_title`,`translation_source_language`,`translation_target_language`,`translation_started_by`),
  KEY `cx_translation_languages` (`translation_source_language`,`translation_target_language`)
) ENGINE=InnoDB AUTO_INCREMENT=445845 DEFAULT CHARSET=binary
db1056.eqiad.wmnet:3306
*************************** 1. row ***************************
       Table: cx_translations
Create Table: CREATE TABLE `cx_translations` (
  `translation_id` int(11) NOT NULL AUTO_INCREMENT,
  `translation_source_title` varbinary(512) NOT NULL,
  `translation_target_title` varbinary(512) NOT NULL,
  `translation_source_language` varbinary(36) NOT NULL,
  `translation_target_language` varbinary(36) NOT NULL,
  `translation_source_url` blob NOT NULL,
  `translation_target_url` blob,
  `translation_status` enum('draft','published','deleted') DEFAULT NULL,
  `translation_start_timestamp` varbinary(14) NOT NULL,
  `translation_last_updated_timestamp` varbinary(14) NOT NULL,
  `translation_progress` tinyblob NOT NULL,
  `translation_started_by` int(11) DEFAULT NULL,
  `translation_last_update_by` int(11) DEFAULT NULL,
  `translation_source_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_target_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_cx_version` tinyint(3) unsigned DEFAULT '1',
  PRIMARY KEY (`translation_id`),
  UNIQUE KEY `cx_translation_ref` (`translation_source_title`,`translation_source_language`,`translation_target_language`,`translation_started_by`),
  KEY `cx_translation_languages` (`translation_source_language`,`translation_target_language`)
) ENGINE=InnoDB AUTO_INCREMENT=445845 DEFAULT CHARSET=binary
db1055.eqiad.wmnet:3306
*************************** 1. row ***************************
       Table: cx_translations
Create Table: CREATE TABLE `cx_translations` (
  `translation_id` int(11) NOT NULL AUTO_INCREMENT,
  `translation_source_title` varbinary(512) NOT NULL,
  `translation_target_title` varbinary(512) NOT NULL,
  `translation_source_language` varbinary(36) NOT NULL,
  `translation_target_language` varbinary(36) NOT NULL,
  `translation_source_url` blob NOT NULL,
  `translation_target_url` blob,
  `translation_status` enum('draft','published','deleted') DEFAULT NULL,
  `translation_start_timestamp` varbinary(14) NOT NULL,
  `translation_last_updated_timestamp` varbinary(14) NOT NULL,
  `translation_progress` tinyblob NOT NULL,
  `translation_started_by` int(11) DEFAULT NULL,
  `translation_last_update_by` int(11) DEFAULT NULL,
  `translation_source_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_target_revision_id` int(10) unsigned DEFAULT NULL,
  `translation_cx_version` tinyint(3) unsigned DEFAULT '1',
  PRIMARY KEY (`translation_id`),
  UNIQUE KEY `cx_translation_ref` (`translation_source_title`,`translation_source_language`,`translation_target_language`,`translation_started_by`),
  KEY `cx_translation_languages` (`translation_source_language`,`translation_target_language`)
) ENGINE=InnoDB AUTO_INCREMENT=445845 DEFAULT CHARSET=binary
$ mysql -h db1055.eqiad.wmnet wikishared -e "(SELECT count(*) FROM cx_translations) UNION ALL (SELECT count(*) FROM cx_translations WHERE translation_cx_version = 1)"
+----------+
| count(*) |
+----------+
|   411923 |
|   411923 |
+----------+
jcrespo moved this task from In progress to Done on the DBA board.Mar 20 2018, 1:07 PM
jcrespo moved this task from Backlog to Done on the Blocked-on-schema-change board.
Nikerabbit moved this task from Backlog to QA on the Language-2018-Jan-Mar board.Mar 20 2018, 1:48 PM
Arrbee moved this task from QA to Done on the Language-2018-Jan-Mar board.Mar 21 2018, 7:02 AM
jcrespo removed jcrespo as the assignee of this task.Mar 27 2018, 9:57 AM

Can this be resolved?

KartikMistry closed this task as Resolved.Mar 27 2018, 11:35 AM

Can this be resolved?

Yes. Done!