DM synchronization error, failed to create table for in schema tracker\" RawCause:\"[schema:8239]Unknown placement policy 'on_ssd'

Translated
1

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

Original topic: DM同步报错, failed to create table for in schema tracker" RawCause:"[schema:8239]Unknown placement policy ‘on_ssd’

| username: dba-kit

After setting the placement policy, DM synchronization reports: Failed to create table for in schema tracker\" RawCause:\"[schema:8239]Unknown placement policy 'on_ssd'. However, the ‘on_ssd’ policy does exist in TiDB, and other tasks have no errors, only this one has an error (maybe related to table creation for this task?). Could the experts take a look and advise on how I can fix this error?

2
| username: lance6716 | Original post link

Please provide the relevant logs and complete error information.

3
| username: dba-kit | Original post link 
[2022/10/31 09:45:11.454 +08:00] [INFO] [syncer.go:1531] ["received ungraceful exit ctx, exit now"] [task=fund_task] [unit="binlog replication"]
[2022/10/31 09:45:11.454 +08:00] [INFO] [subtask.go:356] ["unit process returned"] [subtask=fund_task] [unit=Sync] [stage=Paused] [status="{\"totalEvents\":506,\"totalTps\":2,\"syncerBinlog\":\"(mysql-bin.001200, 635889583)\",\"syncerBinlogGtid\":\"1e787e9a-ea02-11eb-8239-00163e0eb750:1-136252793,1fd7fe20-ea02-11eb-9db6-00163e02d592:1-20195454,3e773ad8-46c1-11ec-992b-0242a9fe7b02:1-139,777234c5-87b7-11ea-9279-00163e1054e6:1-1486672619,784b7fc9-87b7-11ea-a0b7-00163e068fb5:1-1724587847,7e04612e-1c6a-11ed-ac0c-52540048a565:1\",\"binlogType\":\"local\"}"]
[2022/10/31 09:45:11.454 +08:00] [INFO] [local_reader.go:782] ["binlog reader closing"] [task=fund_task] [unit="binlog replication"] [component="binlog reader"]
[2022/10/31 09:45:11.454 +08:00] [WARN] [local_reader.go:288] ["parse relay finished"] [task=fund_task] [unit="binlog replication"] [component="binlog reader"] [error="parse relay log file mysql-bin.001200 from offset 4 in dir relay_log/dm-172.30.241.143-18262/7e04612e-1c6a-11ed-ac0c-52540048a565.000001: context canceled"]
[2022/10/31 09:45:11.454 +08:00] [INFO] [local_reader.go:788] ["binlog reader closed"] [task=fund_task] [unit="binlog replication"] [component="binlog reader"]
[2022/10/31 09:45:11.471 +08:00] [ERROR] [subtask.go:377] ["unit process error"] [subtask=fund_task] [unit=Sync] ["error information"="ErrCode:44003 ErrClass:\"schema-tracker\" ErrScope:\"downstream\" ErrLevel:\"high\" Message:\"startLocation: [position: (mysql-bin|000001.001200, 635890184), gtid-set: 1e787e9a-ea02-11eb-8239-00163e0eb750:1-136252793,1fd7fe20-ea02-11eb-9db6-00163e02d592:1-20195455,3e773ad8-46c1-11ec-992b-0242a9fe7b02:1-139,777234c5-87b7-11ea-9279-00163e1054e6:1-1486672621,784b7fc9-87b7-11ea-a0b7-00163e068fb5:1-1724587847,7e04612e-1c6a-11ed-ac0c-52540048a565:1], endLocation: [position: (mysql-bin|000001.001200, 635898306), gtid-set: 1e787e9a-ea02-11eb-8239-00163e0eb750:1-136252793,1fd7fe20-ea02-11eb-9db6-00163e02d592:1-20195455,3e773ad8-46c1-11ec-992b-0242a9fe7b02:1-139,777234c5-87b7-11ea-9279-00163e1054e6:1-1486672621,784b7fc9-87b7-11ea-a0b7-00163e068fb5:1-1724587847,7e04612e-1c6a-11ed-ac0c-52540048a565:1]: failed to create table for `db`.`table` in schema tracker\" RawCause:\"[schema:8239]Unknown placement policy 'on_ssd'\" "]
[2022/10/31 09:45:11.471 +08:00] [INFO] [subtask.go:379] [paused] [subtask=fund_task] [unit=Sync]
4
| username: dba-kit | Original post link

This can be consistently reproduced. I completely deleted the old tasks, truncated all the tables, then created a new service to re-import from MySQL, and the same issue still occurs.

5
| username: dba-kit | Original post link 
» binlog-schema list -s fund-mysql fund_task db table
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "CREATE TABLE `table` ( `createtime` datetime DEFAULT CURRENT_TIMESTAMP, `updatetime` datetime DEFAULT CURRENT_TIMESTAMP, `fund_code` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Fund Code', `period` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Period', `type_name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Indicator Name', `value` double DEFAULT NULL COMMENT 'Indicator Value', `start_date` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Indicator Start Time', `end_date` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Indicator End Time', PRIMARY KEY (`fund_code`,`period`,`type_name`) /*T![clustered_index] CLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci /*T![placement] PLACEMENT POLICY=`on_ssd` */",
            "source": "fund-mysql",
            "worker": "dm-********-18262"
        }
    ]
}
»
6
| username: dba-kit | Original post link

Update on the latest progress: I found the binlog-schema command. I see that the update subcommand supports fetching the table structure from the source. If you encounter similar errors, you can:

  1. Execute binlog-schema update -s fund-mysql fund_task db table --from-source to specify fetching the table structure from MySQL.
  2. resume-task fund_task, the task can continue (although you have to execute it for each table, which is not very practical, it can only be a temporary solution).
7
| username: lance6716 | Original post link

Thank you for the feedback. We will strive to fix it in the next version. You can check the progress at track table structure from downstream failed to handle missing placement rule · Issue #7493 · pingcap/tiflow · GitHub.

8
| username: h5n1 | Original post link

Could you please elaborate on this sentence? Does it mean that there is no placement rules information in DM?

9
| username: dba-kit | Original post link

FYI, because any new table creation will cause replication errors. Here is a supplementary SHELL command to generate repair statements, which can be scheduled to run periodically in crond.

#! /bin/bash
PD_ADDR="127.0.0.1:8261"
cmds=$(tiup dmctl --master-addr ${PD_ADDR} query-status --more | jq -r '.sources[] | .sourceStatus.source as $src | .subTaskStatus[] | select(.stage == "Paused") | {name: .name, source: $src, info :.result.errors[].Message | capture("`(?<db>\\S+)`.`(?<table>\\S+)`")} | "binlog-schema update --from-source -s " + .source + " " + .name + " " + .info.db + " " + .info.table + "\nresume-task " + .name') 

echo "${cmds}" | while read cmd; do
    echo "------  $cmd ------"
    echo "$cmd" | tiup dmctl --master-addr  ${PD_ADDR}
    
done
10
| username: lance6716 | Original post link

The issue could not be reproduced. We need your DM version and more logs, such as the logs from a few seconds before and after the first occurrence of Unknown placement policy.

11
| username: dba-kit | Original post link

It is DM in TiDB version 6.1.1. Can the downstream synchronized tables with placement policy be synchronized normally on your side? You need to clear the task cache to reproduce it.

12
| username: lance6716 | Original post link

I tested the nightly version, will check the older version tomorrow.

13
| username: lance6716 | Original post link

DM loads the CREATE TABLE statement from the downstream as the table structure for the synchronization table. This CREATE TABLE references the placement rule in the downstream TiDB, but DM does not load the placement rule from the downstream (because synchronization does not use this). Unfortunately, DM did not handle it well when executing CREATE TABLE in its internal components, resulting in an error.

14
| username: dba-kit | Original post link

If it is confirmed that DM has not handled it, it is recommended to update the compatibility section of the official documentation:

15
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.