Continued on the problem of Canal dynamicTopic

Continued on the problem of Canal dynamicTopic

Recently, I set up a set of canal in a new company. I set and 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
# table black regex*\\.\\_.*\\_ghc,.*\\.\\_.*\\_gho,.*\\.\\_.*\\_del
# table field filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)
# table field black filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2),test2.t_company:id/name/contact/ch

# mq config
# dynamic topic route by schema or table regex,mytest2\\..*,.*\\..**\\..*
# hash partition config^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

Refer to the explanation in the 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

canal.instance.filter.query.dml Whether to ignore DML statements (after mysql5.6, each DML statement will also record SQL into binlog in row mode, please refer to MySQL documentation) false

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

177 original articles published, 65 praised, 380000 visitors+
His message board follow

Keywords: SQL Database github MySQL

Added by limey on Sat, 07 Mar 2020 16:02:18 +0200