Troubleshooting Aurora zero-ETL integrations with Amazon Redshift - Amazon Aurora
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Troubleshooting Aurora zero-ETL integrations with Amazon Redshift

You can check the state of a zero-ETL integration by querying the SVV_INTEGRATION system table in Amazon Redshift. If the state column has a value of ErrorState, it means something's wrong. For more information, see Monitoring integrations using system tables.

Use the following information to troubleshoot common issues with Aurora zero-ETL integrations with Amazon Redshift.

I can't create a zero-ETL integration

If you can't create a zero-ETL integration, make sure that the following are correct for your source DB cluster:

  • Your source DB cluster is running Aurora MySQL version 3.05 (compatible with MySQL 8.0.32) or higher, or Aurora PostgreSQL (compatible with PostgreSQL 15.4 and Zero-ETL Support). To validate the engine version, choose the Configuration tab for the DB cluster and check the Engine version.

  • You correctly configured DB cluster parameters. If the required parameters are set incorrectly or not associated with the cluster, creation fails. See Step 1: Create a custom DB cluster parameter group.

In addition, make sure the following are correct for your target data warehouse:

My integration is stuck in a state of Syncing

Your integration might consistently show a status of Syncing if you change the value of one of the required DB parameters.

To fix this issue, check the values of the parameters in the parameter group associated with the source DB cluster, and make sure that they match the required values. For more information, see Step 1: Create a custom DB cluster parameter group.

If you modify any parameters, make sure to reboot the DB cluster to apply the changes.

My tables aren't replicating to Amazon Redshift

Your data might not be replicating because one or more of your source tables doesn't have a primary key. The monitoring dashboard in Amazon Redshift displays the status of these tables as Failed, and the status of the overall zero-ETL integration changes to Needs attention.

To resolve this issue, you can identify an existing key in your table that can become a primary key, or you can add a synthetic primary key. For detailed solutions, see the following resources:

One or more of my Amazon Redshift tables requires a resync

Running certain commands on your source DB cluster might require your tables to be resynchronized. In these cases, the SVV_INTEGRATION_TABLE_STATE system view shows a table_state of ResyncRequired, which means that the integration must completely reload data for that specific table from MySQL to Amazon Redshift.

When the table starts to resynchronize, it enters a state of Syncing. You don't need to take any manual action to resynchronize a table. While table data is resynchronizing, you can't access it in Amazon Redshift.

The following are some example operations that can put a table into a ResyncRequired state, and possible alternatives to consider.

Operation Example Alternative
Adding a column into a specific position
ALTER TABLE table_name ADD COLUMN column_name INTEGER NOT NULL first;
Amazon Redshift doesn't support adding columns into specific positions using first or after keywords. If the order of columns in the target table isn't critical, add the column to the end of the table using a simpler command:
ALTER TABLE table_name ADD COLUMN column_name column_type;
Adding a timestamp column with the default CURRENT_TIMESTAMP
ALTER TABLE table_name ADD COLUMN column_name TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
The CURRENT_TIMESTAMP value for existing table rows is calculated by Aurora MySQL and can't be simulated in Amazon Redshift without full table data resynchronization.

If possible, switch the default value to a literal constant like 2023-01-01 00:00:15 to avoid latency in table availability.

Performing multiple column operations within a single command
ALTER TABLE table_name ADD COLUMN column_1, RENAME COLUMN column_2 TO column_3;
Consider splitting the command into two separate operations, ADD and RENAME, which won't require resynchronization.