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

Foreign key regression from 10.11.9 to 10.11.10 (and 11.4.4)

Details

    Description

      After upgrading to v10.11.10 creation of a foreign key failed with no information in engine innodb status. The same keys are allowed in 10.11.9 and below.

      reproducer SQL:

      CREATE TABLE `a` (
      		 
        `a_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `a_p_id` int(10) unsigned DEFAULT NULL,
      		 
        PRIMARY KEY (`a_id`),
      		 
        FOREIGN KEY (`a_p_id`) REFERENCES `a` (`a_id`)
      		 
      ) ENGINE=InnoDBa
      		 
       
      		 
       
      		 
      CREATE TABLE `b` (
      		 
        `b_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `b_p_id` int(10) unsigned DEFAULT NULL,
      		 
        PRIMARY KEY (`b_id`),
      		 
        FOREIGN KEY (`b_p_id`) REFERENCES `b` (`b_id`)
      		 
      ) ENGINE=InnoDB
      		 
       
      		 
       
      		 
      CREATE TABLE `b_a` (
      		 
        `b_id` int(10) unsigned NOT NULL,
      		 
        `a_p_id` int(10) unsigned NOT NULL,
      		 
        `a_id` int(10) unsigned DEFAULT NULL,
      		 
        PRIMARY KEY (`b_id`,`a_p_id`),
      		 
        FOREIGN KEY (`b_id`) REFERENCES `b` (`b_id`) ON DELETE CASCADE ON UPDATE CASCADE,
      		 
        FOREIGN KEY (`a_p_id`, `a_id`) REFERENCES `a` (`a_p_id`, `a_id`) ON UPDATE CASCADE
      		 
      ) ENGINE=InnoDB

      Changing the table b_a latest foreign key to "on delete cascade" does allow the table to be created, but that is not what I want to archieve.

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34392 modification of the column fails to check foreign key constraint

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

          Activity

            Ascending order - Click to sort in descending order
            dicode Tim Westervoorde created issue -
            marko Marko Mäkelä made changes -
            Field Original Value New Value
            Assignee Thirunarayanan Balathandayuthapani [ thiru ]
            dicode Tim Westervoorde made changes -
            Affects Version/s 10.11.10 [ 29904 ]
            Affects Version/s 10.11.9 [ 29834 ]
            dicode Tim Westervoorde made changes -
            Summary Foreign key regression from 10.11.9 to 10.11.19 (and 11.4.4) Foreign key regression from 10.11.9 to 10.11.10 (and 11.4.4)
            serg Sergei Golubchik made changes -
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.4 [ 29301 ]
            dicode Tim Westervoorde made changes -
            Description After upgrading to v10.11.10 creation of a foreign key failed with no information in engine innodb status. The same keys are allowed in 10.11.9 and below.

            reproducer SQL:

            {code}
            CREATE TABLE `a` (
              `a_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `a_p_id` int(10) unsigned DEFAULT NULL,
              PRIMARY KEY (`a_id`),
              FOREIGN KEY (`a_p_id`) REFERENCES `a` (`a_id`)
            ) ENGINE=InnoDB


            CREATE TABLE `b` (
              `b_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `b_p_id` int(10) unsigned DEFAULT NULL,
              PRIMARY KEY (`b_id`),
              FOREIGN KEY (`b_p_id`) REFERENCES `b` (`b_id`)
            ) ENGINE=InnoDB


            CREATE TABLE `b_a` (
              `b_id` int(10) unsigned NOT NULL,
              `a_p_id` int(10) unsigned NOT NULL,
              `a_id` int(10) unsigned DEFAULT NULL,
              PRIMARY KEY (`b_id`,`a_p_id`),
              FOREIGN KEY (`b_id`) REFERENCES `b` (`b_id`) ON DELETE CASCADE ON UPDATE CASCADE,
              FOREIGN KEY (`a_p_id`, `a_id`) REFERENCES `a` (`a_p_id`, `a_id`) ON UPDATE CASCADE
            ) ENGINE=InnoDB
            {code}

            Changing the table b_a latest foreign key to "on delete cascade" does allow the table to be created, but that is not what I want to archieve.             		After upgrading to v10.11.10 creation of a foreign key failed with no information in engine innodb status. The same keys are allowed in 10.11.9 and below.

            reproducer SQL:

            {code}
            CREATE TABLE `a` (
              `a_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `a_p_id` int(10) unsigned DEFAULT NULL,
              PRIMARY KEY (`a_id`),
              FOREIGN KEY (`a_p_id`) REFERENCES `a` (`a_id`)
            ) ENGINE=InnoDBa


            CREATE TABLE `b` (
              `b_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `b_p_id` int(10) unsigned DEFAULT NULL,
              PRIMARY KEY (`b_id`),
              FOREIGN KEY (`b_p_id`) REFERENCES `b` (`b_id`)
            ) ENGINE=InnoDB


            CREATE TABLE `b_a` (
              `b_id` int(10) unsigned NOT NULL,
              `a_p_id` int(10) unsigned NOT NULL,
              `a_id` int(10) unsigned DEFAULT NULL,
              PRIMARY KEY (`b_id`,`a_p_id`),
              FOREIGN KEY (`b_id`) REFERENCES `b` (`b_id`) ON DELETE CASCADE ON UPDATE CASCADE,
              FOREIGN KEY (`a_p_id`, `a_id`) REFERENCES `a` (`a_p_id`, `a_id`) ON UPDATE CASCADE
            ) ENGINE=InnoDB
            {code}

            Changing the table b_a latest foreign key to "on delete cascade" does allow the table to be created, but that is not what I want to archieve.
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Needs Feedback [ 10501 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Affects Version/s 11.2.6 [ 29906 ]
            Affects Version/s 10.6.20 [ 29903 ]
            Affects Version/s 10.5.27 [ 29902 ]
            Labels regression
            Priority Major [ 3 ] Critical [ 2 ]
            marko Marko Mäkelä made changes -
            Status Needs Feedback [ 10501 ] Open [ 1 ]
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status Confirmed [ 10101 ] In Review [ 10002 ]
            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.5.28 [ 29952 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.4 [ 29301 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            JIraAutomate JiraAutomate made changes -
            Fix Version/s 10.6.21 [ 29953 ]
            Fix Version/s 10.11.11 [ 29954 ]
            Fix Version/s 11.4.5 [ 29956 ]
            Fix Version/s 11.7.2 [ 29914 ]

            People

              thiru Thirunarayanan Balathandayuthapani
              dicode Tim Westervoorde
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.