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

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.