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

Drop of non-existing FK succeeds for temporary table

Details

    Description

      --source include/have_innodb.inc
      		 
       
      		 
      CREATE TEMPORARY TABLE t (a INT, KEY(a)) ENGINE=InnoDB;
      		 
      INSERT INTO t VALUES (1);
      		 
      ALTER TABLE t DROP FOREIGN KEY fk;
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE t;

      10.3 77951dd7

      		 
      CREATE TEMPORARY TABLE t (a INT, KEY(a)) ENGINE=InnoDB;
      		 
      INSERT INTO t VALUES (1);
      		 
      ALTER TABLE t DROP FOREIGN KEY fk;
      		 
      DROP TABLE t;

      So, although there is no foreign key fk (or any other), ALTER succeeds.
      Reproducible on all current version lines of MariaDB and on MySQL 5.7. Not reproducible on MySQL 8.0.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Fix Version/s 10.3 [ 22126 ]
          Fix Version/s 10.4 [ 22408 ]
          Fix Version/s 10.5 [ 23123 ]
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 10.7 [ 24805 ]
          Fix Version/s 10.8 [ 26121 ]
          Fix Version/s 10.9 [ 26905 ]
          Fix Version/s 10.10 [ 27530 ]
          Affects Version/s 10.4 [ 22408 ]
          Affects Version/s 10.5 [ 23123 ]
          Affects Version/s 10.6 [ 24028 ]
          Affects Version/s 10.7 [ 24805 ]
          Affects Version/s 10.8 [ 26121 ]
          Affects Version/s 10.9 [ 26905 ]
          Affects Version/s 10.10 [ 27530 ]
          Assignee Oleksandr Byelkin [ sanja ]
          Description {code:sql}
          --source include/have_innodb.inc

          CREATE TEMPORARY TABLE t (a INT, KEY(a)) ENGINE=InnoDB;
          INSERT INTO t VALUES (1);
          ALTER TABLE t DROP FOREIGN KEY fk;

          # Cleanup
          DROP TABLE t;
          {code}

          {code:sql|title=10.3 77951dd7}
          CREATE TEMPORARY TABLE t (a INT, KEY(a)) ENGINE=InnoDB;
          INSERT INTO t VALUES (1);
          ALTER TABLE t DROP FOREIGN KEY fk;
          DROP TABLE t;
          {code}

          So, although there is no foreign key fk (or any other), ALTER succeeds.
          Reproducible on all current version lines of MariaDB and on MySQL 5.7. Not reproducible on MySQL 8.0.
          Labels upstream-fixed
          elenst Elena Stepanova added a comment -

          According to marko,

          The culprit should be outside InnoDB, because ALTER TABLE on temporary tables always goes through the ALGORITHM=COPY route.

          elenst Elena Stepanova added a comment - According to marko , The culprit should be outside InnoDB, because ALTER TABLE on temporary tables always goes through the ALGORITHM=COPY route.
          elenst Elena Stepanova made changes -
          Summary Drop of non-existing FK dropped succeeds on temporary table Drop of non-existing FK succeeds on temporary table
          elenst Elena Stepanova made changes -
          Summary Drop of non-existing FK succeeds on temporary table Drop of non-existing FK succeeds for temporary table
          marko Marko Mäkelä added a comment -

          I do not think that temporary tables ever supported FOREIGN KEY constraints in InnoDB, not even when they were actually persistent tables (before InnoDB in MySQL 5.7 and MariaDB 10.2 stopped writing any log for them).

          marko Marko Mäkelä added a comment - I do not think that temporary tables ever supported FOREIGN KEY constraints in InnoDB, not even when they were actually persistent tables (before InnoDB in MySQL 5.7 and MariaDB 10.2 stopped writing any log for them).
          elenst Elena Stepanova added a comment -

          And yet, adding an invalid FK to a temporary InnoDB table causes typical "incorrectly formed" errors (unlike adding the same to a MyISAM table, which truly doesn't care). So if it's unsupported, it's inconsistently unsupported.

          elenst Elena Stepanova added a comment - And yet, adding an invalid FK to a temporary InnoDB table causes typical "incorrectly formed" errors (unlike adding the same to a MyISAM table, which truly doesn't care). So if it's unsupported, it's inconsistently unsupported.
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.7 [ 24805 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.3 [ 22126 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.8 [ 26121 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.9 [ 26905 ]
          Fix Version/s 10.10 [ 27530 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.4 [ 22408 ]
          sanja Oleksandr Byelkin made changes -
          Assignee Oleksandr Byelkin [ sanja ] Aleksey Midenkov [ midenok ]

          People

            midenok Aleksey Midenkov
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.