~drizzle-trunk/drizzle/development

-- source include/have_innodb.inc

-- source include/have_binlog_format_mixed_or_statement.inc

-- source include/not_embedded.inc

###

### bug#22725 : incorrect killed error in binlogged query

###

connect (con1, localhost, root,,);

connect (con2, localhost, root,,);

create table t1 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;

create table t2 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=MyISAM;

create table t3 (a int auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB;

#

# effective test for bug#22725

#

connection con1;

select get_lock("a", 20);

connection con2;

let $ID= `select connection_id()`;

reset master;

send insert into t2 values (null, null), (null, get_lock("a", 10));

connection con1;

disable_abort_on_error;

disable_query_log;

disable_result_log;

eval kill query $ID;

connection con2;

--error 0,ER_QUERY_INTERRUPTED

reap;

let $rows= `select count(*) from t2  /* must be 2 or 0 */`;

--exec $DRIZZLE_BINLOG --force-if-open --start-position=135 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog

--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR

eval select

(@a:=load_file("$MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog"))

is not null;

--replace_result $DRIZZLE_TEST_DIR DRIZZLE_TEST_DIR

let $error_code= `select @a like "%#%error_code=0%" /* must return 1 or 0*/`;

let $insert_binlogged= `select @a like "%insert into%" /* must return 1 or 0 */`;

eval set @result= $rows- $error_code - $insert_binlogged;

enable_abort_on_error;

enable_query_log;

enable_result_log;

select @result /* must be zero either way */;

--remove_file $MYSQLTEST_VARDIR/tmp/kill_query_calling_sp.binlog

connection con1;

select RELEASE_LOCK("a");

#

# bug#27571 asynchronous setting mysql_`query`::error and Query_log_e::error_code

#

# checking that killing inside of select loops is safe as before

# killing after the loop can be only simulated - another test

delete from t1;

delete from t2;

insert into t1 values (1,1),(2,2);

#

# simple update

#

connection con1;

begin; update t1 set b=11 where a=2;

connection con2;

let $ID= `select connection_id()`;

begin;

send update t1 set b=b+10;

connection con1;

--replace_result $ID ID

eval kill query $ID;

rollback;

# Bug #32148  	killi query may be ineffective 

# forced to comment out the test's outcome 

# and mask out ineffective ER_QUERY_INTERRUPTED

# todo1: revert back upon fixing bug#32148

# todo2: the tests need refining in that

#        killing should wait till the victim requested

#        its lock (wait_condition available in 5.1 tests)

connection con2;

--error 0,ER_QUERY_INTERRUPTED

reap;

rollback;

select * from t1  order by a /* must be the same as before (1,1),(2,2) */;

#

# multi update

# commented out as Bug #31807 multi-update,delete killing does not report with ER_QUERY_INTERRUPTED

# in the way

#

# connection con1;

# begin; update t1 set b=b+10;

# connection con2;

# send update t1 as t_1,t1 as t_2 set t_1.b=11 where t_2.a=2;

# connection con1;

# --replace_result $ID ID

# eval kill query $ID;

# rollback;

# disable_abort_on_error;

# connection con2;

# --error HY000,ER_QUERY_INTERRUPTED

# reap;

# select * from t1 /* must be the same as before (1,1),(2,2) */;

# enable_abort_on_error;

#

# simple delete

#

connection con1;

begin; delete from t1 where a=2;

connection con2;

let $ID= `select connection_id()`;

begin;

send delete from t1 where a=2;

connection con1;

--replace_result $ID ID

eval kill query $ID;

rollback;

connection con2;

--error 0,ER_QUERY_INTERRUPTED

reap;

rollback;

# todo1,2 above

select * from t1 order by a /* must be the same as before (1,1),(2,2) */;

#

# multi delete

# the same as for multi-update

#

# connection con1;

# begin; delete from t1 where a=2;

# connection con2;

# send delete t1 from t1 where t1.a=2;

# connection con1;

# --replace_result $ID ID

# eval kill query $ID;

# rollback;

# connection con2;

# --error 0,ER_QUERY_INTERRUPTED

# reap;

# select * from t1 /* must be the same as before (1,1),(2,2) */;

#

# insert select

#

connection con1;

--disable_warnings

drop table if exists t4;

--enable_warnings

create table t4 (a int, b int) engine=innodb;

insert into t4 values (3, 3);

begin; insert into t1 values (3, 3);

connection con2;

let $ID= `select connection_id()`;

begin;

send insert into t1 select * from t4 for update;

connection con1;

--replace_result $ID ID

eval kill query $ID;

rollback;

connection con2;

--error 0,ER_QUERY_INTERRUPTED

reap;

# todo 1,2 above

rollback;

select * from t1 /* must be the same as before (1,1),(2,2) */;

drop table t4; # cleanup for the sub-case

###

## non-ta table case: killing must be recorded in binlog

###

create table t4 (a int, b int) ENGINE=MyISAM /* for killing update and delete */;

delimiter |;

create function bug27563(n int) 

RETURNS int(11)

DETERMINISTIC

begin

  if @b > 0 then

     select get_lock("a", 20)  into @a;

  else 

     set @b= 1;

  end if;

  return n;

end|

delimiter ;|

#

# update

#

delete from t4;

insert into t4 values (1,1), (1,1);

reset master;

connection con1;

select get_lock("a", 20);

connection con2;

let $ID= `select connection_id()`;

set @b= 0;

send update t4 set b=b + bug27563(b);

connection con1;

let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';

source include/wait_condition.inc;

select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';

--replace_result $ID ID

eval kill query $ID;

connection con2;

--error ER_QUERY_INTERRUPTED

reap;

select * from t4 order by b /* must be (1,1), (1,2) */;

select @b  /* must be 1 at the end of a stmt calling bug27563() */;

--echo must have the update query event more to FD

source include/show_binlog_events.inc;

# a proof the query is binlogged with an error

--exec $DRIZZLE_BINLOG --force-if-open --start-position=106 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog

--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR

eval select

(@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))

is not null;

--replace_result $DRIZZLE_TEST_DIR DRIZZLE_TEST_DIR

let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;

eval select $error_code /* must return 0 to mean the killed query is in */;

# cleanup for the sub-case

connection con1;

select RELEASE_LOCK("a");

--remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog

#

# delete

#

delete from t4;

insert into t4 values (1,1), (2,2);

reset master;

connection con1;

select get_lock("a", 20);

connection con2;

let $ID= `select connection_id()`;

set @b= 0;

send delete from t4 where b=bug27563(1) or b=bug27563(2);

connection con1;

let $wait_condition= select count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';

source include/wait_condition.inc;

select count(*) FROM INFORMATION_SCHEMA.PROCESSLIST where state='User lock';

--replace_result $ID ID

eval kill query $ID;

connection con2;

--error ER_QUERY_INTERRUPTED

reap;

select count(*) from t4 /* must be 1 */;

select @b  /* must be 1 at the end of a stmt calling bug27563() */;

--echo must have the delete query event more to FD

source include/show_binlog_events.inc;

# a proof the query is binlogged with an error

--exec $DRIZZLE_BINLOG --force-if-open --start-position=106 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog

--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR

eval select

(@a:=load_file("$MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog"))

is not null;

--replace_result $DRIZZLE_TEST_DIR DRIZZLE_TEST_DIR

let $error_code= `select @a like "%#%error_code=0%" /* must return 0*/`;

eval select $error_code /* must return 0 to mean the killed query is in */;

# cleanup for the sub-case

connection con1;

select RELEASE_LOCK("a");

--remove_file $MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog

drop table t4;

#

# load data - see simulation tests

#

# bug#27571 cleanup

drop function bug27563;

#

# common cleanup 

#

drop table t1,t2,t3;

--echo end of the tests