Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-7264

[ERROR] InnoDB: dict_load_foreigns() returned 38 for ALTER TABLE t1

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: 5.6.47-87.0, 5.7.30-33
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      ALTER table failed with the following error for a table with a foreign key.

      [ERROR] InnoDB: dict_load_foreigns() returned 38 for ALTER TABLE

       

      Reproduciable test case: 

      Percona server 5.6.49

       

      Test1:
      drop database test1; create database test1; use test1;
      CREATE TABLE t1 (a INT, KEY(a), UNIQUE(a), FOREIGN KEY (a) REFERENCES t1 (a)) ENGINE=InnoDB;
      ALTER TABLE t1 DROP INDEX a;
      ALTER TABLE t1 ADD COLUMN v INT;
      
      mysql [localhost] {msandbox} (test1) > ALTER TABLE t1 ADD COLUMN v INT;
      ERROR 1215 (HY000): Cannot add foreign key constraint
      mysql [localhost] {msandbox} (test1) >
      
      Test2:
      drop database test; create database test; use test;
      CREATE TABLE t1 (f VARCHAR(256)) ENGINE=InnoDB;
      SET SESSION FOREIGN_KEY_CHECKS= OFF ;
      ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x);
      ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
      
      
      OR (Also reproduciable without setting FOREIGN_KEY_CHECKS= OFF)
      
      CREATE TABLE t1 (a INT, KEY(a), UNIQUE(a), FOREIGN KEY (a) REFERENCES t1 (a)) ENGINE=InnoDB
      ALTER TABLE t1 DROP INDEX a;
      ALTER TABLE t1 ADD COLUMN v INT;
      
      mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
      ERROR 1215 (HY000): Cannot add foreign key constraint
      mysql [localhost] {msandbox} (test) >
      
      Version: '5.6.49-89.0' socket: '/tmp/mysql_sandbox5649.sock' port: 5649 Percona Server (GPL), Release 89.0, Revision d043d30
      2020-08-23 18:51:21 16339 [ERROR] InnoDB: dict_load_foreigns() returned 38 for ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f)
      2020-08-23 18:53:32 16339 [ERROR] InnoDB: dict_load_foreigns() returned 38 for ALTER TABLE t1 ADD COLUMN v INT
      

       After mysql restart accessing these tables will add the following warning in mysql log.

       

      mysql [localhost] {msandbox} ((none)) > use test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
      Database changed
      mysql [localhost] {msandbox} (test) > show create table t1\G
      ERROR 1146 (42S02): Table 'test.t1' doesn't exist
      mysql [localhost] {msandbox} (test) >
      

       

      mysql log:

      Version: '5.6.49-89.0' socket: '/tmp/mysql_sandbox5649.sock' port: 5649 Percona Server (GPL), Release 89.0, Revision d043d30
       2020-08-23 20:16:29 16156 [Warning] InnoDB: Load table 'test/t1' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
       2020-08-23 20:16:29 16156 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
       2020-08-23 20:16:34 16156 [Warning] InnoDB: Load table 'test/t1' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
       2020-08-23 20:16:34 16156 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

       

      Percona Server 5.7.30:

      Test1:
      mysql [localhost] {msandbox} (test1) > ALTER TABLE t1 ADD COLUMN v INT;
      Query OK, 0 rows affected, 1 warning (0.06 sec)
      Records: 0 Duplicates: 0 Warnings: 1
      mysql [localhost] {msandbox} (test1) > show warnings;
      +---------+------+------------------------------------------------+
      | Level | Code | Message |
      +---------+------+------------------------------------------------+
      | Warning | 1088 | InnoDB: Could not add foreign key constraints. |
      +---------+------+------------------------------------------------+
      1 row in set (0.00 sec)
      Test2:
      mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
      Query OK, 0 rows affected, 2 warnings (0.42 sec)
      Records: 0 Duplicates: 0 Warnings: 2
      mysql [localhost] {msandbox} (test) > show warnings;
      +---------+------+--------------------------------------------------+
      | Level | Code | Message |
      +---------+------+--------------------------------------------------+
      | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID |
      | Warning | 1088 | InnoDB: Could not add foreign key constraints. |
      +---------+------+--------------------------------------------------+
      2 rows in set (0.00 sec)
      

       

      5.7 also affected with different behavior.

       

      Run Test1 and Test2, restart mysql and try to access these tables,
      mysql [localhost] {msandbox} (test) > ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
      Query OK, 0 rows affected, 2 warnings (0.41 sec)
      Records: 0 Duplicates: 0 Warnings: 2
      mysql [localhost] {msandbox} (test) > show warnings;
      +---------+------+--------------------------------------------------+
      | Level | Code | Message |
      +---------+------+--------------------------------------------------+
      | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID |
      | Warning | 1088 | InnoDB: Could not add foreign key constraints. |
      +---------+------+--------------------------------------------------+
      2 rows in set (0.00 sec)
      

       

       

      Restart mysql service. 

       

      mysql [localhost] {msandbox} (test) > show create table t1;
      ERROR 1146 (42S02): Table 'test.t1' doesn't exist

       

       

      mysql error log:

      2020-08-23T14:30:18.236096Z 2 [Warning] InnoDB: Load table `test`.`t1` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
      2020-08-23T14:30:18.236248Z 2 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
      
      mysql [localhost] {msandbox} (test) > drop database test;
      ERROR 1010 (HY000): Error dropping database (can't rmdir './test', errno: 39)
      

       

       

      2020-08-23T14:36:40.187472Z 2 [Warning] InnoDB: Load table `test`.`t1` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
      2020-08-23T14:36:40.187696Z 2 [ERROR] InnoDB: Cannot load table test/t1 from InnoDB internal data dictionary during drop database
      

        

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              lalit.choudhary Lalit Choudhary
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: