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)
|
|
Bug
Major
