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

    XMLWordPrintable

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

          Activity

            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.