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

        1. marialog.txt
          5 kB
          Simon Williams
        2. migrate.sql
          0.5 kB
          Simon Williams

        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.