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:

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

If you don't see one or more tables reflected in Amazon Redshift, you can run the following command to resynchronize them:

ALTER DATABASE dbname INTEGRATION REFRESH TABLES table1, table2;

For more information, see ALTER DATABASE in the Amazon Redshift SQL reference.

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.

ALTER TABLE table_name
  ADD COLUMN column_name INTEGER
  NOT NULL first;

ALTER TABLE table_name
  ADD COLUMN column_name column_type;

ALTER TABLE table_name
  ADD COLUMN column_name TIMESTAMP
  NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE table_name
  ADD COLUMN column_1,
  RENAME COLUMN column_2 TO column_3;