# Guide: Handle upstream schema changes with zero downtime
How to add a column, or drop a column, from your source MySQL database, without any downtime in Materialize
> **Public Preview:** This feature is in public preview.

> **Note:** Changing column types is currently unsupported.


Materialize allows you to handle certain types of upstream table schema changes
seamlessly, specifically:

- Adding a column in the upstream database.
- Dropping a column in the upstream database.

This guide walks you through how to handle these changes without any downtime in Materialize.

## Prerequisites

Some familiarity with Materialize. If you've never used Materialize before,
start with our [guide to getting started](/get-started/quickstart/).

### Set up a MySQL database

For this guide, set up a MySQL 8.0.1+ database. In your MySQL database, create a
table `t1` in `mydb` and populate it:

```sql
CREATE DATABASE mydb;
USE mydb;

CREATE TABLE t1 (
    a INT
);

INSERT INTO t1 (a) VALUES (10);
```

### Configure your MySQL database

Configure your MySQL database for GTID-based binlog replication. You must set
`binlog_row_metadata=FULL` to use the new [`CREATE
SOURCE`](/sql/create-source/mysql-v2/) syntax.

- [Amazon Aurora for MySQL](/ingest-data/mysql/amazon-aurora/)
- [Amazon RDS for MySQL](/ingest-data/mysql/amazon-rds/)
- [Azure DB for MySQL](/ingest-data/mysql/azure-db/)
- [Google Cloud SQL for MySQL](/ingest-data/mysql/google-cloud-sql/)
- [Self-hosted MySQL](/ingest-data/mysql/self-hosted/)

### Connect your source database to Materialize

Create a connection to your MySQL database using the [`CREATE CONNECTION` syntax](/sql/create-connection/).

## Create a source

In Materialize, create a source using the [`CREATE SOURCE`
syntax](/sql/create-source/mysql-v2/).

```mzsql
CREATE SOURCE my_source
  FROM MYSQL CONNECTION mysql_connection;
```

## Create a table from the source

To start ingesting specific tables from your source database, create a
table in Materialize. We'll add it into the `v1` schema.

```mzsql
CREATE SCHEMA v1;

CREATE TABLE v1.t1
    FROM SOURCE my_source (REFERENCE mydb.t1);
```

Once you've created a table from source, the [initial
snapshot](/ingest-data/#snapshotting) of table `v1.t1` will begin.

> **Note:** During the snapshotting, the data ingestion for the existing tables for the same
> source is temporarily blocked. As such, if possible, you can resize the cluster
> to speed up the snapshotting process and once the process finishes, resize the
> cluster for steady-state. You can monitor the snapshot progress on the overview
> page for the source in the Materialize console.


## Create a view on top of the table

For this guide, add a materialized view `matview` (also in schema `v1`) that
sums column `a` from table `t1`.

```mzsql
CREATE MATERIALIZED VIEW v1.matview AS
    SELECT SUM(a) FROM v1.t1;
```

## Handle upstream column addition

### A. Add a column in your upstream MySQL database

In your upstream MySQL database, add a new column `b` to the table `t1`:

```sql
ALTER TABLE t1
    ADD COLUMN b BOOLEAN DEFAULT false;

INSERT INTO t1 (a, b) VALUES (20, true);
```

> **Note:** In MySQL, `BOOL`/`BOOLEAN` is an alias for MySQL type `TINYINT(1)`. As such, the
> column `b` will be ingested as `smallint` in Materialize. See  [MySQL's data type documentation](https://dev.mysql.com/doc/refman/9.7/en/other-vendor-data-types.html).


This operation has no immediate effect in Materialize. In Materialize:

- The table `v1.t1` will continue to ingest only column `a`.
- The materialized view `v1.matview` will continue to have access to column `a`
  only.

### B. Incorporate the new column in Materialize

MySQL uses binlog-based replication, which automatically includes all columns.
To incorporate the new column into Materialize, create a new `v2` schema and
recreate the table in the new schema:

```mzsql
CREATE SCHEMA v2;

CREATE TABLE v2.t1
    FROM SOURCE my_source (REFERENCE mydb.t1);
```

The [snapshotting](/ingest-data/#snapshotting) of table `v2.t1` will begin.
`v2.t1` will include columns `a` and `b`.

> **Note:** During the snapshotting, the data ingestion for the existing tables for the same
> source is temporarily blocked. As such, if possible, you can resize the cluster
> to speed up the snapshotting process and once the process finishes, resize the
> cluster for steady-state. You can monitor the snapshot progress on the overview
> page for the source in the Materialize console.


When `v2.t1` has finished snapshotting, create a new materialized view in the
new schema. Since `v2.matview` references `v2.t1`, it can reference column `b`:

```mzsql {hl_lines="4"}
CREATE MATERIALIZED VIEW v2.matview AS
    SELECT SUM(a)
    FROM v2.t1
    WHERE b = 1;
```

## Handle upstream column drop

### A. Exclude the column in Materialize

To drop a column safely, first create a new schema in Materialize and recreate
the table excluding the column you intend to drop. In this example, we'll drop
column `b`.

```mzsql
CREATE SCHEMA v3;

CREATE TABLE v3.t1
    FROM SOURCE my_source (REFERENCE mydb.t1) WITH (EXCLUDE COLUMNS (b));
```

> **Note:** During the snapshotting, the data ingestion for the existing tables for the same
> source is temporarily blocked. As such, if possible, you can resize the cluster
> to speed up the snapshotting process and once the process finishes, resize the
> cluster for steady-state. You can monitor the snapshot progress on the overview
> page for the source in the Materialize console.


### B. Drop the column in your upstream MySQL database

In your upstream MySQL database, drop column `b` from table `t1`:

```sql
ALTER TABLE t1 DROP COLUMN b;
```

Dropping column `b` will have no effect on `v3.t1` in Materialize, provided
you completed step A before dropping the column. However, the drop affects
`v2.t1` and `v2.matview` from our earlier examples. When you attempt to
read from either, Materialize will report an error that the source table schema
has been altered.

Once you have finished migrating any views and queries from `v2` to `v3`, you
can clean up the old objects:

```mzsql
DROP TABLE v2.t1;
DROP MATERIALIZED VIEW v2.matview;
DROP SCHEMA v2;
```