Page MenuHomePhabricator

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.

Event Timeline

KartikMistry assigned this task to santhosh.
KartikMistry reassigned this task from santhosh to jcrespo.
KartikMistry raised the priority of this task from to Medium.
KartikMistry updated the task description. (Show Details)
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.

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).

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

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.

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.

@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.

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.

jcrespo changed the task status from Invalid to Resolved.Dec 17 2015, 12:10 PM