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

modification of the column fails to check foreign key constraint

Details

    Description

      Testcase with 2 tables

      create table tab1 (
      		 
          t1id int unsigned not null auto_increment,
      		 
          t1val varchar(30) not null,
      		 
          constraint pkt1 primary key (t1id)
      		 
      );
      		 
      create table tab2 (
      		 
          t2id int unsigned not null auto_increment,
      		 
          t1id int unsigned,
      		 
          t2tval datetime,
      		 
          t2nval int,
      		 
          constraint pkt2 primary key (t2id)
      		 
      );
      		 
      alter table tab2 add index FkIdxt2t1id (t1id), add constraint Fkt2t1id foreign key (t1id) references tab1 (t1id);

      Altering columns in one statement failes

      alter table tab2 modify t1id int unsigned not null, modify t2tval timestamp(3), modify t2nval bigint;
      		 

      Cannot change column 't1id': used in a foreign key constraint 'Fkt2t1id'

      alter not null in seperate statement works, also 2 modify data types

      alter table tab2 modify t1id int unsigned not null;
      		 
      alter table tab2 modify t2tval timestamp(3), modify t2nval bigint;

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35351 Remove const from get_foreign_key_list/get_parent_foreign_key_list by merging 10.11 to 11.2

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35445 Foreign key regression from 10.11.9 to 10.11.10 (and 11.4.4)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          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
            svh Stefan van Hasselt created issue -
            marko Marko Mäkelä made changes -
            Field Original Value New Value
            marko Marko Mäkelä made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.4 [ 29301 ]
            Assignee Thirunarayanan Balathandayuthapani [ thiru ]
            Labels regression
            Priority Minor [ 4 ] Critical [ 2 ]
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            serg Sergei Golubchik made changes -
            Description Testcase with 2 tables
            {code:sql}
            reate table tab1 (
                t1id int unsigned not null auto_increment,
                t1val varchar(30) not null,
                constraint pkt1 primary key (t1id)
            );
            create table tab2 (
                t2id int unsigned not null auto_increment,
                t1id int unsigned,
                t2tval datetime,
                t2nval int,
                constraint pkt2 primary key (t2id)
            );
            alter table tab2 add index FkIdxt2t1id (t1id), add constraint Fkt2t1id foreign key (t1id) references tab1 (t1id);
            {code}
            Altering columns in one statement failes
            {code:sql}alter table tab2 modify t1id int unsigned not null, modify t2tval timestamp(3), modify t2nval bigint;{code}
            {noformat}
            Cannot change column 't1id': used in a foreign key constraint 'Fkt2t1id'
            {noformat}
            alter not null in seperate statement works, also 2 modify data types
            {code:sql}alter table tab2 modify t1id int unsigned not null;
            alter table tab2 modify t2tval timestamp(3), modify t2nval bigint;{code}             		Testcase with 2 tables
            {code:sql}
            create table tab1 (
                t1id int unsigned not null auto_increment,
                t1val varchar(30) not null,
                constraint pkt1 primary key (t1id)
            );
            create table tab2 (
                t2id int unsigned not null auto_increment,
                t1id int unsigned,
                t2tval datetime,
                t2nval int,
                constraint pkt2 primary key (t2id)
            );
            alter table tab2 add index FkIdxt2t1id (t1id), add constraint Fkt2t1id foreign key (t1id) references tab1 (t1id);
            {code}
            Altering columns in one statement failes
            {code:sql}alter table tab2 modify t1id int unsigned not null, modify t2tval timestamp(3), modify t2nval bigint;{code}
            {noformat}
            Cannot change column 't1id': used in a foreign key constraint 'Fkt2t1id'
            {noformat}
            alter not null in seperate statement works, also 2 modify data types
            {code:sql}alter table tab2 modify t1id int unsigned not null;
            alter table tab2 modify t2tval timestamp(3), modify t2nval bigint;{code}
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Summary alter foreign key column failed in combination 2nd modify column Inplace algorithm violates the foreign key constraint
            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 -
            Summary Inplace algorithm violates the foreign key constraint Nullability of the foreign key column fails to check foreign key constraint
            thiru Thirunarayanan Balathandayuthapani made changes -
            Summary Nullability of the foreign key column fails to check foreign key constraint Null-ability of the column fails to check foreign key constraint
            thiru Thirunarayanan Balathandayuthapani made changes -
            Summary Null-ability of the column fails to check foreign key constraint modification of the column fails to check foreign key constraint
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Marko Mäkelä [ marko ] Debarun Banerjee [ JIRAUSER54513 ]
            debarun Debarun Banerjee made changes -
            Assignee Debarun Banerjee [ JIRAUSER54513 ] Thirunarayanan Balathandayuthapani [ thiru ]
            svh Stefan van Hasselt made changes -
            Affects Version/s 11.4.2 [ 29633 ]
            julien.fritsch Julien Fritsch made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            julien.fritsch Julien Fritsch made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Fix Version/s 10.5.27 [ 29902 ]
            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 In Progress [ 3 ] Closed [ 6 ]
            JIraAutomate JiraAutomate made changes -
            Fix Version/s 10.6.20 [ 29903 ]
            Fix Version/s 10.11.10 [ 29904 ]
            Fix Version/s 11.2.6 [ 29906 ]
            Fix Version/s 11.4.4 [ 29907 ]
            danblack Daniel Black made changes -
            danblack Daniel Black made changes -
            serg Sergei Golubchik made changes -
            marko Marko Mäkelä made changes -

            People

              thiru Thirunarayanan Balathandayuthapani
              svh Stefan van Hasselt
              Votes:
              1 Vote for this issue
              Watchers:
              8 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.