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 ] |
Thanks for the report. I assume you have query cache enabled.
MTR test case
--source include/have_innodb.inc
--connect (con1,localhost,root,,)
--disconnect con1
--connection default
Actual result on 10.2
delete from t1;
select * from t2;
t2id id
1 1
optimize table t2;
Table Op Msg_type Msg_text
test.t2 optimize note Table does not support optimize, doing recreate + analyze instead
test.t2 optimize status OK
select * from t2;
t2id id
Not reproducible on 10.1.
Not reproducible when query cache is disabled.