AWS Aurora MySQL Serverless v2 (MySQL v 8.0.23) CDC with Incremental | Deduped + History sync error

  • Is this your first time deploying Airbyte?: No
  • OS Version / Instance: AWS EC2 instance type t3.medium
  • Memory / Disk: 8Gb/30 GB
  • Deployment: Docker
  • Airbyte Version: 0.36.1-alpha
  • Source name/version: MySQL (0.5.11)
  • Destination name/version: Clickhouse (0.1.5)
  • Source name/version: AWS Aurora MySQL Serverless v2 (Compatible with MySQL 8.0.23)
  • Destination name/version: Clickhouse 22.1.3.7
  • Step: The issue is happening during sync and source and connectors are created successfully.
  • Description: I created a source for our recently migrated AWS RDs MySQL to AWS Aurora MySQL Serverless v2 with the Replication method of CDC and it connected successfully. Then I created a connection to our ClickHouse destination as you can see in the screenshot below and then hit sync. I faced the error logs attached. Do you know what’s the cause?

It seems the main error is this:

2022-05-11 14:13:22 source > 2022-05-11 14:13:22 INFO i.d.c.m.SnapshotReader(execute):448 - snapshot continuing with database(s): [crm_trips]

2022-05-11 14:13:22 source > 2022-05-11 14:13:22 INFO i.d.c.m.SnapshotReader(readBinlogPosition):906 - Step 5: read binlog position of MySQL primary server

2022-05-11 14:13:22 source > 2022-05-11 14:13:22 INFO i.d.c.m.SnapshotReader(lambda$readBinlogPosition$18):918 - using binlog ‘mysql-bin-changelog.000005’ at position ‘1272169’ and gtid ‘’

2022-05-11 14:13:22 source > 2022-05-11 14:13:22 INFO i.d.c.m.SnapshotReader(execute):494 - Step 6: generating DROP and CREATE statements to reflect current database schemas:

2022-05-11 14:13:24 source > 2022-05-11 14:13:24 INFO i.d.c.m.SnapshotReader(execute):754 - Step 7: rolling back transaction after abort

2022-05-11 14:13:24 source > 2022-05-11 14:13:24 ERROR i.d.c.m.AbstractReader(failed):219 - Failed due to error: Aborting snapshot due to error when last running ‘SHOW CREATE TABLE mysql.host’: Can’t find file: ‘host’ (errno: 2 - No such file or directory)

2022-05-11 14:13:24 source > org.apache.kafka.connect.errors.ConnectException: Can’t find file: ‘host’ (errno: 2 - No such file or directory) Error code: 1017; SQLSTATE: HY000.

logs-5889.txt (348.5 KB)


I also now tried to change the Replication Method to STANDARD and then reset the source and again synced, and get this error log this time:

logs-5891.txt (121.5 KB)

Hello Arash, you’re using Incremental+Dedup+history and the stream doesn’t have the cursor field defined, see log error:

2022-05-11 14:19:31 e[44msourcee[0m > Exception in thread "main" java.lang.IllegalStateException: No cursor field specified for stream attempting to do incremental.

If you standard method to sync instead of CDC it works?

logs-5898.txt (121.5 KB)
Hello, @marcosmarxm I tested the Standard method and it failed again.
You can see the logs above attached.

logs-5889.txt (348.5 KB)
@marcosmarxm Please note that the main log file for ncremental+Dedup+history is attached to this post and you mistakenly checked the other log file I attached for the Standard method instead of CDC. Please check this one.

As a separate question, I dug into the documentation to see if all configurations are correct.
Related to this section (MySQL | Airbyte Documentation) I observed that expire_logs_days is deprecated, but it’s still in your documentation: MySQL :: WL#10924: Deprecate expire_logs_days

Why it’s like that?

Thanks for the heads up Arash, I submitted the change here: https://github.com/airbytehq/airbyte/pull/12824

Marcos, What about my above posts on the issue? Would you please help us with this issue?

Arash the problems happens because your table can sync as incremental but Airbyte can’t identify the cursor field.

2022-05-11 14:19:31 e[44msourcee[0m > java.lang.IllegalStateException: No cursor field specified for stream attempting to do incremental.

Can I ask you to test run a full refresh to see if the sync works?

I’ll test spin up a Aurora Mysql v2 in my side to reproduce your error too

@marcosmarxm But the issue you mentioned is not in this log file which is related to incremental+Dedup+history
logs-5889.txt (348.5 KB)

Yes, it works for a full refresh. Only Incrementals do not work.

Arash sorry the long delay here. The error was threw by Debezium not finding a specific file.

2022-05-11 14:05:21 e[44msourcee[0m > Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: org.apache.kafka.connect.errors.ConnectException: Can't find file: 'host' (errno: 2 - No such file or directory) Error code: 1017; SQLSTATE: HY000.2022-05-11 14:05:21 e[44msourcee[0m > at io.airbyte.integrations.debezium.internals.DebeziumRecordIterator.requestClose(DebeziumRecordIterator.java:132)2022-05-11 14:05:21 e[44msourcee[0m > at io.airbyte.integrations.debezium.internals.DebeziumRecordIterator.computeNext(DebeziumRecordIterator.java:79)2022-05-11 14:05:21 e[44msourcee[0m > at io.airbyte.integrations.debezium.internals.DebeziumRecordIterator.computeNext(DebeziumRecordIterator.java:32)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:146)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:141)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.TransformedIterator.hasNext(TransformedIterator.java:46)2022-05-11 14:05:21 e[44msourcee[0m > at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.java:38)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:146)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:141)2022-05-11 14:05:21 e[44msourcee[0m > at io.airbyte.commons.util.CompositeIterator.computeNext(CompositeIterator.java:63)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:146)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:141)2022-05-11 14:05:21 e[44msourcee[0m > at io.airbyte.commons.util.CompositeIterator.computeNext(CompositeIterator.java:63)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:146)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:141)2022-05-11 14:05:21 e[44msourcee[0m > at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.java:38)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:146)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:141)2022-05-11 14:05:21 e[44msourcee[0m > at io.airbyte.commons.util.DefaultAutoCloseableIterator.computeNext(DefaultAutoCloseableIterator.java:38)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:146)2022-05-11 14:05:21 e[44msourcee[0m > at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:141)2022-05-11 14:05:21 e[44msourcee[0m > at java.base/java.util.Iterator.forEachRemaining(Iterator.java:132)

Not sure if it’s because a Aurora configuration because our integration test apply some of them using CDC enabled.

Thanks, Marcos. I shared this error of not finding a specific file in the first post of this thread: AWS Aurora MySQL Serverless v2 (MySQL v 8.0.23) CDC with Incremental | Deduped + History sync error

So what is the solution?

I didn’t find a proper solution at the moment Arash. Would be possible to you share how did you configure your CDC with Aurora Mysql so our team can reproduce with enviroment and apply same logic with our tests?
As a workaround you can sync data without CDC at the moment.

@marcosmarxm I created a custom Parameter Group based on default.aurora-mysql8.0 parameter group and changed the value of log_bin_trust_function_creators to 1.

Also, I created a DB cluster parameter group from default.aurora-mysql8.0 and overridden these values:

Then modified the cluster and instance to use these DB Parameter Group and DB cluster Parameter Group and then reboot the instance to use these parameter groups.

You can do these under the parameter groups section of RDS.

Let me know if you have any questions.

Thanks Arash, I’ll try to reproduce in my end.

Hi @marcosmarxm was the issue resolved? I’m trying to research if using Airbyte for Aurora Serverless v2 CDC is supported. However, my use case is Postgres, not MySQL.

Sorry Arash, I didn’t have time to investigate it. The Airbyte connectors is compatible with MySQL database, using with Aurora can’t be 100% compatible and you must debug any further issues.