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

MariaDB crashes with foreign_key_checks=0 when changing a column and adding a foreign key at the same time

Details

    Description

      docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
      		 
      mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql

      migrate.sql:

      CREATE TABLE items (
      		 
          id VARCHAR(20) NOT NULL,
      		 
          PRIMARY KEY (id)
      		 
      );
      		 
      CREATE TABLE sgs_data (
      		 
          id INT(11) NOT NULL AUTO_INCREMENT,
      		 
          Number varchar(15) NOT NULL DEFAULT '',
      		 
          PRIMARY KEY (id)
      		 
      );
      		 
      SET foreign_key_checks=0;
      		 
      ALTER TABLE sgs_data
      		 
          CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
      		 
          ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);

      This causes the MariaDB server to crash (see attached log output).

      • Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
      • Removing `SET foreign_key_checks=0` avoids the crash
      • Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

      Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31086 MODIFY COLUMN can break FK constraints, and lead to unrestorable dumps

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            SystemParadox Simon Williams created issue -
            SystemParadox Simon Williams made changes -
            Field Original Value New Value
            Description {{
            docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
            mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql
            }}

            {{
            -- migrate.sql
            CREATE TABLE items (
                id VARCHAR(20) NOT NULL,
                PRIMARY KEY (id)
            );
            CREATE TABLE sgs_data (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Number varchar(15) NOT NULL DEFAULT '',
                PRIMARY KEY (id)
            );
            SET foreign_key_checks=0;
            ALTER TABLE sgs_data
                CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
                ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);
            }}

            This causes the MariaDB server to crash.

            * Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
            * Removing `SET foreign_key_checks=0` avoids the crash
            * Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

            Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).             		```
            docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
            mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql
            ```

            ```
            -- migrate.sql
            CREATE TABLE items (
                id VARCHAR(20) NOT NULL,
                PRIMARY KEY (id)
            );
            CREATE TABLE sgs_data (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Number varchar(15) NOT NULL DEFAULT '',
                PRIMARY KEY (id)
            );
            SET foreign_key_checks=0;
            ALTER TABLE sgs_data
                CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
                ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);
            ```

            This causes the MariaDB server to crash.

            * Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
            * Removing `SET foreign_key_checks=0` avoids the crash
            * Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

            Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).
            SystemParadox Simon Williams made changes -
            Description ```
            docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
            mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql
            ```

            ```
            -- migrate.sql
            CREATE TABLE items (
                id VARCHAR(20) NOT NULL,
                PRIMARY KEY (id)
            );
            CREATE TABLE sgs_data (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Number varchar(15) NOT NULL DEFAULT '',
                PRIMARY KEY (id)
            );
            SET foreign_key_checks=0;
            ALTER TABLE sgs_data
                CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
                ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);
            ```

            This causes the MariaDB server to crash.

            * Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
            * Removing `SET foreign_key_checks=0` avoids the crash
            * Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

            Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).             		{code}
            docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
            mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql
            {/code}

            ```
            -- migrate.sql
            CREATE TABLE items (
                id VARCHAR(20) NOT NULL,
                PRIMARY KEY (id)
            );
            CREATE TABLE sgs_data (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Number varchar(15) NOT NULL DEFAULT '',
                PRIMARY KEY (id)
            );
            SET foreign_key_checks=0;
            ALTER TABLE sgs_data
                CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
                ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);
            ```

            This causes the MariaDB server to crash.

            * Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
            * Removing `SET foreign_key_checks=0` avoids the crash
            * Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

            Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).
            SystemParadox Simon Williams made changes -
            Description {code}
            docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
            mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql
            {/code}

            ```
            -- migrate.sql
            CREATE TABLE items (
                id VARCHAR(20) NOT NULL,
                PRIMARY KEY (id)
            );
            CREATE TABLE sgs_data (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Number varchar(15) NOT NULL DEFAULT '',
                PRIMARY KEY (id)
            );
            SET foreign_key_checks=0;
            ALTER TABLE sgs_data
                CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
                ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);
            ```

            This causes the MariaDB server to crash.

            * Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
            * Removing `SET foreign_key_checks=0` avoids the crash
            * Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

            Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).             		{code:SQL}
            docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
            mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql
            {code}

            ```
            -- migrate.sql
            CREATE TABLE items (
                id VARCHAR(20) NOT NULL,
                PRIMARY KEY (id)
            );
            CREATE TABLE sgs_data (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Number varchar(15) NOT NULL DEFAULT '',
                PRIMARY KEY (id)
            );
            SET foreign_key_checks=0;
            ALTER TABLE sgs_data
                CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
                ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);
            ```

            This causes the MariaDB server to crash.

            * Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
            * Removing `SET foreign_key_checks=0` avoids the crash
            * Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

            Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).
            SystemParadox Simon Williams made changes -
            Description {code:SQL}
            docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
            mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql
            {code}

            ```
            -- migrate.sql
            CREATE TABLE items (
                id VARCHAR(20) NOT NULL,
                PRIMARY KEY (id)
            );
            CREATE TABLE sgs_data (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Number varchar(15) NOT NULL DEFAULT '',
                PRIMARY KEY (id)
            );
            SET foreign_key_checks=0;
            ALTER TABLE sgs_data
                CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
                ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);
            ```

            This causes the MariaDB server to crash.

            * Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
            * Removing `SET foreign_key_checks=0` avoids the crash
            * Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

            Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).             		{code:SQL}
            docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
            mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql
            {code}

            migrate.sql:

            {code:SQL}
            CREATE TABLE items (
                id VARCHAR(20) NOT NULL,
                PRIMARY KEY (id)
            );
            CREATE TABLE sgs_data (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Number varchar(15) NOT NULL DEFAULT '',
                PRIMARY KEY (id)
            );
            SET foreign_key_checks=0;
            ALTER TABLE sgs_data
                CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
                ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);
            {code}

            This causes the MariaDB server to crash.

            * Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
            * Removing `SET foreign_key_checks=0` avoids the crash
            * Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

            Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).
            SystemParadox Simon Williams made changes -
            Description {code:SQL}
            docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
            mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql
            {code}

            migrate.sql:

            {code:SQL}
            CREATE TABLE items (
                id VARCHAR(20) NOT NULL,
                PRIMARY KEY (id)
            );
            CREATE TABLE sgs_data (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Number varchar(15) NOT NULL DEFAULT '',
                PRIMARY KEY (id)
            );
            SET foreign_key_checks=0;
            ALTER TABLE sgs_data
                CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
                ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);
            {code}

            This causes the MariaDB server to crash.

            * Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
            * Removing `SET foreign_key_checks=0` avoids the crash
            * Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

            Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).             		{code:SQL}
            docker run --rm -p 20433:3306 -e MARIADB_DATABASE=test -e MARIADB_ROOT_PASSWORD=root mariadb:11.1.2
            mysql -h 127.0.0.1 --port=20433 -u root -proot test < migrate.sql
            {code}

            migrate.sql:

            {code:SQL}
            CREATE TABLE items (
                id VARCHAR(20) NOT NULL,
                PRIMARY KEY (id)
            );
            CREATE TABLE sgs_data (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Number varchar(15) NOT NULL DEFAULT '',
                PRIMARY KEY (id)
            );
            SET foreign_key_checks=0;
            ALTER TABLE sgs_data
                CHANGE COLUMN `Number` itemId VARCHAR(20) NOT NULL,
                ADD CONSTRAINT fk_sgs_data_itemId FOREIGN KEY (itemId) REFERENCES items (id);
            {code}

            This causes the MariaDB server to crash (see attached log output).

            * Moving the CHANGE COLUMN to a separate ALTER statement avoids the crash
            * Removing `SET foreign_key_checks=0` avoids the crash
            * Removing `CREATE TABLE items` still results in a crash even though the foreign key is invalid. If you additionally remove the `SET foreign_key_checks=0` it will instead report that the foreign key is malformed and not crash.

            Verified on 10.6.15, 10.9.8 and 11.1.2 (all with docker).
            marko Marko Mäkelä added a comment -

            This is something that was missed in MDEV-32527, MDEV-32337, MDEV-32060, MDEV-31869 and caused by the fix of MDEV-31086.

            We fail to check for a null pointer:

            diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
            		 
            index 092aa54737d..6da95e42c11 100644
            		 
            --- a/storage/innobase/handler/handler0alter.cc
            		 
            +++ b/storage/innobase/handler/handler0alter.cc
            		 
            @@ -7759,12 +7759,15 @@ bool check_col_is_in_fk_indexes(
            		 
             
               for (const auto &a : add_fk)
            		 
               {
            		 
            -    for (ulint i= 0; i < a->n_fields; i++)
            		 
            +    if (const dict_index_t* foreign_index= a->foreign_index)
            		 
                 {
            		 
            -      if (a->foreign_index->fields[i].col == col)
            		 
            +      for (ulint i= 0; i < a->n_fields; i++)
            		 
                   {
            		 
            -        fk_id= a->id;
            		 
            -        goto err_exit;
            		 
            +        if (foreign_index->fields[i].col == col)
            		 
            +        {
            		 
            +          fk_id= a->id;
            		 
            +          goto err_exit;
            		 
            +        }
            		 
                   }
            		 
                 }
            		 
               }

            marko Marko Mäkelä added a comment - This is something that was missed in MDEV-32527 , MDEV-32337 , MDEV-32060 , MDEV-31869 and caused by the fix of MDEV-31086 . We fail to check for a null pointer: diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index 092aa54737d..6da95e42c11 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -7759,12 +7759,15 @@ bool check_col_is_in_fk_indexes( for (const auto &a : add_fk) { - for (ulint i= 0; i < a->n_fields; i++) + if (const dict_index_t* foreign_index= a->foreign_index) { - if (a->foreign_index->fields[i].col == col) + for (ulint i= 0; i < a->n_fields; i++) { - fk_id= a->id; - goto err_exit; + if (foreign_index->fields[i].col == col) + { + fk_id= a->id; + goto err_exit; + } } } }
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Component/s Storage Engine - InnoDB [ 10129 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.1 [ 28549 ]
            Fix Version/s 11.2 [ 28603 ]
            Fix Version/s 11.3 [ 28565 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Assignee Thirunarayanan Balathandayuthapani [ thiru ]
            Labels crash crash foreign-keys regression
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            marko Marko Mäkelä made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani added a comment -

            https://github.com/MariaDB/server/pull/2814

            thiru Thirunarayanan Balathandayuthapani added a comment - https://github.com/MariaDB/server/pull/2814
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä added a comment -

            OK to push.

            marko Marko Mäkelä added a comment - OK to push.
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Fix Version/s 10.4.32 [ 29300 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.0 [ 28320 ]
            Fix Version/s 11.1 [ 28549 ]
            Fix Version/s 11.3 [ 28565 ]
            Fix Version/s 11.2 [ 28603 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            JIraAutomate JiraAutomate made changes -
            Fix Version/s 10.5.23 [ 29012 ]
            Fix Version/s 10.6.16 [ 29014 ]
            Fix Version/s 10.10.7 [ 29018 ]
            Fix Version/s 10.11.6 [ 29020 ]
            Fix Version/s 11.0.4 [ 29021 ]
            Fix Version/s 11.1.3 [ 29023 ]
            Fix Version/s 11.2.2 [ 29035 ]
            marko Marko Mäkelä made changes -
            Fix Version/s 10.4.33 [ 29516 ]
            Fix Version/s 10.5.24 [ 29517 ]
            Fix Version/s 10.6.17 [ 29518 ]
            Fix Version/s 10.11.7 [ 29519 ]
            Fix Version/s 11.0.5 [ 29520 ]
            Fix Version/s 11.1.4 [ 29024 ]
            Fix Version/s 11.2.3 [ 29521 ]
            Fix Version/s 11.3.2 [ 29522 ]
            Fix Version/s 10.5.23 [ 29012 ]
            Fix Version/s 10.6.16 [ 29014 ]
            Fix Version/s 10.10.7 [ 29018 ]
            Fix Version/s 10.11.6 [ 29020 ]
            Fix Version/s 11.0.4 [ 29021 ]
            Fix Version/s 11.1.3 [ 29023 ]
            Fix Version/s 11.2.2 [ 29035 ]
            Fix Version/s 10.4.32 [ 29300 ]

            People

              thiru Thirunarayanan Balathandayuthapani
              SystemParadox Simon Williams
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.