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

CREATE INDEX fails to heal a FOREIGN KEY constraint

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

          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34309 CHECK TABLE should check that foreign key relations are correct

          • Critical - Crashes, loss of data, severe memory leak.
          • In Testing

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment - - edited

            In fact, it is not a problem of the CHECK, the FK itself does not work properly in this case. So, the CHECK correctly shows a problem, it's just that we shouldn't have a problem here.

            main 4c956fa15be059e35f8ed0de85dc72d5827cee4d

            		 
            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 (a int) engine=InnoDB;
            		 
            set foreign_key_checks=1;
            		 
            alter table t1 add key(a);
            		 
            insert into t1 values (1);
            		 
            insert into t2 values (1);
            		 
            mysqltest: At line 9: query 'insert into t2 values (1)' failed: ER_NO_REFERENCED_ROW_2 (1452): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
            		 
            create or replace table t2 (b int, foreign key (b) references t1(a)) engine=InnoDB;
            		 
            insert into t2 values (1);
            		 
            drop table t2, t1;

            The above is true for 10.6+.
            10.5 is worse, it doesn't even allow to re-create the referencing table. But there is no way such a thing would be fixed in 10.5 now, so it doesn't matter.

            elenst Elena Stepanova added a comment - - edited In fact, it is not a problem of the CHECK, the FK itself does not work properly in this case. So, the CHECK correctly shows a problem, it's just that we shouldn't have a problem here. main 4c956fa15be059e35f8ed0de85dc72d5827cee4d 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 (a int ) engine=InnoDB; set foreign_key_checks=1; alter table t1 add key (a); insert into t1 values (1); insert into t2 values (1); mysqltest: At line 9: query 'insert into t2 values (1)' failed: ER_NO_REFERENCED_ROW_2 (1452): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)) create or replace table t2 (b int , foreign key (b) references t1(a)) engine=InnoDB; insert into t2 values (1); drop table t2, t1; The above is true for 10.6+. 10.5 is worse, it doesn't even allow to re-create the referencing table. But there is no way such a thing would be fixed in 10.5 now, so it doesn't matter.
            marko Marko Mäkelä added a comment - - edited

            Let’s limit the scope of this ticket only to the omission of InnoDB in commit_cache_norebuild().

            When it comes to CHECK TABLE, in my opinion it is wrong to claim "error" and "Corrupt" when there is no foreign key constraint violation. Yes, the constraint is kind of malformed, but InnoDB would block any DML from violating the constraint. This is analogous to MDEV-35445, where the intention of an actual user is to use constraints in a creative way, relying on DML time errors.

            marko Marko Mäkelä added a comment - - edited Let’s limit the scope of this ticket only to the omission of InnoDB in commit_cache_norebuild() . When it comes to CHECK TABLE , in my opinion it is wrong to claim "error" and "Corrupt" when there is no foreign key constraint violation. Yes, the constraint is kind of malformed, but InnoDB would block any DML from violating the constraint. This is analogous to MDEV-35445 , where the intention of an actual user is to use constraints in a creative way, relying on DML time errors.
            marko Marko Mäkelä added a comment -

            https://github.com/MariaDB/server/pull/3800

            marko Marko Mäkelä added a comment - https://github.com/MariaDB/server/pull/3800

            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.