Review schema changes for T119495
Closed, ResolvedPublic

Description

  • The ALTER TABLES to run: https://gerrit.wikimedia.org/r/#/c/255662/3/sql/patch-store-revision-ids.sql
  • Where to run those changes: extension1, DB: wikishared
  • When to run those changes: With train on next Tuesday (14 December)
  • If the schema change is backwards compatible: compatible with the current code deployed.
  • If the schema change has been tested already on some of the test/beta wikis. Usually, as a last test, change should be applied to testwiki first: We will be testing on Labs, Beta and testwiki (along with deployment).
  • If it involves new columns or tables, if the data should be made available on the labs replicas. Similar question if it involves deletion of data previously available on labs.
KartikMistry updated the task description. (Show Details)
KartikMistry raised the priority of this task from to Normal.
KartikMistry assigned this task to santhosh.
KartikMistry reassigned this task from santhosh to jcrespo.
KartikMistry set Security to None.
KartikMistry added subscribers: gerritbot, Aklapper, santhosh and 2 others.

Some input in needed from @santhosh (Re: backwards compatible) in Description.

Arrbee moved this task from Backlog to In Review on the LE-CX7-Sprint 3 board.Dec 1 2015, 7:33 AM
Arrbee moved this task from Backlog to In Review on the LE-CX7-Sprint 4 board.

Dropping #blocked-by-schema-change because as far as I know I am not the one blocking this "(We will schedule this)". Please readd it when it is actually being blocked by a DBA (adding the regular DBA tag).

Amire80 moved this task from Needs Triage to CX7 on the ContentTranslation board.Dec 7 2015, 10:45 AM
KartikMistry updated the task description. (Show Details)Dec 10 2015, 4:05 PM

@jcrespo, we can proceed this now as code will be deployed with next train run and table need to updated before it.

Will code fail if applied right now?

The new columns allow null now, so current code can ignore and does ignore them.

Thanks, however, I cannot guarantee it will be done by 14. It is the end of my day, and tomorrow traditionally only emergency deployments are done.

I will try to perform it on Monday, but as documented on the tag, usually I ask for 2 weeks of advance notice. In this case it *may* be doable, but only because it is on the x1 server and there is not a lot of traffic on these tables (assuming no other more urgent ask arrives between now and Monday). Apologies for this.

jcrespo closed this task as Resolved.Dec 14 2015, 10:57 AM

The change has been applied successfuly, and no regression has been noted. This is the new table structure:

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 NOT NULL,
  `translation_target_revision_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`translation_id`),
  UNIQUE KEY `cx_translation_pair` (`translation_source_title`,`translation_source_language`,`translation_target_language`),
  KEY `cx_translation_languages` (`translation_source_language`,`translation_target_language`)
) ENGINE=InnoDB AUTO_INCREMENT=94833 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

This was easier than I feared due to the low usage at the time of the schema change.

santhosh reopened this task as Open.Dec 15 2015, 6:44 AM

@jcrespo A small mistake happened, the patch sql that Kartik linked was pointing to an older patchset. Later Niklas made those two columns nullable(translation_source_revision_id and translation_target_revision_id ). See Niklas comment above.

See the diff https://gerrit.wikimedia.org/r/#/c/255662/3..10/sql/patch-store-revision-ids.sql

Fortunately the int type fields get default value 0 and nothing breaks now.
But to make it future-proof, we would like to get those fields nullable as per the last PS at https://gerrit.wikimedia.org/r/#/c/255662/

Sorry for the confusion.

jcrespo closed this task as Invalid.Dec 15 2015, 7:56 AM

Schema changes cannot be reverted. See: https://wikitech.wikimedia.org/wiki/Schema_changes

Please create a new task for a new schema change based on the current state.

Schema changes cannot be reverted. See: https://wikitech.wikimedia.org/wiki/Schema_changes

There is no revert involved.

Please create a new task for a new schema change based on the current state.

Done. See T121509: Correct the schema to make revision id columns of cx_translations nullable

jcrespo changed the task status from Invalid to Resolved.Dec 17 2015, 12:10 PM
KartikMistry moved this task from In Review to Done on the LE-CX7-Sprint 4 board.Dec 21 2015, 6:30 AM