check constraints ddl can't return forever

[jiyfhust]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t` (`a` int(11) DEFAULT NULL);
insert t values(1);
alter table t ADD CONSTRAINT chk CHECK (a > 1) ENFORCED;
alter table t ADD CONSTRAINT chk CHECK (a > 1) ENFORCED;
alter table t drop CONSTRAINT chk;

2. What did you expect to see? (Required)

MySQL 8.0:

mysql> CREATE TABLE `t` (`a` int(11) DEFAULT NULL);
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> insert t values(1);
Query OK, 1 row affected (0.01 sec)

mysql> alter table t ADD CONSTRAINT chk CHECK (a > 1) ENFORCED;
ERROR 3819 (HY000): Check constraint 'chk' is violated.
mysql> alter table t ADD CONSTRAINT chk CHECK (a > 1) ENFORCED;
ERROR 3819 (HY000): Check constraint 'chk' is violated.
mysql> alter table t drop CONSTRAINT chk;
ERROR 3940 (HY000): Constraint 'chk' does not exist.

3. What did you see instead (Required)

mysql> CREATE TABLE `t` (`a` int(11) DEFAULT NULL);
Query OK, 0 rows affected (0.12 sec)

mysql> insert t values(1);
Query OK, 1 row affected (0.00 sec)

mysql> alter table t ADD CONSTRAINT chk CHECK (a > 1) ENFORCED;
ERROR 3819 (HY000): Check constraint 'chk' is violated.
mysql> alter table t ADD CONSTRAINT chk CHECK (a > 1) ENFORCED;
ERROR 3822 (HY000): Duplicate check constraint name 'chk'.
mysql> alter table t drop CONSTRAINT chk;
----------can't return forever------------

TiDB errlog:

[2023/10/14 10:16:29.470 +08:00] [INFO] [ddl_worker.go:996] ["run DDL job"] [worker="worker 1, tp general"] [category=ddl] [jobID=203] [conn=2048917508] [category=ddl] [job="ID:203, Type:drop check constraint, State:cancelling, SchemaState:none, SchemaID:2, TableID:200, RowCount:0, ArgLen:0, start time: 2023-10-14 10:12:26.329 +0800 CST, Err:[ddl:8204]Invalid DDL job%!(EXTRA string=constraint, model.SchemaState=public), ErrCount:237, SnapshotVersion:0"]
[2023/10/14 10:16:29.474 +08:00] [INFO] [ddl_worker.go:1207] ["schema version doesn't change"] [category=ddl]
[2023/10/14 10:16:29.481 +08:00] [INFO] [ddl_worker.go:996] ["run DDL job"] [worker="worker 1, tp general"] [category=ddl] [jobID=203] [conn=2048917508] [category=ddl] [job="ID:203, Type:drop check constraint, State:running, SchemaState:none, SchemaID:2, TableID:200, RowCount:0, ArgLen:0, start time: 2023-10-14 10:12:26.329 +0800 CST, Err:[ddl:8204]Invalid DDL job%!(EXTRA string=constraint, model.SchemaState=public), ErrCount:237, SnapshotVersion:0"]
[2023/10/14 10:16:29.483 +08:00] [WARN] [ddl_worker.go:958] ["run DDL job error"] [worker="worker 1, tp general"] [category=ddl] [jobID=203] [conn=2048917508] [error="[ddl:8204]Invalid DDL job%!(EXTRA string=constraint, model.SchemaState=public)"]
[2023/10/14 10:16:29.485 +08:00] [WARN] [ddl_worker.go:966] ["DDL job error count exceed the limit, cancelling it now"] [worker="worker 1, tp general"] [category=ddl] [jobID=203] [conn=2048917508] [errorCountLimit=3]
[2023/10/14 10:16:29.491 +08:00] [INFO] [ddl_worker.go:840] ["run DDL job failed, sleeps a while then retries it."] [worker="worker 1, tp general"] [category=ddl] [jobID=203] [conn=2048917508] [waitTime=1s] [error="[ddl:8204]Invalid DDL job%!(EXTRA string=constraint, model.SchemaState=public)"]

4. What is your TiDB version? (Required)

master