mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-02-04 21:02:17 +01:00
Code Issues Projects Releases Packages Wiki Activity Actions
mariadb/mysql-test/suite/versioning/t/update.test
Marko Mäkelä ddd7d5d8e3 MDEV-24035 Failing assertion: UT_LIST_GET_LEN(lock.trx_locks) == 0 causing disruption and replication failure 
Under unknown circumstances, the SQL layer may wrongly disregard an
invocation of thd_mark_transaction_to_rollback() when an InnoDB
transaction had been aborted (rolled back) due to one of the following errors:
* HA_ERR_LOCK_DEADLOCK
* HA_ERR_RECORD_CHANGED (if innodb_snapshot_isolation=ON)
* HA_ERR_LOCK_WAIT_TIMEOUT (if innodb_rollback_on_timeout=ON)

Such an error used to cause a crash of InnoDB during transaction commit.
These changes aim to catch and report the error earlier, so that not only
this crash can be avoided but also the original root cause be found and
fixed more easily later.

The idea of this fix is from Michael 'Monty' Widenius.

HA_ERR_ROLLBACK: A new error code that will be translated into
ER_ROLLBACK_ONLY, signalling that the current transaction
has been aborted and the only allowed action is ROLLBACK.

trx_t::state: Add TRX_STATE_ABORTED that is like
TRX_STATE_NOT_STARTED, but noting that the transaction had been
rolled back and aborted.

trx_t::is_started(): Replaces trx_is_started().

ha_innobase: Check the transaction state in various places.
Simplify the logic around SAVEPOINT.

ha_innobase::is_valid_trx(): Replaces ha_innobase::is_read_only().

The InnoDB logic around transaction savepoints, commit, and rollback
was unnecessarily complex and might have contributed to this
inconsistency. So, we are simplifying that logic as well.

trx_savept_t: Replace with const undo_no_t*. When we rollback to
a savepoint, all we need to know is the number of undo log records
that must survive.

trx_named_savept_t, DB_NO_SAVEPOINT: Remove. We can store undo_no_t
directly in the space allocated at innobase_hton->savepoint_offset.

fts_trx_create(): Do not copy previous savepoints.

fts_savepoint_rollback(): If a savepoint was not found, roll back
everything after the default savepoint of fts_trx_create().
The test innodb_fts.savepoint is extended to cover this code.

Reviewed by: Vladislav Lesin
Tested by: Matthias Leich
2024-12-12 18:02:00 +02:00

433 lines
15 KiB
Text
Raw Permalink Blame History

source suite/versioning/engines.inc;
source suite/versioning/common.inc;
--disable_query_log
call mtr.add_suppression("InnoDB: Transaction was aborted due to ");
--enable_query_log
replace_result $sys_datatype_expl SYS_DATATYPE;
eval create table t1(
x int unsigned,
y int unsigned,
sys_trx_start $sys_datatype_expl as row start invisible,
sys_trx_end $sys_datatype_expl as row end invisible,
period for system_time (sys_trx_start, sys_trx_end))
with system versioning;
insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000);
select x, y from t1 order by x, y;
update t1 set y = y + 1 where x > 7;
select x, y from t1 order by x, y;
select x, y from t1 for system_time all order by sys_trx_end, x, y;
drop table t1;
replace_result $sys_datatype_expl SYS_DATATYPE;
eval create table t1 (
id bigint primary key,
x int,
y int without system versioning,
sys_trx_start $sys_datatype_expl as row start invisible,
sys_trx_end $sys_datatype_expl as row end invisible,
period for system_time (sys_trx_start, sys_trx_end))
with system versioning;
set timestamp= unix_timestamp('2000-01-01 00:00:00');
insert into t1 values(1, 1, 1);
--disable_cursor_protocol
select sys_trx_start into @tmp1 from t1;
--enable_cursor_protocol
set timestamp= unix_timestamp('2000-01-01 01:00:00');
update t1 set x= 11, y= 11 where id = 1;
select @tmp1 < sys_trx_start as A1, x, y from t1;
--disable_cursor_protocol
select sys_trx_start into @tmp1 from t1;
--enable_cursor_protocol
set timestamp= unix_timestamp('2000-01-01 02:00:00');
update t1 set y= 1 where id = 1;
select @tmp1 = sys_trx_start as A2, x from t1;
drop table t1;
set timestamp= default;
replace_result $sys_datatype_expl SYS_DATATYPE;
eval create table t1 (
x int,
y int,
sys_trx_start $sys_datatype_expl as row start invisible,
sys_trx_end $sys_datatype_expl as row end invisible,
period for system_time (sys_trx_start, sys_trx_end)
) with system versioning;
insert into t1 (x, y) values (1, 1), (2, 1), (3, 1), (4, 1), (5, 1);
start transaction;
update t1 set y= y + 1 where x = 3;
update t1 set y= y + 1 where x = 2;
update t1 set y= y + 1 where x = 3;
update t1 set y= y + 1 where x > 3;
update t1 set y= y + 1 where x > 4;
commit;
replace_result $sys_datatype_max MAXVAL;
eval select x, y, sys_trx_end = $sys_datatype_max as current from t1 for system_time all order by sys_trx_end, x, y;
drop table t1;
replace_result $sys_datatype_expl SYS_DATATYPE;
eval create table t1 (
id int primary key auto_increment,
x int,
sys_trx_start $sys_datatype_expl as row start invisible,
sys_trx_end $sys_datatype_expl as row end invisible,
period for system_time (sys_trx_start, sys_trx_end))
with system versioning;
set @t0= now(6);
insert into t1 (x) values (1);
set @t1= now(6);
update t1 set x= 2 where id = 1;
set @t2= now(6);
update t1 set x= 3 where id = 1;
select x from t1 for system_time as of timestamp @t0;
select x from t1 for system_time as of timestamp @t1;
select x from t1 for system_time as of timestamp @t2;
select x from t1 for system_time as of timestamp now(6);
drop table t1;
replace_result $sys_datatype_expl SYS_DATATYPE;
eval create table t1(
x int unsigned,
sys_trx_end $sys_datatype_expl as row end invisible,
sys_trx_start $sys_datatype_expl as row start invisible,
y int unsigned,
period for system_time (sys_trx_start, sys_trx_end),
primary key(x, y))
with system versioning;
insert into t1(x, y) values (1, 1000), (3, 3000), (4, 4000), (5, 5000);
insert into t1(x, y) values(3, 3000) on duplicate key update y = y+1;
insert into t1(x, y) values(4, 4444) on duplicate key update y = y+1;
select x, y from t1 for system_time all order by sys_trx_end, x, y;
select x, y from t1 order by x, y;
drop table t1;
replace_result $sys_datatype_expl SYS_DATATYPE;
eval create table t1 (
x int unsigned,
y int unsigned,
sys_trx_start $sys_datatype_expl as row start invisible,
sys_trx_end $sys_datatype_expl as row end invisible,
period for system_time (sys_trx_start, sys_trx_end))
with system versioning;
create table t2 like t1;
insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000);
insert into t2(x, y) values (1, 1010), (2, 2010), (3, 3010), (4, 4010), (5, 5010), (6, 6010), (7, 7010), (8, 8010), (9, 9010);
update t1, t2 set t1.y = t1.x + t1.y, t2.y = t2.x + t2.y where t1.x > 7 and t2.x < 7;
select x, y from t1 for system_time all order by sys_trx_end, x, y;
select x, y from t1 order by x, y;
select x, y from t2 for system_time all order by sys_trx_end, x, y;
select x, y from t2 order by x, y;
drop table t1;
drop table t2;
replace_result $sys_datatype_expl SYS_DATATYPE;
eval create table t1 (
id bigint primary key without system versioning,
name varchar(128),
salary bigint without system versioning,
sys_trx_start $sys_datatype_expl as row start invisible,
sys_trx_end $sys_datatype_expl as row end invisible,
period for system_time (sys_trx_start, sys_trx_end))
with system versioning;
create table t2 like t1;
insert into t1 values (1, "Jeremy", 3000);
insert into t2 values (1, "Jeremy", 4000);
--disable_cursor_protocol
select sys_trx_start into @tmp1 from t1;
select sys_trx_start into @tmp2 from t2;
--enable_cursor_protocol
update t1, t2 set t1.name= "Jerry", t2.name= "Jerry" where t1.id = t2.id and t1.name = "Jeremy";
select @tmp1 < sys_trx_start as A1, name from t1;
select @tmp2 < sys_trx_start as A2, name from t2;
--disable_cursor_protocol
select sys_trx_start into @tmp1 from t1;
select sys_trx_start into @tmp2 from t2;
--enable_cursor_protocol
update t1, t2 set t1.salary= 2500, t2.salary= 2500 where t1.id = t2.id and t1.name = "Jerry";
select @tmp1 = sys_trx_start as B1, salary from t1;
select @tmp2 = sys_trx_start as B2, salary from t2;
drop table t1;
drop table t2;
--echo # Ensure FTS retains correct history
replace_result $sys_datatype_expl SYS_DATATYPE;
eval create table t1 (
x int, y text, fulltext (y),
row_start $sys_datatype_expl as row start invisible,
row_end $sys_datatype_expl as row end invisible,
period for system_time (row_start, row_end))
with system versioning engine innodb;
insert into t1 values (1, repeat('LONG', 2048));
update t1 set x= x + 1;
select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y;
update t1 set y= 'SHORT';
select x, left(y, 4), length(y), check_row(row_start, row_end) from t1 for system_time all order by x, y;
drop tables t1;
--echo ### Issue tempesta-tech/mariadb#365, bug 7 (duplicate of historical row)
create or replace table t1 (a int primary key, b int)
with system versioning engine myisam;
insert into t1 (a) values (1);
replace t1 values (1,2),(1,3),(2,4);
--echo #
--echo # MDEV-14829 Assertion `0' failed in Protocol::end_statement upon concurrent UPDATE
--echo #
set @old_lock_wait_timeout= @@innodb_lock_wait_timeout;
set @@innodb_lock_wait_timeout= 100000000;
create or replace table t1 (pk int, a char(3), b char(3), primary key(pk))
engine=innodb with system versioning;
insert into t1 (pk) values (1);
connect (con1,localhost,root,,test);
start transaction;
select * from t1 for update;
connection default;
send update t1 set b = 'foo';
connection con1;
let $wait_condition= select count(*) from information_schema.innodb_lock_waits;
source include/wait_condition.inc;
error ER_LOCK_DEADLOCK;
update t1 set a = 'bar';
disconnect con1;
connection default;
reap;
drop table t1;
set @@innodb_lock_wait_timeout= @old_lock_wait_timeout;
--echo #
--echo # MDEV-19406 Assertion on updating view of join with versioned table
--echo #
--disable_warnings
create or replace table t1 (pk int primary key, a date, b int, index(b)) engine=innodb with system versioning;
create or replace table t2 (c int);
create or replace view v as select * from t1 join t2;
insert into t1 (pk) values (1);
update t1 set a= '2012-12-12';
update v set a= '2000-01-01' order by b limit 1; # point of failure
drop view v;
drop table t1, t2;
--enable_warnings
--echo #
--echo # MDEV-20441 ER_CRASHED_ON_USAGE upon update on versioned Aria table
--echo #
create or replace table t1 (a varchar(8))
engine=aria row_format=fixed
with system versioning;
insert into t1 (a) values ('foo');
update t1 set a = 'bar';
drop table t1;
--echo #
--echo # MDEV-21147 Assertion `marked_for_read()' upon UPDATE on versioned table via view
--echo #
create or replace table t1 (
pk int, a char(8), b char(8),
primary key (pk)
) with system versioning;
create or replace view v1 as select * from t1;
insert into t1 values (1, null, 'd') , (2, null, 'i') ;
update v1 set a= null where b = '';
create or replace table t1 (id int, k int, primary key (id)) engine=innodb with system versioning;
insert into t1 values (1,1),(2,2);
create or replace view v1 as select * from t1;
update v1 set id= 2 where k = 0;
create or replace table t1 (a int) with system versioning;
create or replace view v1 as select * from t1;
create or replace procedure sp() update v1 set xx = 1;
--error ER_BAD_FIELD_ERROR
call sp;
--error ER_BAD_FIELD_ERROR
call sp;
# cleanup
drop procedure sp;
drop view v1;
drop table t1;
--echo #
--echo # MDEV-21342 Assertion in set_ok_status() upon spatial field error on system-versioned table
--echo #
create or replace table t1 (f point, key(f)) with system versioning engine=myisam;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
update t1 set f = null where f = 'foo';
# cleanup
drop table t1;
--echo #
--echo # MDEV-22061 InnoDB: Assertion of missing row in sec index row_start upon REPLACE on a system-versioned table
--echo #
create or replace table t1 (
a int,
b int,
row_start bigint(20) unsigned generated always as row start,
row_end bigint(20) unsigned generated always as row end,
unique key (b,row_end),
key (row_start),
period for system_time (row_start,row_end)
) engine=innodb with system versioning;
insert into t1 (a, b) values (1, 2);
replace into t1 (a, b) values (3, 2);
replace into t1 (a, b) values (4, 2);
# cleanup
drop table t1;
--echo #
--echo # MDEV-20661 Virtual fields are not recalculated on system fields value assignment
--echo #
replace_result $sys_datatype_expl SYS_DATATYPE;
eval create table t1 (
a int,
row_start $sys_datatype_expl as row start invisible,
row_end $sys_datatype_expl as row end invisible,
period for system_time (row_start, row_end),
v1 bigint unsigned as (a ^ row_start) unique,
v2 bigint unsigned as (a ^ row_end) unique
) engine=innodb with system versioning;
insert into t1 (a) values (1), (2);
update ignore t1 set a= 3;
delete history from t1;
# cleanup
drop table t1;
--echo #
--echo # MDEV-23446 UPDATE does not insert history row if the row is not changed
--echo #
replace_result $sys_datatype_expl SYS_DATATYPE;
eval create table t1 (
a int,
row_start $sys_datatype_expl as row start invisible,
row_end $sys_datatype_expl as row end invisible,
period for system_time (row_start, row_end)) with system versioning;
insert into t1 values (1);
update t1 set a= 1;
select *, check_row(row_start, row_end) from t1 for system_time all order by row_end;
--echo # multi-update
create or replace table t2 like t1;
create or replace table t3 like t1;
insert into t2 values (1);
insert into t3 values (1);
update t2, t3 set t2.a= 1, t3.a= 1 where t2.a = t3.a;
select *, check_row(row_start, row_end) from t2 for system_time all order by row_end;
select *, check_row(row_start, row_end) from t2 for system_time all order by row_end;
# cleanup
drop tables t1, t2, t3;
--echo #
--echo # MDEV-26778 row_start is not updated in current row for InnoDB
--echo #
create or replace table t1 (x int) with system versioning;
insert t1 values (1);
update t1 set x= 1;
--disable_cursor_protocol
select row_start into @r from t1;
--enable_cursor_protocol
select check_row_ts(row_start, row_end) from t1 for system_time all where row_start = @r;
drop table t1;
--echo #
--echo # MDEV-22973 Assertion in compare_record upon multi-update involving versioned table via view
--echo #
create or replace table t1 (a int, primary key (a)) engine=myisam;
insert into t1 values (0);
create or replace table t2 (pk int, b int, primary key (pk), key(b)) engine=innodb with system versioning;
insert into t2 values (1, 0), (2, 0);
create or replace view v as select a, b from t1, t2;
update v set b= null where a = 0 order by b;
# cleanup
drop view v;
drop table t1, t2;
--echo #
--echo # MDEV-24522 Assertion `inited==NONE' fails upon UPDATE on versioned table with unique blob
--echo #
create table t1 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning;
insert into t1 values (1, 1, 'foo'), (2, 11, 'bar');
update t1 set a = 3 where b <= 9;
update t1 set a = 3 where b <= 10;
# cleanup
drop table t1;
create table t1 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning;
create table t2 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning;
insert into t1 values (1, 1, 'foo'), (2, 11, 'bar');
insert into t2 values (1, 1, 'foo'), (2, 11, 'bar');
update t1 set a = 3 where b <= 9;
update t2 set a = 3 where b <= 9;
update t1, t2 set t1.a = 3, t2.a = 3 where t1.b <= 10 and t2.b <= 10 and t1.b = t2.b;
# cleanup
drop tables t1, t2;
--echo #
--echo # MDEV-23100 ODKU of non-versioning column inserts history row
--echo #
create table t1 (
x int unique,
y int without system versioning
) with system versioning;
insert into t1 (x, y) values ('1', '1');
insert into t1 (x, y) values ('1', '2')
on duplicate key update y = 3;
select x, y, check_row_ts(row_start, row_end) from t1 for system_time all order by row_end;
drop table t1;
--echo #
--echo # MDEV-25644 UPDATE not working properly on transaction precise system versioned table
--echo #
create or replace table t1 (nid int primary key, nstate int, ntype int) engine innodb;
--replace_result $sys_datatype_expl SYS_DATATYPE
eval alter table t1 add
row_start $sys_datatype_expl generated always as row start invisible,
add row_end $sys_datatype_expl generated always as row end invisible,
add period for system_time(row_start, row_end),
add system versioning;
insert into t1 values (1, 1, 1);
select nid, nstate, check_row(row_start, row_end) from t1 for system_time all order by row_start, row_end;
start transaction;
update t1 set nstate= nstate where nid = 1;
select nid, nstate, check_row(row_start, row_end) from t1 for system_time all order by row_start, row_end;
--echo # Bug: ERROR 1761 (23000): Foreign key constraint for table 'xxx', record '1-18446744073709551615' would lead to a duplicate entry in table 'xxx', key 'PRIMARY'
update t1 set nstate= 3 where nid= 1;
# Under one transaction trx_id generates only one history row, that differs from timestamp
select nid, nstate, check_row(row_start, row_end) from t1 for system_time all order by row_start, row_end;
commit;
drop tables t1;
--echo # End of 10.4 tests
source suite/versioning/common_finish.inc;
Reference in a new issue View git blame Copy permalink