DM: SplitDDL detaches ALGORITHM= / LOCK= qualifiers from the ALTER they qualify

[choudharypankaj]

What did you do?

Replicate an ALTER TABLE carrying an ALGORITHM qualifier from MySQL/Aurora to TiDB.
Upstream:
ALTER TABLE db.orders ADD COLUMN c INT NULL, ALGORITHM=INSTANT;

What did you expect to see?

DM applies one statement downstream, preserving the qualifier:

ALTER TABLE db.orders ADD COLUMN c INT NULL, ALGORITHM = INSTANT

What did you see instead?

DM emits two statements — the operation loses its qualifier, and the qualifier becomes a standalone no-op:

dm-worker.log:
ddl.go:459 originSQL: ALTER TABLE db.orders ADD COLUMN c INT NULL, ALGORITHM=INSTANT
baseconn.go:184 query="ALTER TABLE db.orders ADD COLUMN c INT NULL" ← qualifier lost
baseconn.go:184 query="ALTER TABLE db.orders ALGORITHM = INSTANT" ← orphan

Root cause

dm/pkg/parser/common.go, SplitDDL(), *ast.AlterTableStmt case (line 295 on v8.5.5):

v.Specs = []*ast.AlterTableSpec{spec} // one emitted statement per AST spec

ALGORITHM/LOCK are modelled as their own AlterTableSpec (ast.AlterTableAlgorithm / ast.AlterTableLock), not as attributes of the operation, so per-spec splitting separates them.

The splitting is justified at dm/syncer/ddl.go:311:

// TiDB can't handle multi schema change DDL, so we split it here.

On TiDB v8.5.5 the premise no longer holds — both of these execute successfully:

ALTER TABLE t ADD COLUMN b INT NULL, ADD COLUMN c INT NULL, DROP COLUMN a;
ALTER TABLE t ADD COLUMN d INT NULL, ADD COLUMN e INT NULL, ALGORITHM=INSTANT;

Why it matters

Against a TiDB-only target the impact is cosmetic — TiDB ignores the qualifier semantically (isIgnorableSpec). But TiDB still records the original query text in the DDL job (setDDLJobQuery uses s.OriginText()), so the qualifier is meaningful to anything replicating onward from TiDB.

In an Aurora → DM → TiDB → TiCDC → Aurora topology, TiCDC forwards job.Query verbatim. Because DM strips the qualifier, the downstream MySQL receives a bare ALTER — and MySQL silently falls back to a full table rebuild when an instant operation isn't possible.

Versions of the cluster

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

DM version:       v8.5.5

Upstream MySQL/MariaDB server version:

Aurora MySQL 3.10.3 (MySQL 8.0.42)

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

TiDB version:     v8.5.5
Downstream of TiDB (via TiCDC): Aurora MySQL 3.10.3

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)

running

[YangKeao]

/assign @GMHDBJD

Sorry, I assigned to the wrong one 🤦 .

[YangKeao]

/assign @GMHDBJD

[D3Hunter]

In an Aurora → DM → TiDB → TiCDC → Aurora topology, TiCDC forwards job.Query verbatim. Because DM strips the qualifier, the downstream MySQL receives a bare ALTER — and MySQL silently falls back to a full table rebuild when an instant operation isn't possible.

I don't think any database will or should record too many things that doesn't matter/support to itself(ALGORITHM=INSTANT here) in this scenario, needless to say carry those things again through the replication(TiCDC here) of its own.

[YangKeao]

I've checked the options in DDL. It seems that every options for DDL statements are meaningless for TiDB, so this issue will not cause real problem. Besides the ALGORITHM=..., we have:

1. LOCK=xxx. TiDB ignores this option.
2. WITH/WITHOUT VALIDATION. TiDB only reports unsupported warning.
3. Other options are for the table and they are safe to execute in a single standalone DDL statement. For example COMMENT=....

[choudharypankaj]

For a migration that runs a handful of DDLs none of this matters much — a few extra barriers, maybe an unexpected rebuild, and nobody notices the lag.
For migration where continuous schema-change are carried out, the only option is to run the DDL outside the replication loop which often results in heavy lifting from application to avoid any race condition.

TiDB already keeps the whole statement text. job.Query is just the raw session query and it doesn't strip ALGORITHM. TiCDC is able to stream the query as it is to the Aurora Fail forward.
TiDB has native multi-schema change with subjobs, qualifier included. So the entire splitting behaviour is a workaround for a limitation that no longer exists.
This was ran on TiDB:

mysql> ALTER TABLE msc.t ADD COLUMN d INT NULL, ADD COLUMN e INT NULL;
Query OK, 0 rows affected (0.31 sec)

[joechenrh]

So the entire splitting behaviour is a workaround for a limitation that no longer exists.

There are still some multi schema changes that can't run on TiDB, like whose later change depends on the previous change in the ALTER TABLE. For example:

alter table t1 add column c1 int null, add index i1(c1);

[choudharypankaj]

@joechenrh Thanks! I didn't account for the edge case that you mentioned earlier. It looks like splitting is doing the real work.

Why it matters
TiDB records the original statement text in the DDL job and TiCDC replicates ALTER TABLE without touching the ALGORITHM=/LOCK= tokens. In an Aurora → DM → TiDB → TiCDC → Aurora topology, DM is the only hop that drops it. The failforward then receives a bare ALTER and picks its own algorithm.

So the ask isn't "stop splitting": keep the split, just attach ALGORITHM=/LOCK= to each emitted statement instead of emitting them standalone.

[lance6716]

Very old issue #3287