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

ERROR 1025 (HY000): Error on rename, errno: 150 "Foreign key constraint is incorrectly formed"

Details

    Description

      Similar to the previously fixed MDEV-29092 we see:

      11.5.0 e4afa610539ae01164485554e2de839bea9de816 (Debug)

      		 
      11.5.0-dbg>CREATE TABLE t (c1 INT PRIMARY KEY,c2 INT,FOREIGN KEY(c2) REFERENCES t (c1));
      		 
      Query OK, 0 rows affected (0.011 sec)
      		 
       
      		 
      11.5.0-dbg>ALTER TABLE t DROP PRIMARY KEY;
      		 
      ERROR 1025 (HY000): Error on rename of './test/#sql-alter-2ff04a-4' to './test/t' (errno: 150 "Foreign key constraint is incorrectly formed")
      		 
      11.5.0-dbg>SHOW WARNINGS;                          
      +-------+------+---------------------------------------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                                                   |
      		 
      +-------+------+---------------------------------------------------------------------------------------------------------------------------+
      		 
      | Error | 1025 | Error on rename of './test/#sql-alter-2ff04a-4' to './test/t' (errno: 150 "Foreign key constraint is incorrectly formed") |
      		 
      +-------+------+---------------------------------------------------------------------------------------------------------------------------+
      		 
      1 row in set (0.000 sec)

      11.5.0 e4afa610539ae01164485554e2de839bea9de816 (Debug)

      		 
      2024-06-15 11:50:06 4 [ERROR] InnoDB: In ALTER TABLE `test`.`t` has or is referenced in foreign key constraints which are not compatible with the new table definition.

      MTR Testcase:

      --source include/have_innodb.inc
      		 
      CREATE TABLE t (c1 INT PRIMARY KEY,c2 INT,FOREIGN KEY(c2) REFERENCES t (c1)) ENGINE=InnoDB;
      		 
      ALTER TABLE t DROP PRIMARY KEY;
      		 
      SHOW WARNINGS;

      Bug confirmed present in:
      MariaDB: 10.5.26 (dbg), 10.5.26 (opt), 10.6.19 (dbg), 10.6.19 (opt), 10.11.9 (dbg), 10.11.9 (opt), 11.1.6 (dbg), 11.1.6 (opt), 11.2.5 (dbg), 11.2.5 (opt), 11.4.2 (dbg), 11.4.2 (opt), 11.5.0 (dbg), 11.5.0 (opt), 11.6.0 (dbg), 11.6.0 (opt)

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29092 FOREIGN_KEY_CHECKS does not prevent non-copy alter from creating invalid FK structure

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Roel Roel Van de Paar added a comment -

            Another example (InnoDB table)

            CREATE TABLE t1 (id INT PRIMARY KEY,t1_id INT,INDEX par_ind (t1_id),FOREIGN KEY(t1_id) REFERENCES t1 (id) ON DELETE CASCADE);
            		 
            ALTER TABLE t1 DROP PRIMARY KEY;

            11.6.0 26f01f8be598e77c8d157cc6358508b7193a9187 (Optimized, CLI)

            		 
            ERROR 1025 (HY000): Error on rename of './test/#sql-alter-263f47-4' to './test/t1' (errno: 150 "Foreign key constraint is incorrectly formed")

            11.6.0 26f01f8be598e77c8d157cc6358508b7193a9187 (Optimized, Error log)

            		 
            2024-06-18 15:34:30 4 [ERROR] InnoDB: In ALTER TABLE `test`.`t1` has or is referenced in foreign key constraints which are not compatible with the new table definition.

            Roel Roel Van de Paar added a comment - Another example (InnoDB table) CREATE TABLE t1 (id INT PRIMARY KEY ,t1_id INT , INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE ); ALTER TABLE t1 DROP PRIMARY KEY ; 11.6.0 26f01f8be598e77c8d157cc6358508b7193a9187 (Optimized, CLI) ERROR 1025 (HY000): Error on rename of './test/#sql-alter-263f47-4' to './test/t1' (errno: 150 "Foreign key constraint is incorrectly formed") 11.6.0 26f01f8be598e77c8d157cc6358508b7193a9187 (Optimized, Error log) 2024-06-18 15:34:30 4 [ERROR] InnoDB: In ALTER TABLE `test`.`t1` has or is referenced in foreign key constraints which are not compatible with the new table definition.

            People

              thiru Thirunarayanan Balathandayuthapani
              Roel Roel Van de Paar
              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.