[MDEV-12485] foreign key on delete cascade stale entries with query cache enabled Created: 2017-04-11  Updated: 2017-05-05  Resolved: 2017-05-05

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, Query Cache, Storage Engine - InnoDB
Affects Version/s: 10.2.5, 10.2
Fix Version/s: 10.2.6

Type: Bug Priority: Major
Reporter: Tim Westervoorde Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: 10.2-ga
Environment:

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)



 Comments   
Comment by Elena Stepanova [ 2017-04-11 ]

Thanks for the report. I assume you have query cache enabled.

MTR test case

 
--source include/have_innodb.inc
 
 
 
set global query_cache_type=1;
 
set global query_cache_size=1024*1024;
 
 
 
--connect (con1,localhost,root,,)
 
 
 
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;
 
select * from t2;
 
 
 
delete from t1;
 
select * from t2;
 
 
 
optimize table t2;
 
select * from t2;
 
 
 
drop table t2;
 
drop table t1;
 
 
 
--disconnect con1
 
--connection default
 
 
 
set global query_cache_type=DEFAULT;
 
set global query_cache_size=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.

Comment by Tim Westervoorde [ 2017-04-11 ]

Hi Elena,

I can confirm this works as expected with the query cache disabled

Comment by Oleksandr Byelkin [ 2017-04-28 ] 

--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;

Comment by Oleksandr Byelkin [ 2017-04-28 ]

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

Comment by Oleksandr Byelkin [ 2017-04-28 ]

current INNODB sends key for QC devided by '/' instead of '\0'

Comment by Oleksandr Byelkin [ 2017-04-28 ]

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.

—

Comment by Oleksandr Byelkin [ 2017-04-28 ]

github tree bb-10.2-MDEV-12485

Comment by Marko Mäkelä [ 2017-05-03 ]

OK to push after addressing my review comments.

Generated at Thu Feb 08 07:58:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.