search

midenok / mariadb

MariaDB server is a community developed fork of MySQL server. Started by core members of the original MySQL team, MariaDB actively works with outside developers to deliver the most featureful, stable, and sanely licensed open SQL server in the industry.
GNU General Public License v2.0
0 stars 0 forks source link

MDEV-23446 UPDATE does not insert history row if the row is not changed #84

Open midenok opened 3 years ago

midenok commented 3 years ago

Bug: foreign key cascade set null doesn't work

Reproduce

--source include/have_innodb.inc

set timestamp = 1;
set time_zone='+02:00';
select now();
create table t1 (
  pk int unsigned primary key,
  f1 varchar(255)  character set ucs2,
  f2 longtext character set ucs2,
  f3 varchar(255),
  f4 char(255),
  f5 longtext character set ucs2,
  f6 int unsigned,
  f7 int unsigned,
  f8 int unsigned,
  f9 int unsigned,
  f10 int unsigned,
  f11 int unsigned,
  f12 varchar(255) character set ucs2,
  f13 char(255)  character set ucs2,
  f14 char(255) character set ucs2,
  f15 varchar(255),
  f16 longtext,
  f17 char(255)
) engine=innodb with system versioning;

insert into t1 values
(1, 'a', 'e', 'f', 'a', 'generate', 1, 2, 3, 4, 5, 6, 'main', 'against', 'b', 'u', 'explode', 'tomorrow'),
(2, repeat('a',127), 'f', 'k', 'game', 'g', 2, 3, 4, 5, 6, 7, repeat('o',222), 'oven', 'flower', repeat('r',120), 'l', 'g'),
(3, 'weekly', 'x', 'v', 'r', 'c', 3, 4, 5, 6, 7, 8, 'validity', 'y', 'h', 'oxygen', 'venture', 'uncertainty'),
(4, 'r', 't', repeat('b',153), 'modern', 'h', 4, 5, 6, 7, 8, 9, repeat('g',128), 'a', 'w', 'f', 'b', 'b'),
(5, 'h', 'y', repeat('v',107), 'knife', 'profession', 5, 6, 7, 8, 9, 0, 'infection', 'u', 'likelihood', repeat('n',149), 'folk', 'd'),
(6, 'g', 'violent', repeat('o',28), 'capital', 'p', 6, 7, 8, 9, 0, 1, 'w', 'patron', 'd', 'y', 'originally', 'k'),
(7, 'k', 'uncomfortable', repeat('v',248), 'y', 'link', 7, 8, 9, 0, 1, 2, repeat('j',204), 'j', 'statute', 'emphasis', 'u', 'water'),
(8, 'preparation', 'water', 'suck', 'silver', 'a', 8, 9, 0, 1, 2, 3, 'h', 'q', 'o', 't', 'k', 'y'),
(9, 'y', 'f', 'e', 'a', 'dawn', 9, 0, 1, 2, 3, 4, 'peak', 'parking', 'b', 't', 'timber', 'c'),
(10, repeat('h',78), 'apologize', 'direct', 'u', 'frankly', 0, 1, 2, 3, 4, 5, 'h', 'exhibit', 'f', 'd', 'effective', 'c'),
(11, 'i', 'h', 'a', 'y', 'u', 1, 2, 3, 4, 5, 6, 'l', 'b', 'm', 'respond', 'ideological', 'credibility');

create table t2 (
  pk int primary key,
  f char(255) character set ucs2,
  key(f)
) engine=innodb;

insert into t2 values (1,'against'),(2,'q');

set sql_mode= '';
set timestamp = 2;
select * from t1 into outfile 't1.data';
set timestamp = 3;
update t1 set f13 = 'q';
set timestamp = 4;
load data infile 't1.data' replace into table t1;
select * from t1 into outfile 't1.data.2' ;
set timestamp = 5;
load data infile 't1.data.2' replace into table t1;
select * from t2 into outfile 't2.data';
set timestamp = 6;
load data infile 't2.data' replace into table t2;
set foreign_key_checks = off;
alter table t1 add foreign key (f13) references t2 (f) on delete set null;
set timestamp = 7;
load data infile 't1.data' replace into table t1;
set foreign_key_checks = on;

set session sql_mode= 'no_backslash_escapes';
set timestamp = 8;
load data infile 't1.data' replace into table t1;
set timestamp = 9;
select f13, row_start, row_end from t1 for system_time all where f13 = 'q' or f13 = 'against';
select * from t2;
replace into t2 select * from t2;

# cleanup
drop table t1, t2;
--let $datadir= `select @@datadir`
--remove_file $datadir/test/t1.data
--remove_file $datadir/test/t1.data.2
--remove_file $datadir/test/t2.data

Result

Record not found on UPDATE SET NULL.

#0  row_upd_clust_step (node=0x7efdf002f0e0, thr=0x7efdf0045880) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0upd.cc:3116
#1  0x0000000000ff9882 in row_upd (node=0x7efdf002f0e0, thr=0x7efdf0045880) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0upd.cc:3302
#2  0x0000000000ff9395 in row_upd_step (thr=0x7efdf0045880) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0upd.cc:3446
#3  0x0000000000f7eb0f in row_update_cascade_for_mysql (thr=0x7efdf0045880, node=0x7efdf002f0e0, table=0x7efdf002c880) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0mysql.cc:2243
#4  0x0000000000f438ee in row_ins_foreign_check_on_constraint (thr=0x7efdf0045880, foreign=0x7efdf0045440, pcur=0x7efe300a9ab0, entry=0x7efdf0040058, mtr=0x7efe300a95f0) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0ins.cc:1339
#5  0x0000000000f411e6 in row_ins_check_foreign_constraint (check_ref=0, foreign=0x7efdf0045440, table=0x7efdf0039b40, entry=0x7efdf0040058, thr=0x7efdf0045880) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0ins.cc:1762
#6  0x000000000100069d in row_upd_check_references_constraints (node=0x7efdf0036a60, pcur=0x7efe300aa0c8, table=0x7efdf0039b40, index=0x7efdf0033390, offsets=0x7efdf007cdd8, thr=0x7efdf0045880, mtr=0x7efe300aa1c8) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0upd.cc:295
#7  0x0000000001003634 in row_upd_sec_index_entry (node=0x7efdf0036a60, thr=0x7efdf0045880) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0upd.cc:2507
#8  0x0000000000ffd99d in row_upd_sec_step (node=0x7efdf0036a60, thr=0x7efdf0045880) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0upd.cc:2556
#9  0x0000000000ff9ae7 in row_upd (node=0x7efdf0036a60, thr=0x7efdf0045880) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0upd.cc:3331
#10 0x0000000000ff9395 in row_upd_step (thr=0x7efdf0045880) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0upd.cc:3446
#11 0x0000000000f7d2f1 in row_update_for_mysql (prebuilt=0x7efdf0035dc0) at /home/midenok/src/mariadb/10.3/src/storage/innobase/row/row0mysql.cc:1847
#12 0x0000000000da2f94 in ha_innobase::delete_row (this=0x7efdf0037cd0, record=0x7efdf0035b28 "\245\002") at /home/midenok/src/mariadb/10.3/src/storage/innobase/handler/ha_innodb.cc:8948
#13 0x0000000000b20b9e in handler::ha_delete_row (this=0x7efdf0037cd0, buf=0x7efdf0035b28 "\245\002") at /home/midenok/src/mariadb/10.3/src/sql/handler.cc:6554
#14 0x000000000074f5db in write_record (thd=0x7efdf0000d28, table=0x7efdf0034d48, info=0x7efdf00153a8) at /home/midenok/src/mariadb/10.3/src/sql/sql_insert.cc:1987
#15 0x000000000075638e in select_insert::send_data (this=0x7efdf0015360, values=...) at /home/midenok/src/mariadb/10.3/src/sql/sql_insert.cc:3936
#16 0x000000000080a225 in end_send (join=0x7efdf0015410, join_tab=0x7efdf0016f00, end_of_records=false) at /home/midenok/src/mariadb/10.3/src/sql/sql_select.cc:20891
#17 0x000000000082cf65 in evaluate_join_record (join=0x7efdf0015410, join_tab=0x7efdf0016b70, error=0) at /home/midenok/src/mariadb/10.3/src/sql/sql_select.cc:19931
#18 0x000000000082be31 in AGGR_OP::end_send (this=0x7efdf0017450) at /home/midenok/src/mariadb/10.3/src/sql/sql_select.cc:27832
#19 0x000000000080b270 in sub_select_postjoin_aggr (join=0x7efdf0015410, join_tab=0x7efdf0016b70, end_of_records=true) at /home/midenok/src/mariadb/10.3/src/sql/sql_select.cc:19427
#20 0x000000000082bfb2 in sub_select (join=0x7efdf0015410, join_tab=0x7efdf00167e0, end_of_records=true) at /home/midenok/src/mariadb/10.3/src/sql/sql_select.cc:19662
#21 0x0000000000812615 in do_select (join=0x7efdf0015410, procedure=0x0) at /home/midenok/src/mariadb/10.3/src/sql/sql_select.cc:19253
#22 0x00000000008112ba in JOIN::exec_inner (this=0x7efdf0015410) at /home/midenok/src/mariadb/10.3/src/sql/sql_select.cc:4120
#23 0x000000000081031e in JOIN::exec (this=0x7efdf0015410) at /home/midenok/src/mariadb/10.3/src/sql/sql_select.cc:3914
#24 0x00000000007e6ee7 in mysql_select (thd=0x7efdf0000d28, tables=0x7efdf00144b8, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489925888, result=0x7efdf0015360, unit=0x7efdf0004be0, select_lex=0x7efdf0005368) at /home/midenok/src/mariadb/10.3/src/sql/sql_select.cc:4319
#25 0x00000000007e6723 in handle_select (thd=0x7efdf0000d28, lex=0x7efdf0004b20, result=0x7efdf0015360, setup_tables_done_option=1073741824) at /home/midenok/src/mariadb/10.3/src/sql/sql_select.cc:370
#26 0x000000000079d653 in mysql_execute_command (thd=0x7efdf0000d28) at /home/midenok/src/mariadb/10.3/src/sql/sql_parse.cc:4597
#27 0x0000000000795206 in mysql_parse (thd=0x7efdf0000d28, rawbuf=0x7efdf0013c10 "replace into t2 select * from t2", length=32, parser_state=0x7efe300ae5e8, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.3/src/sql/sql_parse.cc:7837

frame 0

3109            success = btr_pcur_restore_position(mode, pcur, &mtr);
3110
3111            if (!success) {
3112                    err = DB_RECORD_NOT_FOUND;
3113
3114                    mtr_commit(&mtr);
3115
3116                    return(err);
3117            }
(rr) p err
$11 = DB_RECORD_NOT_FOUND

Notes

show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `pk` int(10) unsigned NOT NULL,
  `f1` varchar(255) CHARACTER SET ucs2 DEFAULT NULL,
  `f2` longtext CHARACTER SET ucs2 DEFAULT NULL,
  `f3` varchar(255) DEFAULT NULL,
  `f4` char(255) DEFAULT NULL,
  `f5` longtext CHARACTER SET ucs2 DEFAULT NULL,
  `f6` int(10) unsigned DEFAULT NULL,
  `f7` int(10) unsigned DEFAULT NULL,
  `f8` int(10) unsigned DEFAULT NULL,
  `f9` int(10) unsigned DEFAULT NULL,
  `f10` int(10) unsigned DEFAULT NULL,
  `f11` int(10) unsigned DEFAULT NULL,
  `f12` varchar(255) CHARACTER SET ucs2 DEFAULT NULL,
  `f13` char(255) CHARACTER SET ucs2 DEFAULT NULL,
  `f14` char(255) CHARACTER SET ucs2 DEFAULT NULL,
  `f15` varchar(255) DEFAULT NULL,
  `f16` longtext DEFAULT NULL,
  `f17` char(255) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `f13` (`f13`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f13`) REFERENCES `t2` (`f`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
show create table t2;
Table   Create Table
t2      CREATE TABLE `t2` (
  `pk` int(11) NOT NULL,
  `f` char(255) CHARACTER SET ucs2 DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `f` (`f`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
midenok commented 3 years ago

Cursor stored

#0  btr_pcur_store_position (cursor=0x7f43100a3ab0, mtr=0x7f43100a35f0) at /home/midenok/src/mariadb/10.3b/src/storage/innobase/btr/btr0pcur.cc:105
#1  0x0000000000f439f4 in row_ins_foreign_check_on_constraint (thr=0x7f42c4045bc0, foreign=0x7f42c4045500, pcur=0x7f43100a3ab0, entry=0x7f42c4083388, mtr=0x7f43100a35f0) at /home/midenok/src/mariadb/10.3b/src/storage/innobase/row/row0ins.cc:1317
#2  0x0000000000f413b6 in row_ins_check_foreign_constraint (check_ref=0, foreign=0x7f42c4045500, table=0x7f42c4039c00, entry=0x7f42c4083388, thr=0x7f42c4045bc0) at /home/midenok/src/mariadb/10.3b/src/storage/innobase/row/row0ins.cc:1762
#3  0x000000000100054d in row_upd_check_references_constraints (node=0x7f42c4036b20, pcur=0x7f43100a40c8, table=0x7f42c4039c00, index=0x7f42c4033450, offsets=0x7f42c4082ad8, thr=0x7f42c4045bc0, mtr=0x7f43100a41c8) at /home/midenok/src/mariadb/10.3b/src/storage/innobase/row/row0upd.cc:295
#4  0x00000000010034e4 in row_upd_sec_index_entry (node=0x7f42c4036b20, thr=0x7f42c4045bc0) at /home/midenok/src/mariadb/10.3b/src/storage/innobase/row/row0upd.cc:2507
#5  0x0000000000ffd84d in row_upd_sec_step (node=0x7f42c4036b20, thr=0x7f42c4045bc0) at /home/midenok/src/mariadb/10.3b/src/storage/innobase/row/row0upd.cc:2556
#6  0x0000000000ff9997 in row_upd (node=0x7f42c4036b20, thr=0x7f42c4045bc0) at /home/midenok/src/mariadb/10.3b/src/storage/innobase/row/row0upd.cc:3331
#7  0x0000000000ff9245 in row_upd_step (thr=0x7f42c4045bc0) at /home/midenok/src/mariadb/10.3b/src/storage/innobase/row/row0upd.cc:3446
#8  0x0000000000f7d381 in row_update_for_mysql (prebuilt=0x7f42c4035e80) at /home/midenok/src/mariadb/10.3b/src/storage/innobase/row/row0mysql.cc:1847
#9  0x0000000000da30d4 in ha_innobase::delete_row (this=0x7f42c4037d90, record=0x7f42c4035be8 "\245\002") at /home/midenok/src/mariadb/10.3b/src/storage/innobase/handler/ha_innodb.cc:8948
#10 0x0000000000b20cde in handler::ha_delete_row (this=0x7f42c4037d90, buf=0x7f42c4035be8 "\245\002") at /home/midenok/src/mariadb/10.3b/src/sql/handler.cc:6554
#11 0x000000000074f5ab in write_record (thd=0x7f42c4000d28, table=0x7f42c4034e08, info=0x7f42c40153a8) at /home/midenok/src/mariadb/10.3b/src/sql/sql_insert.cc:1987
#12 0x000000000075635e in select_insert::send_data (this=0x7f42c4015360, values=...) at /home/midenok/src/mariadb/10.3b/src/sql/sql_insert.cc:3936

Divergence is row_ins_foreign_check_on_constraint() (1317 vs 1339).

(rr) p dbug_print_rec(rec, index)
$3 = 0x7f42c40888c0 "COMPACT RECORD(info_bits=0, 3 fields): {[510]   q", ' ' <repeats 506 times>, "(0x000000710020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020),[4]    (0x00000008),[7]     B?(0x7FFFFFFF0F423F)}"
(rr) p foreign->foreign_table->name.m_name
$7 = 0x7f42c4013970 "test/t1"
(rr) p foreign->referenced_table->name.m_name
$8 = 0x7f42c4032050 "test/t2"
(rr) p table->name.m_name
$9 = 0x7f42c4013970 "test/t1"
(rr) p pcur.btr_cur.index->name.m_name
$15 = 0x7f42c405add0 "f13"
(rr) p pcur.btr_cur.index->table->name.m_name
$16 = 0x7f42c4013970 "test/t1"

Cause

REPLACE is executed on parent table t2 which triggers referential action of updating f13 on foreign table t1. Cursor is stored in row_ins_foreign_check_on_constraint(), then it is restored by row_upd_clust_step() in row_update_cascade_for_mysql() and record is not found. NB: cursor is stored on secondary index and missed record is in clustered index.

Notes

First we have to fix #85 which is more clear failure of executing referential action.