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

Multiple Foreign Keys to same column and a multi-column index cause foreign key constraints to fail

Details

    Description

      If you have two columns in a child table which have a foreign key constraint to the same column of the parent table the constraints will fail on update of the foreign key in the parent table if you add a multi column unique key over both columns in the child table.

      This only happens if you add unique constraints.

      SQL Code for reproduction:

      Schema

      CREATE TABLE IF NOT EXISTS user (
      		 
      id INT NOT NULL AUTO_INCREMENT,
      		 
      name VARCHAR(55),
      		 
      PRIMARY KEY (`id`)
      		 
      );
      		 
       
      		 
      CREATE TABLE IF NOT EXISTS user_map (
      		 
      map_id INT NOT NULL AUTO_INCREMENT,
      		 
      user_a INT,
      		 
      user_b INT,
      		 
        PRIMARY KEY (`map_id`),
      		 
        UNIQUE KEY `one_way` (`user_a`,`user_b`),
      		 
        UNIQUE KEY `other_way` (`user_b`,`user_a`),
      		 
        CONSTRAINT `acc_connections_ibfk_1` FOREIGN KEY (`user_a`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
      		 
        CONSTRAINT `acc_connections_ibfk_2` FOREIGN KEY (`user_b`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
      		 
      );

      Data

      INSERT INTO user (name) VALUES ("User A");
      		 
      INSERT INTO user_map (user_a,user_b) VALUES (1,1);

      Error

      UPDATE user set id = 10 where id = 1;

      See DB fiddle:

      https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=331761416b63948b2828f4a9317423fc

      Attachments

        Issue Links

          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22361 Cross-engine foreign keys support

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment -

            Thanks for the report!
            I repeated as described on 10.2-10.6

            -- source include/have_innodb.inc
            		 
            create table user (id int not null primary key) engine=innodb;
            		 
            insert into user values (1);
            		 
             
            		 
            create table user_map ( 
              user_a int, 
              user_b int,
            		 
              unique key (user_b,user_a),
            		 
              constraint c1 foreign key (user_a) references user (id) on update cascade on delete cascade,
            		 
              constraint c2 foreign key (user_b) references user (id) on update cascade on delete cascade
            		 
            )engine=innodb;
            		 
            insert into user_map  values (1,1);
            		 
             
            		 
            update user set id = 10 where id = 1;


            MariaDB [test]> update user set id = 10 where id = 1;
            		 
            ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`user_map`, CONSTRAINT `c2` FOREIGN KEY (`user_b`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

            alice Alice Sherepa added a comment - Thanks for the report! I repeated as described on 10.2-10.6 -- source include/have_innodb.inc create table user (id int not null primary key ) engine=innodb; insert into user values (1);   create table user_map ( user_a int , user_b int , unique key (user_b,user_a), constraint c1 foreign key (user_a) references user (id) on update cascade on delete cascade , constraint c2 foreign key (user_b) references user (id) on update cascade on delete cascade )engine=innodb; insert into user_map values (1,1);   update user set id = 10 where id = 1; MariaDB [test]> update user set id = 10 where id = 1; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`user_map`, CONSTRAINT `c2` FOREIGN KEY (`user_b`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
            sebi2020 Sebastian T added a comment -

            I think the bug also affects the latest versions (10.7.x) . But I have not been able to test this so far.

            sebi2020 Sebastian T added a comment - I think the bug also affects the latest versions (10.7.x) . But I have not been able to test this so far.
            marko Marko Mäkelä added a comment -

            The multi-column index need not be UNIQUE KEY; this will occur when defining a non-unique INDEX as well.

            marko Marko Mäkelä added a comment - The multi-column index need not be UNIQUE KEY ; this will occur when defining a non-unique INDEX as well.
            marko Marko Mäkelä added a comment -

            With an (implicitly or explicitly defined) single-column index on column user_b, the function row_ins_check_foreign_constraint() will return DB_SUCCESS without invoking cmp_dtuple_rec():

            	if (que_node_get_type(thr->run_node) == QUE_NODE_UPDATE) {
            		 
            		upd_node = static_cast<upd_node_t*>(thr->run_node);
            		 
             
            		 
            		if (upd_node->is_delete != PLAIN_DELETE
            		 
            		    && upd_node->foreign == foreign) {
            		 
            			/* If a cascaded update is done as defined by a
            		 
            			foreign key constraint, do not check that
            		 
            			constraint for the child row. In ON UPDATE CASCADE
            		 
            			the update of the parent row is only half done when
            		 
            			we come here: if we would check the constraint here
            		 
            			for the child row it would fail.
            		 
             
            		 
            			A QUESTION remains: if in the child table there are
            		 
            			several constraints which refer to the same parent
            		 
            			table, we should merge all updates to the child as
            		 
            			one update? And the updates can be contradictory!
            		 
            			Currently we just perform the update associated
            		 
            			with each foreign key constraint, one after
            		 
            			another, and the user has problems predicting in
            		 
            			which order they are performed. */
            		 
             
            		 
            			goto exit_func;
            		 
            		}
            		 
            	}

            When there is a multi-column index starting with user_b, upd_node->foreign will be constraint c1 and foreign will be constraint c2. When there is a single-column index on user_b, both will be constraint c2.

            marko Marko Mäkelä added a comment - With an (implicitly or explicitly defined) single-column index on column user_b , the function row_ins_check_foreign_constraint() will return DB_SUCCESS without invoking cmp_dtuple_rec() : if (que_node_get_type(thr->run_node) == QUE_NODE_UPDATE) { upd_node = static_cast <upd_node_t*>(thr->run_node);   if (upd_node->is_delete != PLAIN_DELETE && upd_node->foreign == foreign) { /* If a cascaded update is done as defined by a foreign key constraint, do not check that constraint for the child row. In ON UPDATE CASCADE the update of the parent row is only half done when we come here: if we would check the constraint here for the child row it would fail.   A QUESTION remains: if in the child table there are several constraints which refer to the same parent table, we should merge all updates to the child as one update? And the updates can be contradictory! Currently we just perform the update associated with each foreign key constraint, one after another, and the user has problems predicting in which order they are performed. */   goto exit_func; } } When there is a multi-column index starting with user_b , upd_node->foreign will be constraint c1 and foreign will be constraint c2 . When there is a single-column index on user_b , both will be constraint c2 .
            marko Marko Mäkelä added a comment -

            For the UPDATE statement, there will be 4 calls to row_ins_check_foreign_constraint() in the successful run (with a 1-column index) and only 2 calls when the 2-column index is present. The second (recursive) call appears to fail because row_upd() is processing the indexes in dict_table_t::indexes order, which will not be the same as the dict_table_t::foreign_set order.

            If I change the table definition to the following, then the 4th call will return DB_NO_REFERENCED_ROW, instead of the 2nd call returning that.

            create table user_map (
            		 
              user_a int,
            		 
              user_b int,
            		 
              index (user_b), index(user_a,user_b),
            		 
              constraint c1 foreign key (user_a) references user (id) on update cascade on delete cascade,
            		 
              constraint c2 foreign key (user_b) references user (id) on update cascade on delete cascade
            		 
            )engine=innodb;

            I tried different names for the columns user_a and user_b. The only way I can reproduce this failure is to define a multi-column index (unique, non-unique, or PRIMARY KEY). Because of the recursion in the code, it is somewhat hard to find the exact root cause of this.

            marko Marko Mäkelä added a comment - For the UPDATE statement, there will be 4 calls to row_ins_check_foreign_constraint() in the successful run (with a 1-column index) and only 2 calls when the 2-column index is present. The second (recursive) call appears to fail because row_upd() is processing the indexes in dict_table_t::indexes order, which will not be the same as the dict_table_t::foreign_set order. If I change the table definition to the following, then the 4th call will return DB_NO_REFERENCED_ROW , instead of the 2nd call returning that. create table user_map ( user_a int , user_b int , index (user_b), index (user_a,user_b), constraint c1 foreign key (user_a) references user (id) on update cascade on delete cascade , constraint c2 foreign key (user_b) references user (id) on update cascade on delete cascade )engine=innodb; I tried different names for the columns user_a and user_b . The only way I can reproduce this failure is to define a multi-column index (unique, non-unique, or PRIMARY KEY ). Because of the recursion in the code, it is somewhat hard to find the exact root cause of this.
            marko Marko Mäkelä added a comment -

            I did not test old versions, but it seems to me that this logic was broken already when ON UPDATE CASCADE was implemented (and something unrelated changed in the adaptive hash index) in MySQL 4.0.7, 21 years ago, almost 1 year ‘before my time’. I do not notice any essential changes to this logic since then.

            The following stack trace highlights the problem:

            10.6 2b01e5103df8fdde7a4d3db90d699fab80cba0d6

            		 
            #0  row_ins_check_foreign_constraint (check_ref=check_ref@entry=1, foreign=foreign@entry=0x7f325005cd60, table=table@entry=0x7f325005bd10, entry=entry@entry=0x7f32500691d0, thr=thr@entry=0x7f3250067110)
            		 
                at /mariadb/10.6/storage/innobase/row/row0ins.cc:1515
            		 
            #1  0x00005580dd489974 in row_ins_check_foreign_constraints (table=0x7f325005bd10, index=index@entry=0x7f325005e480, pk=pk@entry=true, entry=entry@entry=0x7f32500691d0, thr=thr@entry=0x7f3250067110)
            		 
                at /mariadb/10.6/storage/innobase/row/row0ins.cc:1952
            		 
            #2  0x00005580dd48be31 in row_ins_clust_index_entry (index=index@entry=0x7f325005e480, entry=entry@entry=0x7f32500691d0, thr=thr@entry=0x7f3250067110, n_ext=0)
            		 
                at /mariadb/10.6/storage/innobase/row/row0ins.cc:3207
            		 
            #3  0x00005580dd4dee39 in row_upd_clust_rec_by_insert (node=node@entry=0x7f3250196220, index=index@entry=0x7f325005e480, thr=thr@entry=0x7f3250067110, referenced=referenced@entry=false, 
                foreign=foreign@entry=false, mtr=mtr@entry=0x7f3270658f80) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2343
            		 
            #4  0x00005580dd4e04d4 in row_upd_clust_step (node=node@entry=0x7f3250196220, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2703
            		 
            #5  0x00005580dd4e07a3 in row_upd (node=node@entry=0x7f3250196220, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2779
            		 
            #6  0x00005580dd4e0a93 in row_upd_step (thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921
            		 
            #7  0x00005580dd4a1edb in row_update_cascade_for_mysql (thr=thr@entry=0x7f3250067110, node=node@entry=0x7f3250196220, table=<optimized out>) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:2015
            		 
            #8  0x00005580dd488a60 in row_ins_foreign_check_on_constraint (thr=thr@entry=0x7f3250067110, foreign=foreign@entry=0x7f32501387e0, pcur=pcur@entry=0x7f3270659660, entry=entry@entry=0x7f32500655a8, 
                mtr=mtr@entry=0x7f32706599e0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1351
            		 
            #9  0x00005580dd4895da in row_ins_check_foreign_constraint (check_ref=check_ref@entry=0, foreign=foreign@entry=0x7f32501387e0, table=table@entry=0x7f3250194700, entry=entry@entry=0x7f32500655a8, 
                thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1744
            		 
            #10 0x00005580dd4de818 in row_upd_check_references_constraints (node=node@entry=0x7f32500c5e30, pcur=pcur@entry=0x7f32500c5f58, table=table@entry=0x7f3250194700, index=index@entry=0x7f32500aca70, 
                offsets=offsets@entry=0x7f3270659e60, thr=thr@entry=0x7f3250067110, mtr=0x7f327065a3a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:252
            		 
            #11 0x00005580dd4df2c8 in row_upd_clust_rec_by_insert (node=node@entry=0x7f32500c5e30, index=index@entry=0x7f32500aca70, thr=thr@entry=0x7f3250067110, referenced=referenced@entry=true, 
                foreign=foreign@entry=false, mtr=mtr@entry=0x7f327065a3a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2302
            		 
            #12 0x00005580dd4e04d4 in row_upd_clust_step (node=node@entry=0x7f32500c5e30, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2703
            		 
            #13 0x00005580dd4e07a3 in row_upd (node=node@entry=0x7f32500c5e30, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2779
            		 
            #14 0x00005580dd4e0a93 in row_upd_step (thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921
            		 
            #15 0x00005580dd4a0af1 in row_update_for_mysql (prebuilt=0x7f32500c5470) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:1688
            		 
            #16 0x00005580dd2aa467 in ha_innobase::update_row (this=0x7f32500c4bf0, old_row=0x7f3250194a00 "\377\001", new_row=0x7f32501949f8 "\377\n") at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:8692

            The incompatible upd_node->foreign != foreign had been assigned in row_ins_foreign_check_on_constraint() (stack frame 8). During the first invocation of row_ins_check_foreign_constraint() for the UPDATE (stack frame 9) we had upd_node->foreign == nullptr. This is for the following simplified test:

            --source include/have_innodb.inc
            		 
            create table user (id int primary key) engine=innodb;
            		 
            insert into user values (1);
            		 
            create table user_map (u1 int, u2 int, primary key(u2,u1),
            		 
              constraint c1 foreign key (u1) references user (id) on update cascade,
            		 
              constraint c2 foreign key (u2) references user (id) on update cascade
            		 
            )engine=innodb;
            		 
            insert into user_map values (1,1);
            		 
            update user set id = 10 where id = 1;

            I see that this particular execution involves updating the PRIMARY KEY(id) in the parent table user. That is done by delete-marking the old record and inserting a new record. But, the test also fails in the same way if I revise the parent table definition to the following:

            create table user (id int not null, index(id)) engine=innodb;

            In that scenario, we will have upd_node->foreign==nullptr during both invocations of the function, and the rest of the call stack will be a little different:

            10.6 2b01e5103df8fdde7a4d3db90d699fab80cba0d6

            		 
            #0  row_ins_check_foreign_constraint (check_ref=check_ref@entry=1, foreign=foreign@entry=0x7f705005d790, table=table@entry=0x7f705005c740, entry=entry@entry=0x7f7050066020, thr=thr@entry=0x7f7050077cb0)
            		 
                at /mariadb/10.6/storage/innobase/row/row0ins.cc:1515
            		 
            #1  0x000055d73416f974 in row_ins_check_foreign_constraints (table=0x7f705005c740, index=index@entry=0x7f705005f000, pk=pk@entry=true, entry=entry@entry=0x7f7050066020, thr=thr@entry=0x7f7050077cb0)
            		 
                at /mariadb/10.6/storage/innobase/row/row0ins.cc:1952
            		 
            #2  0x000055d734171e31 in row_ins_clust_index_entry (index=index@entry=0x7f705005f000, entry=entry@entry=0x7f7050066020, thr=thr@entry=0x7f7050077cb0, n_ext=0)
            		 
                at /mariadb/10.6/storage/innobase/row/row0ins.cc:3207
            		 
            #3  0x000055d7341c4e39 in row_upd_clust_rec_by_insert (node=node@entry=0x7f70500278e0, index=index@entry=0x7f705005f000, thr=thr@entry=0x7f7050077cb0, referenced=referenced@entry=false, 
                foreign=foreign@entry=false, mtr=mtr@entry=0x7f7060e5a230) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2343
            		 
            #4  0x000055d7341c64d4 in row_upd_clust_step (node=node@entry=0x7f70500278e0, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2703
            		 
            #5  0x000055d7341c67a3 in row_upd (node=node@entry=0x7f70500278e0, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2779
            		 
            #6  0x000055d7341c6a93 in row_upd_step (thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921
            		 
            #7  0x000055d734187edb in row_update_cascade_for_mysql (thr=thr@entry=0x7f7050077cb0, node=node@entry=0x7f70500278e0, table=<optimized out>) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:2015
            		 
            #8  0x000055d73416ea60 in row_ins_foreign_check_on_constraint (thr=thr@entry=0x7f7050077cb0, foreign=foreign@entry=0x7f70501387e0, pcur=pcur@entry=0x7f7060e5a910, entry=entry@entry=0x7f7050067290, 
                mtr=mtr@entry=0x7f7060e5ac90) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1351
            		 
            #9  0x000055d73416f5da in row_ins_check_foreign_constraint (check_ref=check_ref@entry=0, foreign=foreign@entry=0x7f70501387e0, table=table@entry=0x7f7050194700, entry=entry@entry=0x7f7050067290, 
                thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1744
            		 
            #10 0x000055d7341c4818 in row_upd_check_references_constraints (node=node@entry=0x7f70500c6650, pcur=pcur@entry=0x7f7060e5b120, table=0x7f7050194700, index=index@entry=0x7f70500280a0, offsets=<optimized out>, 
                thr=thr@entry=0x7f7050077cb0, mtr=0x7f7060e5b380) at /mariadb/10.6/storage/innobase/row/row0upd.cc:252
            		 
            #11 0x000055d7341c5c90 in row_upd_sec_index_entry (node=node@entry=0x7f70500c6650, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2006
            		 
            #12 0x000055d7341c5e74 in row_upd_sec_step (node=node@entry=0x7f70500c6650, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2059
            		 
            #13 0x000055d7341c67f6 in row_upd (node=node@entry=0x7f70500c6650, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2806
            		 
            #14 0x000055d7341c6a93 in row_upd_step (thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921
            		 
            #15 0x000055d734186af1 in row_update_for_mysql (prebuilt=0x7f70500c5c90) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:1688
            		 
            #16 0x000055d733f90467 in ha_innobase::update_row (this=0x7f70500c5410, old_row=0x7f7050194a00 "\377\001", new_row=0x7f70501949f8 "\377\n") at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:8692

            marko Marko Mäkelä added a comment - I did not test old versions, but it seems to me that this logic was broken already when ON UPDATE CASCADE was implemented (and something unrelated changed in the adaptive hash index) in MySQL 4.0.7, 21 years ago, almost 1 year ‘before my time’. I do not notice any essential changes to this logic since then. The following stack trace highlights the problem: 10.6 2b01e5103df8fdde7a4d3db90d699fab80cba0d6 #0 row_ins_check_foreign_constraint (check_ref=check_ref@entry=1, foreign=foreign@entry=0x7f325005cd60, table=table@entry=0x7f325005bd10, entry=entry@entry=0x7f32500691d0, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1515 #1 0x00005580dd489974 in row_ins_check_foreign_constraints (table=0x7f325005bd10, index=index@entry=0x7f325005e480, pk=pk@entry=true, entry=entry@entry=0x7f32500691d0, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1952 #2 0x00005580dd48be31 in row_ins_clust_index_entry (index=index@entry=0x7f325005e480, entry=entry@entry=0x7f32500691d0, thr=thr@entry=0x7f3250067110, n_ext=0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:3207 #3 0x00005580dd4dee39 in row_upd_clust_rec_by_insert (node=node@entry=0x7f3250196220, index=index@entry=0x7f325005e480, thr=thr@entry=0x7f3250067110, referenced=referenced@entry=false, foreign=foreign@entry=false, mtr=mtr@entry=0x7f3270658f80) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2343 #4 0x00005580dd4e04d4 in row_upd_clust_step (node=node@entry=0x7f3250196220, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2703 #5 0x00005580dd4e07a3 in row_upd (node=node@entry=0x7f3250196220, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2779 #6 0x00005580dd4e0a93 in row_upd_step (thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921 #7 0x00005580dd4a1edb in row_update_cascade_for_mysql (thr=thr@entry=0x7f3250067110, node=node@entry=0x7f3250196220, table=<optimized out>) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:2015 #8 0x00005580dd488a60 in row_ins_foreign_check_on_constraint (thr=thr@entry=0x7f3250067110, foreign=foreign@entry=0x7f32501387e0, pcur=pcur@entry=0x7f3270659660, entry=entry@entry=0x7f32500655a8, mtr=mtr@entry=0x7f32706599e0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1351 #9 0x00005580dd4895da in row_ins_check_foreign_constraint (check_ref=check_ref@entry=0, foreign=foreign@entry=0x7f32501387e0, table=table@entry=0x7f3250194700, entry=entry@entry=0x7f32500655a8, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1744 #10 0x00005580dd4de818 in row_upd_check_references_constraints (node=node@entry=0x7f32500c5e30, pcur=pcur@entry=0x7f32500c5f58, table=table@entry=0x7f3250194700, index=index@entry=0x7f32500aca70, offsets=offsets@entry=0x7f3270659e60, thr=thr@entry=0x7f3250067110, mtr=0x7f327065a3a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:252 #11 0x00005580dd4df2c8 in row_upd_clust_rec_by_insert (node=node@entry=0x7f32500c5e30, index=index@entry=0x7f32500aca70, thr=thr@entry=0x7f3250067110, referenced=referenced@entry=true, foreign=foreign@entry=false, mtr=mtr@entry=0x7f327065a3a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2302 #12 0x00005580dd4e04d4 in row_upd_clust_step (node=node@entry=0x7f32500c5e30, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2703 #13 0x00005580dd4e07a3 in row_upd (node=node@entry=0x7f32500c5e30, thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2779 #14 0x00005580dd4e0a93 in row_upd_step (thr=thr@entry=0x7f3250067110) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921 #15 0x00005580dd4a0af1 in row_update_for_mysql (prebuilt=0x7f32500c5470) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:1688 #16 0x00005580dd2aa467 in ha_innobase::update_row (this=0x7f32500c4bf0, old_row=0x7f3250194a00 "\377\001", new_row=0x7f32501949f8 "\377\n") at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:8692 The incompatible upd_node->foreign != foreign had been assigned in row_ins_foreign_check_on_constraint() (stack frame 8). During the first invocation of row_ins_check_foreign_constraint() for the UPDATE (stack frame 9) we had upd_node->foreign == nullptr . This is for the following simplified test: --source include/have_innodb.inc create table user (id int primary key ) engine=innodb; insert into user values (1); create table user_map (u1 int , u2 int , primary key (u2,u1), constraint c1 foreign key (u1) references user (id) on update cascade , constraint c2 foreign key (u2) references user (id) on update cascade )engine=innodb; insert into user_map values (1,1); update user set id = 10 where id = 1; I see that this particular execution involves updating the PRIMARY KEY(id) in the parent table user . That is done by delete-marking the old record and inserting a new record. But, the test also fails in the same way if I revise the parent table definition to the following: create table user (id int not null , index (id)) engine=innodb; In that scenario, we will have upd_node->foreign==nullptr during both invocations of the function, and the rest of the call stack will be a little different: 10.6 2b01e5103df8fdde7a4d3db90d699fab80cba0d6 #0 row_ins_check_foreign_constraint (check_ref=check_ref@entry=1, foreign=foreign@entry=0x7f705005d790, table=table@entry=0x7f705005c740, entry=entry@entry=0x7f7050066020, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1515 #1 0x000055d73416f974 in row_ins_check_foreign_constraints (table=0x7f705005c740, index=index@entry=0x7f705005f000, pk=pk@entry=true, entry=entry@entry=0x7f7050066020, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1952 #2 0x000055d734171e31 in row_ins_clust_index_entry (index=index@entry=0x7f705005f000, entry=entry@entry=0x7f7050066020, thr=thr@entry=0x7f7050077cb0, n_ext=0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:3207 #3 0x000055d7341c4e39 in row_upd_clust_rec_by_insert (node=node@entry=0x7f70500278e0, index=index@entry=0x7f705005f000, thr=thr@entry=0x7f7050077cb0, referenced=referenced@entry=false, foreign=foreign@entry=false, mtr=mtr@entry=0x7f7060e5a230) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2343 #4 0x000055d7341c64d4 in row_upd_clust_step (node=node@entry=0x7f70500278e0, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2703 #5 0x000055d7341c67a3 in row_upd (node=node@entry=0x7f70500278e0, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2779 #6 0x000055d7341c6a93 in row_upd_step (thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921 #7 0x000055d734187edb in row_update_cascade_for_mysql (thr=thr@entry=0x7f7050077cb0, node=node@entry=0x7f70500278e0, table=<optimized out>) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:2015 #8 0x000055d73416ea60 in row_ins_foreign_check_on_constraint (thr=thr@entry=0x7f7050077cb0, foreign=foreign@entry=0x7f70501387e0, pcur=pcur@entry=0x7f7060e5a910, entry=entry@entry=0x7f7050067290, mtr=mtr@entry=0x7f7060e5ac90) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1351 #9 0x000055d73416f5da in row_ins_check_foreign_constraint (check_ref=check_ref@entry=0, foreign=foreign@entry=0x7f70501387e0, table=table@entry=0x7f7050194700, entry=entry@entry=0x7f7050067290, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1744 #10 0x000055d7341c4818 in row_upd_check_references_constraints (node=node@entry=0x7f70500c6650, pcur=pcur@entry=0x7f7060e5b120, table=0x7f7050194700, index=index@entry=0x7f70500280a0, offsets=<optimized out>, thr=thr@entry=0x7f7050077cb0, mtr=0x7f7060e5b380) at /mariadb/10.6/storage/innobase/row/row0upd.cc:252 #11 0x000055d7341c5c90 in row_upd_sec_index_entry (node=node@entry=0x7f70500c6650, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2006 #12 0x000055d7341c5e74 in row_upd_sec_step (node=node@entry=0x7f70500c6650, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2059 #13 0x000055d7341c67f6 in row_upd (node=node@entry=0x7f70500c6650, thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2806 #14 0x000055d7341c6a93 in row_upd_step (thr=thr@entry=0x7f7050077cb0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921 #15 0x000055d734186af1 in row_update_for_mysql (prebuilt=0x7f70500c5c90) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:1688 #16 0x000055d733f90467 in ha_innobase::update_row (this=0x7f70500c5410, old_row=0x7f7050194a00 "\377\001", new_row=0x7f70501949f8 "\377\n") at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:8692
            marko Marko Mäkelä added a comment -

            At the time the DB_NO_REFERENCED_ROW is reported for the PRIMARY KEY(u2,u1) test variant, we have 1 delete-marked row id=1 in the parent table user, and a delete-marked row (u2,u1)=(1,1) in the child table user_map. The secondary index record (u1,u2)=(1,1) has not yet been delete-marked. That page was last accessed by row_ins_foreign_check_on_constraint() before the last (failing) call to row_ins_check_foreign_constraint():

            10.6 2b01e5103df8fdde7a4d3db90d699fab80cba0d6

            		 
            #6  0x000056058a5c42bd in btr_pcur_open (mtr=0x7f70a00699e0, cursor=0x7f70a0069660, latch_mode=BTR_SEARCH_LEAF, mode=PAGE_CUR_GE, tuple=0x7f707c0655a8) at /mariadb/10.6/storage/innobase/include/btr0pcur.h:431
            		 
            #7  row_ins_check_foreign_constraint (check_ref=check_ref@entry=0, foreign=foreign@entry=0x7f707c05cd60, table=table@entry=0x7f707c194700, entry=entry@entry=0x7f707c0655a8, thr=thr@entry=0x7f707c067110)
            		 
                at /mariadb/10.6/storage/innobase/row/row0ins.cc:1627
            		 
            #8  0x000056058a619818 in row_upd_check_references_constraints (node=node@entry=0x7f707c0c5e30, pcur=pcur@entry=0x7f707c0c5f58, table=table@entry=0x7f707c194700, index=index@entry=0x7f707c0aca70, 
                offsets=offsets@entry=0x7f70a0069e60, thr=thr@entry=0x7f707c067110, mtr=0x7f70a006a3a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:252
            		 
            #9  0x000056058a61a2c8 in row_upd_clust_rec_by_insert (node=node@entry=0x7f707c0c5e30, index=index@entry=0x7f707c0aca70, thr=thr@entry=0x7f707c067110, referenced=referenced@entry=true, 
                foreign=foreign@entry=false, mtr=mtr@entry=0x7f70a006a3a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2302

            This page latch would also be released before the failing call to row_ins_check_foreign_constraint():

            10.6 2b01e5103df8fdde7a4d3db90d699fab80cba0d6

            		 
            #5  0x000056058a565bda in mtr_t::commit (this=this@entry=0x7f70a00699e0) at /mariadb/10.6/storage/innobase/mtr/mtr0mtr.cc:235
            		 
            #6  0x000056058a5c3a33 in row_ins_foreign_check_on_constraint (thr=thr@entry=0x7f707c067110, foreign=foreign@entry=0x7f707c05cd60, pcur=pcur@entry=0x7f70a0069660, entry=entry@entry=0x7f707c0655a8, 
                mtr=mtr@entry=0x7f70a00699e0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1345

            When no problematic index has been defined on the child table user_map, we’d have 4 calls to row_ins_check_foreign_constraint(), one recursive pair for each FOREIGN KEY constraint.

            I can even reproduce this with a single-column PRIMARY KEY, but the 2 FOREIGN KEY constraints are necessary for it to fail:

            --source include/have_innodb.inc
            		 
            create table user (id int primary key) engine=innodb;
            		 
            insert into user values (1);
            		 
            create table user_map (u1 int primary key, u2 int,
            		 
              constraint c1 foreign key (u1) references user (id) on update cascade,
            		 
              constraint c2 foreign key (u2) references user (id) on update cascade
            		 
            )engine=innodb;
            		 
            insert into user_map values (1,1);
            		 
            update user set id = 10 where id = 1;
            		 
            drop table user_map,user;

            If I make u1 or u2 alone the PRIMARY KEY of the child table user_map, the UPDATE will fail. If I omit the PRIMARY KEY, the test will pass. I have tried to permute u1,u2 and c1,c2 in the definition in various ways, but this failure does not seem to be related to this source code comment about the ordering of ON UPDATE operations.

            marko Marko Mäkelä added a comment - At the time the DB_NO_REFERENCED_ROW is reported for the PRIMARY KEY(u2,u1) test variant, we have 1 delete-marked row id=1 in the parent table user , and a delete-marked row (u2,u1)=(1,1) in the child table user_map . The secondary index record (u1,u2)=(1,1) has not yet been delete-marked. That page was last accessed by row_ins_foreign_check_on_constraint() before the last (failing) call to row_ins_check_foreign_constraint() : 10.6 2b01e5103df8fdde7a4d3db90d699fab80cba0d6 #6 0x000056058a5c42bd in btr_pcur_open (mtr=0x7f70a00699e0, cursor=0x7f70a0069660, latch_mode=BTR_SEARCH_LEAF, mode=PAGE_CUR_GE, tuple=0x7f707c0655a8) at /mariadb/10.6/storage/innobase/include/btr0pcur.h:431 #7 row_ins_check_foreign_constraint (check_ref=check_ref@entry=0, foreign=foreign@entry=0x7f707c05cd60, table=table@entry=0x7f707c194700, entry=entry@entry=0x7f707c0655a8, thr=thr@entry=0x7f707c067110) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1627 #8 0x000056058a619818 in row_upd_check_references_constraints (node=node@entry=0x7f707c0c5e30, pcur=pcur@entry=0x7f707c0c5f58, table=table@entry=0x7f707c194700, index=index@entry=0x7f707c0aca70, offsets=offsets@entry=0x7f70a0069e60, thr=thr@entry=0x7f707c067110, mtr=0x7f70a006a3a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:252 #9 0x000056058a61a2c8 in row_upd_clust_rec_by_insert (node=node@entry=0x7f707c0c5e30, index=index@entry=0x7f707c0aca70, thr=thr@entry=0x7f707c067110, referenced=referenced@entry=true, foreign=foreign@entry=false, mtr=mtr@entry=0x7f70a006a3a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2302 This page latch would also be released before the failing call to row_ins_check_foreign_constraint() : 10.6 2b01e5103df8fdde7a4d3db90d699fab80cba0d6 #5 0x000056058a565bda in mtr_t::commit (this=this@entry=0x7f70a00699e0) at /mariadb/10.6/storage/innobase/mtr/mtr0mtr.cc:235 #6 0x000056058a5c3a33 in row_ins_foreign_check_on_constraint (thr=thr@entry=0x7f707c067110, foreign=foreign@entry=0x7f707c05cd60, pcur=pcur@entry=0x7f70a0069660, entry=entry@entry=0x7f707c0655a8, mtr=mtr@entry=0x7f70a00699e0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1345 When no problematic index has been defined on the child table user_map , we’d have 4 calls to row_ins_check_foreign_constraint() , one recursive pair for each FOREIGN KEY constraint. I can even reproduce this with a single-column PRIMARY KEY , but the 2 FOREIGN KEY constraints are necessary for it to fail: --source include/have_innodb.inc create table user (id int primary key ) engine=innodb; insert into user values (1); create table user_map (u1 int primary key , u2 int , constraint c1 foreign key (u1) references user (id) on update cascade , constraint c2 foreign key (u2) references user (id) on update cascade )engine=innodb; insert into user_map values (1,1); update user set id = 10 where id = 1; drop table user_map, user ; If I make u1 or u2 alone the PRIMARY KEY of the child table user_map , the UPDATE will fail. If I omit the PRIMARY KEY , the test will pass. I have tried to permute u1 , u2 and c1 , c2 in the definition in various ways, but this failure does not seem to be related to this source code comment about the ordering of ON UPDATE operations .
            marko Marko Mäkelä added a comment -

            I think that this is related to Heikki Tuuri’s source code comment that I mentioned:

            	if (que_node_get_type(thr->run_node) == QUE_NODE_UPDATE) {
            		 
            	        upd_node = thr->run_node;
            		 
             
            		 
            	        if (!(upd_node->is_delete) && upd_node->foreign == foreign) {
            		 
            		        /* If a cascaded update is done as defined by a 
            			foreign key constraint, do not check that
            		 
            			constraint for the child row. In ON UPDATE CASCADE
            		 
            			the update of the parent row is only half done when
            		 
            			we come here: if we would check the constraint here
            		 
            			for the child row it would fail.
            		 
             
            		 
            			A QUESTION remains: if in the child table there are
            		 
            			several constraints which refer to the same parent
            		 
            			table, we should merge all updates to the child as
            		 
            			one update? And the updates can be contradictory!
            		 
            			Currently we just perform the update associated
            		 
            			with each foreign key constraint, one after
            		 
            			another, and the user has problems predicting in
            		 
            			which order they are performed. */
            		 
             
            		 
            		        return(DB_SUCCESS);
            		 
            		}
            		 
            	}

            The error is being flagged while executing ON UPDATE CASCADE for one of the constraints. If we have a multi-column index that contains both child columns, or if at least one of the child columns is included in the PRIMARY KEY, then the cascaded update will fail because we happen to check the constraints in an unexpected order. That is, when PRIMARY KEY(u1) is being updated (for the SQL at the end of my previous comment), the check fails for CONSTRAINT c2 because its implicitly created index c2(u2,u1) contains the index entry (1,10) and not (10,10) at the time the search tuple is being constructed:

            10.6 2b01e5103df8fdde7a4d3db90d699fab80cba0d6

            		 
            #4  0x000055d4f0f27590 in row_build_index_entry (row=0x7f5b3404bb00, ext=0x0, index=index@entry=0x7f5b34043560, heap=0x7f5b3404eaa0) at /mariadb/10.6/storage/innobase/include/row0row.inl:126
            		 
            #5  0x000055d4f0f2d5ae in row_upd_sec_index_entry (node=node@entry=0x7f5b34178d70, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:1860
            		 
            #6  0x000055d4f0f2de74 in row_upd_sec_step (node=node@entry=0x7f5b34178d70, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2059
            		 
            #7  0x000055d4f0f2e7f6 in row_upd (node=node@entry=0x7f5b34178d70, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2806
            		 
            #8  0x000055d4f0f2ea93 in row_upd_step (thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921
            		 
            #9  0x000055d4f0eefedb in row_update_cascade_for_mysql (thr=thr@entry=0x7f5b3404cff0, node=node@entry=0x7f5b34178d70, table=<optimized out>) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:2015
            		 
            #10 0x000055d4f0ed6a60 in row_ins_foreign_check_on_constraint (thr=thr@entry=0x7f5b3404cff0, foreign=foreign@entry=0x7f5b3411db10, pcur=pcur@entry=0x7f5b54074660, entry=entry@entry=0x7f5b3404af58, 
                mtr=mtr@entry=0x7f5b540749e0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1351
            		 
            #11 0x000055d4f0ed75da in row_ins_check_foreign_constraint (check_ref=check_ref@entry=0, foreign=foreign@entry=0x7f5b3411db10, table=table@entry=0x7f5b34179b20, entry=entry@entry=0x7f5b3404af58, 
                thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1744
            		 
            #12 0x000055d4f0f2c818 in row_upd_check_references_constraints (node=node@entry=0x7f5b340d3af0, pcur=pcur@entry=0x7f5b340d3c18, table=table@entry=0x7f5b34179b20, index=index@entry=0x7f5b3400d020, 
                offsets=offsets@entry=0x7f5b54074e60, thr=thr@entry=0x7f5b3404cff0, mtr=0x7f5b540753a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:252
            		 
            #13 0x000055d4f0f2d2c8 in row_upd_clust_rec_by_insert (node=node@entry=0x7f5b340d3af0, index=index@entry=0x7f5b3400d020, thr=thr@entry=0x7f5b3404cff0, referenced=referenced@entry=true, 
                foreign=foreign@entry=false, mtr=mtr@entry=0x7f5b540753a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2302
            		 
            #14 0x000055d4f0f2e4d4 in row_upd_clust_step (node=node@entry=0x7f5b340d3af0, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2703
            		 
            #15 0x000055d4f0f2e7a3 in row_upd (node=node@entry=0x7f5b340d3af0, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2779
            		 
            #16 0x000055d4f0f2ea93 in row_upd_step (thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921
            		 
            #17 0x000055d4f0eeeaf1 in row_update_for_mysql (prebuilt=0x7f5b340d3130) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:1688
            		 
            #18 0x000055d4f0cf8467 in ha_innobase::update_row (this=0x7f5b340d28b0, old_row=0x7f5b34179e20 "\377\001", new_row=0x7f5b34179e18 "\377\n") at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:8692
            		 
            #19 0x000055d4f0848b75 in handler::ha_update_row (this=0x7f5b340d28b0, old_data=0x7f5b34179e20 "\377\001", new_data=0x7f5b34179e18 "\377\n") at /mariadb/10.6/sql/handler.cc:7718

            As far as I understand, to fix this we would have to create a combined update vector that would update both columns u1 and u2 in a single go. Perhaps the main memory data structure for foreign key constraints should be refactored so that there would only be one ‘constraint’ data structure for each distinct references target of the child table, such as user(id) in this case.

            I think that this is something that must be considered as part of refactoring the FOREIGN KEY constraints in MDEV-22361.

            I am reluctant to fix this in the current InnoDB implementation. It can be done if there is strong customer demand.

            marko Marko Mäkelä added a comment - I think that this is related to Heikki Tuuri’s source code comment that I mentioned: if (que_node_get_type(thr->run_node) == QUE_NODE_UPDATE) { upd_node = thr->run_node;   if (!(upd_node->is_delete) && upd_node->foreign == foreign) { /* If a cascaded update is done as defined by a foreign key constraint, do not check that constraint for the child row. In ON UPDATE CASCADE the update of the parent row is only half done when we come here: if we would check the constraint here for the child row it would fail.   A QUESTION remains: if in the child table there are several constraints which refer to the same parent table, we should merge all updates to the child as one update? And the updates can be contradictory! Currently we just perform the update associated with each foreign key constraint, one after another, and the user has problems predicting in which order they are performed. */   return (DB_SUCCESS); } } The error is being flagged while executing ON UPDATE CASCADE for one of the constraints. If we have a multi-column index that contains both child columns, or if at least one of the child columns is included in the PRIMARY KEY , then the cascaded update will fail because we happen to check the constraints in an unexpected order. That is, when PRIMARY KEY(u1) is being updated (for the SQL at the end of my previous comment), the check fails for CONSTRAINT c2 because its implicitly created index c2(u2,u1) contains the index entry (1,10) and not (10,10) at the time the search tuple is being constructed: 10.6 2b01e5103df8fdde7a4d3db90d699fab80cba0d6 #4 0x000055d4f0f27590 in row_build_index_entry (row=0x7f5b3404bb00, ext=0x0, index=index@entry=0x7f5b34043560, heap=0x7f5b3404eaa0) at /mariadb/10.6/storage/innobase/include/row0row.inl:126 #5 0x000055d4f0f2d5ae in row_upd_sec_index_entry (node=node@entry=0x7f5b34178d70, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:1860 #6 0x000055d4f0f2de74 in row_upd_sec_step (node=node@entry=0x7f5b34178d70, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2059 #7 0x000055d4f0f2e7f6 in row_upd (node=node@entry=0x7f5b34178d70, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2806 #8 0x000055d4f0f2ea93 in row_upd_step (thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921 #9 0x000055d4f0eefedb in row_update_cascade_for_mysql (thr=thr@entry=0x7f5b3404cff0, node=node@entry=0x7f5b34178d70, table=<optimized out>) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:2015 #10 0x000055d4f0ed6a60 in row_ins_foreign_check_on_constraint (thr=thr@entry=0x7f5b3404cff0, foreign=foreign@entry=0x7f5b3411db10, pcur=pcur@entry=0x7f5b54074660, entry=entry@entry=0x7f5b3404af58, mtr=mtr@entry=0x7f5b540749e0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1351 #11 0x000055d4f0ed75da in row_ins_check_foreign_constraint (check_ref=check_ref@entry=0, foreign=foreign@entry=0x7f5b3411db10, table=table@entry=0x7f5b34179b20, entry=entry@entry=0x7f5b3404af58, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0ins.cc:1744 #12 0x000055d4f0f2c818 in row_upd_check_references_constraints (node=node@entry=0x7f5b340d3af0, pcur=pcur@entry=0x7f5b340d3c18, table=table@entry=0x7f5b34179b20, index=index@entry=0x7f5b3400d020, offsets=offsets@entry=0x7f5b54074e60, thr=thr@entry=0x7f5b3404cff0, mtr=0x7f5b540753a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:252 #13 0x000055d4f0f2d2c8 in row_upd_clust_rec_by_insert (node=node@entry=0x7f5b340d3af0, index=index@entry=0x7f5b3400d020, thr=thr@entry=0x7f5b3404cff0, referenced=referenced@entry=true, foreign=foreign@entry=false, mtr=mtr@entry=0x7f5b540753a0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2302 #14 0x000055d4f0f2e4d4 in row_upd_clust_step (node=node@entry=0x7f5b340d3af0, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2703 #15 0x000055d4f0f2e7a3 in row_upd (node=node@entry=0x7f5b340d3af0, thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2779 #16 0x000055d4f0f2ea93 in row_upd_step (thr=thr@entry=0x7f5b3404cff0) at /mariadb/10.6/storage/innobase/row/row0upd.cc:2921 #17 0x000055d4f0eeeaf1 in row_update_for_mysql (prebuilt=0x7f5b340d3130) at /mariadb/10.6/storage/innobase/row/row0mysql.cc:1688 #18 0x000055d4f0cf8467 in ha_innobase::update_row (this=0x7f5b340d28b0, old_row=0x7f5b34179e20 "\377\001", new_row=0x7f5b34179e18 "\377\n") at /mariadb/10.6/storage/innobase/handler/ha_innodb.cc:8692 #19 0x000055d4f0848b75 in handler::ha_update_row (this=0x7f5b340d28b0, old_data=0x7f5b34179e20 "\377\001", new_data=0x7f5b34179e18 "\377\n") at /mariadb/10.6/sql/handler.cc:7718 As far as I understand, to fix this we would have to create a combined update vector that would update both columns u1 and u2 in a single go. Perhaps the main memory data structure for foreign key constraints should be refactored so that there would only be one ‘constraint’ data structure for each distinct references target of the child table, such as user(id) in this case. I think that this is something that must be considered as part of refactoring the FOREIGN KEY constraints in MDEV-22361 . I am reluctant to fix this in the current InnoDB implementation. It can be done if there is strong customer demand.

            People

              marko Marko Mäkelä
              sebi2020 Sebastian T
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.