# Handle upstream schema changes
How to add a column, drop a column, or drop a constraint in your source MySQL database, without any downtime in Materialize

> For the complete documentation index, see [llms.txt](https://preview.materialize.com/materialize/40269/llms.txt).


> **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.
- Dropping a `PRIMARY KEY`, `UNIQUE`, or `NOT NULL` constraint 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](https://preview.materialize.com/materialize/40269/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 PRIMARY KEY
);

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`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-source/mysql-v2/index.md) syntax.

- [Amazon Aurora for MySQL](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/mysql/amazon-aurora/index.md)
- [Amazon RDS for MySQL](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/mysql/amazon-rds/index.md)
- [Azure DB for MySQL](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/mysql/azure-db/index.md)
- [Google Cloud SQL for MySQL](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/mysql/google-cloud-sql/index.md)
- [Self-hosted MySQL](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/mysql/self-hosted/index.md)

### Connect your source database to Materialize

Create a connection to your MySQL database using the [`CREATE CONNECTION` syntax](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-connection/index.md).

## Create a source

In Materialize, create a source using the [`CREATE SOURCE`
syntax](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-source/mysql-v2/index.md).

```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](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/index.md#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](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/index.md#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;
```

## Handle upstream constraint drop

> **Public Preview:** This feature is in public preview.


Materialize ignores foreign key and `CHECK` constraints. As such, you can add or
drop them without affecting ingestion. To handle changes in `PRIMARY KEY`,
`UNIQUE`, and `NOT NULL` constraints, follow the steps below.

### A. Exclude the constraint in Materialize

To drop a `PRIMARY KEY`, `UNIQUE`, or `NOT NULL` constraint, first create a new
`v4` schema in Materialize and recreate table `t1` in the new schema, excluding
the constraint you intend to drop. In this example, we'll drop the primary key.

MySQL identifies `PRIMARY KEY` and `UNIQUE` constraints by their index name. The
primary key's index is always named `PRIMARY`, and a `UNIQUE` constraint uses
the name of its index. The constraint name is a string literal and must match
the upstream index name exactly, including case. To list the index names of a
table, run `SHOW INDEX FROM t1;` in your MySQL database.

```mzsql
CREATE SCHEMA v4;

CREATE TABLE v4.t1
    FROM SOURCE my_source (REFERENCE mydb.t1) WITH (EXCLUDE CONSTRAINTS ('PRIMARY'));
```

Materialize does not record the excluded constraint as a key of `v4.t1`.

#### Excluding nullability constraints

`EXCLUDE CONSTRAINTS` only accepts `PRIMARY KEY` and `UNIQUE` constraint
names. Individual `NOT NULL` constraints cannot be excluded by name. To drop
`NOT NULL` constraints safely, use `EXCLUDE ALL NOT NULL CONSTRAINTS`:

```mzsql
CREATE SCHEMA v4;

CREATE TABLE v4.t1
    FROM SOURCE my_source (REFERENCE mydb.t1) WITH (EXCLUDE ALL NOT NULL CONSTRAINTS);
```

Note that when excluding `NOT NULL` constraints:

- Primary key columns remain `NOT NULL`, unless the primary key is also
  excluded with `EXCLUDE CONSTRAINTS`.
- Columns in a `UNIQUE` constraint become nullable. This means Materialize no
  longer uses that constraint as a key when optimizing queries, which might
  reduce query performance.
- `PRIMARY KEY` and `UNIQUE` constraints are still recorded. Dropping one
  upstream still puts the table into an error state unless it is also
  excluded:

  ```mzsql
  CREATE TABLE v4.t1
      FROM SOURCE my_source (REFERENCE mydb.t1)
      WITH (EXCLUDE ALL NOT NULL CONSTRAINTS, EXCLUDE CONSTRAINTS ('PRIMARY'));
  ```

#### Excluding all constraints

To record no constraints at all, use `EXCLUDE ALL CONSTRAINTS`: the table has
no keys and every column is nullable, so any later constraint drop is safe.
Queries that relied on those keys for optimization may run slower.

```mzsql
CREATE TABLE v4.t1
    FROM SOURCE my_source (REFERENCE mydb.t1) WITH (EXCLUDE ALL CONSTRAINTS);
```

> **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 constraint in your upstream MySQL database

In your upstream MySQL database, drop the primary key from table `t1`:

```sql
ALTER TABLE t1 DROP PRIMARY KEY;
```

Dropping the primary key will have no effect on `v4.t1` in Materialize, provided
you completed step A before dropping it. However, the drop affects `v3.t1` from
our earlier example, which still records the primary key as a key. When you
attempt to read from it, Materialize will report an error that the constraint
was dropped upstream.

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

```mzsql
DROP TABLE v3.t1;
DROP SCHEMA v3;
```