Applying, auditing, and controlling Online DDL
Vitess provides two interfaces to interacting with Online DDL:
- SQL commands, via
VTGate - Command line interface, via
vtctl
Supported interactions are:
- Running migrations (submitting Online DDL requests)
- Tracking migrations
- Cancelling a migration
- Cancelling all pending migrations
- Retrying a migration
- Cleaning migration artifacts
- Reverting a migration
Running migrations #
To run a managed schema migration, you should:
- Formulate your DDLs (
CREATE,ALTER,DROP) queries - Choose a ddl_strategy
When the user submits an online DDL, Vitess responds with a UUID, a job Id used to later track or control the migration. The migration does not start immediately. It is queued at the tablets and executed at some point in the future.
Via VTGate/SQL #
mysql> set @@ddl_strategy='online';
mysql> alter table corder add column ts timestamp not null default current_timestamp;
+--------------------------------------+
| uuid |
+--------------------------------------+
| bf4598ab_8d55_11eb_815f_f875a4d24e90 |
+--------------------------------------+
mysql> drop table customer;
+--------------------------------------+
| uuid |
+--------------------------------------+
| 6848c1a4_8d57_11eb_815f_f875a4d24e90 |
+--------------------------------------+
@@ddl_strategybehaves like a MySQL session variable, though is only recognized byVTGate. Setting@@ddl_strategyonly applies to that same connection and does not affect other connections. The strategy applies to all migrations executed in that session. You may subsequently set@@ddl_strategyto different value.- If you run
vtgatewithout-ddl_strategy, then@@ddl_strategydefaults to'direct', which implies schema migrations are synchronous. You will need toset @@ddl_strategy='gh-ost'to run followupALTER TABLEstatements viagh-ost. - If you run
vtgate -ddl_strategy "gh-ost", then@@ddl_strategydefaults to'gh-ost'in each new session. AnyALTER TABLEwill run viagh-ost. You mayset @@ddl_strategy='pt-osc'to make migrations run throughpt-online-schema-change, orset @@ddl_strategy='direct'to run migrations synchronously.
Via vtctl/ApplySchema #
You may use vtctl or vtctlclient (the two are interchangeable for the purpose of this document) to apply schema changes. The ApplySchema command supports both synchronous and online schema migrations. To run an online schema migration you will supply the -ddl_strategy command line flag:
$ vtctlclient ApplySchema -ddl_strategy "online" -sql "ALTER TABLE demo MODIFY id bigint UNSIGNED" commerce
a2994c92_f1d4_11ea_afa3_f875a4d24e90
You my run multiple migrations withing the same ApplySchema command:
$ vtctlclient ApplySchema -skip_preflight -ddl_strategy "online" -sql "ALTER TABLE demo MODIFY id bigint UNSIGNED; CREATE TABLE sample (id int PRIMARY KEY); DROP TABLE another;" commerce
3091ef2a_4b87_11ec_a827_0a43f95f28a3
ApplySchema accepts the following flags:
-ddl_strategy: by default migrations run directly via MySQL standard DDL. This flag must be aupplied to indicate an online strategy. See also DDL strategies and ddl_strategy flags.-migration_context <unique-value>: all migrations in aApplySchemacommand are logically grouped via a unique context. A unique value will be supplied automatically. The user may choose to supply their own value, and it's their responsibility to provide with a unique value. Any string format is accepted. The context can then be used to search for migrations, viaSHOW VITESS_MIGRATIONS LIKE 'the-context'. It is visible inSHOW VITESS_MIGRATIONS ...output as themigration_contextcolumn.-skip_preflight: skip an internal Vitess schema validation. When running an online DDL it's recommended to add-skip_preflight. In future Vitess versions this flag may be removed or default totrue.
Tracking migrations #
You may track the status of a single or of multiple migrations. Since migrations run asycnhronously, it is the user's responsibility to audit the progress and state of submitted migrations. Users are likely to want to know when a migration is complete (or failed) so as to be able to deploy code changes or run other operations.
Common patterns are:
- Show state of a specific migration
- Show all
running,completeorfailedmigrations - Show recent migrations
Via VTGate/SQL #
Examples for a single shard cluster:
mysql> show vitess_migrations like 'bf4598ab_8d55_11eb_815f_f875a4d24e90' \G
*************************** 1. row ***************************
id: 23
migration_uuid: bf4598ab_8d55_11eb_815f_f875a4d24e90
keyspace: commerce
shard: 0
mysql_schema: vt_commerce
mysql_table: corder
migration_statement: alter table corder add column ts timestamp not null default current_timestamp()
strategy: online
options:
added_timestamp: 2021-03-25 12:35:01
requested_timestamp: 2021-03-25 12:34:58
ready_timestamp: 2021-03-25 12:35:04
started_timestamp: 2021-03-25 12:35:04
liveness_timestamp: 2021-03-25 12:35:06
completed_timestamp: 2021-03-25 12:35:06
cleanup_timestamp: NULL
migration_status: complete
log_path:
artifacts: _bf4598ab_8d55_11eb_815f_f875a4d24e90_20210325123504_vrepl,
retries: 0
tablet: zone1-0000000100
tablet_failure: 0
progress: 100
migration_context: vtgate:a8352418-8d55-11eb-815f-f875a4d24e90
ddl_action: alter
message:
eta_seconds: 0
mysql> show vitess_migrations like 'complete' \G
...
*************************** 21. row ***************************
id: 24
migration_uuid: 6848c1a4_8d57_11eb_815f_f875a4d24e90
keyspace: commerce
shard: 0
mysql_schema: vt_commerce
mysql_table: customer
migration_statement: drop table customer
strategy: online
options:
added_timestamp: 2021-03-25 12:46:53
requested_timestamp: 2021-03-25 12:46:51
ready_timestamp: 2021-03-25 12:46:57
started_timestamp: 2021-03-25 12:46:57
liveness_timestamp: 2021-03-25 12:46:57
completed_timestamp: 2021-03-25 12:46:57
cleanup_timestamp: NULL
migration_status: complete
log_path:
artifacts: _vt_HOLD_6848c1a48d5711eb815ff875a4d24e90_20210326104657,
retries: 0
tablet: zone1-0000000100
tablet_failure: 0
progress: 100
migration_context: vtgate:a8352418-8d55-11eb-815f-f875a4d24e90
ddl_action: drop
message:
eta_seconds: 0
mysql> show vitess_migrations where completed_timestamp > now() - interval 1 day;
+----+--------------------------------------+----------+-------+--------------+-------------+---------------------------------------------------------------------------------+----------+---------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+-------------------+------------------+----------+-------------------------------------------------------------+---------+------------------+----------------+----------+---------------------------------------------+------------+---------+-------------+
| id | migration_uuid | keyspace | shard | mysql_schema | mysql_table | migration_statement | strategy | options | added_timestamp | requested_timestamp | ready_timestamp | started_timestamp | liveness_timestamp | completed_timestamp | cleanup_timestamp | migration_status | log_path | artifacts | retries | tablet | tablet_failure | progress | migration_context | ddl_action | message | eta_seconds |
+----+--------------------------------------+----------+-------+--------------+-------------+---------------------------------------------------------------------------------+----------+---------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+-------------------+------------------+----------+-------------------------------------------------------------+---------+------------------+----------------+----------+---------------------------------------------+------------+---------+-------------+
| 23 | bf4598ab_8d55_11eb_815f_f875a4d24e90 | commerce | 0 | vt_commerce | corder | alter table corder add column ts timestamp not null default current_timestamp() | online | | 2021-03-25 12:35:01 | 2021-03-25 12:34:58 | 2021-03-25 12:35:04 | 2021-03-25 12:35:04 | 2021-03-25 12:35:06 | 2021-03-25 12:35:06 | NULL | complete | | _bf4598ab_8d55_11eb_815f_f875a4d24e90_20210325123504_vrepl, | 0 | zone1-0000000100 | 0 | 100 | vtgate:a8352418-8d55-11eb-815f-f875a4d24e90 | alter | | 0 |
| 24 | 6848c1a4_8d57_11eb_815f_f875a4d24e90 | commerce | 0 | vt_commerce | customer | drop table customer | online | | 2021-03-25 12:46:53 | 2021-03-25 12:46:51 | 2021-03-25 12:46:57 | 2021-03-25 12:46:57 | 2021-03-25 12:46:57 | 2021-03-25 12:46:57 | NULL | complete | | _vt_HOLD_6848c1a48d5711eb815ff875a4d24e90_20210326104657, | 0 | zone1-0000000100 | 0 | 100 | vtgate:a8352418-8d55-11eb-815f-f875a4d24e90 | drop | | 0 |
| 25 | 6fd57dd3_8d57_11eb_815f_f875a4d24e90 | commerce | 0 | vt_commerce | customer | revert 6848c1a4_8d57_11eb_815f_f875a4d24e90 | online | | 2021-03-25 12:47:08 | 2021-03-25 12:47:04 | 2021-03-25 12:47:12 | 2021-03-25 12:47:12 | 2021-03-25 12:47:12 | 2021-03-25 12:47:12 | NULL | complete | | _vt_HOLD_6848c1a48d5711eb815ff875a4d24e90_20210326104657, | 0 | zone1-0000000100 | 0 | 100 | vtgate:a8352418-8d55-11eb-815f-f875a4d24e90 | create | | 0 |
+----+--------------------------------------+----------+-------+--------------+-------------+---------------------------------------------------------------------------------+----------+---------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+-------------------+------------------+----------+-------------------------------------------------------------+---------+------------------+----------------+----------+---------------------------------------------+------------+---------+-------------+
show vitess_migrationsshows the entire history of migrations.show vitess_migrations like ...filters migrations bymigration_uuid, ormigration_context, ormigration_status.show vitess_migrations where ...lets the user specify arbitrary conditions.- All commands return results for the keyspace (schema) in use.
Via vtctl/ApplySchema #
Examples for a 4-shard cluster:
$ vtctlclient OnlineDDL commerce show ab3ffdd5_f25c_11ea_bab4_0242c0a8b007
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | ddl_action | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| test-0000000201 | 40-80 | vt_commerce | demo | alter | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:24:33 | 2020-09-09 05:24:34 | complete |
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete |
| test-0000000401 | c0- | vt_commerce | demo | alter | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete |
| test-0000000101 | -40 | vt_commerce | demo | alter | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
$ vtctlclient OnlineDDL commerce show 8a797518_f25c_11ea_bab4_0242c0a8b007
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | ddl_action | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| test-0000000401 | c0- | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | running |
| test-0000000201 | 40-80 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | 2020-09-09 05:23:33 | complete |
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | running |
| test-0000000101 | -40 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | running |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
$ vtctlclient OnlineDDL commerce show 8a797518_f25c_11ea_bab4_0242c0a8b007
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | ddl_action | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| test-0000000401 | c0- | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | failed |
| test-0000000101 | -40 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | failed |
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | failed |
| test-0000000201 | 40-80 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | 2020-09-09 05:23:33 | complete |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
$ vtctlclient OnlineDDL commerce show recent
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | ddl_action | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| test-0000000201 | 40-80 | vt_commerce | demo | alter | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete |
| test-0000000201 | 40-80 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | 2020-09-09 05:23:33 | complete |
| test-0000000201 | 40-80 | vt_commerce | demo | alter | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:24:33 | 2020-09-09 05:24:34 | complete |
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete |
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | failed |
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete |
| test-0000000401 | c0- | vt_commerce | demo | alter | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete |
| test-0000000401 | c0- | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | failed |
| test-0000000401 | c0- | vt_commerce | demo | alter | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete |
| test-0000000101 | -40 | vt_commerce | demo | alter | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete |
| test-0000000101 | -40 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | failed |
| test-0000000101 | -40 | vt_commerce | demo | alter | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
$ vtctlclient OnlineDDL commerce show failed
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | ddl_action | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | failed |
| test-0000000401 | c0- | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | failed |
| test-0000000101 | -40 | vt_commerce | demo | alter | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online | 2020-09-09 05:23:32 | | failed |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
The syntax for tracking migrations is:
vtctlclient OnlineDDL [-json] <keyspace> show <migration_id|all|recent|queued|ready|running|complete|failed|cancelled>
With -json flag, the results are returned as a well formed JSON construct.
Showing migration logs #
gh-ost and pt-osc tools generate logs files, which are retrievable for 24 hours after migration completion/failure.
Via VTGate/SQL #
mysql> show vitess_migration '3a273866_e867_11eb_ab12_0a43f95f28a3' logs \G
*************************** 1. row ***************************
migration_log: 2021-07-19 07:59:23 INFO starting gh-ost 261355426d8fc31b590733ca8ff8e79012103c18
2021-07-19 07:59:23 INFO Migrating `vt_commerce`.`corder`
2021-07-19 07:59:23 INFO executing gh-ost-on-startup hook: /tmp/online-ddl-3a273866_e867_11eb_ab12_0a43f95f28a3-943208852/gh-ost-on-startup
ok
2021-07-19 07:59:23 INFO inspector connection validated on ip-REDACTED:17100
2021-07-19 07:59:23 INFO User has SUPER, REPLICATION SLAVE privileges, and has ALL privileges on `vt_commerce`.*
2021-07-19 07:59:23 INFO binary logs validated on ip-REDACTED:17100
2021-07-19 07:59:23 INFO Restarting replication on ip-REDACTED:17100 to make sure binlog settings apply to replication thread
2021-07-19 07:59:23 INFO Inspector initiated on ip-REDACTED:17100, version 5.7.30-log
2021-07-19 07:59:23 INFO Table found. Engine=InnoDB
...
Cancelling a migration #
The user may cancel a migration, as follows:
- If the migration hasn't started yet (it is
queuedorready), then it is removed from queue and will not be executed. - If the migration is
running, then it is forcibly interrupted. The migration is expected to transition tofailedstate. - In all other cases, cancelling a migration has no effect.
Via VTGate/SQL #
Examples for a single shard cluster:
id: 28
migration_uuid: aa89f255_8d68_11eb_815f_f875a4d24e90
keyspace: commerce
shard: 0
mysql_schema: vt_commerce
mysql_table: corder
migration_statement: alter table corder add column handler_id int not null
strategy: gh-ost
options:
added_timestamp: 2021-03-25 14:50:27
requested_timestamp: 2021-03-25 14:50:24
ready_timestamp: 2021-03-25 14:50:31
started_timestamp: 2021-03-25 14:50:32
liveness_timestamp: 2021-03-25 14:50:32
completed_timestamp: NULL
cleanup_timestamp: NULL
migration_status: running
...
mysql> alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' cancel;
Query OK, 1 row affected (0.01 sec)
mysql> show vitess_migrations like 'aa89f255_8d68_11eb_815f_f875a4d24e90' \G
*************************** 1. row ***************************
id: 28
migration_uuid: aa89f255_8d68_11eb_815f_f875a4d24e90
keyspace: commerce
shard: 0
mysql_schema: vt_commerce
mysql_table: corder
migration_statement: alter table corder add column handler_id int not null
strategy: gh-ost
options: --throttle-flag-file=/tmp/throttle.flag
added_timestamp: 2021-03-25 14:50:27
requested_timestamp: 2021-03-25 14:50:24
ready_timestamp: 2021-03-25 14:50:31
started_timestamp: 2021-03-25 14:50:32
liveness_timestamp: 2021-03-25 14:50:32
completed_timestamp: NULL
cleanup_timestamp: NULL
migration_status: failed
...
alter vitess_migration ... canceltakes exactly one migration's UUID.alter vitess_migration ... cancelresponds with number of affected migrations.
Via vtctl/ApplySchema #
Examples for a 4-shard cluster:
vtctlclient OnlineDDL <keyspace> cancel <migration_id>
Example:
$ vtctlclient OnlineDDL commerce show 2201058f_f266_11ea_bab4_0242c0a8b007
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | ddl_action | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | running |
| test-0000000101 | -40 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | running |
| test-0000000401 | c0- | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | running |
| test-0000000201 | 40-80 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | running |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
$ vtctlclient OnlineDDL commerce cancel 2201058f_f266_11ea_bab4_0242c0a8b007
+-----------------+--------------+
| Tablet | RowsAffected |
+-----------------+--------------+
| test-0000000401 | 1 |
| test-0000000101 | 1 |
| test-0000000201 | 1 |
| test-0000000301 | 1 |
+-----------------+--------------+
$ vtctlclient OnlineDDL commerce show 2201058f_f266_11ea_bab4_0242c0a8b007
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | ddl_action | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| test-0000000401 | c0- | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | failed |
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | failed |
| test-0000000201 | 40-80 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | failed |
| test-0000000101 | -40 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | failed |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
Cancelling all keyspace migrations #
The user may cancel all migrations in a keyspace. A migration is cancellable if it is in queued, ready or running states, as described previously. It is a high impact operation and should be used with care.
Via VTGate/SQL #
Examples for a single shard cluster:
mysql> alter vitess_migration cancel all;
Query OK, 1 row affected (0.02 sec)
Via vtctl/ApplySchema #
Examples for a 4-shard cluster:
vtctlclient OnlineDDL <keyspace> cancel-all
Example:
$ vtctlclient OnlineDDL commerce show all
+------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
| zone1-0000000100 | 0 | vt_commerce | corder | 2c581994_353a_11eb_8b72_f875a4d24e90 | online | | | queued |
| zone1-0000000100 | 0 | vt_commerce | corder | 2c6420c9_353a_11eb_8b72_f875a4d24e90 | online | | | queued |
| zone1-0000000100 | 0 | vt_commerce | corder | 2c7040df_353a_11eb_8b72_f875a4d24e90 | online | | | queued |
| zone1-0000000100 | 0 | vt_commerce | corder | 2c7c0572_353a_11eb_8b72_f875a4d24e90 | online | | | queued |
| zone1-0000000100 | 0 | vt_commerce | corder | 2c87f7cd_353a_11eb_8b72_f875a4d24e90 | online | | | queued |
+------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
$ vtctlclient OnlineDDL commerce cancel-all
+------------------+--------------+
| Tablet | RowsAffected |
+------------------+--------------+
| zone1-0000000100 | 5 |
+------------------+--------------+
vtctlclient OnlineDDL commerce show all
+------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
| zone1-0000000100 | 0 | vt_commerce | corder | 2c581994_353a_11eb_8b72_f875a4d24e90 | online | | | cancelled |
| zone1-0000000100 | 0 | vt_commerce | corder | 2c6420c9_353a_11eb_8b72_f875a4d24e90 | online | | | cancelled |
| zone1-0000000100 | 0 | vt_commerce | corder | 2c7040df_353a_11eb_8b72_f875a4d24e90 | online | | | cancelled |
| zone1-0000000100 | 0 | vt_commerce | corder | 2c7c0572_353a_11eb_8b72_f875a4d24e90 | online | | | cancelled |
| zone1-0000000100 | 0 | vt_commerce | corder | 2c87f7cd_353a_11eb_8b72_f875a4d24e90 | online | | | cancelled |
+------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
Retrying a migration #
The user may retry running a migration. If the migration is in failed or in cancelled state, Vitess will re-run the migration, with exact same arguments as previously intended. If the migration is in any other state, retry does nothing.
It is not possible to retry a migration with different options. e.g. if the user initially runs ALTER TABLE demo MODIFY id BIGINT with @@ddl_strategy='gh-ost --max-load Threads_running=200' and the migration fails, retrying it will use exact same options. It is not possible to retry with @@ddl_strategy='gh-ost --max-load Threads_running=500'.
Via VTGate/SQL #
Examples for a single shard cluster:
*************************** 1. row ***************************
id: 28
migration_uuid: aa89f255_8d68_11eb_815f_f875a4d24e90
keyspace: commerce
shard: 0
mysql_schema: vt_commerce
mysql_table: corder
migration_statement: alter table corder add column handler_id int not null
strategy: gh-ost
options: --throttle-flag-file=/tmp/throttle.flag
added_timestamp: 2021-03-25 14:50:27
requested_timestamp: 2021-03-25 14:50:24
ready_timestamp: 2021-03-25 14:56:22
started_timestamp: 2021-03-25 14:56:22
liveness_timestamp: 2021-03-25 14:56:22
completed_timestamp: NULL
cleanup_timestamp: NULL
migration_status: failed
...
mysql> alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' retry;
Query OK, 1 row affected (0.00 sec)
mysql> show vitess_migrations like 'aa89f255_8d68_11eb_815f_f875a4d24e90' \G
*************************** 1. row ***************************
id: 28
migration_uuid: aa89f255_8d68_11eb_815f_f875a4d24e90
keyspace: commerce
shard: 0
mysql_schema: vt_commerce
mysql_table: corder
migration_statement: alter table corder add column handler_id int not null
strategy: gh-ost
options: --throttle-flag-file=/tmp/throttle.flag
added_timestamp: 2021-03-25 14:50:27
requested_timestamp: 2021-03-25 14:50:24
ready_timestamp: 2021-03-25 14:56:42
started_timestamp: 2021-03-25 14:56:42
liveness_timestamp: 2021-03-25 14:56:42
completed_timestamp: NULL
cleanup_timestamp: NULL
migration_status: running
...
alter vitess_migration ... retrytakes exactly one migration's UUID.alter vitess_migration ... retryresponds with number of affected migrations.
Via vtctl/ApplySchema #
Examples for a 4-shard cluster:
$ vtctlclient OnlineDDL commerce show 2201058f_f266_11ea_bab4_0242c0a8b007
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | ddl_action | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| test-0000000401 | c0- | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | failed |
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | failed |
| test-0000000201 | 40-80 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | failed |
| test-0000000101 | -40 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:32:31 | | failed |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
$ vtctlclient OnlineDDL commerce retry 2201058f_f266_11ea_bab4_0242c0a8b007
+-----------------+--------------+
| Tablet | RowsAffected |
+-----------------+--------------+
| test-0000000101 | 1 |
| test-0000000201 | 1 |
| test-0000000301 | 1 |
| test-0000000401 | 1 |
+-----------------+--------------+
$ vtctlclient OnlineDDL commerce show 2201058f_f266_11ea_bab4_0242c0a8b007
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+-------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | ddl_action | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+-------------------+---------------------+------------------+
| test-0000000201 | 40-80 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | | | queued |
| test-0000000101 | -40 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | | | queued |
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | | | queued |
| test-0000000401 | c0- | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | | | queued |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+-------------------+---------------------+------------------+
$ vtctlclient OnlineDDL commerce show 2201058f_f266_11ea_bab4_0242c0a8b007
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| Tablet | shard | mysql_schema | mysql_table | ddl_action | migration_uuid | strategy | started_timestamp | completed_timestamp | migration_status |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
| test-0000000101 | -40 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:37:33 | | running |
| test-0000000401 | c0- | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:37:33 | | running |
| test-0000000201 | 40-80 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:37:33 | | running |
| test-0000000301 | 80-c0 | vt_commerce | demo | alter | 2201058f_f266_11ea_bab4_0242c0a8b007 | online | 2020-09-09 06:37:33 | | running |
+-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
Cleaning migration artifacts #
Migrations yield artifacts: these are leftover tables, such as the ghost or shadow tables in an ALTER DDL. These tables are audited and collected as part of table lifecycle.
The artifacts are essential to Reverting a migration, and are kept intact for a while before destroyed.
However, the artifacts also consume disk space. If the user is convinced they will not need the artifacts, they may explicitly request that the artifacts are dropped sooner.
Once cleanup is requested, the migration cannot be reverted.
The artifact tables are not purged immediately. Rather, they are sent for processing into the lifecycle mechanism.
Via VTGate/SQL #
Per migration, request artifact cleanup via:
mysql> alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' cleanup;
Query OK, 1 row affected (0.00 sec)
Reverting a migration #
Vitess offers 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. See Revertible Migrations.
Via VTGate/SQL #
Examples for a single shard cluster:
mysql> show create table corder\G
Create Table: CREATE TABLE `corder` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) DEFAULT NULL,
`sku` varbinary(128) DEFAULT NULL,
`price` bigint(20) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
mysql> alter table corder drop column ts, add key customer_idx(customer_id);
+--------------------------------------+
| uuid |
+--------------------------------------+
| 1a689113_8d77_11eb_815f_f875a4d24e90 |
+--------------------------------------+
mysql> show create table corder\G
Create Table: CREATE TABLE `corder` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) DEFAULT NULL,
`sku` varbinary(128) DEFAULT NULL,
`price` bigint(20) DEFAULT NULL,
PRIMARY KEY (`order_id`),
KEY `customer_idx` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> revert vitess_migration '1a689113_8d77_11eb_815f_f875a4d24e90';
+--------------------------------------+
| uuid |
+--------------------------------------+
| a02e6612_8d79_11eb_815f_f875a4d24e90 |
+--------------------------------------+
mysql> show create table corder\G
Create Table: CREATE TABLE `corder` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) DEFAULT NULL,
`sku` varbinary(128) DEFAULT NULL,
`price` bigint(20) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
- A
revertis its own migration, hence has its own UUID
Via vtctl/ApplySchema #
$ vtctlclient ApplySchema -ddl_strategy "online" -sql "revert vitess_migration '1a689113_8d77_11eb_815f_f875a4d24e90'" commerce
Applying, auditing, and controlling Online DDL