Debezium MYSQL(Aurora) Connector not working.

[Pranav Lakhani]

Hi Team,

Everything was working with MSK and Debezium with Mysql Aurora till now. before 2 day, we have started to add one new more table , and its not working properly and throwing the error :   "Encountered change event for table whose schema isn't known to this connector"

We have tried lot many solution but nothing work for us, Strange thing for us is below, we have added only one table but each time in history table there are few table entry (8 entries) inserted and it stopped, working with above error. we have tried below.

1. Created new connector.

2. Delete History topics.

3. Tried with multiple options.

Scenario is like that, some time connector show UP status and whenever any change event fired, it show "Encountered change event for table whose schema isn't known to this connector" and connector is stopped, when we have added.inconsistent.schema.handling.mode as "warn" connector is not down anymore. it show keep up and running but events are also not refelected in topic as well below is my connector conf.

Weired is that, no new table is working, existing all things working fine , if i add different database schema table it is working. 

{
  "connector.class": "io.debezium.connector.mysql.MySqlConnector",
  "snapshot.locking.mode": "none",
  "errors.log.include.messages": "true",
  "topic.creation.default.partitions": "1",
  "tasks.max": "1",
  "database.history.consumer.sasl.jaas.config": "org.apache.kafka.common.security.scram.ScramLoginModule required username=\"xxxx\" password=\"gSJsdsdsdsdsksdszj\";",
  "database.history.kafka.topic": "xxx-history.sxxx-account-table-history",
  "bootstrap.servers": "b-2.dev-msk-cluster.cic8o1.c9.kafka.us-east-1.amazonaws.com:9096,b-1.dev-msk-cluster.cic8o1.c9.kafka.us-east-1.amazonaws.com:9096",
  "database.history.consumer.security.protocol": "SASL_SSL",
  "table.whitelist": "xxxx.tbl_account",
  "tombstones.on.delete": "true",
  "schema.history.internal.kafka.topic": "xxxx-history.speed-account-schmea-history",
  "snapshot.new.tables": "parallel",
  "topic.creation.default.replication.factor": "2",
  "database.history.skip.unparseable.ddl": "true",
  "value.converter": "org.apache.kafka.connect.json.JsonConverter",
  "errors.log.enable": "true",
  "database.whitelist": "speed",
  "key.converter": "org.apache.kafka.connect.json.JsonConverter",
  "database.allowPublicKeyRetrieval": "true",
  "database.history.producer.sasl.mechanism": "SCRAM-SHA-512",
  "database.history.producer.sasl.jaas.config": "org.apache.kafka.common.security.scram.ScramLoginModule required username=\"xxxx\" password=\"gSJxxxxxxxx\";",
  "database.user": "xxxxx_debezium",
  "database.server.id": "87945",
  "database.history.producer.security.protocol": "SASL_SSL",
  "database.history.kafka.bootstrap.servers": "b-2.dev-msk-cluster.cic8o1.c9.kafka.us-east-1.amazonaws.com:9096,b-1.dev-msk-cluster.cic8o1.c9.kafka.us-east-1.amazonaws.com:9096",
  "event.deserialization.failure.handling.mode": "warn",
  "database.server.name": "xxxx-tbl-account",
  "schema.history.internal.skip.unparseable.ddl": "false",
  "database.port": "4307",
  "inconsistent.schema.handling.mode": "skip",
  "autoReconnect": "true",
  "gtid.new.channel.position": "latest",
  "gtid.source.filter.dml.events": "true",
  "key.converter.schemas.enable": "false",
  "database.hostname": "aurora-cluster-stage.cluster-cxxxxxxg9yht.us-east-1.rds.amazonaws.com",
  "database.password": "xxxxxxxxxx",
  "value.converter.schemas.enable": "false",
  "name": "xxxxx-account-table-1",
  "table.include.list": "xxxx.tbl_account",
  "database.history.consumer.sasl.mechanism": "SCRAM-SHA-512",
  "snapshot.mode": "initial",
  "ddl.parser.mode": "antlr",
  "database.include.list": "xxxxx"
}

[Chris Cranford]

Hi Pranav,

What is the process you're taking when adding a new table to the connector?  Are you simply adding it to the "table.include.list" and letting the connector restart?  I see you also have set "snapshot.new.tables", what version of Debezium are you on?

Chris

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/204bea19-fe2b-4a43-9f12-faa83df64070n%40googlegroups.com.

[Pranav Lakhani]

Hi Chris,

Debezium Version from ECS Service:

FROM confluentinc/cp-kafka-connect:7.0.1
RUN confluent-hub install --no-prompt debezium/debezium-connector-mysql:1.8.1

Weird scenario, we have created new kafka connect as well and recreated connector and facing same issue. just for hint , recently we have downgraded the aurora version from 8 to 5.7 due to aurora issue with kafka and that time we have recreated all connector with same kafka broker. is it something create problem ?

As well as when adding any new table , there is 8 records which each time appear in all history table , and prevent to further logging message and failed with above error. I am providing that records over here , if it is useful for debug purpose

{
  "source": {
    "server": "speed-xxxx"
  },
  "position": {
    "ts_sec": 1673524018,
    "file": "mysql-bin-changelog.000071",
    "pos": 1985820,
    "gtids": "bf9f5e88-a5b9-314e-b25e-d3bcd1270b17:1-41872",
    "snapshot": true
  },
  "databaseName": "",
  "ddl": "SET character_set_server=latin1, collation_server=latin1_swedish_ci",
  "tableChanges": []
}


{
  "source": {
    "server": "speed-xxxx"
  },
  "position": {
    "ts_sec": 1673524018,
    "file": "mysql-bin-changelog.000071",
    "pos": 1985820,
    "gtids": "bf9f5e88-a5b9-314e-b25e-d3bcd1270b17:1-41872",
    "snapshot": true
  },
  "databaseName": "speed",
  "ddl": "DROP TABLE IF EXISTS `xxxx`.`tbl_user-xxxx`",
  "tableChanges": []
}

{
  "source": {
    "server": "speed-xxxx"
  },
  "position": {
    "ts_sec": 1673524018,
    "file": "mysql-bin-changelog.000071",
    "pos": 1985820,
    "gtids": "bf9f5e88-a5b9-314e-b25e-d3bcd1270b17:1-41872",
    "snapshot": true
  },
  "databaseName": "speed",
  "ddl": "DROP TABLE IF EXISTS `speed`.`tbl_user_link-xxxxxx`",
  "tableChanges": []
}

{
  "source": {
    "server": "speed-xxxx"
  },
  "position": {
    "ts_sec": 1673524018,
    "file": "mysql-bin-changelog.000071",
    "pos": 1985820,
    "gtids": "bf9f5e88-a5b9-314e-b25e-d3bcd1270b17:1-41872",
    "snapshot": true
  },
  "databaseName": "speed",
  "ddl": "DROP DATABASE IF EXISTS `speed-xxxx`",
  "tableChanges": []
}


{
  "source": {
    "server": "speed-xxxx"
  },
  "position": {
    "ts_sec": 1673524018,
    "file": "mysql-bin-changelog.000071",
    "pos": 1985820,
    "gtids": "bf9f5e88-a5b9-314e-b25e-d3bcd1270b17:1-41872",
    "snapshot": true
  },
  "databaseName": "speed",
  "ddl": "CREATE DATABASE `speed-xxxx` CHARSET latin1 COLLATE latin1_swedish_ci",
  "tableChanges": []
}

{
  "source": {
    "server": "speed-xxxx"
  },
  "position": {
    "ts_sec": 1673524018,
    "file": "mysql-bin-changelog.000071",
    "pos": 1985820,
    "gtids": "bf9f5e88-a5b9-314e-b25e-d3bcd1270b17:1-41872",
    "snapshot": true
  },
  "databaseName": "speed-xxx",
  "ddl": "USE `speed`",
  "tableChanges": []
}

{
  "source": {
    "server": "speed-xxxx"
  },
  "position": {
    "ts_sec": 1673524018,
    "file": "mysql-bin-changelog.000071",
    "pos": 1985820,
    "gtids": "bf9f5e88-a5b9-314e-b25e-d3bcd1270b17:1-41872",
    "snapshot": true
  },
  "databaseName": "speed",
  "ddl": "CREATE TABLE `tbl_xxx_user` (\n  `id` varchar(40) xxxxxxxx  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci",
  "tableChanges": []
}

{
  "source": {
    "server": "speed-xxxx"
  },
  "position": {
    "ts_sec": 1673524018,
    "file": "mysql-bin-changelog.000071",
    "pos": 1985820,
    "gtids": "bf9f5e88-a5b9-314e-b25e-d3bcd1270b17:1-41872",
    "snapshot": true
  },
  "databaseName": "speed",
  "ddl": "CREATE TABLE `tbl_xxx_link xxxxx  f8mb4_0900_ai_ci",
  "tableChanges": []
}

Regards, Pranav

[jiri.p...@gmail.com]

Hi,

what if you remove inconsistent.schema.handling.mode option?

J.

[Pranav Lakhani]

Hi,

If I remove that option it failed with same error. and same with "fail" Option.

if I set "skip" or "warn" , Connector is not anymore failed with any error but topic is also not having any events as well.

Regards, Pranav