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
Hi Elena,
I can confirm this works as expected with the query cache disabled
--source include/have_innodb.inc
|
|
set global query_cache_type=1;
|
set global query_cache_size=1024*1024;
|
set query_cache_type=1;
|
|
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 t2;
|
|
delete from t1;
|
select * from t2;
|
|
optimize table t2;
|
select * from t2;
|
|
drop table t2;
|
drop table t1;
|
|
set global query_cache_type=DEFAULT;
|
set global query_cache_size=DEFAULT;
|
before 10.2 (in 10.1) innodb called table invalidation on cascade action:
(gdb) p key + 5
|
$1 = 0x7ffff04459e5 "t2"
|
(gdb) frame 2
|
#2 0x00007ffff1acd221 in innobase_invalidate_query_cache (trx=0x7fffb0002088, full_name=0x7fffb002f678 "test", full_name_len=8) at /home/sanja/maria/git/server/storage/innobase/handler/ha_innodb.cc:3071
|
(gdb) frame 13
|
#13 0x0000555555c722ca in handler::ha_delete_row (this=0x7fffb00032e8, buf=0x7fffb0142b68 "\377\001") at /home/sanja/maria/git/server/sql/handler.cc:5987
|
(gdb) frame 12
|
#12 0x00007ffff1ad8470 in ha_innodb::delete_row (this=0x7fffb00032e8, record=0x7fffb0142b68 "\377\001") at /home/sanja/maria/git/server/storage/innobase/handler/ha_innodb.cc:8993
|
(gdb) frame 1
|
#1 0x00005555559d8cb9 in mysql_query_cache_invalidate4 (thd=0x555557c38140, key=0x7ffff04459e0 "test", key_length=8, using_trx=1) at /home/sanja/maria/git/server/sql/sql_cache.cc:1258
|
(gdb) where
|
#0 Query_cache::invalidate (this=0x555556d6d5e0 <query_cache>, thd=0x555557c38140, key=0x7ffff04459e0 "test", key_length=8, using_transactions=1 '\001') at /home/sanja/maria/git/server/sql/sql_cache.cc:2296
|
#1 0x00005555559d8cb9 in mysql_query_cache_invalidate4 (thd=0x555557c38140, key=0x7ffff04459e0 "test", key_length=8, using_trx=1) at /home/sanja/maria/git/server/sql/sql_cache.cc:1258
|
#2 0x00007ffff1acd221 in innobase_invalidate_query_cache (trx=0x7fffb0002088, full_name=0x7fffb002f678 "test", full_name_len=8) at /home/sanja/maria/git/server/storage/innobase/handler/ha_innodb.cc:3071
|
#3 0x00007ffff1bc3e52 in row_ins_invalidate_query_cache (thr=0x7fffb016d518, name=0x7fffb016fb20 "test/t2") at /home/sanja/maria/git/server/storage/innobase/row/row0ins.cc:928
|
#4 0x00007ffff1bc3faa in row_ins_foreign_check_on_constraint (thr=0x7fffb016d518, foreign=0x7fffb0001f58, pcur=0x7ffff0445d80, entry=0x7fffb002f9c0, mtr=0x7ffff04461c0) at /home/sanja/maria/git/server/storage/innobase/row/row0ins.cc:992
|
#5 0x00007ffff1bc5713 in row_ins_check_foreign_constraint (check_ref=0, foreign=0x7fffb0001f58, table=0x7fffb0003be8, entry=0x7fffb002f9c0, thr=0x7fffb016d518) at /home/sanja/maria/git/server/storage/innobase/row/row0ins.cc:1650
|
#6 0x00007ffff1c21a06 in row_upd_check_references_constraints (node=0x7fffb016d290, pcur=0x7fffb002f3c8, table=0x7fffb0003be8, index=0x7fffb0143d48, offsets=0x7ffff0446840, thr=0x7fffb016d518, mtr=0x7ffff0446b60) at /home/sanja/maria/git/server/storage/innobase/row/row0upd.cc:310
|
#7 0x00007ffff1c25e5b in row_upd_del_mark_clust_rec (node=0x7fffb016d290, index=0x7fffb0143d48, offsets=0x7ffff0446840, thr=0x7fffb016d518, referenced=1, foreign=0, mtr=0x7ffff0446b60) at /home/sanja/maria/git/server/storage/innobase/row/row0upd.cc:2532
|
#8 0x00007ffff1c264db in row_upd_clust_step (node=0x7fffb016d290, thr=0x7fffb016d518) at /home/sanja/maria/git/server/storage/innobase/row/row0upd.cc:2690
|
#9 0x00007ffff1c26810 in row_upd (node=0x7fffb016d290, thr=0x7fffb016d518) at /home/sanja/maria/git/server/storage/innobase/row/row0upd.cc:2807
|
#10 0x00007ffff1c26c88 in row_upd_step (thr=0x7fffb016d518) at /home/sanja/maria/git/server/storage/innobase/row/row0upd.cc:2959
|
#11 0x00007ffff1be115f in row_update_for_mysql (mysql_rec=0x7fffb0142b68 "\377\001", prebuilt=0x7fffb016c888) at /home/sanja/maria/git/server/storage/innobase/row/row0mysql.cc:1842
|
#12 0x00007ffff1ad8470 in ha_innodb::delete_row (this=0x7fffb00032e8, record=0x7fffb0142b68 "\377\001") at /home/sanja/maria/git/server/storage/innobase/handler/ha_innodb.cc:8993
|
#13 0x0000555555c722ca in handler::ha_delete_row (this=0x7fffb00032e8, buf=0x7fffb0142b68 "\377\001") at /home/sanja/maria/git/server/sql/handler.cc:5987
|
#14 0x0000555555dcc880 in mysql_delete (thd=0x555557c38140, table_list=0x7fffb00066e0, conds=0x0, order_list=0x555557c3c4c0, limit=18446744073709551615, options=0, result=0x0) at /home/sanja/maria/git/server/sql/sql_delete.cc:595
|
#15 0x0000555555a2efde in mysql_execute_command (thd=0x555557c38140) at /home/sanja/maria/git/server/sql/sql_parse.cc:4116
|
revision-id: 8bd42a727aac40698925799de55b018e5e87abc9 (mariadb-10.2.5-118-g8bd42a727aa)
parent(s): 4b24467ff37d6db82500e736e832d0a53842ac9b
committer: Oleksandr Byelkin
timestamp: 2017-04-28 19:42:32 +0200
message:
MDEV-12485 foreign key on delete cascade stale entries with query cache enabled
During merge of innodb code QC invalidation was removed from innodb part but not added to server part.
We decided to keep it in innodb to keep server/engine interface the same.
—
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 defaultActual result on 10.2
delete from t1;select * from t2;t2id id1 1optimize table t2;Table Op Msg_type Msg_texttest.t2 optimize note Table does not support optimize, doing recreate + analyze insteadtest.t2 optimize status OKselect * from t2;t2id idNot reproducible on 10.1.
Not reproducible when query cache is disabled.