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

ALTER TABLE ... ADD COLUMN damages foreign keys which are pointed to the table being altered

    XMLWordPrintable

Details

    Description

      CREATE DATABASE tmp_1;
      use tmp_1;
       
      CREATE TABLE users
      (
          u_id INT NOT NULL,
          UNIQUE INDEX UNIQ_1483A5E967CB5338 (u_id),
          PRIMARY KEY (u_id)
      ) ENGINE = InnoDB;
       
      CREATE TABLE contracts
      (
          id      INT AUTO_INCREMENT NOT NULL,
          user_id INT                NOT NULL,
          INDEX IDX_950A973BE43E81 (user_id),
          PRIMARY KEY (id)
      ) ENGINE = InnoDB;
       
      ALTER TABLE contracts ADD CONSTRAINT FK_950A973BE43E81 FOREIGN KEY (user_id) REFERENCES users (u_id);
       
      ALTER TABLE contracts ADD status ENUM ('a', 'b', 'c');
       
      ALTER TABLE users
          CHANGE u_id id INT NOT NULL,
          ADD name VARCHAR(255) DEFAULT NULL;
       
      ALTER TABLE users CHANGE id u_id INT NOT NULL;
       
      ALTER TABLE contracts CHANGE status status VARCHAR(20) DEFAULT NULL;
      

      Expected outcome:
      Column status in contracts table is changed to type VARCHAR(20) successfully.

      Actual outcome:
      The queries (the last line) fail with this error:

      ERROR 1025 (HY000): Error on rename of './tmp_1/#sql-1_8' to './tmp_1/contracts' (errno: 150 "Foreign key constraint is incorrectly formed")
      

      After the error occurs, the table contracts does not exist anymore (in the filesystem it is replaced with a file #sql-1_8).

      Reproduction and details:
      This seems to be a very specific bug which I cannot reproduce in any other way. Some points that I've noticed while trying to reproduce this:

      • This is reproducable on all version from mariadb:10.3.2 and upwards (probably introduced with https://mariadb.com/kb/en/library/instant-add-column-for-innodb/)
      • The last line must be an ALTER TABLE statement which changes a columns type from enum to varchar or vice versa, no other types work as far as I know.
      • Before changing a column type which triggers the error, there must be a new column added to the table which is the owning side of a foreign key in a single SQL query. i.e. if we changed

        ALTER TABLE users CHANGE u_id id INT NOT NULL, ADD name VARCHAR(255) DEFAULT NULL;
        

        to

        ALTER TABLE users CHANGE u_id id INT NOT NULL;
        ALTER TABLE users ADD name VARCHAR(255) DEFAULT NULL;
        

        There would not be an error anymore.

      Attachments

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              auciutad Tadas Aučiūnas
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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