unsafe_binlog.inc

# common/binlog/unsafe_binlog.inc
#
# The variable
#     $engine_type       -- storage engine to be tested
# has to be set before sourcing this script.
#
# Notes:
# 1. This test uses at least in case of InnoDB options
#     --transaction-isolation=READ-COMMITTED
#     innodb_lock_timeout = 5
# 2. The comments/expectations refer to InnoDB.
#    They might be not valid for other storage engines.
#
# Last update:
# 2006-08-02 ML test refactored
#               old name was innodb_unsafe_binlog.test
#               main code went into common/binlog/unsafe_binlog.inc
#

#
# Test cases for bug#15650
#      DELETE with LEFT JOIN crashes server with innodb_locks_unsafe_for_binlog
#

--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
--enable_warnings
eval create table t1 (id int not null, f_id int not null, f int not null,
primary key(f_id, id)) engine = $engine_type;
eval create table t2 (id int not null,s_id int not null,s varchar(200),
primary key(id)) engine = $engine_type;
INSERT INTO t1 VALUES (8, 1, 3);
INSERT INTO t1 VALUES (1, 2, 1);
INSERT INTO t2 VALUES (1, 0, '');
INSERT INTO t2 VALUES (8, 1, '');
commit;
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
WHERE mm.id IS NULL;
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
where mm.id is null lock in share mode;
drop table t1,t2;

#
# Test case for unlock row bug where unlock releases all locks granted for
# a row. Only the latest lock should be released.
#

connect (a,localhost,root,,);
connect (b,localhost,root,,);
connection a;
eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
commit;
set autocommit = 0;
select * from t1 lock in share mode;
update t1 set b = 5 where b = 1;
connection b;
set autocommit = 0;
#
# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
#
--error ER_LOCK_WAIT_TIMEOUT
select * from t1 where a = 2 and b = 2 for update;
connection a;
commit;
connection b;
commit;
drop table t1;
connection default;
disconnect a;
disconnect b;

#
# unlock row test
#

connect (a,localhost,root,,);
connect (b,localhost,root,,);
connection a;
eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
commit;
set autocommit = 0;
set transaction isolation level read committed;
start transaction;
update t1 set b = 5 where b = 1;
connection b;
set transaction isolation level read committed;
start transaction;
#
# X-lock to record (7,3) should be released in a update
#
select * from t1 where a = 7 and b = 3 for update;
commit;
connection a;
commit;
drop table t1;
connection default;
disconnect a;
disconnect b;


#
# Consistent read should be used in following selects
#
# 1) INSERT INTO ... SELECT
# 2) UPDATE ... = ( SELECT ...)
# 3) CREATE ... SELECT

connect (a,localhost,root,,);
connect (b,localhost,root,,);
connection a;
eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
insert into t1 values (1,2),(5,3),(4,2);
eval create table t2(d int not null, e int, primary key(d)) engine = $engine_type;
insert into t2 values (8,6),(12,1),(3,1);
commit;
set transaction isolation level read committed;
start transaction;
select * from t2 for update;

--echo # Connection b:
connection b;
set transaction isolation level read committed;
start transaction;
insert into t1 select * from t2;
update t1 set b = (select e from t2 where a = d);
eval create table t3(d int not null, e int, primary key(d)) engine = $engine_type;
select * from t2;
commit;

connection a;
commit;
connection default;
disconnect a;
disconnect b;
drop table t1, t2, t3;

#
# Consistent read should not be used if
#
# (a) isolation level is serializable OR
# (b) select ... lock in share mode OR
# (c) select ... for update
#
# in following queries:
#
# 1) INSERT INTO ... SELECT
# 2) UPDATE ... = ( SELECT ...)
# 3) CREATE ... SELECT

connect (a,localhost,root,,);
connect (b,localhost,root,,);
connect (c,localhost,root,,);
connect (d,localhost,root,,);
eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type;
connect (e,localhost,root,,);
connect (f,localhost,root,,);
connect (g,localhost,root,,);
eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type;
connect (h,localhost,root,,);
connect (i,localhost,root,,);
connect (j,localhost,root,,);
eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type;
connection a;
eval create table t1(a int not null, b int, primary key(a)) engine = $engine_type;
insert into t1 values (1,2),(5,3),(4,2);
eval create table t2(a int not null, b int, primary key(a)) engine = $engine_type;
insert into t2 values (8,6),(12,1),(3,1);
eval create table t3(d int not null, b int, primary key(d)) engine = $engine_type;
insert into t3 values (8,6),(12,1),(3,1);
eval create table t5(a int not null, b int, primary key(a)) engine = $engine_type;
insert into t5 values (1,2),(5,3),(4,2);
eval create table t6(d int not null, e int, primary key(d)) engine = $engine_type;
insert into t6 values (8,6),(12,1),(3,1);
eval create table t8(a int not null, b int, primary key(a)) engine = $engine_type;
insert into t8 values (1,2),(5,3),(4,2);
eval create table t9(d int not null, e int, primary key(d)) engine = $engine_type;
insert into t9 values (8,6),(12,1),(3,1);
commit;
set autocommit = 0;
select * from t2 for update;
connection b;
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--send
insert into t1 select * from t2;
connection c;
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--send
update t3 set b = (select b from t2 where a = d);
connection d;
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--send
create table t4(a int not null, b int, primary key(a)) select * from t2;
connection e;
set autocommit = 0;
--send
insert into t5 (select * from t2 lock in share mode);
connection f;
set autocommit = 0;
--send
update t6 set e = (select b from t2 where a = d lock in share mode);
connection g;
set autocommit = 0;
--send
create table t7(a int not null, b int, primary key(a)) select * from t2 lock in share mode;
connection h;
set autocommit = 0;
--send
insert into t8 (select * from t2 for update);
connection i;
set autocommit = 0;
--send
update t9 set e = (select b from t2 where a = d for update);
connection j;
set autocommit = 0;
--send
create table t10(a int not null, b int, primary key(a)) select * from t2 for update;

connection b;
--error ER_LOCK_WAIT_TIMEOUT
reap;

connection c;
--error ER_LOCK_WAIT_TIMEOUT
reap;

connection d;
--error ER_LOCK_WAIT_TIMEOUT
reap;

connection e;
--error ER_LOCK_WAIT_TIMEOUT
reap;

connection f;
--error ER_LOCK_WAIT_TIMEOUT
reap;

connection g;
--error ER_LOCK_WAIT_TIMEOUT
reap;

connection h;
--error ER_LOCK_WAIT_TIMEOUT
reap;

connection i;
--error ER_LOCK_WAIT_TIMEOUT
reap;

connection j;
--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
reap;

connection a;
commit;

connection default;
disconnect a;
disconnect b;
disconnect c;
disconnect d;
disconnect e;
disconnect f;
disconnect g;
disconnect h;
disconnect i;
disconnect j;
drop table t1, t2, t3, t5, t6, t8, t9;