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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.3(EOL), 10.4(EOL)
    • 10.3.17, 10.4.7
    • None
    • Docker container using the official mariadb:10.3.2 image

    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

            Changing it to critical since the bug can potentially corrupt the database

            auciutad Tadas Aučiūnas added a comment - Changing it to critical since the bug can potentially corrupt the database

            For instant alter, InnoDB failed to change the foreign key cache. So it leads to the failure of consecutive DDLs.

            thiru Thirunarayanan Balathandayuthapani added a comment - For instant alter, InnoDB failed to change the foreign key cache. So it leads to the failure of consecutive DDLs.

            Patch is in bb-10.3-MDEV-19974

            thiru Thirunarayanan Balathandayuthapani added a comment - Patch is in bb-10.3- MDEV-19974

            I suggest to rename a newly added argument. Otherwise looks good.

            kevg Eugene Kosov (Inactive) added a comment - I suggest to rename a newly added argument. Otherwise looks good.

            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.