create table ddl event: 'create table otherSchema.tableA like tableB' raise changefeed closed

[dlf412]

What did you do?

step1. create cdc changefeed:

tiup ctl:v7.1.1 cdc changefeed create --server=192.168.236.61:8300 --sink-uri=mysql://root:[email protected]:4000

step2. create table on 192.168.236.61:

use t2;
create table t1.tt like origin_table;

t1.tt table is created no error:

What did you expect to see?

t1.tt table will be created successfully on 192.168.236.66

What did you see instead?

t1.tt table not be created and changefeed be closed.

[2024/01/19 18:26:15.170 +08:00] [INFO] [schema_storage.go:217] ["handle DDL"] [namespace=default] [changefeed=f3cb8b03-8184-4547-be07-defeac1e9e53] [DDL="create table t1.tt like origin_table"] [job="ID:117, Type:create table, State:done, SchemaState:public, SchemaID:90, TableID:116, RowCount:0, ArgLen:0, start time: 2024-01-19 18:26:13.705 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"] [finishTs=447128528172875777] [role=owner]
[2024/01/19 18:26:15.170 +08:00] [INFO] [ddl_puller.go:125] ["handle job"] [job="ID:117, Type:create table, State:done, SchemaState:public, SchemaID:90, TableID:116, RowCount:0, ArgLen:0, start time: 2024-01-19 18:26:13.705 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"] [skip=false]
[2024/01/19 18:26:15.170 +08:00] [INFO] [ddl_puller.go:582] ["[ddl] handleDDLJobEntry"] [job="ID:117, Type:create table, State:done, SchemaState:public, SchemaID:90, TableID:116, RowCount:0, ArgLen:0, start time: 2024-01-19 18:26:13.705 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[2024/01/19 18:26:15.170 +08:00] [INFO] [ddl_puller.go:592] ["receive new ddl job"] [namespace=default] [changefeed=f3cb8b03-8184-4547-be07-defeac1e9e53_ddl_puller] [query="create table t1.tt like origin_table"] [jobID=117] [job="ID:117, Type:create table, State:done, SchemaState:public, SchemaID:90, TableID:116, RowCount:0, ArgLen:0, start time: 2024-01-19 18:26:13.705 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"]
[2024/01/19 18:26:15.170 +08:00] [INFO] [schema_storage.go:217] ["handle DDL"] [namespace=default] [changefeed=f3cb8b03-8184-4547-be07-defeac1e9e53] [DDL="create table t1.tt like origin_table"] [job="ID:117, Type:create table, State:done, SchemaState:public, SchemaID:90, TableID:116, RowCount:0, ArgLen:0, start time: 2024-01-19 18:26:13.705 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"] [finishTs=447128528172875777] [role=processor]
[2024/01/19 18:26:15.170 +08:00] [INFO] [ddl_puller.go:125] ["handle job"] [job="ID:117, Type:create table, State:done, SchemaState:public, SchemaID:90, TableID:116, RowCount:0, ArgLen:0, start time: 2024-01-19 18:26:13.705 +0800 CST, Err:<nil>, ErrCount:0, SnapshotVersion:0"] [skip=false]
[2024/01/19 18:26:15.181 +08:00] [INFO] [ddl_manager.go:208] ["handle a ddl job"] [namespace=default] [ID=f3cb8b03-8184-4547-be07-defeac1e9e53] [tableID=116] [jobID=117] [query="create table t1.tt like origin_table"] [finishedTs=447128528172875777]
[2024/01/19 18:26:15.431 +08:00] [INFO] [ddl_manager.go:287] ["execute a ddl event"] [query="create table t1.tt like origin_table"] [commitTs=447128528172875777] [checkpointTs=447128528172875777]
[2024/01/19 18:26:15.431 +08:00] [INFO] [ddl_sink.go:415] ["add special comment to DDL"] [namespace=default] [changefeed=f3cb8b03-8184-4547-be07-defeac1e9e53] [DDL="create table t1.tt like origin_table"] [charset=] [collate=] [result="CREATE TABLE `t1`.`tt` LIKE `origin_table`"]
[2024/01/19 18:26:15.431 +08:00] [INFO] [ddl_sink.go:328] ["ddl is sent"] [namespace=default] [changefeed=f3cb8b03-8184-4547-be07-defeac1e9e53] [ddlSentTs=447128528172875777]
[2024/01/19 18:26:15.432 +08:00] [INFO] [ddl_sink.go:207] ["begin emit ddl event"] [namespace=default] [changefeed=f3cb8b03-8184-4547-be07-defeac1e9e53] [DDL="{\"StartTs\":447128528146923530,\"CommitTs\":447128528172875777,\"Query\":\"CREATE TABLE `t1`.`tt` LIKE `origin_table`\",\"TableInfo\":{\"id\":116,\"name\":{\"O\":\"tt\",\"L\":\"tt\"},\"charset\":\"utf8mb4\",\"collate\":\"utf8mb4_bin\",\"cols\":[{\"id\":1,\"name\":{\"O\":\"id\",\"L\":\"id\"},\"offset\":0,\"origin_default\":null,\"origin_default_bit\":null,\"default\":null,\"default_bit\":null,\"default_is_expr\":false,\"generated_expr_string\":\"\",\"generated_stored\":false,\"dependences\":null,\"type\":{\"Tp\":3,\"Flag\":4099,\"Flen\":11,\"Decimal\":0,\"Charset\":\"binary\",\"Collate\":\"binary\",\"Elems\":null,\"ElemsIsBinaryLit\":null,\"Array\":false},\"state\":5,\"comment\":\"\",\"hidden\":false,\"change_state_info\":null,\"version\":2},{\"id\":2,\"name\":{\"O\":\"name\",\"L\":\"name\"},\"offset\":1,\"origin_default\":null,\"origin_default_bit\":null,\"default\":null,\"default_bit\":null,\"default_is_expr\":false,\"generated_expr_string\":\"\",\"generated_stored\":false,\"dependences\":null,\"type\":{\"Tp\":252,\"Flag\":0,\"Flen\":65535,\"Decimal\":0,\"Charset\":\"utf8mb4\",\"Collate\":\"utf8mb4_bin\",\"Elems\":null,\"ElemsIsBinaryLit\":null,\"Array\":false},\"state\":5,\"comment\":\"\",\"hidden\":false,\"change_state_info\":null,\"version\":2}],\"index_info\":[],\"constraint_info\":null,\"fk_info\":null,\"state\":5,\"pk_is_handle\":true,\"is_common_handle\":false,\"common_handle_version\":0,\"comment\":\"\",\"auto_inc_id\":0,\"auto_id_cache\":0,\"auto_rand_id\":0,\"max_col_id\":2,\"max_idx_id\":0,\"max_fk_id\":0,\"max_cst_id\":0,\"update_timestamp\":447128528146923530,\"ShardRowIDBits\":0,\"max_shard_row_id_bits\":0,\"auto_random_bits\":0,\"auto_random_range_bits\":0,\"pre_split_regions\":0,\"partition\":null,\"compression\":\"\",\"view\":null,\"sequence\":null,\"Lock\":null,\"version\":5,\"tiflash_replica\":null,\"is_columnar\":false,\"temp_table_type\":0,\"cache_table_status\":0,\"policy_ref_info\":null,\"stats_options\":null,\"exchange_partition_info\":null,\"ttl_info\":null,\"SchemaID\":90,\"TableName\":{\"db-name\":\"t1\",\"tbl-name\":\"tt\",\"tbl-id\":116,\"is-partition\":false},\"Version\":447128528172875777,\"RowColumnsOffset\":{\"1\":0,\"2\":1},\"ColumnsFlag\":{\"1\":11,\"2\":64},\"HandleIndexID\":-1,\"IndexColumnsOffset\":[[0]]},\"PreTableInfo\":null,\"Type\":3,\"Done\":{},\"Charset\":\"\",\"Collate\":\"\"}"]
[2024/01/19 18:26:15.442 +08:00] [WARN] [mysql_ddl_sink.go:126] ["Execute DDL with error, retry later"] [startTs=447128528146923530] [ddl="CREATE TABLE `t1`.`tt` LIKE `origin_table`"] [namespace=default] [changefeed=f3cb8b03-8184-4547-be07-defeac1e9e53] [error="Error 1146 (42S02): Table 't1.origin_table' doesn't exist"]
[2024/01/19 18:26:15.442 +08:00] [ERROR] [ddl_sink.go:220] ["Execute DDL failed"] [namespace=default] [changefeed=f3cb8b03-8184-4547-be07-defeac1e9e53] [DDL="{\"StartTs\":447128528146923530,\"CommitTs\":447128528172875777,\"Query\":\"CREATE TABLE `t1`.`tt` LIKE `origin_table`\",\"TableInfo\":{\"id\":116,\"name\":{\"O\":\"tt\",\"L\":\"tt\"},\"charset\":\"utf8mb4\",\"collate\":\"utf8mb4_bin\",\"cols\":[{\"id\":1,\"name\":{\"O\":\"id\",\"L\":\"id\"},\"offset\":0,\"origin_default\":null,\"origin_default_bit\":null,\"default\":null,\"default_bit\":null,\"default_is_expr\":false,\"generated_expr_string\":\"\",\"generated_stored\":false,\"dependences\":null,\"type\":{\"Tp\":3,\"Flag\":4099,\"Flen\":11,\"Decimal\":0,\"Charset\":\"binary\",\"Collate\":\"binary\",\"Elems\":null,\"ElemsIsBinaryLit\":null,\"Array\":false},\"state\":5,\"comment\":\"\",\"hidden\":false,\"change_state_info\":null,\"version\":2},{\"id\":2,\"name\":{\"O\":\"name\",\"L\":\"name\"},\"offset\":1,\"origin_default\":null,\"origin_default_bit\":null,\"default\":null,\"default_bit\":null,\"default_is_expr\":false,\"generated_expr_string\":\"\",\"generated_stored\":false,\"dependences\":null,\"type\":{\"Tp\":252,\"Flag\":0,\"Flen\":65535,\"Decimal\":0,\"Charset\":\"utf8mb4\",\"Collate\":\"utf8mb4_bin\",\"Elems\":null,\"ElemsIsBinaryLit\":null,\"Array\":false},\"state\":5,\"comment\":\"\",\"hidden\":false,\"change_state_info\":null,\"version\":2}],\"index_info\":[],\"constraint_info\":null,\"fk_info\":null,\"state\":5,\"pk_is_handle\":true,\"is_common_handle\":false,\"common_handle_version\":0,\"comment\":\"\",\"auto_inc_id\":0,\"auto_id_cache\":0,\"auto_rand_id\":0,\"max_col_id\":2,\"max_idx_id\":0,\"max_fk_id\":0,\"max_cst_id\":0,\"update_timestamp\":447128528146923530,\"ShardRowIDBits\":0,\"max_shard_row_id_bits\":0,\"auto_random_bits\":0,\"auto_random_range_bits\":0,\"pre_split_regions\":0,\"partition\":null,\"compression\":\"\",\"view\":null,\"sequence\":null,\"Lock\":null,\"version\":5,\"tiflash_replica\":null,\"is_columnar\":false,\"temp_table_type\":0,\"cache_table_status\":0,\"policy_ref_info\":null,\"stats_options\":null,\"exchange_partition_info\":null,\"ttl_info\":null,\"SchemaID\":90,\"TableName\":{\"db-name\":\"t1\",\"tbl-name\":\"tt\",\"tbl-id\":116,\"is-partition\":false},\"Version\":447128528172875777,\"RowColumnsOffset\":{\"1\":0,\"2\":1},\"ColumnsFlag\":{\"1\":11,\"2\":64},\"HandleIndexID\":-1,\"IndexColumnsOffset\":[[0]]},\"PreTableInfo\":null,\"Type\":3,\"Done\":{},\"Charset\":\"\",\"Collate\":\"\"}"] [error="[CDC:ErrChangefeedUnretryable]changefeed is in unretryable state, please check the error message, and you should manually handle it: Error 1146 (42S02): Table 't1.origin_table' doesn't exist"] [errorVerbose="[CDC:ErrChangefeedUnretryable]changefeed is in unretryable state, please check the error message, and you should manually handle it: Error 1146 (42S02): Table 't1.origin_table' doesn't exist\ngithub.com/pingcap/errors.AddStack\n\tgithub.com/pingcap/[email protected]/errors.go:174\ngithub.com/pingcap/errors.(*Error).GenWithStackByArgs\n\tgithub.com/pingcap/[email protected]/normalize.go:164\ngithub.com/pingcap/tiflow/pkg/errors.WrapError\n\tgithub.com/pingcap/tiflow/pkg/errors/helper.go:34\ngithub.com/pingcap/tiflow/pkg/errors.WrapChangefeedUnretryableErr\n\tgithub.com/pingcap/tiflow/pkg/errors/helper.go:153\ngithub.com/pingcap/tiflow/cdc/sink/ddlsink/mysql.(*DDLSink).WriteDDLEvent\n\tgithub.com/pingcap/tiflow/cdc/sink/ddlsink/mysql/mysql_ddl_sink.go:107\ngithub.com/pingcap/tiflow/cdc/owner.(*ddlSinkImpl).writeDDLEvent.func1\n\tgithub.com/pingcap/tiflow/cdc/owner/ddl_sink.go:214\ngithub.com/pingcap/tiflow/cdc/owner.(*ddlSinkImpl).retrySinkActionWithErrorReport\n\tgithub.com/pingcap/tiflow/cdc/owner/ddl_sink.go:164\ngithub.com/pingcap/tiflow/cdc/owner.(*ddlSinkImpl).writeDDLEvent\n\tgithub.com/pingcap/tiflow/cdc/owner/ddl_sink.go:234\ngithub.com/pingcap/tiflow/cdc/owner.(*ddlSinkImpl).run.func1\n\tgithub.com/pingcap/tiflow/cdc/owner/ddl_sink.go:262\nruntime.goexit\n\truntime/asm_amd64.s:1598"]
......
[2024/01/19 18:26:15.797 +08:00] [INFO] [processor.go:938] ["Processor closed agent successfully"] [namespace=default] [changefeed=f3cb8b03-8184-4547-be07-defeac1e9e53]
[2024/01/19 18:26:15.797 +08:00] [INFO] [processor.go:947] ["processor closed"] [namespace=default] [changefeed=f3cb8b03-8184-4547-be07-defeac1e9e53]
[2024/01/19 18:26:15.797 +08:00] [WARN] [server.go:284] ["handler not found"] [topic=changefeed/default/f3cb8b03-8184-4547-be07-defeac1e9e53/agent]

Versions of the cluster

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

| Release Version: v7.1.1
Edition: Community
Git Commit Hash: cf441574864be63938524e7dfcf7cc659edc3dd8
Git Branch: heads/refs/tags/v7.1.1
UTC Build Time: 2023-07-19 10:20:53
GoVersion: go1.20.6
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |

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

TiKV
Release Version:   7.1.1
Edition:           Community
Git Commit Hash:   a9676d148e61f741ce9ee699f992b9ce06f91706
Git Commit Branch: heads/refs/tags/v7.1.1
UTC Build Time:    2023-07-22 16:59:13
Rust Version:      rustc 1.67.0-nightly (96ddd32c4 2022-11-14)
Enable Features:   pprof-fp jemalloc mem-profiling portable sse test-engine-kv-rocksdb test-engine-raft-raft-engine cloud-aws cloud-gcp cloud-azure
Profile:           dist_release

TiCDC version (execute cdc version):

Release Version: v7.1.1
Git Commit Hash: ddedac969d73323165adfd3ff6f1a55df4df8f36
Git Branch: heads/refs/tags/v7.1.1
UTC Build Time: 2023-07-19 13:32:57
Go Version: go version go1.20.6 linux/amd64
Failpoint Build: false

[fubinzh]

/severity major

[asddongmen]

The reason for this issue is that the DDL(Received by cdc) does not provide any information indicating that it should be executed in the t2 database. As a result, TiCDC is unable to handle it correctly.

I will conduct further investigation to find a solution, if any.

In the meantime, you can explicitly specify the database name in the SQL statement as a workaround for this issue. For example:

create table t1.tt like t2.origin_table;

cc @dlf412

[asddongmen]

After investigating, I have found that it is impossible for CDC to handle DDLs reported in this issue correctly. This is because CDC lacks the necessary context information about the database where this DML was executed.

So I consider it as a product functionality limit, not a bug.

To address this, I will add a description to the official TiCDC documentation to inform users that TiCDC can only handle cross-database DDLs that explicitly specify the database name.

Once this documentation update is complete, I will proceed to close this issue. cc @dlf412 @fubinzh @flowbehappy

[asddongmen]

May be we can make TiDB provide enough information for TiCDC to handle such type of DDLs in the future.

[dlf412]

May be we can make TiDB provide enough information for TiCDC to handle such type of DDLs in the future.

We are hopeful that this feature can be implemented as soon as possible. It's imperative for TiDB to provide sufficient context to enable TiCDC to handle such types of DDLs. This is essential to create a logical closed loop, ensuring seamless integration and functionality.