Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.3.27, 10.3(EOL), 10.4(EOL), 10.5
-
None
-
10.3.27-MariaDB-0+deb10u1
Description
Version 10.3.27 on Debian tested, have not tested other platforms.
Create two tables, both with system versioning. Table one has some random records in it. Table two has a foreign key reference to table one with a cascading delete. Create some records.
Running a delete query of the format "DELETE table1 FROM table1" does not cascade the deletion to table2 while the query "DELETE FROM table1" does cascade as expected.
--source include/have_innodb.inc
|
|
|
create table t1 ( |
id tinyint unsigned not null auto_increment, |
value char(1), |
primary key ( id ) |
) engine=innodb with system versioning; |
create table t2 ( |
parent tinyint unsigned not null, |
foreign key ( parent ) references t1 ( id ) on update cascade on delete cascade |
) engine=innodb with system versioning; |
|
|
insert into t1 (value) values ('a'), ('b'); |
insert into t2 values (1), (2); |
|
|
delete from t1 where id = 1; |
delete t1 from t1 where id = 2; |
select * from t1; |
|
|
drop tables t2, t1; |
Thanks for the report! I repeated on 10.3-10.5:
10.5
MariaDB [test]> create table test1 (id int not null primary key,value char(1)-> )engine=innodb with system versioning;Query OK, 0 rows affected (0.109 sec)MariaDB [test]> insert into test1 values (1,'a'),(2,'b');Query OK, 2 rows affected (0.006 sec)Records: 2 Duplicates: 0 Warnings: 0MariaDB [test]> create table test2 (-> parent int not null,-> foreign key ( parent ) references test1 ( id ) on update cascade on delete cascade-> )engine=innodb;Query OK, 0 rows affected (0.048 sec)MariaDB [test]> insert into test2 values (1),(2);Query OK, 2 rows affected (0.011 sec)Records: 2 Duplicates: 0 Warnings: 0MariaDB [test]> select * from test2 left join test1 on test2.parent = test1.id;+--------+------+-------+| parent | id | value |+--------+------+-------+| 1 | 1 | a || 2 | 2 | b |+--------+------+-------+2 rows in set (0.018 sec)MariaDB [test]> delete test1.* from test1 where id = 1;Query OK, 1 row affected (0.008 sec)MariaDB [test]> select * from test2 left join test1 on test2.parent = test1.id;+--------+------+-------+| parent | id | value |+--------+------+-------+| 1 | NULL | NULL || 2 | 2 | b |+--------+------+-------+2 rows in set (0.004 sec)MariaDB [test]> alter table test1 drop system versioning;Query OK, 0 rows affected (0.073 sec)Records: 0 Duplicates: 0 Warnings: 0MariaDB [test]> delete test1.* from test1 where id = 2;Query OK, 1 row affected (0.004 sec)MariaDB [test]> select * from test2 left join test1 on test2.parent = test1.id;+--------+------+-------+| parent | id | value |+--------+------+-------+| 1 | NULL | NULL |+--------+------+-------+1 row in set (0.001 sec)