pingcap / pingcap/tidb

TiDB should generate restorable FK TableInfo even when tidb_enable_foreign_key=0

Open
#69,900 2 comments 0 reactions 1 assignee Claimed by @bb7133 View on GitHub
affects-8.5 component/br severity/major type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)

When `tidb_enable_foreign_key=0`, TiDB still records foreign key metadata in `TableInfo.ForeignKeys` if the user defines a foreign key. The FK is syntax-only / invalid (`FKVersion0`), and `SHOW CREATE TABLE` prints `/* FOREIGN KEY INVALID */`.

This behavior can be verified on v8.5.4:

```sql
CREATE DATABASE invalid_fk_demo;
USE invalid_fk_demo;
CREATE TABLE parent (id INT PRIMARY KEY);

SET GLOBAL tidb_enable_foreign_key = 0;
CREATE TABLE child_invalid (
id INT PRIMARY KEY,
parent_id INT,
CONSTRAINT fk_invalid FOREIGN KEY (parent_id) REFERENCES parent(id)
);

SHOW CREATE TABLE child_invalid;
```

The output contains:

```sql
CONSTRAINT `fk_invalid` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) /* FOREIGN KEY INVALID */
```

This means `tidb_enable_foreign_key=0` does not mean TiDB discards the FK metadata. TiDB still persists FK metadata for compatibility / syntax preservation. Therefore, this metadata should be complete and restorable.

However, in some backup metadata for tables created with FK syntax while FK enforcement was disabled, the `fk_info` can contain an empty referenced schema, for example:

```json
"fk_info": [
{
"id": 1,
"fk_name": {"O": "gift_templates_app_id_fkey", "L": "gift_templates_app_id_fkey"},
"ref_schema": {"O": "", "L": ""},
"ref_table": {"O": "applications", "L": "applications"},
"ref_cols": [{"O": "id", "L": "id"}],
"cols": [{"O": "app_id", "L": "app_id"}],
"state": 5,
"version": 0
}
]
```

Restoring such metadata into a newer TiDB can fail while creating the table.

This is related to #64969, but the expected behavior here is more specific: if TiDB decides to keep FK metadata even when `tidb_enable_foreign_key=0`, that `TableInfo` must still be self-consistent and restorable. A syntax-only / invalid FK should not make BR restore fail.

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

TiDB/BR should be able to restore tables whose FK syntax was recorded while `tidb_enable_foreign_key=0`.

Expected behavior could be one of the following:

1. When creating such tables, TiDB should always generate complete FK metadata, including a valid `RefSchema`, even if the FK version is `FKVersion0` and the constraint is not enforced.
2. When restoring old backup metadata, BR/DDL should normalize legacy same-schema FK metadata whose `RefSchema` is empty before creating the DDL job.
3. If a `FKVersion0` FK is treated as syntax-only metadata, restore/DDL should not let it break DDL scheduling metadata construction. For example, `getSharedInvolvingSchemaInfo` can skip `fk.Version < model.FKVersion1`, or restore can strip invalid FK metadata and let users rebuild valid FK constraints later.

The core requirement is: disabling FK enforcement must not generate or preserve broken FK `TableInfo` that cannot be restored later.

### 3. What did you see instead (Required)

Restore fails before data ranges are processed, while creating the table, with:

```text
Error: DDL job operating on schema or table, must have non-empty name set in InvolvingSchemaInfo
```

The source path is:

- `CreateTableWithInfo` calls `getSharedInvolvingSchemaInfo(tbInfo)`.
- `getSharedInvolvingSchemaInfo` currently iterates `info.ForeignKeys` and appends:

```go
model.InvolvingSchemaInfo{
Database: fk.RefSchema.L,
Table: fk.RefTable.L,
Mode: model.SharedInvolving,
}
```

- For FK metadata whose `RefSchema` is empty, this creates:

```go
{Database: "", Table: "applications", Mode: SharedInvolving}
```

- `DoDDLJobWrapper` calls `job.CheckInvolvingSchemaInfo()`, which rejects db/table involving entries with empty database or table name and returns the error above.

There are also many FK code paths that already treat `FKVersion0` specially and skip invalid FKs, for example:

- `checkTableForeignKeysValid`: skips `fk.Version < model.FKVersion1`
- `checkTableForeignKeyValidInOwner`: skips `fk.Version < model.FKVersion1`
- `addIndexForForeignKey`: skips `fk.Version < model.FKVersion1`
- `infoschema.addReferredForeignKeys`: skips `fk.Version < model.FKVersion1`

So the restore/DDL scheduling path should either preserve complete FK metadata or handle invalid/legacy FK metadata consistently.

### 4. What is your TiDB version? (Required)

Verified current behavior on TiDB playground v8.5.4:

```text
Release Version: v8.5.4
Git Commit Hash: e4e814fdc0afe9c3a6e5e96f129d83df802ab820
```

The problematic restore/DDL source path exists in newer code as well.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.