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

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?

| username: Jolyne | Original post link

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

| 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.

| username: Jolyne | Original post link

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

| 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?

| username: Jolyne | Original post link

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

| 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.

| username: Jolyne | Original post link

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

| 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.

| 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
| username: 舞动梦灵 | Original post link

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

| username: wangccsy | Original post link

What about unsynchronized events?

| username: 江湖故人 | Original post link

This should also filter out the error messages you sent.

| 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.

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

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

| username: Jiawei | Original post link

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

| username: 舞动梦灵 | Original post link

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

| 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.

| username: Jiawei | Original post link

Here

| username: 舞动梦灵 | Original post link

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