Details

    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.

      Attachments

        Activity

          elenst Elena Stepanova added a comment - - edited

          The problem lies outside the flashback scope. Flashback can only revert what it finds in the binary log. Cascading operations are currently not logged, thus flashback does not work with them.
          It needs to be documented as a limitation.

          greenman, while at it, could you please double-check current flashback documentation in KB? It's not big, so it shouldn't take too much time, and I think some corrections might be needed, factual and/or otherwise. Also, I've seen several references to --support-flashback variable in various places. I've fixed those that I found, but maybe there are more.

          elenst Elena Stepanova added a comment - - edited The problem lies outside the flashback scope. Flashback can only revert what it finds in the binary log. Cascading operations are currently not logged, thus flashback does not work with them. It needs to be documented as a limitation. greenman , while at it, could you please double-check current flashback documentation in KB? It's not big, so it shouldn't take too much time, and I think some corrections might be needed, factual and/or otherwise. Also, I've seen several references to --support-flashback variable in various places. I've fixed those that I found, but maybe there are more.

          People

            greenman Ian Gilfillan
            920895156@qq.com Devin Yu
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.