- 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.
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.
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
There is no revert involved.
Please create a new task for a new schema change based on the current state.