<@U04S8PLJWMT> We are running into issues where a ...
# ask-ai
j
@kapa.ai We are running into issues where a MySQL table isn’t capturing all of the CDC changes into Snowflake. We occasionally have to do a full refresh of certain streams. Why would this be occurring and where should we look to resolve this? We have these optional parameters on the MySQL source: useCursorFetch=true&defaultFetchSize=1000 Checkpoint Target Time Interval = 300 We are using MySQL v3.50.9 source and Snowflake v4.0.4 destination
k
The most likely causes, based on the docs and issues, fall into a few categories: ### 1. Binlogs needed for CDC were removed If the binlog position stored in Airbyte’s state is no longer available on the MySQL server, the connector must re-snapshot and you’ll see full refresh behavior. [[MySQL troubleshooting](https://docs.airbyte.com/integrations/sources/mysql/mysql-troubleshooting#troubleshooting)] From the MySQL connector docs: > “Saved offset no longer present on the server, Airbyte is going to trigger a sync from scratch” This happens when binlogs expire before the next sync: - High write volume → logs roll over quickly. -
binlog_expire_logs_seconds
(or RDS
binlog retention hours
) too low. [[MySQL troubleshooting](https://docs.airbyte.com/integrations/sources/mysql/mysql-troubleshooting#troubleshooting)] Where to look / what to do - Check MySQL server settings for: -
binlog_expire_logs_seconds
(standard MySQL) - or
binlog retention hours
(RDS/Aurora) - Increase retention (e.g. 7 days is recommended in docs) and/or: - Run the Airbyte sync more frequently so binlogs aren’t purged before they’re read. [[MySQL troubleshooting](https://docs.airbyte.com/integrations/sources/mysql/mysql-troubleshooting#troubleshooting)] If those logs were purged between syncs, Airbyte will re-snapshot the table and may miss intermediate changes unless you do a manual backfill. ### 2. General CDC limitations / expectations CDC in Airbyte: - Uses “at least once” delivery for change records, not exactly-once. [[MySQL troubleshooting](https://docs.airbyte.com/integrations/sources/mysql/mysql-troubleshooting)] - For MySQL, requires a primary key to use CDC incremental; tables without PK can only be replicated by CDC in Full Refresh mode. [[MySQL troubleshooting](https://docs.airbyte.com/integrations/sources/mysql/mysql-troubleshooting)] Also, CDC only captures `INSERT`/`UPDATE`/`DELETE` and will not capture changes made by
TRUNCATE
or some
ALTER
operations. [[CDC overview](https://docs.airbyte.com/platform/understanding-airbyte/cdc)] Where to look - Confirm affected tables have primary keys and are configured as Incremental + CDC in Airbyte. - Confirm application changes are normal DML and not truncations/reloads outside CDC. ### 3. MySQL-specific CDC issues & environment There are several open issues where: - Initial snapshot works, but subsequent CDC increments miss changes or don’t pick up any new records. [[Aurora issue](https://discuss.airbyte.io/t/3329); [Snowflake sync issue](https://github.com/airbytehq/airbyte/issues/53614)] - Some MySQL deployments have CDC problems depending on binlog-related configuration (e.g.,
log_bin_use_v1_row_events
). [[MySQL CDC issue](https://github.com/airbytehq/airbyte/issues/51607)] While those issues don’t document a definitive fix for your exact connector version (MySQL 3.50.9), they show: - Behavior like “first full refresh OK, later runs miss rows” is a known pattern. - Binlog configuration is important; reviewing all
log_bin*
/ binlog-related MySQL variables is recommended. [[MySQL CDC issue](https://github.com/airbytehq/airbyte/issues/51607)] Where to look - Inspect MySQL server variables: - All
log_bin*
settings, especially around how row events are written. - Check Airbyte sync logs for: - Messages about “Saved offset no longer present on the server”. - Debezium / CDC errors. - If possible, test the same connection with: - A slightly different MySQL source version (some users report differences between versions in CDC behavior). [[MySQL CDC issue](https://github.com/airbytehq/airbyte/issues/51607)] ### 4. Checkpoint and query timeout interaction You’re using: -
Checkpoint Target Time Interval = 300
(5 minutes) - Custom JDBC options:
useCursorFetch=true&defaultFetchSize=1000
The docs note that if MySQL’s
max_execution_time
is lower than the checkpoint interval, queries may time out, causing aborted or retried syncs. [[MySQL troubleshooting](https://docs.airbyte.com/integrations/sources/mysql/mysql-troubleshooting#troubleshooting)] Where to look - Check for
MySQL Query Timeout
errors in logs. - Ensure MySQL
max_execution_time
(ms) is greater than 300,000 ms. - If you see timeouts, either increase
max_execution_time
or reduce the checkpoint interval. --- ### Summary of next debugging steps 1. Inspect MySQL binlog retention and activity - Confirm binlogs covering the entire period between syncs are available. - Increase retention and/or sync frequency. 2. Review Airbyte logs for CDC messages - Specifically, look for “Saved offset no longer present” or Debezium / binlog errors. 3. Validate table configuration - Ensure affected tables have primary keys and are configured as Incremental + CDC. 4. Check MySQL server variables -
binlog_expire_logs_seconds
/
binlog retention hours
- Other
log_bin*
options that may alter row event format. 5. Check for query timeouts - Align MySQL
max_execution_time
with your
Checkpoint Target Time Interval
. If you can share (sanitized) snippets of the failing sync logs around where the snapshot ends and CDC begins, that usually pinpoints whether it’s a binlog-retention issue, a Debezium/binlog-format issue, or a timeout/connection problem.