Continued on the problem of Canal dynamicTopic

Recently, I set up a set of canal in a new company. I set canal.mq.topic and canal.mq.dynamicTopic according to < < dynamictopic question > >

The intention is to send some messages that do not match the dynamic topic matching statements to a default topic and avoid an invalid topic exception

# table regex
canal.instance.filter.regex=fanboshi\\..*,sysbench\\..*
# table black regex
canal.instance.filter.black.regex=.*\\.\\_.*\\_ghc,.*\\.\\_.*\\_gho,.*\\.\\_.*\\_del
# table field filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.field=test1.t_product:id/subject/keywords,test2.t_company:id/name/contact/ch
# table field black filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
#canal.instance.filter.black.field=test1.t_product:subject/product_image,test2.t_company:id/name/contact/ch

# mq config
canal.mq.topic=default_topic
# dynamic topic route by schema or table regex
#canal.mq.dynamicTopic=mytest1.user,mytest2\\..*,.*\\..*
canal.mq.dynamicTopic=.*\\..*
#canal.mq.partition=0
# hash partition config
#canal.mq.partitionsNum=3
#canal.mq.partitionHash=test.table:id^name,.*\\..*

A few days ago, my colleague said that dynamicTopic had encountered a bug. Today, I am preparing to test some sentences by myself, so I want to see whether the default Topic can consume messages (if it is consumed, it means that the statements I execute cannot be matched by dynamicTopic rules and sent to this "default Topic")

A large number of messages were found

{"data":null,"database":"","es":1583581779000,"id":655469,"isDdl":false,"mysqlType":null,"old":null,"pkNames":null,"sql":"UPDATE sbtest1 SET c=? WHERE id=?","sqlType":null,"table":"sbtest1","ts":1583581779149,"type":"QUERY"}
{"data":null,"database":"","es":1583581779000,"id":655469,"isDdl":false,"mysqlType":null,"old":null,"pkNames":null,"sql":"DELETE FROM sbtest8 WHERE id=?","sqlType":null,"table":"sbtest8","ts":1583581779149,"type":"QUERY"}
{"data":null,"database":"","es":1583581779000,"id":655469,"isDdl":false,"mysqlType":null,"old":null,"pkNames":null,"sql":"INSERT INTO sbtest8 (id, k, c, pad) VALUES (?, ?, ?, ?)","sqlType":null,"table":"sbtest8","ts":1583581779149,"type":"QUERY"}
{"data":null,"database":"","es":1583581779000,"id":655470,"isDdl":false,"mysqlType":null,"old":null,"pkNames":null,"sql":"insert into fanboshi.monitor_delay(ctime) values(now())","sqlType":null,"table":"monitor_delay","ts":1583581779465,"type":"QUERY"}
{"data":null,"database":"","es":1583581780000,"id":655471,"isDdl":false,"mysqlType":null,"old":null,"pkNames":null,"sql":"insert into fanboshi.monitor_delay(ctime) values(now())","sqlType":null,"table":"monitor_delay","ts":1583581780570,"type":"QUERY"}
{"data":null,"database":"","es":1583581781000,"id":655472,"isDdl":false,"mysqlType":null,"old":null,"pkNames":null,"sql":"insert into fanboshi.monitor_delay(ctime) values(now())","sqlType":null,"table":"monitor_delay","ts":1583581781475,"type":"QUERY"}

At first, I was a little confused. Later, I found that these statements are actually in the format of "statement", which is the original statement

So I searched github

https://github.com/alibaba/canal/issues/1361

Refer to the explanation in the FAQ: https://github.com/alibaba/canal/wiki/FAQ

Question 1: is INSERT/UPDATE/DELETE resolved to a Query or DDL statement?

Answer 1:
The main reason for this is that the binlog received is a Query event, for example:

1. Binlog format is non row mode, which can be viewed through show variables like 'binlog [format'. For statement/mixed mode, DML statements will exist as SQL statements
2. After mysql5.6 +, when binlog is row mode, a switch (binlog rows query log events = true, which can also be seen in show variables) is used to record the original SQL of DML. The binlog event is RowsQueryLogEvent, and there are row records. ps. canal can be filtered through the properties setting: can.instance.filter.query.dml = true

I'm sure it's row format here. Maybe it's the reason why binlog? Rows? Query? Log? Events = 1. It's really set to on

As for what the author said

ps. canal can be filtered through the properties setting: canal.instance.filter.query.dml = true

https://github.com/alibaba/canal/wiki/AdminGuide

This explanation is not very clear. It doesn't matter what the filter is. Anyway, it's unnecessary

Now I wonder if the problem my colleague encountered was because binlog > rows > query > log > events was enabled