key was too long error when dm replicating to downstream TiDB w/ non-default max-index-length

[michaelmdeng]

What did you do?

we use dm to replicate tables between upstream mysql and downstream tidb. The table has different schema/index definition between mysql/tidb and downstream tidb and tidb has a larger-than-default max-index-length config (3306 > 3072).

upstream:

// total index length 767 * 4 = 3068 < 3072
create table t(a bigint(20), b varbinary(767), c varbinary(767), d varbinary(767), e varbinary(767), primary key(a), key(b, c, d, e))

downstream:

// total index length 767 * 4 + 20 = 3088 > 3072
create table t(a bigint(20), b varbinary(767), c varbinary(767), d varbinary(767), e varbinary(767), primary key(a), key(b, c, d, e, a))

dm task has config like:

name: uds-masterstore-003-full-shadow-task
task-mode: incremental
is-sharding: false
shard-mode: \"\"
strict-optimistic-shard-mode: false
ignore-checking-items: []
meta-schema: dm_meta
enable-heartbeat: false
heartbeat-update-interval: 1
heartbeat-report-interval: 10
timezone: \"\"
case-sensitive: false
collation_compatible: loose
target-database:
  host: host
  port: 3306
  user: user
  password: '******'
  max-allowed-packet: null
  session:
    tidb_txn_mode: optimistic
  security: null
mysql-instances:
- source-id: uds-masterstore-003-full-sha-src
  meta:
    binlog-name: mysql-bin-changelog.692003
    binlog-pos: 81222967
    binlog-gtid: \"\"
  filter-rules: []
  column-mapping-rules: []
  route-rules: []
  expression-filters: []
  black-white-list: \"\"
  block-allow-list: balist-01
  mydumper-config-name: dump-01
  mydumper: null
  mydumper-thread: 0
  loader-config-name: load-01
  loader: null
  loader-thread: 0
  syncer-config-name: sync-01
  syncer: null
  syncer-thread: 0
  validator-config-name: validator-01
online-ddl: true
shadow-table-rules: []
trash-table-rules: []
online-ddl-scheme: \"\"
routes: {}
filters: {}
column-mappings: {}
expression-filter: {}
black-white-list: {}
block-allow-list:
  balist-01:
    do-tables: []
    do-dbs:
    - oyster_production
    ignore-tables: []
    ignore-dbs: []
mydumpers:
  dump-01:
    mydumper-path: ./bin/mydumper
    threads: 4
    chunk-filesize: \"64\"
    statement-size: 0
    rows: 0
    where: \"\"
    skip-tz-utc: true
    extra-args: \"\"
loaders:
  load-01:
    pool-size: 16
    dir: ./dumped_data
    sorting-dir-physical: \"\"
    import-mode: logical
    on-duplicate: \"\"
    on-duplicate-logical: replace
    on-duplicate-physical: none
    disk-quota-physical: 0
    checksum-physical: required
    analyze: optional
    range-concurrency: 0
    compress-kv-pairs: \"\"
    pd-addr: \"\"
syncers:
  sync-01:
    meta-file: \"\"
    worker-count: 128
    batch: 100
    queue-size: 1024
    checkpoint-flush-interval: 1
    compact: true
    multiple-rows: true
    max-retry: 0
    auto-fix-gtid: false
    enable-gtid: false
    disable-detect: false
    safe-mode: true
    safe-mode-duration: 60s
    enable-ansi-quotes: false
validators:
  validator-01:
    mode: none
    worker-count: 4
    validate-interval: 10s
    check-interval: 5s
    row-error-delay: 30m0s
    meta-flush-interval: 5m0s
    batch-query-size: 100
    max-pending-row-size: 500m
    max-pending-row-count: 2147483647
clean-dump-file: true
ansi-quotes: false
remove-meta: false
experimental:
  async-checkpoint-flush: false

source has config like:

enable: true
enable-gtid: false
auto-fix-gtid: false
relay-dir: relay-dir
meta-dir: \"\"
flavor: mysql
charset: \"\"
enable-relay: false
relay-binlog-name: \"\"
relay-binlog-gtid: \"\"
source-id: uds-masterstore-003-full-sha-src
from:
  host: host
  port: 3306
  user: user
  password: '******'
  max-allowed-packet: null
  session: {}
  security: null
purge:
  interval: 3600
  expires: 0
  remain-space: 15
checker:
  check-enable: true
  backoff-rollback: 5m0s
  backoff-max: 5m0s
  check-interval: 5s
  backoff-min: 1s
  backoff-jitter: true
  backoff-factor: 2
server-id: 429587949
tracer: {}
case-sensitive: false
filters: []

What did you expect to see?

When we use DM to replicate between these tables we encounter the error like

[2024/07/31 19:58:03.342 +00:00] [ERROR] [subtask.go:359] ["unit process error"] [subtask=task] [unit=Sync] ["error information"="ErrCode:44003 ErrClass:\"schema-tracker\" ErrScope:\"downstream\" ErrLevel:\"high\" Message:\"startLocation: [position: (mysql-bin-changelog.692003, 81232424), gtid-set: 00000000-0000-0000-0000-000000000000:0], endLocation: [position: (mysql-bin-changelog.692003, 81232526), gtid-set: 00000000-0000-0000-0000-000000000000:0]: failed to create table for `schema`.`table` in schema tracker\" RawCause:\"[ddl:1071]Specified key was too long (3086 bytes); max key length is 3072 bytes\" "]

Even though max-index-length setting is increased on the downstream tidb, it appears the dm schema tracker uses the default config which remains 3072. Additionally, the schema tracker tries to suppress these errors and run in non-strict SQL mode, but it only affects single column indexes w/ non-unique constraint.

What did you see instead?

We expected to see dm successfully replicate mutations between upstream and downstream. Additionally, we expect that dm would grab the relevant config from the downstream tidb rather than use the default.

Versions of the cluster

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

Release Version: v7.5.0-master
Git Commit Hash: e123e4a25ef329e2271804cd9b1d14afda481310
Git Branch: heads/v7.5.1-v1-abnb
UTC Build Time: 2024-05-20 20:11:33
Go Version: go version go1.21.0 linux/amd64
Failpoint Build: false

Upstream MySQL/MariaDB server version:

(paste upstream MySQL/MariaDB server version here)

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

tidb> SELECT tidb_version() \G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.5.1-v2.0-abnb
Edition: Community
Git Commit Hash: 3b349611bee25d9e5bf5c769f8bb3c9e93dcd96a
Git Branch: heads/v7.5.1-v2.0-abnb
UTC Build Time: 2024-07-20 03:42:05
GoVersion: go1.21.12
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.09 sec)

How did you deploy DM: tiup or manually?

manually

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

>
>

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

» query-status uds-masterstore-003-full-shadow-task
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "sourceStatus": {
                "source": "uds-masterstore-003-full-sha-src",
                "worker": "uds-full-shadow-stg-dm-worker-18",
                "result": null,
                "relayStatus": null
            },
            "subTaskStatus": [
                {
                    "name": "uds-masterstore-003-full-shadow-task",
                    "stage": "Paused",
                    "unit": "Sync",
                    "result": {
                        "isCanceled": false,
                        "errors": [
                            {
                                "ErrCode": 44003,
                                "ErrClass": "schema-tracker",
                                "ErrScope": "downstream",
                                "ErrLevel": "high",
                                "Message": "startLocation: [position: (mysql-bin-changelog.692003, 81232424), gtid-set: 00000000-0000-0000-0000-000000000000:0], endLocation: [position: (mysql-bin-changelog.692003, 81232526), gtid-set: 00000000-0000-0000-0000-000000000000:0]: failed to create table for `oyster_production`.`index_BackgroundCheckParamsRaw_ki9evez0` in schema tracker",
                                "RawCause": "[ddl:1071]Specified key was too long (3076 bytes); max key length is 3072 bytes",
                                "Workaround": ""
                            }
                        ],
                        "detail": null
                    },
                    "unresolvedDDLLockID": "",
                    "sync": {
                        "totalEvents": "35",
                        "totalTps": "0",
                        "recentTps": "0",
                        "masterBinlog": "(mysql-bin-changelog.692963, 119134455)",
                        "masterBinlogGtid": "",
                        "syncerBinlog": "(mysql-bin-changelog.692003, 81231957)",
                        "syncerBinlogGtid": "",
                        "blockingDDLs": [
                        ],
                        "unresolvedGroups": [
                        ],
                        "synced": false,
                        "binlogType": "remote",
                        "secondsBehindMaster": "0",
                        "blockDDLOwner": "",
                        "conflictMsg": "",
                        "totalRows": "35",
                        "totalRps": "0",
                        "recentRps": "0"
                    },
                    "validation": null
                }
            ]
        }
    ]
}

[lance6716]

I expect SchemaTracker will ignore the index length check

https://github.com/pingcap/tidb/blob/da7ed5cdc93feb1d8c252236f9d6e65d6346235e/pkg/ddl/schematracker/dm_tracker_test.go#L83-L89

Can you provide more log of dm-worker to locate the error?

[lance6716]

it only affects single column indexes w/ non-unique constraint.

Oh I see it. Thank you!

[michaelmdeng]

@lance6716 I wonder how you think about the approach of #11465 (increase the global max-index-length config for DM's schema tracker) vs trying to copy the tidb config, or updating ddl library to use non-default index length.

I don't think it's a 1:1 guarantee that DDLs that fail against TiDB necessarily fail against the schema tracker? Would it be ok if the schema tracker was more lenient/permissive in these cases?

[lance6716]

I think schema tracker should skip all checking in DDL, to provide a most compatible DDL action. I will also try to fix it in tidb's schema tracker component. Your PR will be much quicker than my fix so I also hope we can merge it.