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

ALTER causes discrepancy in FK information between server and engine, unexpected ER_CANNOT_ADD_FOREIGN

Details

    Description

      Possibly related to MDEV-24532.

      --source include/have_innodb.inc
      		 
       
      		 
      CREATE TABLE t1 (a INT, b INT, KEY(a), FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=InnoDB;
      		 
      SET FOREIGN_KEY_CHECKS= OFF;
      		 
      ALTER TABLE t1 MODIFY a SMALLINT;
      		 
      SHOW CREATE TABLE t1;
      		 
      SELECT * FROM information_schema.innodb_sys_foreign;
      		 
      SELECT UNIQUE_CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME FROM information_schema.referential_constraints;
      		 
      TRUNCATE TABLE t1;
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE t1;

      In the test case above, 10.2+ versions accept and execute ALTER TABLE, and the column type gets modified. However, after that

      • SHOW CREATE TABLE does not show the foreign key any longer;
      • information_schema.innodb_sys_foreign still has a record for the foreign key on t1, but information_schema.referential_constraints does not;
      • further TRUNCATE TABLE fails with ER_CANNOT_ADD_FOREIGN.

      10.5 1b12e251

      		 
      SHOW CREATE TABLE t1;
      		 
      Table	Create Table
      		 
      t1	CREATE TABLE `t1` (
      		 
        `a` smallint(6) DEFAULT NULL,
      		 
        `b` int(11) DEFAULT NULL,
      		 
        KEY `a` (`a`),
      		 
        KEY `b` (`b`)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      		 
      SELECT * FROM information_schema.innodb_sys_foreign;
      		 
      ID	FOR_NAME	REF_NAME	N_COLS	TYPE
      		 
      test/t1_ibfk_1	test/t1	test/t1	1	0
      		 
      SELECT UNIQUE_CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME FROM information_schema.referential_constraints;
      		 
      UNIQUE_CONSTRAINT_NAME	TABLE_NAME	REFERENCED_TABLE_NAME
      		 
      TRUNCATE TABLE t1;
      		 
      bug.trunc4 'innodb'                      [ fail ]
      		 
              Test ended at 2021-01-09 02:27:46
      		 
       
      		 
      CURRENT_TEST: bug.trunc4
      		 
      mysqltest: At line 9: query 'TRUNCATE TABLE t1' failed: 1215: Cannot add foreign key constraint for `t1`

      Reproducible on 10.2-10.5.
      On 10.1 and MySQL 5.7 ALTER TABLE fails with ER_ERROR_ON_RENAME, and no further damage is done.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23876 event_scheduler acquires Backup lock and does not release upon completion

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24532 Table corruption ER_NO_SUCH_TABLE_IN_ENGINE or ER_CRASHED_ON_USAGE after ALTER on table with foreign key

          • Major - Major loss of function.
          • Closed

          Activity

            elenst Elena Stepanova added a comment -

            Here is another test case. I don't know if it's exactly the same issue, since there is no visible discrepancy here, just the unexpected error, but creating yet another JIRA report about TRUNCATE table with corrupt foreign keys, in addition to this one and MDEV-24532, seems way too excessive. Feel free to treat it any way you like.

            --source include/have_innodb.inc
            		 
             
            		 
            CREATE TABLE t1 (a INT, b INT, KEY ind1(a,b), KEY ind2(a), FOREIGN KEY(a) REFERENCES t1(a)) ENGINE=InnoDB;
            		 
            ALTER TABLE t1 DROP KEY ind1;
            		 
            TRUNCATE TABLE t1;
            		 
             
            		 
            # Cleanup
            		 
            DROP TABLE t1;

            The test case is admittedly not very practical, but on the "positive" side it doesn't require disabling FOREIGN_KEY_CHECKS.
            Unlike with the test case in the description, ALTER does not cause the FK to disappear from SHOW CREATE or I_S tables; however, TRUNCATE still causes ER_CANNOT_ADD_FOREIGN.

            elenst Elena Stepanova added a comment - Here is another test case. I don't know if it's exactly the same issue, since there is no visible discrepancy here, just the unexpected error, but creating yet another JIRA report about TRUNCATE table with corrupt foreign keys, in addition to this one and MDEV-24532 , seems way too excessive. Feel free to treat it any way you like. --source include/have_innodb.inc   CREATE TABLE t1 (a INT , b INT , KEY ind1(a,b), KEY ind2(a), FOREIGN KEY (a) REFERENCES t1(a)) ENGINE=InnoDB; ALTER TABLE t1 DROP KEY ind1; TRUNCATE TABLE t1;   # Cleanup DROP TABLE t1; The test case is admittedly not very practical, but on the "positive" side it doesn't require disabling FOREIGN_KEY_CHECKS. Unlike with the test case in the description, ALTER does not cause the FK to disappear from SHOW CREATE or I_S tables; however, TRUNCATE still causes ER_CANNOT_ADD_FOREIGN.

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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