Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Restore failed with foreigh key table when ddl-batch-size=1 #51838

Closed
3pointer opened this issue Mar 16, 2024 · 3 comments · Fixed by #51870
Closed

Restore failed with foreigh key table when ddl-batch-size=1 #51838

3pointer opened this issue Mar 16, 2024 · 3 comments · Fixed by #51870
Assignees
Labels
affects-6.5 This bug affects the 6.5.x(LTS) versions. affects-7.1 This bug affects the 7.1.x(LTS) versions. affects-7.5 This bug affects the 7.5.x(LTS) versions. component/ddl This issue is related to DDL of TiDB. severity/major type/bug The issue is confirmed as a bug.

Comments

@3pointer
Copy link
Contributor

3pointer commented Mar 16, 2024

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

  1. create two tables, one has fk.
CREATE TABLE parent (
    id INT KEY
);

CREATE TABLE child (
    id INT,
    pid INT,
    INDEX idx_pid (pid),
    FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE
);
  1. insert values into two tables
  2. Do a full backup
  3. Do a full restore to another cluster with --ddl-batch-size=1

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

  1. restore success

3. What did you see instead (Required)

  1. restore stuck

4. What is your TiDB version? (Required)

v6.5 or later(include master)

@3pointer 3pointer added the type/bug The issue is confirmed as a bug. label Mar 16, 2024
@3pointer
Copy link
Contributor Author

3pointer commented Mar 16, 2024

The reason is when doing sequential create table. TiDB has a check. which ask table.Info's state to be None or WriteOnly.

tidb/pkg/ddl/table.go

Lines 195 to 220 in b3cb95d

func createTableWithForeignKeys(d *ddlCtx, t *meta.Meta, job *model.Job, tbInfo *model.TableInfo, fkCheck bool) (ver int64, err error) {
switch tbInfo.State {
case model.StateNone:
// create table in non-public state
tbInfo, err = createTable(d, t, job, fkCheck)
if err != nil {
return ver, errors.Trace(err)
}
tbInfo.State = model.StateWriteOnly
ver, err = updateVersionAndTableInfo(d, t, job, tbInfo, true)
if err != nil {
return ver, errors.Trace(err)
}
job.SchemaState = model.StateWriteOnly
case model.StateWriteOnly:
tbInfo.State = model.StatePublic
ver, err = updateVersionAndTableInfo(d, t, job, tbInfo, true)
if err != nil {
return ver, errors.Trace(err)
}
job.FinishTableJob(model.JobStateDone, model.StatePublic, ver, tbInfo)
return ver, nil
default:
return ver, errors.Trace(dbterror.ErrInvalidDDLJob.GenWithStackByArgs("table", tbInfo.State))
}
return ver, errors.Trace(err)

But the restore table's state is Public. which is not handling in this code.(in default branch). It's better to handle Public state here.

@3pointer
Copy link
Contributor Author

3pointer commented Mar 16, 2024

A workaround for restore is don't set ddl-batch-size to 1

@3pointer
Copy link
Contributor Author

If restore cluster is stuck now. and the workaround should be

  1. find the Job ID of the running create table DDL via admin show ddl jobs.
  2. use admin cancel ddl job {JOB_ID} to cancel the running job.
  3. do restore again with --ddl-batch-size={>1}

@jebter jebter added severity/major component/ddl This issue is related to DDL of TiDB. labels Mar 18, 2024
@YangKeao YangKeao self-assigned this Mar 18, 2024
@YangKeao YangKeao added affects-6.5 This bug affects the 6.5.x(LTS) versions. affects-7.1 This bug affects the 7.1.x(LTS) versions. affects-7.5 This bug affects the 7.5.x(LTS) versions. labels Mar 18, 2024
@YangKeao YangKeao removed may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 labels Mar 18, 2024
ti-chi-bot bot pushed a commit that referenced this issue Mar 22, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
affects-6.5 This bug affects the 6.5.x(LTS) versions. affects-7.1 This bug affects the 7.1.x(LTS) versions. affects-7.5 This bug affects the 7.5.x(LTS) versions. component/ddl This issue is related to DDL of TiDB. severity/major type/bug The issue is confirmed as a bug.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants