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

    XMLWordPrintable

    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

            Activity

              People

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

                Dates

                Created:
                Updated:

                  Git Integration