CREATE SOURCE: MySQL (New Syntax)

Creates a new source from MySQL. Materialize supports creating sources from MySQL version 8.0.1+. Once a new source is created, you can CREATE TABLE FROM SOURCE to create the corresponding tables in Materialize and start the data ingestion process.

Prerequisites

To create a source from MySQL(8.0.1+), you must first:

• Configure upstream MySQL instance
  • Enable GTID-based binary log(binlog) replication. You must set binlog_row_metadata=FULL to use the new CREATE SOURCE syntax.
  • Create a replication user and password for Materialize to use to connect.
• Configure network security
  • Ensure Materialize can connect to your MySQL instance.
• Create a connection to MySQL in Materialize
  • The connection setup depends on the network security configuration.

Syntax

To create a source from an external MySQL database:

CREATE SOURCE [IF NOT EXISTS] <source_name>
[IN CLUSTER <cluster_name>]
FROM MYSQL CONNECTION <connection_name>
[WITH ( <with_option> [, ...] )]
;

Ingesting data

After a source is created, you can create tables from the source referencing upstream MySQL tables that have GTID-based binlog replication enabled (Note: binlog_row_metadata=FULL is required to use the new syntax). You can create multiple tables that reference the same upstream table. See CREATE TABLE FROM SOURCE for details.

Handling table schema changes

The use of CREATE SOURCE with the new CREATE TABLE FROM SOURCE allows for the handling of certain upstream schema changes, specifically adding or dropping columns in the upstream tables, without downtime.

See Handle upstream schema changes for details.

See also Handling upstream operations for additional upstream operation considerations.

Supported types

With the new syntax, after a MySQL source is created, you CREATE TABLE FROM SOURCE to create a corresponding table in Materialize and start ingesting data.

Materialize natively supports the following MySQL types:

• bigint
• binary
• bit
• blob
• boolean
• char
• date
• datetime
• decimal
• double
• float
• int
• json
• longblob
• longtext
• mediumblob
• mediumint
• mediumtext
• numeric
• real
• smallint
• text
• time
• timestamp
• tinyblob
• tinyint
• tinytext
• varbinary
• varchar

When replicating tables that contain the unsupported data types, you can:

• Use TEXT COLUMNS option for the following unsupported MySQL types:

  • enum
  • year

  The specified columns will be treated as text and will not offer the expected MySQL type features.

• Use the EXCLUDE COLUMNS option to exclude any columns that contain unsupported data types.

Zero values for date, datetime, and timestamp

MySQL allows the special “zero” values 0000-00-00, 0000-00-00 00:00:00 in date, datetime, and timestamp columns when the server sql_mode does not include NO_ZERO_DATE or NO_ZERO_IN_DATE. These values are not representable in Materialize’s corresponding native types, so they will cause ingestion to fail for the affected column.

To ingest columns that contain zero values, use TEXT COLUMNS to decode the affected columns as text. The zero values for date, datetime, timestamp, and year are preserved verbatim as strings (e.g. "0000-00-00 00:00:00", "0000").

For more information, including strategies for handling unsupported types, see CREATE TABLE FROM SOURCE.

Change data capture

The source uses MySQL’s binlog replication protocol to continually ingest changes resulting from INSERT, UPDATE and DELETE operations in the upstream database. This process is known as change data capture.

The replication method used is based on global transaction identifiers (GTIDs), and guarantees transactional consistency — any operation inside a MySQL transaction is assigned the same timestamp in Materialize, which means that the source will never show partial results based on partially replicated transactions.

Before creating a source in Materialize, you must configure the upstream MySQL database for GTID-based binlog replication:

If you’re running MySQL using a managed service, additional configuration changes might be required. To enable GTID-based binlog replication for your MySQL service, see the integration guides.

Binlog retention

By default, MySQL retains binlog files for 30 days (i.e., 2592000 seconds) before automatically removing them. This is configurable via the binlog_expire_logs_seconds system variable. We recommend using the default value for this configuration in order to not compromise Materialize’s ability to resume replication in case of failures or restarts.

In some MySQL managed services, binlog expiration can be overridden by a service-specific configuration parameter. It’s important that you double-check if such a configuration exists, and ensure it’s set to the maximum interval available.

As an example, Amazon RDS for MySQL has its own configuration parameter for binlog retention (binlog retention hours) that overrides binlog_expire_logs_seconds and is set to NULL by default.

Monitoring source progress

By default, MySQL sources expose progress metadata as a subsource that you can use to monitor source ingestion progress. The name of the progress subsource can be specified when creating a source using the EXPOSE PROGRESS AS clause; otherwise, it will be named <src_name>_progress.

The following metadata is available for each source as a progress subsource:

And can be queried using:

SELECT transaction_id
FROM <src_name>_progress;

Progress metadata is represented as a GTID set of future possible GTIDs, which is similar to the gtid_executed system variable on a MySQL replica. The reported transaction_id should increase as Materialize consumes new binlog records from the upstream MySQL database. For more information, see Troubleshooting.

Handling upstream operations

This section describes how changes to upstream tables that Materialize ingests affect the corresponding Materialize tables.

Adding a column

When you add a new column to your upstream table, Materialize continues to ingest only the existing columns.

To incorporate the new column:

• If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, create a new table from the source. See Handle upstream column addition.

• If using the legacy CREATE SOURCE ... FOR ... syntax that creates subsources, use DROP SOURCE to drop the affected subsource, and then add the table back to the source using ALTER SOURCE ... ADD SUBSOURCE. The re-added subsource includes the new column.

Dropping a column

Dropping columns that Materialize does not ingest (for example, columns added after the source was created, or columns that are excluded) is supported. As these columns were never ingested, you can drop them without issue.

If your Materialize source ingests a column, dropping that column from your upstream table puts the affected table into an error state.

• If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, you can safely drop a column by first ignoring it in Materialize. See Handle upstream column drop.

• If using legacy CREATE SOURCE ... FOR ... syntax, use DROP SOURCE to drop the affected subsource, and then add the table back to the source using ALTER SOURCE ... ADD SUBSOURCE.

Changing constraints

Materialize ignores the following constraint changes: foreign key and CHECK. As such, you can add or drop them without affecting ingestion.

Materialize also ignores NOT NULL, UNIQUE, and PRIMARY KEY constraints that are added after the Materialize table is created (that is, the table was created without them). Adding such a constraint, and later dropping it, does not affect ingestion.

Dropping a NOT NULL, UNIQUE, or PRIMARY KEY constraint that existed when the table was created puts the affected table into an error state.

Changing a column’s data type

Changing an ingested column’s data type upstream so that it maps to a different Materialize type than before puts the affected Materialize table into an error state. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Changing an ingested column’s upstream data type so that it continues to map to the same Materialize type does not interrupt ingestion. For example, changing tinyint to smallint, changing within the text/tinytext/mediumtext/longtext family, and adjusting bit(n) precision are all safe.

Appending new values to the end of an existing enum does not put the table into an error state. However, the newly-added values are not recognized, so rows that use them fail to decode until you drop and recreate the table. Existing enum values remain recognized, and rows that use them continue to decode successfully.

Any other enum change puts the affected Materialize table into an error state, including inserting a value before the end, reordering or renaming values, and removing values.

Renaming a column

Renaming a column that Materialize ingests puts the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Table-level operations

The following upstream operations put the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the affected table in Materialize to resume:

• Dropping a table (DROP TABLE).
• Renaming a table or moving it to a different schema.
• Truncating a table (TRUNCATE). To clear a table without putting it into an error state, use an unqualified DELETE FROM t; instead.

Source failure states and recovery

Operations that do not require re-creating the source

Materialize tracks its position in the upstream binary log as a set of global transaction identifiers (GTIDs), which it persists alongside the ingested data. A GTID identifies a transaction across the whole replication topology, not a byte offset in a particular binlog file, so it survives routine operational events: after an interruption the source reconnects, asks the server for the transactions after its last committed GTID, and catches up.

The source recovers on its own. It briefly reports a stalled status while the condition persists, then returns to running and catches up for all of the following:

• Restarting or patching MySQL (including OS-level restarts).
• Restarting Materialize. The source resumes from its tracked GTID set and does not re-snapshot already-ingested data.
• Transient network interruptions between Materialize and MySQL.
• The upstream server running out of disk space, until space is reclaimed.
• A long-running upstream transaction blocking the initial snapshot. The source stalls until that transaction commits or rolls back.
• Failing over to a replica, with the checks described below.

Operations that require re-creating the source

A smaller set of events breaks GTID continuity or makes the binlog stream unreadable. Materialize cannot then guarantee a correct, gap-free view of your data, so it puts the entire source into an error state that requires re-creating the source. Upstream changes to an individual table’s schema are handled separately, and do not error the entire source.

In each case, the remediation is to drop the source and create it again with the statements you originally used, which triggers a fresh snapshot:

DROP SOURCE mz_source CASCADE;

Once the source is back, it is healthy when status is running and error is NULL:

SELECT status, error FROM mz_internal.mz_source_statuses WHERE name = 'mz_source';

Binlog files removed before the resume point

MySQL expires binlog files on a retention schedule, and PURGE BINARY LOGS removes them on demand. If Materialize is disconnected or lagging long enough that the files holding its resume point are removed, the source fails with one of:

mysql server does not have the binlog available at the requested gtid set

mysql server binlog frontier at <frontier> is beyond required frontier <frontier>

To avoid this, keep planned outages shorter than the retention window and monitor source lag against it. For how retention is configured, including the service-specific parameters that override it, see Binlog retention.

Resetting the binary log

RESET BINARY LOGS AND GTIDS (RESET MASTER before MySQL 8.4) discards the binlog files and the server’s GTID history, so the source’s resume point no longer exists. The source fails with:

mysql server does not have the binlog available at the requested gtid set

or, if the server then reissues GTIDs the source has already seen, with an out-of-order GTID error.

Changing a required replication setting

Materialize re-validates the upstream replication settings each time it (re-)establishes the replication stream. If one no longer holds its required value, the source fails with:

mysql server configuration: invalid mysql system setting '<setting>'. Expected '<expected>'. Got '<actual>'.

The validated settings are log_bin, binlog_format, binlog_row_image, gtid_mode, enforce_gtid_consistency, gtid_next, and, when replica_parallel_workers is greater than 1, replica_preserve_commit_order (slave_preserve_commit_order on servers older than MySQL 8.0). For the required values, see Change data capture. Restore the setting upstream, then re-create the source.

Restoring the upstream database

Materialize has no dedicated check for a restore of the upstream database. Depending on how the restore handles GTIDs, the source either fails with one of the errors above or with:

received a gtid set from the server that violates our requirements: <detail>

or, if the restore reuses GTIDs the source has already ingested, it keeps replicating against a history that no longer matches its state. Materialize cannot detect that case, so re-create the source after any restore of the upstream database, including a disaster-recovery restore onto a new server, rather than relying on an error.

Out-of-order GTIDs

If Materialize observes GTIDs in an order it cannot reconcile, the source fails with:

received out of order gtids for source <source_id> at transaction-id <transaction_id>

This is most common when Materialize replicates from a MySQL replica that applies transactions with multiple threads. See Troubleshooting: Received out of order GTIDs for the diagnosis steps and the upstream settings that make it less likely.

Operations that require re-creating only the affected tables

Lowering binlog_row_metadata

A table that Materialize began ingesting while binlog_row_metadata was FULL can no longer be decoded once the setting is lowered, and enters an error state with:

unable to decode: Table <table> was created with binlog_row_metadata=FULL but
binlog_row_metadata has since been set to a different value, meaning we cannot
reliably decode the columns

Restoring binlog_row_metadata=FULL does not clear the error. Set it back to FULL and re-create the affected tables. Tables that were created while the setting was lower keep replicating.

Failovers

Because Materialize replicates by GTID rather than by binlog file and position, it can follow a failover to a replica that was replicating from the failed server with GTIDs enabled. Point the connection at the new primary with ALTER CONNECTION, or at an endpoint that always resolves to the current primary.

On the new primary, confirm that:

• The binlog files covering the source’s resume point are present. A replica configured with a shorter retention than the old primary can leave the source with no way to resume. See Binlog files removed before the resume point.
• The required replication settings hold, including replica_preserve_commit_order while the server is still applying replication from another server. Multi-threaded apply is the main source of out-of-order GTIDs; preserving commit order reduces but does not eliminate the risk, so keep replica_parallel_workers at 0 or 1 where you can.

Example

Prerequisites

To create a source from MySQL(8.0.1+), you must first:

• Configure upstream MySQL instance
  • Enable GTID-based binary log(binlog) replication. You must set binlog_row_metadata=FULL to use the new CREATE SOURCE syntax.
  • Create a replication user and password for Materialize to use to connect.
• Configure network security
  • Ensure Materialize can connect to your MySQL instance.
• Create a connection to MySQL in Materialize
  • The connection setup depends on the network security configuration.

For details, see the MySQL integration guides.

Create a source

Once you have configured the upstream MySQL, network security, and created the connection to MySQL, you can create the source. In this example, assume the connection you created is named mysql_connection.

CREATE SOURCE mysql_source
FROM MYSQL CONNECTION mysql_connection;

After a source is created, you can create a table from the source, referencing specific upstream table(s). Use a DDL transaction block to create multiple tables from the same source.

BEGIN;
CREATE TABLE items
FROM SOURCE mysql_source (REFERENCE mydb.items);

CREATE TABLE orders
FROM SOURCE mysql_source (REFERENCE mydb.orders);
COMMIT;

Related pages

• CREATE TABLE
• CREATE SECRET
• CREATE CONNECTION
• CREATE SOURCE
• MySQL integration guides