Error in Creating Event for DM Synchronization with MySQL, How to Skip

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: DM同步mysql的创建event报错，怎么跳过

| username: 舞动梦灵

Brothers, how can I skip the SQL for the event created by MySQL in TiDB?

2
| username: Jolyne | Original post link

Judging by the error, it seems to be a DDL syntax compatibility issue.

3
| username: 舞动梦灵 | Original post link

When I execute the creation of an event in MySQL:
CREATE EVENT my_event_del1
ON SCHEDULE EVERY 1 MINUTE STARTS ‘2024-01-22 10:00:00’
xxx
TiDB does not support it and reports an error. How can I skip it?
I tried sql-skip but it didn’t work.

4
| username: Jolyne | Original post link

DM has a binlog event operation that can filter some operations.

5
| username: 舞动梦灵 | Original post link

Do you know how to find the configuration file? This was handed over by someone else. I couldn’t find the file with the filter rules. The DM version is 1.0.6. What should the file name be?

6
| username: Jolyne | Original post link

Check the DM configuration file to see if there are any binlog event-related configurations.

7
| username: 舞动梦灵 | Original post link

Can’t find the configuration file. Can this file be directly exported in DML? How to export it? I asked the previous DBA and he said it can be exported.

8
| username: Jolyne | Original post link

He is probably referring to this: 如何过滤 binlog 事件 | PingCAP 文档中心

9
| username: 舞动梦灵 | Original post link

I haven’t started this version 1.0.6. There were dozens before I took over. He mentioned export, but I checked and it seems like this feature is only available in the new version. The old version doesn’t have it.

10
| username: 江湖故人 | Original post link

Find a test environment to see if you can filter out MySQL events like this:

filters:
  rule-1:
    schema-pattern: "test_*"
    table-pattern: "t_*"
    events: ["all ddl"]
    sql-pattern: ["^CREATE.*EVENT"]
    action: Ignore
11
| username: 舞动梦灵 | Original post link

The filtering rules I found are written like this:
“^CREATE\s+DEFINER”,“^drop\s+event”
Is this the same?

12
| username: wangccsy | Original post link

What about unsynchronized events?

13
| username: 江湖故人 | Original post link

This should also filter out the error messages you sent.

14
| username: 舞动梦灵 | Original post link

I read the documentation and it says that version 1.0 is not supported. The error is related to the “parse statement” issue. SQL-skip is not supported.

15
| username: 这里介绍不了我 | Original post link

Take a look at this
Data Migration DDL Special Handling Instructions | PingCAP Documentation Center

16
| username: Jiawei | Original post link

Here it is suggested that you can filter it out through rules.

17
| username: 舞动梦灵 | Original post link

It suggests modifying the filter rule file, but this is a very old filter rule file that cannot be found.

18
| username: Jiawei | Original post link

Do you mean you can’t find the task configuration file?
I remember you can use tiup dm edit-config to edit the cluster online.
It seems that dmctl can also be used to view it. You can check the official documentation for more details on this.

19
| username: Jiawei | Original post link

Here

20
| username: 舞动梦灵 | Original post link

Version 1.0 was not installed using tiup. It was installed using ansible.