Unmanaged Schema Changes

Vitess offers multiple approaches to running unmanaged schema changes. Below, we review each of these approaches.

We assume we have a keyspace (schema) called commerce, with a table called demo, that has the following definition:

CREATE TABLE `demo` (
  `id` int NOT NULL,
  `status` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

ApplySchema

ApplySchema is a vtctlclient command that can be used to apply a schema change to a keyspace. The main advantage of using this tool is that it performs some sanity checks about the schema before applying it. However, a downside is that it can be a little too strict and may not work for all use cases.

Consider the following examples:

$ vtctlclient ApplySchema -- --sql "ALTER TABLE demo modify id bigint unsigned" commerce

SHOW CREATE TABLE demo;
CREATE TABLE `demo` (
  `id` bigint unsigned NOT NULL,
  `status` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

In the above, we run a direct, synchronous, blocking ALTER TABLE statement. Knowing the table is in commerce keyspace, Vitess autodetects the relevant shards, and then autodetects which is the primary server in each shard. It then directly invokes the ALTER TABLE statement on all shards (concurrently), and the vtctlclient command only returns when all are complete.

Vitess will run some validations:

$ vtctlclient ApplySchema -- --sql "ALTER TABLE demo add column status int" commerce
E0908 10:35:53.478462 3711762 main.go:67] remote error: rpc error: code = Unknown desc = schema change failed, ExecuteResult: {
  "FailedShards": null,
  "SuccessShards": null,
  "CurSQLIndex": 0,
  "Sqls": [
    "ALTER TABLE demo add column status int"
  ],
  "ExecutorErr": "rpc error: code = Unknown desc = TabletManager.PreflightSchema on zone1-0000000100 error: /usr/bin/mysql: exit status 1, output: ERROR 1060 (42S21) at line 3: Duplicate column name 'status'\n: /usr/bin/mysql: exit status 1, output: ERROR 1060 (42S21) at line 3: Duplicate column name 'status'\n",
  "TotalTimeSpent": 87104194
}

Vitess was able to determine that the migration is invalid because a column named status already exists. The statement never made it to the MySQL servers. These checks are not thorough, though they cover common scenarios.

If the table is large, then ApplySchema will reject the statement, try to protect the user from blocking their production servers. You may override that by supplying --allow_long_unavailability as follows:

$ vtctlclient ApplySchema -- --allow_long_unavailability --sql "ALTER TABLE demo modify id bigint unsigned" commerce

VTGate

You may run DDL directly from VTGate just like you would send to a MySQL instance. For example:

$ mysql -h 127.0.0.1 -P 15306 commerce
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql> ALTER TABLE demo ADD COLUMN sample INT;
Query OK, 0 rows affected (0.04 sec)

Just like in the previous example, Vitess will find out what the affected shards are, what the identity is of each shard’s primary, then invoke the statement on all shards.

You may apply the change to specific shards by connecting directly to those shards:

$ mysql -h 127.0.0.1 -P 15306 commerce/-80
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql> ALTER TABLE demo ADD COLUMN sample INT;
Query OK, 0 rows affected (0.04 sec)

In the above we connect to VTGate via the mysql command line client, but of course you may connect with any standard MySQL client or from your application.

Please do note that if VTGate does not recognize a DDL syntax, the statement will get rejected and that this approach is not recommended for changing large tables.

Directly to MySQL

You can apply schema changes directly to the underlying MySQL shard primary instances.

VTTablet will eventually notice the change and update itself. This is controlled by the --queryserver-config-schema-reload-time parameter which defaults to 1800 seconds.

You can also explicitly issue the vtctlclient ReloadSchema command to make it reload immediately. Specify a tablet to reload the schema from, as in:

$ vtctlclient ReloadSchema zone1-0000000100

Users will likely want to deploy schema changes via gh-ost or pt-online-schema-change, which do not block the table. Vitess offers managed, online schema changes where it automates the invocation and execution of these tools. Using these schema deployment tools can be a better approach for large tables, because they should incur no downtime.