Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4, 10.6.11, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
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/*!*/; |
|