[MDEV-31313] SYSTEM VERSIONING and FOREIGN KEY CASCADE create orphan rows on replica Created: 2023-05-19  Updated: 2023-09-11  Resolved: 2023-07-20

Status: Closed
Project: MariaDB Server
Component/s: Replication, Versioned Tables
Affects Version/s: 10.4, 10.6.11, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4.31, 10.5.22, 10.6.15, 10.9.8, 10.11.5, 11.0.3, 11.1.2, 11.2.1

Type: Bug Priority: Major
Reporter: Andrea Ponzo Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: replication


 Description   

Having parent table and child table WITH SYSTEM VERSIONING where there is a Foreign Key CASCADE on child one will generate orphans rows on replica.
Using binlog_format ROW (or MIXED).

Steps to reproduce:

CREATE TABLE andre_main (
 
id int(11) NOT NULL AUTO_INCREMENT,
 
processdate datetime DEFAULT NULL,
 
PRIMARY KEY (id)
 
) ENGINE=InnoDB WITH SYSTEM VERSIONING;
 
 
 
 
 
insert into andre_main values (1,now());
 
 
 
CREATE TABLE andre_child (
 
id int(11) NOT NULL AUTO_INCREMENT,
 
ch_name varchar(30),
 
andreid int(11) DEFAULT NULL,
 
PRIMARY KEY (id),
 
KEY andreid (andreid),
 
CONSTRAINT fk_andreid FOREIGN KEY (andreid) REFERENCES andre_main (id) ON DELETE CASCADE
 
) ENGINE=InnoDB  WITH SYSTEM VERSIONING;
 
 
 
insert into andre_child values (null,'vimtomar',1);
 
 
 
delete from andre_main where id=1;

MASTER (correct behavior):

MariaDB [andre]> delete from andre_main where id=1;
 
Query OK, 1 row affected (0.006 sec)
 
 
 
MariaDB [andre]> select * from andre_main;
 
Empty set (0.001 sec)
 
 
 
MariaDB [andre]> select a.*,row_start,row_end from andre_main for system_time all a;
 
+----+---------------------+----------------------------+----------------------------+
 
| id | processdate         | row_start                  | row_end                    |
 
+----+---------------------+----------------------------+----------------------------+
 
|  1 | 2023-05-19 08:14:10 | 2023-05-19 08:14:10.965765 | 2023-05-19 08:16:15.145403 |
 
+----+---------------------+----------------------------+----------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [andre]> select * from andre_child;
 
Empty set (0.001 sec)
 
 
 
MariaDB [andre]> select a.*,row_start,row_end from andre_child for system_time all a;
 
+----+----------+---------+----------------------------+----------------------------+
 
| id | ch_name  | andreid | row_start                  | row_end                    |
 
+----+----------+---------+----------------------------+----------------------------+
 
|  1 | vimtomar |       1 | 2023-05-19 08:14:12.801143 | 2023-05-19 08:16:15.145403 |
 
+----+----------+---------+----------------------------+----------------------------+
 
1 row in set (0.001 sec)

REPLICA( wrong behavior on child table):

MariaDB [andre]> select * from andre_main;
 
Empty set (0.000 sec)
 
 
 
MariaDB [andre]> select a.*,row_start,row_end from andre_main for system_time all a;
 
+----+---------------------+----------------------------+----------------------------+
 
| id | processdate         | row_start                  | row_end                    |
 
+----+---------------------+----------------------------+----------------------------+
 
|  1 | 2023-05-19 08:14:10 | 2023-05-19 08:14:10.965765 | 2023-05-19 08:16:15.145403 |
 
+----+---------------------+----------------------------+----------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [andre]> select * from andre_child;
 
+----+----------+---------+
 
| id | ch_name  | andreid |
 
+----+----------+---------+
 
|  1 | vimtomar |       1 |
 
+----+----------+---------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [andre]>  select a.*,row_start,row_end from andre_child for system_time all a;
 
+----+----------+---------+----------------------------+----------------------------+
 
| id | ch_name  | andreid | row_start                  | row_end                    |
 
+----+----------+---------+----------------------------+----------------------------+
 
|  1 | vimtomar |       1 | 2023-05-19 08:14:12.801143 | 2038-01-18 22:14:07.999999 |
 
+----+----------+---------+----------------------------+----------------------------+
 
1 row in set (0.001 sec)

and now we have orphan row on replica:

MariaDB [andre]> show create table andre_child\G
 
*************************** 1. row ***************************
 
       Table: andre_child
 
Create Table: CREATE TABLE `andre_child` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `ch_name` varchar(30) DEFAULT NULL,
 
  `andreid` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `andreid` (`andreid`),
 
  CONSTRAINT `fk_andreid` FOREIGN KEY (`andreid`) REFERENCES `andre_main` (`id`) ON DELETE CASCADE
 
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 
1 row in set (0.000 sec)
 
 
 
MariaDB [andre]> select * from andre_child;
 
+----+----------+---------+
 
| id | ch_name  | andreid |
 
+----+----------+---------+
 
|  1 | vimtomar |       1 |
 
+----+----------+---------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [andre]> select * from andre_main where id=1;
 
Empty set (0.000 sec)

on master binlog we can see that only the row on 'andre_main' table is moved to historical data:

#Q> delete from andre_main where id=1
 
#230519  8:16:15 server id 101923  end_log_pos 2279756 CRC32 0x23579b04         Table_map: `andre`.`andre_main` mapped to number 434
 
# at 2279756
 
#230519  8:16:15 server id 101923  end_log_pos 2279819 CRC32 0x522fd035         Table_map: `andre`.`andre_child` mapped to number 435
 
# at 2279819
 
#230519  8:16:15 server id 101923  end_log_pos 2279901 CRC32 0xb04eb74a         Update_rows: table id 434 flags: STMT_END_F
 
### UPDATE `andre`.`andre_main`
 
### WHERE
 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
 
###   @2='2023-05-19 08:14:10' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
 
###   @3=1684498450.965765 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
 
###   @4=2147483647.999999 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
 
### SET
 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
 
###   @2='2023-05-19 08:14:10' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
 
###   @3=1684498450.965765 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
 
###   @4=1684498575.145403 /* TIMESTAMP(6) meta=6 nullable=0 is_null=0 */
 
# Number of rows: 1
 
# at 2279901
 
#230519  8:16:15 server id 101923  end_log_pos 2279932 CRC32 0xc9c0b997         Xid = 81518
 
COMMIT/*!*/;



 Comments   
Comment by Alice Sherepa [ 2023-05-19 ]

Thank you for the report! I repeated as described on 10.4-11.0:

--source include/have_innodb.inc
 
--source include/master-slave.inc
 
--source include/have_binlog_format_row.inc
 
 
 
CREATE TABLE andre_main (
 
id int(11) NOT NULL AUTO_INCREMENT,
 
processdate datetime DEFAULT NULL,
 
PRIMARY KEY (id)
 
) ENGINE=InnoDB WITH SYSTEM VERSIONING;
 
 
insert into andre_main values (1,now());
 
 
CREATE TABLE andre_child (
 
id int(11) NOT NULL AUTO_INCREMENT,
 
ch_name varchar(30),
 
andreid int(11) DEFAULT NULL,
 
PRIMARY KEY (id),
 
KEY andreid (andreid),
 
CONSTRAINT fk_andreid FOREIGN KEY (andreid) REFERENCES andre_main (id) ON DELETE CASCADE
 
) ENGINE=InnoDB  WITH SYSTEM VERSIONING;
 
 
insert into andre_child values (null,'vimtomar',1);
 
 
delete from andre_main where id=1;
 
 
 
select a.*,row_start,row_end from andre_main for system_time all a;
 
select a.*,row_start,row_end from andre_child for system_time all a;
 
 
 
select * from andre_child;
 
select * from andre_main;
 
 
 
 
 
sync_slave_with_master;
 
 
 
select a.*,row_start,row_end from andre_main for system_time all a;
 
select a.*,row_start,row_end from andre_child for system_time all a;
 
select * from andre_child;
 
select * from andre_main;
 
 
 
#Cleanup
 
--connection master
 
drop table andre_child;
 
drop table andre_main;
 
 
 
sync_slave_with_master;
 
 
 
--source include/rpl_end.inc

Comment by Aleksey Midenkov [ 2023-07-18 ]

Please review bb-10.4-midenok

Comment by Nikita Malyavin [ 2023-07-19 ]

7bd7bd116 and fb79f477 are good to push after squash

Comment by Aleksey Midenkov [ 2023-07-20 ]

10.4.31 10.5.22 10.6.15 10.9.8 10.10.6 10.11.5 11.0.3 11.1.2 11.2.1

Generated at Thu Feb 08 10:22:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.