Sequence support is incomplete

[overvenus]

Is your feature request related to a problem?

Sequence support is incomplete in TiCDC.

The current capability of TiCDC:

The DDL operation CREATE SEQUENCE and the SEQUENCE function in TiDB. When the upstream TiDB uses SEQUENCE, TiCDC ignores SEQUENCE DDL operations/functions performed upstream. However, DML operations using SEQUENCE functions can be correctly replicated.

2022/02/10, https://docs.pingcap.com/tidb/stable/ticdc-overview#unsupported-scenarios

Besides, there are two flaws we found,

1. TiCDC have no idea about how to deal with DDLs below:

   CREATE SEQUENCE `SEQ1` start with 1 minvalue 1 maxvalue 999999999999999 increment by 1 nocache cycle;
   CREATE TABLE t(k int not null, a int default next value for SEQ1, PRIMARY KEY (k));
   

2. TiCDC filters all SEQUENCE DDL, it means we lost SEQUENCE even if downstream is TiDB. We lose data in TiDB disaster recovery scenario.

Describe the feature you'd like

Support sequence feature completely when downstream is TiDB, and disable sequence completely when downstream is MySQL.

Describe alternatives you've considered

No response

Teachability, Documentation, Adoption, Migration Strategy

No response

[dveeden]

Note that this also affects MariaDB-to-TiDB.

Example:

 "Message": "startLocation: [position: (mariadb-binlog.000001, 882), gtid-set: 0-1-3], endLocation: [position: (mariadb-binlog.000001, 1006), gtid-set: 0-1-4], origin SQL: [CREATE SEQUENCE s START WITH 100 INCREMENT BY 10]: unknown type ddl \u0026{ddlNode:{stmtNode:{node:{utf8Text: enc:0x6921ce0 once:0xc000f00360 text:CREATE SEQUENCE s START WITH 100 INCREMENT BY 10 offset:0}}} IfNotExists:false Name:0xc002510410 SeqOptions:[0xc000f00340 0xc000f00350] TblOptions:[]}",

[dveeden]

@lance6716 @overvenus I noticed this issue was originally about TiCDC and what I reported was about DM. Should I create a separate issue for DM or can we continue with this combined issue?