Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12485

foreign key on delete cascade stale entries with query cache enabled

    Details

      Description

      On tables with foreign key on delete cascade, the data is kept when the referenced item is deleted. However it cannot be deleted or altered and on optimize on the table the data is suddenly gone.

      test case:

      create table t1 ( id int unsigned auto_increment, primary key(id) ) engine=innodb;
      create table t2 ( t2id int unsigned, id int unsigned, primary key(t2id, id), foreign key (`id`) references t1(`id`) on delete cascade ) engine=innodb;
       
      insert into t1 values (1);
      insert into t2 values (1,1);
       
      select * from t1;
      +----+
      | id |
      +----+
      |  1 |
      +----+
      1 row in set (0.00 sec)
       
      select * from t2;
      +------+----+
      | t2id | id |
      +------+----+
      |    1 |  1 |
      +------+----+
      1 row in set (0.00 sec)
       
      delete from t1;
      Query OK, 1 row affected (0.00 sec)
       
      select * from t2;
      +------+----+
      | t2id | id |
      +------+----+
      |    1 |  1 |
      +------+----+
      1 row in set (0.00 sec)
       
       delete from t2;
      Query OK, 0 rows affected (0.00 sec)
       
      select * from t2;
      +------+----+
      | t2id | id |
      +------+----+
      |    1 |  1 |
      +------+----+
      1 row in set (0.00 sec)
       
       optimize table t2;
      +----------------------+----------+----------+-------------------------------------------------------------------+
      | Table                | Op       | Msg_type | Msg_text                                                          |
      +----------------------+----------+----------+-------------------------------------------------------------------+
      | xxxxxxx_xxxxxxxxx.t2 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
      | xxxxxxx_xxxxxxxxx.t2 | optimize | status   | OK                                                                |
      +----------------------+----------+----------+-------------------------------------------------------------------+
      2 rows in set (0.04 sec)
       
      select * from t2;
      Empty set (0.00 sec)
      
      

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              dicode Tim Westervoorde
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: