TiCDC Synchronization Interruption for Non-Primary Key Auto-Increment Sequence Table, but Synchronization Status is Normal

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

Original topic: ticdc对于非主键自增序列表同步中断,但同步状态正常

| username: 人如其名

[TiDB Usage Environment] Test
[TiDB Version] v7.2.0
When synchronizing from a TiDB cluster to another TiDB cluster using TiCDC, if a newly created table contains a non-primary key auto-increment field, it will cause the entire changefeed task to be interrupted. However, querying the task status through commands still shows it as normal.

Upstream Cluster: v7.2.0 192.168.31.201:4003 root:root (CDC installed)
Downstream Cluster: v6.5.3 192.168.31.201:4000 root:root
Specific Cluster Information:

[tidb@host0 config_file]$ tiup cluster display tidb-test
tiup is checking updates for component cluster ...
Starting component `cluster`: /home/tidb/.tiup/components/cluster/v1.12.5/tiup-cluster display tidb-test
Cluster type:       tidb
Cluster name:       tidb-test
Cluster version:    v6.5.3
Deploy user:        tidb
SSH type:           builtin
Dashboard URL:      http://192.168.31.201:2379/dashboard
Grafana URL:        http://192.168.31.201:3000
ID                    Role          Host            Ports                            OS/Arch       Status   Data Dir                           Deploy Dir
--                    ----          ----            -----                            -------       ------   --------                           ----------
192.168.31.201:9093   alertmanager  192.168.31.201  9093/9094                        linux/x86_64  Up       /data/tidb-data/alertmanager-9093  /data/tidb-deploy/alertmanager-9093
192.168.31.201:3000   grafana       192.168.31.201  3000                             linux/x86_64  Up       -                                  /data/tidb-deploy/grafana-3000
192.168.31.201:2379   pd            192.168.31.201  2379/2380                        linux/x86_64  Up|L|UI  /data/tidb-data/pd-2379            /data/tidb-deploy/pd-2379
192.168.31.201:9090   prometheus    192.168.31.201  9090/12020                       linux/x86_64  Up       /data/tidb-data/prometheus-9090    /data/tidb-deploy/prometheus-9090
192.168.31.201:4000   tidb          192.168.31.201  4000/10080                       linux/x86_64  Up       -                                  /data/tidb-deploy/tidb-4000
192.168.31.201:9000   tiflash       192.168.31.201  9000/8123/3930/20170/20292/8234  linux/x86_64  Up       /data/tidb-data/tiflash-9000       /data/tidb-deploy/tiflash-9000
192.168.31.201:20160  tikv          192.168.31.201  20160/20180                      linux/x86_64  Up       /data/tidb-data/tikv-20160         /data/tidb-deploy/tikv-20160
Total nodes: 7
[tidb@host0 config_file]$ tiup cluster display tidb-test2
tiup is checking updates for component cluster ...
Starting component `cluster`: /home/tidb/.tiup/components/cluster/v1.12.5/tiup-cluster display tidb-test2
Cluster type:       tidb
Cluster name:       tidb-test2
Cluster version:    v7.2.0
Deploy user:        tidb
SSH type:           builtin
Dashboard URL:      http://192.168.31.201:2579/dashboard
Grafana URL:        http://192.168.31.201:3003
ID                    Role          Host            Ports                            OS/Arch       Status   Data Dir                           Deploy Dir
--                    ----          ----            -----                            -------       ------   --------                           ----------
192.168.31.201:9393   alertmanager  192.168.31.201  9393/9394                        linux/x86_64  Up       /data/tidb-data/alertmanager-9393  /data/tidb-deploy/alertmanager-9393
192.168.31.201:8300   cdc           192.168.31.201  8300                             linux/x86_64  Up       /data/tidb-data/cdc-8300           /data/tidb-deploy/cdc-8300
192.168.31.201:3003   grafana       192.168.31.201  3003                             linux/x86_64  Up       -                                  /data/tidb-deploy/grafana-3003
192.168.31.201:2579   pd            192.168.31.201  2579/2880                        linux/x86_64  Up|L|UI  /data/tidb-data/pd-2579            /data/tidb-deploy/pd-2579
192.168.31.201:9390   prometheus    192.168.31.201  9390/12324                       linux/x86_64  Up       /data/tidb-data/prometheus-9390    /data/tidb-deploy/prometheus-9390
192.168.31.201:4003   tidb          192.168.31.201  4003/10083                       linux/x86_64  Up       -                                  /data/tidb-deploy/tidb-4003
192.168.31.201:9003   tiflash       192.168.31.201  9003/8125/3933/20173/20295/8236  linux/x86_64  Up       /data/tidb-data/tiflash-9003       /data/tidb-deploy/tiflash-9003
192.168.31.201:20163  tikv          192.168.31.201  20163/20183                      linux/x86_64  Up       /data/tidb-data/tikv-20163         /data/tidb-deploy/tikv-20163
Total nodes: 8
[tidb@host0 config_file]$ 
# Initialize the test database on both upstream and downstream clusters
[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4003 -uroot -proot mysql -e "drop database test;create database test;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4000 -uroot -proot mysql -e "drop database test;create database test;"
mysql: [Warning] Using a password on the command line interface can be insecure.
# Check that the tables in both databases are empty
[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4000 -uroot -proot test -e "show tables"
mysql: [Warning] Using a password on the command line interface can be insecure.
[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4003 -uroot -proot test -e "show tables"
mysql: [Warning] Using a password on the command line interface can be insecure.

Create a new changefeed task:
[tidb@host0 config_file]$ tiup cdc cli changefeed create --server=http://192.168.31.201:8300 --sink-uri="mysql://root:root@192.168.31.201:4000/" --changefeed-id="simple-replication-task" --no-confirm
tiup is checking updates for component cdc ...
Starting component `cdc`: /home/tidb/.tiup/components/cdc/v7.2.0/cdc cli changefeed create --server=http://192.168.31.201:8300 --sink-uri=mysql://root:root@192.168.31.201:4000/ --changefeed-id=simple-replication-task --no-confirm
[WARN] Some tables are not eligible to replicate, because they do not have a primary key or a not-null unique key: []v2.TableName{v2.TableName{Schema:"tpch1", Table:"customer_bak", TableID:246, IsPartition:false}}
Create changefeed successfully!
ID: simple-replication-task
Info: {"upstream_id":7248279335184372145,"namespace":"default","id":"simple-replication-task","sink_uri":"mysql://root:xxxxx@192.168.31.201:4000/","create_time":"2023-07-24T21:54:29.203804473+08:00","start_ts":443077589504098309,"config":{"memory_quota":1073741824,"case_sensitive":true,"enable_old_value":true,"force_replicate":false,"ignore_ineligible_table":true,"check_gc_safe_point":true,"enable_sync_point":false,"bdr_mode":false,"sync_point_interval":600000000000,"sync_point_retention":86400000000000,"filter":{"rules":["*.*"]},"mounter":{"worker_num":16},"sink":{"csv":{"delimiter":",","quote":"\"","null":"\\N","include_commit_ts":false},"encoder_concurrency":16,"terminator":"\r\n","date_separator":"day","enable_partition_separator":true,"enable_kafka_sink_v2":false,"only_output_updated_columns":false,"delete_only_output_handle_key_columns":false,"large_message_only_handle_key_columns":false},"consistent":{"level":"none","max_log_size":64,"flush_interval":2000,"use_file_backend":false},"scheduler":{"enable_table_across_nodes":false,"region_threshold":100000,"write_key_threshold":0},"integrity":{"integrity_check_level":"none","corruption_handle_level":"warn"}},"state":"normal","creator_version":"v7.2.0","resolved_ts":443077589504098309,"checkpoint_ts":443077589504098309,"checkpoint_time":"2023-07-24 21:54:29.141"}

# Check the changefeed status:
]
[tidb@host0 config_file]$ tiup cdc cli changefeed list --server=http://192.168.31.201:8300
tiup is checking updates for component cdc ...
Starting component `cdc`: /home/tidb/.tiup/components/cdc/v7.2.0/cdc cli changefeed list --server=http://192.168.31.201:8300
[
  {
    "id": "simple-replication-task",
    "namespace": "default",
    "summary": {
      "state": "normal",
      "tso": 443077599019663361,
      "checkpoint": "2023-07-24 21:55:05.440",
      "error": null
    }
  }
]

As you can see, the synchronization status is normal after creating TiCDC, and the error is null.

Create a test table t in the upstream:

[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4003 -uroot -proot test -e "create table t (id int primary key)"
mysql: [Warning] Using a password on the command line interface can be insecure.
[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4003 -uroot -proot test -e "insert into t values (1),(2)"
mysql: [Warning] Using a password on the command line interface can be insecure.
[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4003 -uroot -proot test -e "select * from t"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+
| id |
+----+
|  1 |
|  2 |
+----+

Query the test table t in the downstream (data is the same as upstream):

[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4000 -uroot -proot test -e "select * from t"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+
| id |
+----+
|  1 |
|  2 |
+----+

Create a test table t1 in the upstream (cannot be synchronized):

[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4003 -uroot -proot test -e "create table t1(id int primary key,id_auto int auto_increment)"
mysql: [Warning] Using a password on the command line interface can be insecure.
[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4003 -uroot -proot test -e "insert into t1(id) values (1),(2)"
mysql: [Warning] Using a password on the command line interface can be insecure.
[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4003 -uroot -proot test -e "select * from t1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+
| id | id_auto |
+----+---------+
|  1 |       1 |
|  2 |       2 |
+----+---------+

Query related records in the downstream:

[tidb@host0 config_file]$ mysql -h 192.168.31.201 -P4000 -uroot -proot test -e "select * from t1"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'test.t1' doesn't exist
[tidb@host0 config_file]$ 

As you can see, the table t1 has not been synchronized.

Check the synchronization status:

[tidb@host0 config_file]$ tiup cdc cli changefeed list --server=http://192.168.31.201:8300
tiup is checking updates for component cdc ...
Starting component `cdc`: /home/tidb/.tiup/components/cdc/v7.2.0/cdc cli changefeed list --server=http://192.168.31.201:8300
[
  {
    "id": "simple-replication-task",
    "namespace": "default",
    "summary": {
      "state": "normal",
      "tso": 443077639481589762,
      "checkpoint": "2023-07-24 21:57:39.790",
      "error": null
    }
  }
]

The command line shows the synchronization status as normal, and the error is null.
Observe the warning in the TiCDC logs:

[2023/07/24 21:59:09.674 +08:00] [WARN] [mysql_ddl_sink.go:125] ["Execute DDL with error, retry later"] [startTs=443077639468482581] [ddl="CREATE TABLE `t1` (`id` INT PRIMARY KEY,`id_auto` INT AUTO_INCREMENT)"] [namespace=default] [changefeed=simple-replication-task] [error="Error 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key"]

Is it only the table t1 that cannot be synchronized, or are other tables also affected?
Reload data into the upstream table t to see if it can still be written normally:

[root@localhost log]# mysql -h 192.168.31.201 -P4003 -uroot -proot test -e "insert into t values (3)"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost log]# mysql -h 192.168.31.201 -P4003 -uroot -proot test -e "select * from t"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

Query in the downstream:

[root@localhost log]# mysql -h 192.168.31.201 -P4000 -uroot -proot test -e "select * from t"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+
| id |
+----+
|  1 |
|  2 |
+----+

The table t has also stopped synchronizing data. Check the task synchronization status again:

[tidb@host0 config_file]$ tiup cdc cli changefeed list --server=http://192.168.31.201:8300
tiup is checking updates for component cdc ...
Starting component `cdc`: /home/tidb/.tiup/components/cdc/v7.2.0/cdc cli changefeed list --server=http://192.168.31.201:8300
[
  {
    "id": "simple-replication-task",
    "namespace": "default",
    "summary": {
      "state": "normal",
      "tso": 443077639481589762,
      "checkpoint": "2023-07-24 21:57:39.790",
      "error": null
    }
  }
]

[tidb@host0 config_file]$ tiup cdc cli changefeed query --server=http://192.168.31.201:8300 --changefeed-id="simple-replication-task"
tiup is checking updates for component cdc ...
Starting component `cdc`: /home/tidb/.tiup/components/cdc/v7.2.0/cdc cli changefeed query --server=http://192.168.31.201:8300 --changefeed-id=simple-replication-task
{
  "upstream_id": 7248279335184372145,
  "namespace": "default",
  "id": "simple-rep
| username: redgame | Original post link

Eliminate environmental issues, try restarting, and then check again.

| username: dba-kit | Original post link

You are syncing from 7.1 to 6.5, and before TiDB 6.6, as well as in MySQL, it is required that the auto-increment column must be included in the primary key. Only after that is it allowed for the auto-increment column not to be part of the primary key or index. You can check the official documentation:

| username: dba-kit | Original post link

However, the fact that the changefeed status is not “failed” is indeed a bug.

| username: Kongdom | Original post link

:thinking: Report bugs, you must report bugs.

| username: zhanggame1 | Original post link

TiDB does not support auto-increment for non-primary keys, right?

| username: 人如其名 | Original post link

Sure, additionally, for scenarios involving TiDB cluster to TiDB cluster (such as dual clusters in the same city, database migration and upgrade, etc.). After initializing the backup cluster using BR, Dumpling + Lightning (logical mode), or Lightning (local mode), and then using TiCDC for synchronization, what impact does this have on tables with auto-increment primary keys? Is it necessary to reset the initial value of the auto-increment column (set it to the current value + cache to avoid conflicts between values inserted by the TiCDC program and auto-increment values)?