Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.5, 10.2(EOL)
-
CentOS Linux release 7.2.1511 (Core)
Linux wall-e.dicode.local 3.10.0-327.10.1.el7.x86_64 #1 SMP Tue Feb 16 17:03:50 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
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
| Field | Original Value | New Value |
|---|---|---|
| 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) }} |
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: {code:mysql} 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) {code} |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Component/s | Query Cache [ 10120 ] | |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Assignee | Oleksandr Byelkin [ sanja ] | |
| Labels | innodb | 10.2-ga |
| Summary | foreign key on delete cascade stale entries | foreign key on delete cascade stale entries with query cache enabled |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Sprint | 10.2.6 [ 158 ] |
| Rank | Ranked higher |
| Sprint | 10.2.6 [ 158 ] |
| Rank | Ranked higher |
| Assignee | Oleksandr Byelkin [ sanja ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Marko Mäkelä [ marko ] | Oleksandr Byelkin [ sanja ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Marko Mäkelä [ marko ] | Oleksandr Byelkin [ sanja ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.2.6 [ 22527 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 80297 ] | MariaDB v4 [ 151945 ] |