Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2.4
-
CentOS Linux release 7.3.1611 x86_64
mariadb-10.2.4-linux-x86_64.tar.gz
Description
1. I have encountered a issue about foreign key when I tested flashback function.Foreign key(ON DELETE CASCADE ON UPDATE CASCADE) doesn't work when the primary key of master table is deleted on mariadb-10.2.4.
MariaDB [test]> CREATE TABLE `t1` (
|
-> `id` int(11) NOT NULL AUTO_INCREMENT, |
-> `name` varchar(32) DEFAULT '', |
-> PRIMARY KEY (`id`)
|
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
Query OK, 0 rows affected (0.02 sec) |
|
|
MariaDB [test]> CREATE TABLE `t2` (
|
-> `id` int(11) NOT NULL AUTO_INCREMENT, |
-> `t1_id` int(11) NOT NULL, |
-> `name` varchar(32) DEFAULT '', |
-> PRIMARY KEY (`id`),
|
-> CONSTRAINT `t2_fk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
Query OK, 0 rows affected (0.02 sec) |
|
|
MariaDB [test]> insert into t1 values (1,'dage'); |
Query OK, 1 row affected (0.01 sec) |
|
|
MariaDB [test]> insert into t2 values (1,1,'xiaodi'); |
Query OK, 1 row affected (0.00 sec) |
|
|
MariaDB [test]> select * from t1;
|
+----+------+
|
| id | name |
|
+----+------+
|
| 1 | dage | |
+----+------+
|
1 row in set (0.00 sec) |
|
|
MariaDB [test]> select * from t2;
|
+----+-------+--------+
|
| id | t1_id | name |
|
+----+-------+--------+
|
| 1 | 1 | xiaodi | |
+----+-------+--------+
|
1 row in set (0.00 sec) |
|
|
MariaDB [test]> delete from t1 where id=1; |
Query OK, 1 row affected (0.01 sec) |
|
|
MariaDB [test]> select * from t1;
|
Empty set (0.00 sec) |
|
|
MariaDB [test]> select * from t2;
|
+----+-------+--------+
|
| id | t1_id | name |
|
+----+-------+--------+
|
| 1 | 1 | xiaodi | |
+----+-------+--------+
|
1 row in set (0.00 sec) |
|
|
MariaDB [test]> delete from t2 where id =1; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> select * from t2;
|
+----+-------+--------+
|
| id | t1_id | name |
|
+----+-------+--------+
|
| 1 | 1 | xiaodi | |
+----+-------+--------+
|
1 row in set (0.00 sec) |
|
|
MariaDB [test]> update t2 set name='abc'; |
Query OK, 0 rows affected (0.00 sec) |
Rows matched: 0 Changed: 0 Warnings: 0 |
|
|
MariaDB [test]> select * from t2;
|
+----+-------+--------+
|
| id | t1_id | name |
|
+----+-------+--------+
|
| 1 | 1 | xiaodi | |
+----+-------+--------+
|
1 row in set (0.00 sec) |
|
|
MariaDB [test]> truncate table t2;
|
Query OK, 0 rows affected (0.01 sec) |
|
|
MariaDB [test]> select * from t2;
|
Empty set (0.00 sec) |
2.The foreign key (ON DELETE CASCADE ON UPDATE CASCADE) of slave table will be deleted when the primary key of master table is deleted,
but the data changes of slave table will not be recorded in the binlog, so flashback will fail.