[MDEV-15364] FOREIGN CASCADE operations in system versioned referenced tables Created: 2018-02-20  Updated: 2020-12-12  Resolved: 2018-04-09

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Versioned Tables
Affects Version/s: 10.3.4
Fix Version/s: 10.3.6

Type: Bug Priority: Major
Reporter: Eugene Kosov (Inactive) Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-20311 row_ins_step accesses unitialized memory Closed
relates to MDEV-23644 Assertion on evaluating foreign refer... Closed

 Description    

create or replace table author (
 
	id smallint unsigned not null auto_increment primary key,
 
	name varchar(100) not null
 
) engine = innodb;
 
 
 
create or replace table book (
 
	id mediumint unsigned not null auto_increment primary key,
 
	title varchar(200) not null,
 
	author_id smallint unsigned not null,
 
	constraint `fk_book_author`
 
		foreign key (author_id) references author (id)
 
		on delete cascade
 
		on update cascade
 
) engine = innodb with system versioning;
 
 
 
insert into author (name) values ('abdul alhazred');
 
insert into book (title, author_id) values ('necronomicon', last_insert_id());
 
 
 
select author_id, row_start, row_end from book for system_time all;
 
update author set id = 11;
 
select author_id, row_start, row_end from book for system_time all;
 
delete from author;
 
select author_id, row_start, row_end from book for system_time all;

Last SELECT returns this:

select author_id, row_start, row_end from book for system_time all;
 
author_id	row_start	row_end
 
11	2018-02-20 18:20:05.655342	2018-02-20 18:20:05.661452

So, history row was not inserted on UPDATE. This is a bug. MS SQL produces a history in that case: http://sqlfiddle.com/#!18/a073b/15

 Comments   
Comment by Eugene Kosov (Inactive) [ 2018-02-21 ]

Logic on versioned delete (which is UPDATE SET ROW_END=CURRENT_TIMESTAMP) is implemented inside row_update_cascade_for_mysql(). This also looks like a right place to implement versioned UPDATE (which is UPDATE current row + INSERT historical row).

#0  row_update_cascade_for_mysql (thr=0x620000014f40, node=0x61700004b508, table=0x617000045308) at row0mysql.cc:2232
 
#1  row_ins_foreign_check_on_constraint (thr=0x620000014f40, foreign=0x616000055c08, pcur=0x7fffdec06740, entry=0x616000056230, mtr=0x7fffdec06880) at row0ins.cc:1419
 
#2  row_ins_check_foreign_constraint (check_ref=0, foreign=0x616000055c08, table=0x617000042908, entry=0x616000056230, thr=0x620000014f40) at row0ins.cc:1789
 
#3  row_upd_check_references_constraints (node=0x620000014c48, pcur=0x61100005ae00, table=0x617000042908, index=0x618000023508, offsets=0x6190000dc6b0, thr=0x620000014f40, mtr=0x7fffdec082e0) at row0upd.cc:326
 
#4  row_upd_clust_rec_by_insert (node=0x620000014c48, index=0x618000023508, thr=0x620000014f40, referenced=1, foreign=false, mtr=0x7fffdec082e0) at row0upd.cc:2758
 
#5  row_upd_clust_step (node=0x620000014c48, thr=0x620000014f40) at row0upd.cc:3212
 
#6  row_upd (node=0x620000014c48, thr=0x620000014f40) at row0upd.cc:3288
 
#7  row_upd_step (thr=0x620000014f40) at row0upd.cc:3432
 
#8  row_update_for_mysql (prebuilt=0x620000014108) at row0mysql.cc:1940
 
#9  ha_innobase::update_row (this=0x61d0001f04a0, old_row=0x6190000da308 "\001", new_row=0x6190000da2a0 "\v") at ha_innodb.cc:9043
 
#10 handler::ha_update_row (this=0x61d0001f04a0, old_data=0x6190000da308 "\001", new_data=0x6190000da2a0 "\v") at handler.cc:6245
 
#11 mysql_update (thd=0x62a0000ba208, table_list=0x62b000000300, fields=List<Item> with 1 elements, values=List<Item> with 1 elements, conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7fffdec0cd20, updated_return=0x7fffdec0cd40) at sql_update.cc:942
 
#12 mysql_execute_command (thd=0x62a0000ba208) at sql_parse.cc:4550
 
#13 mysql_parse (thd=0x62a0000ba208, rawbuf=0x62b000000220 "update author set id = 11", length=25, parser_state=0x7fffdec12a40, is_com_multi=false, is_next_command=false) at sql_parse.cc:7980
 
#14 dispatch_command (command=COM_QUERY, thd=0x62a0000ba208, packet=0x62900012c209 "update author set id = 11", packet_length=25, is_com_multi=false, is_next_command=false) at sql_parse.cc:1824

Comment by Eugene Kosov (Inactive) [ 2018-02-26 ]

I will try to insert a historical row just after `row_upd_step` in `row_update_cascade_for_mysql`.

Comment by Eugene Kosov (Inactive) [ 2018-03-13 ]

row_update_cascade_for_mysql() calls row_upd_step()

It's possible to insert historical row before updating. But I think this will result in extra BTree search because we need to read current row values. Second search will be performed in subsequent row_upd_step()

I've tried to make row_upd_step() set row_end = CURRENT_TIMESTAMP only and after that insert a new row with updated values. This broke recursive CASCADE UPDATE because it is triggered on update of foreign fields.

Now I make upd_node_t save old row_start to upd_node_t::upd_t::vers_sys_value. After row_upd_step() historical row is created and inserted. Foreign values can be updated from parent upd_node_t which is in progress at that moment and have old rows values.

Comment by Eugene Kosov (Inactive) [ 2018-03-13 ]

I was wrong. At the moment of row_upd_step()` call we have a *btr_pcur_t` which point to a record to update. So we can easily read that record, set *row_end=current_timestamp and insert it before it's updated.

Comment by Eugene Kosov (Inactive) [ 2018-03-19 ]

Corresponding PR https://github.com/MariaDB/server/pull/667

Comment by Marko Mäkelä [ 2018-03-20 ]

I requested some changes.

Comment by Marko Mäkelä [ 2018-04-06 ]

The revised code looks OK to me, with only one bigger issue: the memory for historical_row is allocated from row_prebuilt_t::heap and thus will not be freed before the end of the statement. The actual needed lifetime should only be until the end of the current row operation.

Because I will be away next week, thiru will do the final approval.

Generated at Thu Feb 08 08:20:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.