Changefeed Fails to Replicate 'Drop Primary Key' DDL of a table that only has the dropped Primary key as valid indexes.

[asddongmen]

What did you do?

When a table with a primary key (PK) is replicated by Changefeed, the 'Drop Primary Key' DDL command of it is not replicated as expected.
This resulted in the subsequent Add Primary Key operation failing to replicate because the table downstream still retained the old Primary Key.

What did you expect to see?

Both Drop PK and Add PK DDL can be replicated.

What did you see instead?

as above.

Versions of the cluster

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

(paste TiDB cluster version here)

Upstream TiKV version (execute tikv-server --version):

(paste TiKV version here)

TiCDC version (execute cdc version):

v8.0.0

[asddongmen]

😇 How to reproduce?

First:

CREATE TABLE t1 (
id INT PRIMARY KEY /*T![clustered_index] NONCLUSTERED */,
name VARCHAR(255),
email VARCHAR(255) UNIQUE
);

INSERT INTO t1 (id, name, email) VALUES (1, 'Alice Smith', 'alice.smith@example.com');
INSERT INTO t1 (id, name, email) VALUES (2, 'Bob Johnson', 'bob.johnson@example.com');
INSERT INTO t1 (id, name, email) VALUES (3, 'Charlie Brown', 'charlie.brown@example.com');
// All dml above are replicated

And then:

alter table t1 drop primary key; // Was not replicated, it should be, it's not expected

INSERT INTO t1 (id, name, email) VALUES (4, 'dongmen', 'dongmen@example.com');   // Was not replicated, it is expected.

alter table t1 add primary key (id); // Was not replicated,  it should be, it's not expected.

[asddongmen]

Workaround

The current workaround to this issue involves setting force-replicate=true in the changefeed configuration. This allows the replication of the ineligible table. And no ddl or dml will be lost by the way.

Solution

Solution 1:

One potential solution is to replicate the Drop Primary Key DDL on a table that only has the dropped primary key and no other non-null unique key. However, this could potentially result in data loss.
Upon executing the Drop Primary Key DDL, all subsequent DML operations on table t1 are ignored.
As described in the official documentation, by default, changefeed only replicates tables with valid indexes, where valid indexes refer to primary keys or non-null unique indexes. Because this table only has primary key as a valid index, after dropping primary key, the table's DML is no longer replicated.
Consequently, all DML operations conducted between Drop Primary Key and Add Primary Key may be lost.

Solution 2:

Another possible solution is as follows:
When replicating a table with only one valid index, if TiCDC receives a DDL to delete this valid index, it should terminate the changefeed and report an error. The error message should warn the user that this is a potentially risky operation that could lead to data loss, so we refuse to do it.
If the user needs to continue replication, they need to modify the changefeed configuration to force-replicate=true to avoid data loss.
However, this operation may lead to data redundancy, please refer to the official documentation.
Lastly, our documentation needs to provide a detailed explanation of this behavior.

[flowbehappy]

Workaround

The current workaround to this issue involves setting force-replicate=true in the changefeed configuration. This allows the replication of the ineligible table. And no ddl or dml will be lost by the way.

Solution

Solution 1:

One potential solution is to replicate the Drop Primary Key DDL on a table that only has the dropped primary key and no other non-null unique key. However, this could potentially result in data loss. Upon executing the Drop Primary Key DDL, all subsequent DML operations on table t1 are ignored. As described in the official documentation, by default, changefeed only replicates tables with valid indexes, where valid indexes refer to primary keys or non-null unique indexes. Because this table only has primary key as a valid index, after dropping primary key, the table's DML is no longer replicated. Consequently, all DML operations conducted between Drop Primary Key and Add Primary Key may be lost.

Solution 2:

Another possible solution is as follows: When replicating a table with only one valid index, if TiCDC receives a DDL to delete this valid index, it should terminate the changefeed and report an error. The error message should warn the user that this is a potentially risky operation that could lead to data loss, so we refuse to do it. If the user needs to continue replication, they need to modify the changefeed configuration to force-replicate=true to avoid data loss. However, this operation may lead to data redundancy, please refer to the official documentation. Lastly, our documentation needs to provide a detailed explanation of this behavior.

@benmeadowcroft What do you think about @asddongmen 's suggestion? BTW, I prefer solution 2. Because data lost is a more serious situation.

[benmeadowcroft]

@flowbehappy @asddongmen I see the benefits of solution 2 as well, though we will have to ensure the process for transitioning indexes on a table, without incurring interruption of the change feed, is well documented. For example, does dropping one index and adding another index in the same transaction avoid this problem? Do we recommend they add the second index beforehand, and then later drop the original index? Etc.

We will also need to ensure that the changefeed UX on TiDB Cloud allow for the configuration settings needed to resolve this issue.

[asddongmen]

Thank you for your valuable input.

Does dropping one index and adding another index in the same transaction avoid this problem?

The TiDB does not support executing multiple DDL in a same transaction.

Do we recommend they add the second index beforehand, and then later drop the original index?

Yes, this is a feasible way to avoid this problem. We can include this in the documentation.

@benmeadowcroft
cc @flowbehappy

[asddongmen]

To do list:

• ddl_puller (ticdc): handle dorp pk/uk ddl correctly #10965
• Update TiCDC documentation

[seiya-annie]

/found customer