Revertible migrations

Vitess’s managed schema changes offer lossless revert for online schema migrations: the user may regret a table migration after completion, and roll back the table’s schema to previous state without loss of data.

Revertible migrations supported for:

  • CREATE TABLE statements: the revert is to uncreate the table
  • DROP TABLE statements: the revert is to reinstate the table, populated with data from time of DROP
  • ALTER TABLE statements: supported in vitess strategy, the revert is to reapply previous table schema, without losing any data added/modified since migration completion.
  • Another revert migration. It is possible to revert a revert, revert the revert of a revert, and so forth.

Behavior and limitations

  • A revert is a migration of its own, with a migration UUID, similarly to normal migrations.

  • Migrations are only for revertible for 24h since completion.

  • It’s only possible to revert the last successful migration on a given table. Illustrated following.

    • In the future it may be possible to revert down the stack of completed migrations.
    • To clarify, it’s possibly to revert multiple migrations, even concurrently, but for each table you may only revert the last successful migration on that table.

  • ALTER migrations are revertible only in vitess strategy.

  • If a DDL is a noop, then so is its revert:

    • If a table t exists, and an online DDL is CREATE TABLE IF NOT EXISTS t (...), then the DDL does nothing, and its revert will do nothing.
    • If a table t does not exist, and an online DDL is DROP TABLE IF EXISTS t, then likewise the DDL does nothing, and its revert does nothing.

  • Some ALTER reverts are not guaranteed to succeed. Examples:

    • An ALTER which modifies column i from int to bigint, followed by an INSERT that places a value larger than max int, cannot be reverted, because Vitess cannot place that new value in the old schema.
    • An ALTER which removes a UNIQUE KEY, followed by an INSERT that populates a duplicate value on some column, may not be reverted if that duplicate violates the removed UNIQUE constraint.

    Vitess cannot know ahead of time whether a revert is possible or not.

REVERT syntax

Via SQL:

  1. REVERT VITESS_MIGRATION '69b17887_8a62_11eb_badd_f875a4d24e90';

As of Vitess 12.0 vtctl OnlineDDL revert is deprecated. Use the REVERT VITESS_MIGRATION '...' SQL command either via vtctl ApplySchema or via vtgate.

Via vtctl:

  1. $ vtctldclient ApplySchema --ddl-strategy "vitess" --sql "revert vitess_migration '69b17887_8a62_11eb_badd_f875a4d24e90'" commerce

Both operations return a UUID for the revert migration. The user can track the revert migration to find its state.

Usage & walkthrough

Consider the following annotated flow:

  1. mysql> set @@ddl_strategy='vitess';
  3. mysql> create table t(id int primary key);
  4. +--------------------------------------+
  5. | uuid                                 |
  6. +--------------------------------------+
  7. | 3837e739_8a60_11eb_badd_f875a4d24e90 |
  8. +--------------------------------------+
  9. -- Wait until migration is complete
  11. mysql> alter table t add column ts timestamp not null default current_timestamp;
  12. +--------------------------------------+
  13. | uuid                                 |
  14. +--------------------------------------+
  15. | 6bc591b2_8a60_11eb_badd_f875a4d24e90 |
  16. +--------------------------------------+
  17. -- Wait until migration is complete
  19. mysql> show create table t \G
  20. *************************** 1. row ***************************
  21.        Table: t
  22. Create Table: CREATE TABLE `t` (
  23.   `id` int(11) NOT NULL,
  24.   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  25.   PRIMARY KEY (`id`)
  26. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  28. -- it is now possible to revert 6bc591b2_8a60_11eb_badd_f875a4d24e90, because it was the last successful migration on table t.
  29. -- it is not possible to revert 3837e739_8a60_11eb_badd_f875a4d24e90, because while it was successful, it is not the last
  30. -- successful migration to run on table t t.
  32. mysql> revert vitess_migration '6bc591b2_8a60_11eb_badd_f875a4d24e90';
  33. +--------------------------------------+
  34. | uuid                                 |
  35. +--------------------------------------+
  36. | ead67f31_8a60_11eb_badd_f875a4d24e90 |
  37. +--------------------------------------+
  38. -- Wait until migration is complete
  40. mysql> show create table t \G
  41. *************************** 1. row ***************************
  42.        Table: t
  43. Create Table: CREATE TABLE `t` (
  44.   `id` int(11) NOT NULL,
  45.   PRIMARY KEY (`id`)
  46. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  48. -- It is now possible to revert ead67f31_8a60_11eb_badd_f875a4d24e90 as it is the last successful migration to run on table t.
  49. -- Reverting ead67f31_8a60_11eb_badd_f875a4d24e90 affectively means restoring the changes made by 6bc591b2_8a60_11eb_badd_f875a4d24e90
  51. mysql> revert vitess_migration 'ead67f31_8a60_11eb_badd_f875a4d24e90';
  52. +--------------------------------------+
  53. | uuid                                 |
  54. +--------------------------------------+
  55. | 3b99f686_8a61_11eb_badd_f875a4d24e90 |
  56. +--------------------------------------+
  57. -- Wait until migration is complete
  59. mysql> show create table t \G
  60. *************************** 1. row ***************************
  61.        Table: t
  62. Create Table: CREATE TABLE `t` (
  63.   `id` int(11) NOT NULL,
  64.   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  65.   PRIMARY KEY (`id`)
  66. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  68. -- Let's try an invalid migration:
  69. +--------------------------------------+
  70. | uuid                                 |
  71. +--------------------------------------+
  72. | 7fbdf1c7_8a61_11eb_badd_f875a4d24e90 |
  73. +--------------------------------------+
  74. -- This will fail because column `id` already exists.
  76.                  id: 11
  77.      migration_uuid: 7fbdf1c7_8a61_11eb_badd_f875a4d24e90
  78.            keyspace: commerce
  79.               shard: 0
  80.        mysql_schema: vt_commerce
  81.         mysql_table: t
  82. migration_statement: alter table t add column id bigint
  83.            strategy: vitess
  84.             options: 
  85.     added_timestamp: 2021-03-21 18:21:36
  86. requested_timestamp: 2021-03-21 18:21:32
  87.     ready_timestamp: 2021-03-21 18:21:36
  88.   started_timestamp: 2021-03-21 18:21:36
  89.  liveness_timestamp: 2021-03-21 18:21:36
  90. completed_timestamp: NULL
  91.   cleanup_timestamp: NULL
  92.    migration_status: failed
  93. ...
  94.          ddl_action: alter
  95.             message: Duplicate column name 'id' (errno 1060) (sqlstate 42S21) during query: ALTER TABLE `_7fbdf1c7_8a61_11eb_badd_f875a4d24e90_20210321182136_vrepl` add column id bigint
  96. ...
  98. -- it is impossible to revert 7fbdf1c7_8a61_11eb_badd_f875a4d24e90 because it failed.
  100. +--------------------------------------+
  101. | uuid                                 |
  102. +--------------------------------------+
  103. | c3dff91a_8a61_11eb_badd_f875a4d24e90 |
  104. +--------------------------------------+
  106. mysql> show vitess_migrations like 'c3dff91a_8a61_11eb_badd_f875a4d24e90' \G
  107. *************************** 1. row ***************************
  108.                  id: 12
  109.      migration_uuid: c3dff91a_8a61_11eb_badd_f875a4d24e90
  110.            keyspace: commerce
  111.               shard: 0
  112.        mysql_schema: vt_commerce
  113.         mysql_table: 
  114. migration_statement: revert 7fbdf1c7_8a61_11eb_badd_f875a4d24e90
  115.            strategy: vitess
  116.             options: 
  117.     added_timestamp: 2021-03-21 18:23:31
  118. requested_timestamp: 2021-03-21 18:23:26
  119.     ready_timestamp: 2021-03-21 18:23:36
  120.   started_timestamp: NULL
  121.  liveness_timestamp: NULL
  122. completed_timestamp: NULL
  123.   cleanup_timestamp: NULL
  124.    migration_status: failed
  125. ...
  126.          ddl_action: revert
  127.             message: can only revert a migration in a 'complete' state. Migration 7fbdf1c7_8a61_11eb_badd_f875a4d24e90 is in 'failed' state
  128. ...
  130. mysql> insert into t values (1, now());
  132. mysql> select * from t;
  133. +----+---------------------+
  134. | id | ts                  |
  135. +----+---------------------+
  136. |  1 | 2021-03-21 18:26:47 |
  137. +----+---------------------+
  139. mysql> drop table t;
  140. +--------------------------------------+
  141. | uuid                                 |
  142. +--------------------------------------+
  143. | 69b17887_8a62_11eb_badd_f875a4d24e90 |
  144. +--------------------------------------+
  145. -- Wait until migration is complete
  147. mysql> select * from t;
  148. ERROR 1146 (42S02): ... 
  150. mysql> revert vitess_migration '69b17887_8a62_11eb_badd_f875a4d24e90';
  151. +--------------------------------------+
  152. | uuid                                 |
  153. +--------------------------------------+
  154. | 9eb00275_8a62_11eb_badd_f875a4d24e90 |
  155. +--------------------------------------+
  156. -- Wait until migration is complete
  157. -- `t` was not really dropped, but renamed away. This REVERT reinstates it.
  159. mysql> select * from t;
  160. +----+---------------------+
  161. | id | ts                  |
  162. +----+---------------------+
  163. |  1 | 2021-03-21 18:26:47 |
  164. +----+---------------------+

Implementation details

Revert for CREATE and DROP are implemented similarly for all online strategies.

  • The revert for a CREATE DDL is to rename the table away and into a table lifecycle name, rather than actually DROP it. This keeps th etale safe for a period of time, and makes it possible to reinstate the table, populated with all data, via a 2nd revert.
  • The revert for a DROP relies on the fact that Online DDL DROP TABLE does not, in fact, drop the table, but actually rename it away. Thus, reverting the DROP is merely a RENAME back into its original place.
  • The revert for ALTER is only available for vitess strategy (formerly called online), implemented by VReplication. VReplication keep track of a DDL migration by writing down the GTID position through the migration flow. In particular, at time of cut-over and when tables are swapped, VReplication notes the final GTID pos for the migration. When a revert is requested, Vitess computes a new VReplication rule/filter for the new stream. It them copies the final GTID pos from the reverted migration, and instructs VReplication to resume from that point. As result, a revert for an ALTER migration only needs to catch up with the changelog (binary log entries) since the cut-over of the original migration. To elaborate, it does not need to copy table data, and only needs to consider events for the specific table affected by the revert. This makes the revert operation efficient.