Blocking issue on MySQL → BigQuery sync: stuck after stream error, can't recover

[louistediber]

Hi everyone,

I’m running into a major issue with a MySQL → BigQuery sync that has been working perfectly for over a month, until something broke between May 28 and 29, and now the sync is completely unusable.

Here’s what happened :

• Syncs were running smoothly for weeks, with incremental mode enabled.
• Suddenly, all syncs started failing with this error :
  io.airbyte.cdk.TransientErrorException: Input was fully read, but some streams did not receive a terminal stream status message.
• The error lists ~30 streams (tables) that didn’t receive a "terminal stream status" even though the input was fully read.
• I’ve double-checked with our infra team : no change at all was made on the MySQL database or network configuration during that period.

What I tried :

• I created a new connection from scratch, using the same source/destination, and synced some small tables.
  → It worked perfectly.

My conclusion so far :

• This seems to confirm that the source and destination are healthy.
• The issue appears to come from Airbyte’s inability to recover from a single stream error. Once it breaks, the sync is stuck, even after retries.
• The only current solution seems to be deleting and recreating the whole connection, but that’s problematic:
  • If I recreate the connection, will Airbyte re-ingest everything, including historical data already present in BigQuery ?
  • This could lead to a huge cost spike due to full ingestion instead of only syncing new/changed/deleted rows.

My questions for the community :

• Has anyone encountered this behavior before ?
• Any advice to preserve the state or avoid full re-sync costs when recreating a connection ?
• How do you handle sync recovery after partial failures like this ?
• Any way to force Airbyte to reset just one stream, or clean up its state to move forward ?

This is becoming urgent as we rely on this sync in production. Any help or insight would be very appreciated 🙏

Thanks !
Louis

[Manula-Fernando]

🛠️ Issue 1: Missing Primary Keys in MySQL → BigQuery Sync

Problem: Destination tables in BigQuery lack primary keys, and manual configurations are overwritten during subsequent syncs.

Solution:

1. Verify Primary Key Presence:

   • Ensure that the source MySQL tables have defined primary keys.
   • Airbyte requires primary keys for efficient data synchronization.

2. Configure BigQuery Destination:

   • In the Airbyte UI, navigate to the destination settings.
   • Under "Advanced Settings," enable the option to "Use primary keys."
   • This ensures that Airbyte utilizes the primary keys during data insertion into BigQuery.

3. Prevent Overwrites:

   • To prevent Airbyte from overwriting manual configurations, consider setting the sync mode to "Append" instead of "Overwrite."
   • This approach adds new records without modifying existing ones.

4. Monitor Syncs:

   • After implementing these changes, monitor the syncs to ensure that primary keys are maintained and not overwritten.

---

🔄 Issue 2: Sync Failure with Error io.airbyte.cdk.TransientErrorException

Problem: Syncs fail with the error: Input was fully read, but some streams did not receive a terminal stream status message.

Solution:

1. Check MySQL Binlog Configuration:

   • Ensure that MySQL's binary logging is enabled and properly configured.
   • Airbyte relies on binlogs for Change Data Capture (CDC).([discuss.airbyte.io]1)

2. Adjust Debezium Settings:

   • Increase the schema.history.internal.timeout.ms parameter in Debezium's configuration.
   • This adjustment allows more time for schema history operations, reducing the likelihood of timeouts.([docs.airbyte.com]2, [discuss.airbyte.io]3)

3. Limit Table Access:

   • Restrict the Airbyte user to access only the necessary tables.
   • This practice prevents Airbyte from attempting to read unnecessary tables, which can lead to errors.([discuss.airbyte.io]3)

4. Recreate Connection:

   • If the issue persists, consider deleting and recreating the connection in Airbyte.
   • This action can resolve underlying configuration issues.

5. Monitor Syncs:

   • After implementing these changes, monitor the syncs to ensure successful data transfer without errors.

---

Additional Resources:

• [Airbyte MySQL Troubleshooting Guide](https://docs.airbyte.com/integrations/sources/mysql/mysql-troubleshooting)
• [Debezium Configuration Documentation](https://debezium.io/documentation/reference/)

If you need further assistance or have additional questions, feel free to ask!