DM：The right function is not supported as an expression index for synchronization downstream.

[jianglf4811]

What did you do?

1、mysql(v8.0.26): CREATE TABLE t1 ( id int NOT NULL, name varchar(30) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (id));

alter table t1 add index idx1((right(name,4)));

2、tidb(v8.5.3):
Already set allow-expression-index: true ,and can execute 'alter table t1 add index idx1((right(name,4)));' but only sync create table.

3、dm error:

"Message": "startLocation: [position: (binlog.000002, 9465), gtid-set: 813a9612-f03b-11eb-afe0-02f3bdd9099e:1-46], endLocation: [position: (binlog.000002, 9507), gtid-set: 813a9612-f03b-11eb-afe0-02f3bdd9099e:1-46]: gen insert sqls failed, sourceTable: test.t1, targetTable: test.t1: failed to mock downstream table by create table statement CREATE TABLE t1 (\n id int NOT NULL,\n name varchar(30) DEFAULT NULL,\n PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,\n KEY idx1 ((right(name, 4)))\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin in schema tracker",

"RawCause": "[ddl:8200]Unsupported creating expression index containing unsafe functions without allow-expression-index in config",

What did you expect to see?

Normal synchronization

What did you see instead?

dm error: "Message": "startLocation: [position: (binlog.000002, 9465), gtid-set: 813a9612-f03b-11eb-afe0-02f3bdd9099e:1-46], endLocation: [position: (binlog.000002, 9507), gtid-set: 813a9612-f03b-11eb-afe0-02f3bdd9099e:1-46]: gen insert sqls failed, sourceTable: test.t1, targetTable: test.t1: failed to mock downstream table by create table statement CREATE TABLE t1 (\n id int NOT NULL,\n name varchar(30) DEFAULT NULL,\n PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,\n KEY idx1 ((right(name, 4)))\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin in schema tracker",

"RawCause": "[ddl:8200]Unsupported creating expression index containing unsafe functions without allow-expression-index in config",

Versions of the cluster

DM version (run dmctl -V or dm-worker -V or dm-master -V):

Release Version: v8.5.3

Upstream MySQL/MariaDB server version:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26    |
+-----------+

Downstream TiDB cluster version (execute SELECT tidb_version(); in a MySQL client):

Release Version: v8.5.3

How did you deploy DM: tiup or manually?

tiup

Other interesting information (system version, hardware config, etc):

>
>

current status of DM cluster (execute query-status <task-name> in dmctl)

{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "sourceStatus": {
                "source": "mysql-replica-02",
                "worker": "dm-10.37.129.6-8262",
                "result": null,
                "relayStatus": null
            },
            "subTaskStatus": [
                {
                    "name": "test",
                    "stage": "Paused",
                    "unit": "Sync",
                    "result": {
                        "isCanceled": false,
                        "errors": [
                            {
                                "ErrCode": 44018,
                                "ErrClass": "schema-tracker",
                                "ErrScope": "internal",
                                "ErrLevel": "high",
                                "Message": "startLocation: [position: (binlog.000002, 9465), gtid-set: 813a9612-f03b-11eb-afe0-02f3bdd9099e:1-46], endLocation: [position: (binlog.000002, 9507), gtid-set: 813a9612-f03b-11eb-afe0-02f3bdd9099e:1-46]: gen insert sqls failed, sourceTable: `test`.`t1`, targetTable: `test`.`t1`: failed to mock downstream table by create table statement CREATE TABLE `t1` (\n  `id` int NOT NULL,\n  `name` varchar(30) DEFAULT NULL,\n  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,\n  KEY `idx1` ((right(`name`, 4)))\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin in schema tracker",
                                "RawCause": "[ddl:8200]Unsupported creating expression index containing unsafe functions without allow-expression-index in config",
                                "Workaround": ""
                            }
                        ],
                        "detail": null
                    },
                    "unresolvedDDLLockID": "",
                    "sync": {
                        "totalEvents": "18",
                        "totalTps": "0",
                        "recentTps": "0",
                        "masterBinlog": "(binlog.000002, 9538)",
                        "masterBinlogGtid": "813a9612-f03b-11eb-afe0-02f3bdd9099e:1-46",
                        "syncerBinlog": "(binlog.000002, 9257)",
                        "syncerBinlogGtid": "813a9612-f03b-11eb-afe0-02f3bdd9099e:1-45",
                        "blockingDDLs": [
                        ],
                        "unresolvedGroups": [
                        ],
                        "synced": false,
                        "binlogType": "remote",
                        "secondsBehindMaster": "0",
                        "blockDDLOwner": "",
                        "conflictMsg": "",
                        "totalRows": "18",
                        "totalRps": "0",
                        "recentRps": "0"
                    },
                    "validation": null
                }
            ]
        }
    ]
}

[ti-chi-bot[bot]]

Welcome @jianglf4811! It looks like this is your first issue to pingcap/tiflow 🎉

[lance6716]

there's "in schema tracker" in error message. It means it's not caused by downstream TiDB, but DM's embedded TiDB component. /cc @OliverS929