Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35962

CREATE INDEX fails to heal a FOREIGN KEY constraint

    XMLWordPrintable

Details

    Description

      --source include/have_innodb.inc
       
      set foreign_key_checks=0;
      create or replace table t2 (b int, foreign key (b) references t1(a)) engine=InnoDB;
      create or replace table t1 (pk int primary key, a int) engine=InnoDB;
      set foreign_key_checks=1;
      check table t2 extended;
      alter table t1 add key(a);
      check table t2 extended;
       
      create or replace table t2 (b int, foreign key (b) references t1(a)) engine=InnoDB;
      check table t2 extended;
       
      drop table t2, t1;
      

      bb-11.8-check-table ceb64ee4d1d5af7ab608bbfd35de88510cce82ea

      check table t2 extended;
      Table	Op	Msg_type	Msg_text
      test.t2	check	Warning	No suitable key found for foreign key t2_ibfk_1 in table test.t1
      test.t2	check	error	Corrupt
      alter table t1 add key(a);
      check table t2 extended;
      Table	Op	Msg_type	Msg_text
      test.t2	check	Warning	No suitable key found for foreign key t2_ibfk_1 in table test.t1
      test.t2	check	error	Corrupt
      create or replace table t2 (b int, foreign key (b) references t1(a)) engine=InnoDB;
      check table t2 extended;
      Table	Op	Msg_type	Msg_text
      test.t2	check	status	OK
      

      The first CHECK correctly returns the error: no suitable key, indeed.
      Then the error is corrected, the key is added.
      But the second CHECK still returns the error, although there is no real problem anymore, which is confirmed by re-creating the referencing table with the same definition as before.
      FLUSH TABLES before the 2nd CHECK does not help, either.

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.