~drizzle-trunk/drizzle/development

## Bug#12713 (Error in a stored function called from a SELECT doesn't cause

##    ROLLBACK of statem)

##

## Pre-Requisites :

## - $engine_type should be set

##

set sql_mode=no_engine_substitution;

eval set storage_engine = $engine_type;

set autocommit=1;

--disable_warnings

drop table if exists t1;

drop table if exists t2;

drop table if exists t3;

drop function if exists f2;

drop procedure if exists bug12713_call;

drop procedure if exists bug12713_dump_spvars;

drop procedure if exists dummy;

--enable_warnings

create table t1 (a int);

create table t2 (a int unique);

create table t3 (a int);

# a workaround for Bug#32633: Can not create any routine if

# SQL_MODE=no_engine_substitution

set sql_mode=default;

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

insert into t3 (a) values (1), (2);

delimiter |;

## Cause a failure every time

create function f2(x int) returns int

begin

  insert into t2 (a) values (x);

  insert into t2 (a) values (x);

  return x;

end|

delimiter ;|

set autocommit=0;

flush status;

##============================================================================

## Design notes

##

## In each case, statement rollback is expected.

## for transactional engines, the rollback should be properly executed

## for non transactional engines, the rollback may cause warnings.

##

## The test pattern is as follows

## - insert 1000+N

## - statement with a side effect, that fails to insert N twice

## - a statement rollback is expected (expecting 1 row 1000+N only) in t2

## - a rollback is performed

## - expecting a clean table t2.

##============================================================================

insert into t2 (a) values (1001);

--error ER_DUP_ENTRY

insert into t1 (a) values (f2(1));

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1002);

--error ER_DUP_ENTRY

insert into t3 (a) select f2(2) from t1;

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1003);

--error ER_DUP_ENTRY

update t1 set a= a + f2(3);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1004);

--error ER_DUP_ENTRY

update t1, t3 set t1.a = 0, t3.a = 0 where (f2(4) = 4) and (t1.a = t3.a);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1005);

--error ER_DUP_ENTRY

delete from t1 where (a = f2(5));

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1006);

--error ER_DUP_ENTRY

delete from t1, t3 using t1, t3 where (f2(6) = 6) ;

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1007);

--error ER_DUP_ENTRY

replace t1 values (f2(7));

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1008);

--error ER_DUP_ENTRY

replace into t3 (a) select f2(8) from t1;

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1009);

--error ER_DUP_ENTRY

select f2(9) from t1 ;

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1010);

--error ER_DUP_ENTRY

show databases where (f2(10) = 10);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1011);

--error ER_DUP_ENTRY

show tables where (f2(11) = 11);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1012);

--error ER_DUP_ENTRY

show triggers where (f2(12) = 12);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1013);

--error ER_DUP_ENTRY

show table status where (f2(13) = 13);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1014);

--error ER_DUP_ENTRY

show open tables where (f2(14) = 14);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1015);

--error ER_DUP_ENTRY

show columns in mysql.proc where (f2(15) = 15);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1016);

--error ER_DUP_ENTRY

show status where (f2(16) = 16);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1017);

--error ER_DUP_ENTRY

show variables where (f2(17) = 17);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1018);

--error ER_DUP_ENTRY

show charset where (f2(18) = 18);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1019);

--error ER_DUP_ENTRY

show collation where (f2(19) = 19);

select * from t2;

rollback;

select * from t2;

--echo # We need at least one procedure to make sure the WHERE clause is

--echo # evaluated

create procedure dummy() begin end;

insert into t2 (a) values (1020);

--error ER_DUP_ENTRY

show procedure status where (f2(20) = 20);

select * from t2;

rollback;

select * from t2;

drop procedure dummy;

insert into t2 (a) values (1021);

--error ER_DUP_ENTRY

show function status where (f2(21) = 21);

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1022);

prepare stmt from "insert into t1 (a) values (f2(22))";

--error ER_DUP_ENTRY

execute stmt;

select * from t2;

rollback;

select * from t2;

insert into t2 (a) values (1023);

do (f2(23));

select * from t2;

rollback;

select * from t2;

## Please note :

## This will insert a record 1024 in t1 (statement commit)

## This will insert a record 24 in t1 (statement commit)

## then will rollback the second insert only (24) (statement rollback)

## then will rollback the complete transaction (transaction rollback)

delimiter |;

create procedure bug12713_call ()

begin

  insert into t2 (a) values (24);

  insert into t2 (a) values (24);

end|

delimiter ;|

insert into t2 (a) values (1024);

--error ER_DUP_ENTRY

call bug12713_call();

select * from t2;

rollback;

select * from t2;

--echo =======================================================================

--echo Testing select_to_file

--echo =======================================================================

insert into t2 (a) values (1025);

--replace_result $MYSQLTEST_VARDIR ..

--error ER_DUP_ENTRY

eval select f2(25) into outfile "$MYSQLTEST_VARDIR/tmp/dml.out" from t1;

select * from t2;

rollback;

select * from t2;

--remove_file $MYSQLTEST_VARDIR/tmp/dml.out

insert into t2 (a) values (1026);

--replace_result $MYSQLTEST_VARDIR ..

--error ER_DUP_ENTRY

eval load data infile "../std_data_ln/words.dat" into table t1 (a) set a:=f2(26);

select * from t2;

rollback;

select * from t2;

--echo =======================================================================

--echo Testing select_dumpvar

--echo =======================================================================

insert into t2 (a) values (1027);

--error ER_DUP_ENTRY

select f2(27) into @foo;

select * from t2;

rollback;

select * from t2;

--echo =======================================================================

--echo Testing Select_fetch_into_spvars 

--echo =======================================================================

delimiter |;

create procedure bug12713_dump_spvars ()

begin

  declare foo int;

  declare continue handler for sqlexception

  begin

    select "Exception trapped";

  end;

  select f2(28) into foo;

  select * from t2;

end|

delimiter ;|

insert into t2 (a) values (1028);

call bug12713_dump_spvars ();

rollback;

select * from t2;

--echo =======================================================================

--echo Cleanup

--echo =======================================================================

set autocommit=default;

drop table t1;

drop table t2;

drop table t3;

drop function f2;

drop procedure bug12713_call;

drop procedure bug12713_dump_spvars;

--echo #

--echo # Bug#12713 Error in a stored function called from a SELECT doesn't

--echo # cause ROLLBACK of statem

--echo #

--echo # Verify that two-phase commit is not issued for read-only

--echo # transactions.

--echo #

--echo # Verify that two-phase commit is issued for read-write transactions,

--echo # even if the change is done inside a stored function called from

--echo # SELECT or SHOW statement.

--echo #

set autocommit=0;

--disable_warnings

drop table if exists t1;

drop table if exists t2;

drop function if exists f1;

drop procedure if exists p_verify_status_increment;

--enable_warnings

# Save binlog_format in a user variable. References to system

# variables are "unsafe", meaning they are written as rows instead of

# as statements to the binlog, if the loggging mode is 'mixed'.  But

# we don't want p_verify_status_increment to affect the logging mode.

# Hence, we save binlog_format in a user variable (which is not

# unsafe) and use that inside p_verify_status_increment.

set @binlog_format=@@global.binlog_format;

set sql_mode=no_engine_substitution;

create table t1 (a int unique); 

create table t2 (a int) engine=myisam;

set sql_mode=default;

--echo #

--echo # An auxiliary procedure to track Handler_prepare and Handler_commit

--echo # statistics.

--echo #

delimiter |;

create procedure

p_verify_status_increment(commit_inc_mixed int, prepare_inc_mixed int,

                          commit_inc_row int, prepare_inc_row int)

begin

  declare commit_inc int;

  declare prepare_inc int;

  declare old_commit_count int default ifnull(@commit_count, 0);

  declare old_prepare_count int default ifnull(@prepare_count, 0);

  declare c_res int;

# Use a cursor to have just one access to I_S instead of 2, it is very slow

# and amounts for over 90% of test CPU time

  declare c cursor for

     select variable_value

     from information_schema.session_status

     where variable_name='Handler_commit' or variable_name='Handler_prepare'

     order by variable_name;

  if @binlog_format = 'ROW' then

    set commit_inc= commit_inc_row;

    set prepare_inc= prepare_inc_row;

  else

    set commit_inc= commit_inc_mixed;

    set prepare_inc= prepare_inc_mixed;

  end if;

  open c;

  fetch c into c_res;

  set @commit_count=c_res;

  fetch c into c_res;

  set @prepare_count=c_res;

  close c;

  if old_commit_count + commit_inc <> @commit_count then

    select concat("Expected commit increment: ", commit_inc,

        " actual: ", @commit_count - old_commit_count)

    as 'ERROR';

  elseif old_prepare_count + prepare_inc <> @prepare_count then

    select concat("Expected prepare increment: ", prepare_inc,

        " actual: ", @prepare_count - old_prepare_count)

    as 'ERROR';

  else

    select '' as 'SUCCESS';

  end if;

end|

delimiter ;|

--echo # Reset Handler_commit and Handler_prepare counters

flush status;

--echo #

--echo # 1. Read-only statement: SELECT

--echo #

select * from t1;

call p_verify_status_increment(1, 0, 1, 0);

commit;

call p_verify_status_increment(1, 0, 1, 0);

--echo # 2. Read-write statement: INSERT, insert 1 row. 

--echo #

insert into t1 (a) values (1);

call p_verify_status_increment(2, 2, 2, 2);

commit;

call p_verify_status_increment(2, 2, 2, 2);

--echo # 3. Read-write statement: UPDATE, update 1 row. 

--echo #

update t1 set a=2;

call p_verify_status_increment(2, 2, 2, 2);

commit;

call p_verify_status_increment(2, 2, 2, 2);

--echo # 4. Read-write statement: UPDATE, update 0 rows, 1 row matches WHERE 

--echo #

update t1 set a=2;

call p_verify_status_increment(2, 2, 1, 0);

commit;

call p_verify_status_increment(2, 2, 1, 0);

--echo # 5. Read-write statement: UPDATE, update 0 rows, 0 rows match WHERE 

--echo #

--echo # In mixed replication mode, there is a read-only transaction

--echo # in InnoDB and also the statement is written to the binary log.

--echo # So we have two commits but no 2pc, since the first engine's

--echo # transaction is read-only.

--echo # In the row level replication mode, we only have the read-only

--echo # transaction in InnoDB and nothing is written to the binary log.

--echo #

update t1 set a=3 where a=1;

call p_verify_status_increment(2, 0, 1, 0);

commit;

call p_verify_status_increment(2, 0, 1, 0);

--echo # 6. Read-write statement: DELETE, delete 0 rows. 

--echo #

delete from t1 where a=1;

call p_verify_status_increment(2, 0, 1, 0);

commit;

call p_verify_status_increment(2, 0, 1, 0);

--echo # 7. Read-write statement: DELETE, delete 1 row. 

--echo #

delete from t1 where a=2;

call p_verify_status_increment(2, 2, 2, 2);

commit;

call p_verify_status_increment(2, 2, 2, 2);

--echo # 8. Read-write statement: unqualified DELETE

--echo #

--echo # In statement or mixed replication mode, we call

--echo # handler::ha_delete_all_rows() and write statement text

--echo # to the binary log. This results in two read-write transactions.

--echo # In row level replication mode, we do not call

--echo # handler::ha_delete_all_rows(), but delete rows one by one.

--echo # Since there are no rows, nothing is written to the binary log.

--echo # Thus we have just one read-only transaction in InnoDB.

delete from t1;

call p_verify_status_increment(2, 2, 1, 0);

commit;

call p_verify_status_increment(2, 2, 1, 0);

--echo # 9. Read-write statement: REPLACE, change 1 row. 

--echo #

replace t1 set a=1;

call p_verify_status_increment(2, 2, 2, 2);

commit;

call p_verify_status_increment(2, 2, 2, 2);

--echo # 10. Read-write statement: REPLACE, change 0 rows. 

--echo #

replace t1 set a=1;

call p_verify_status_increment(2, 2, 1, 0);

commit;

call p_verify_status_increment(2, 2, 1, 0);

--echo # 11. Read-write statement: IODKU, change 1 row. 

--echo #

insert t1 set a=1 on duplicate key update a=a+1;

call p_verify_status_increment(2, 2, 2, 2);

select * from t1;

call p_verify_status_increment(1, 0, 1, 0);

commit;

call p_verify_status_increment(2, 2, 2, 2);

--echo # 12. Read-write statement: IODKU, change 0 rows. 

--echo #

insert t1 set a=2 on duplicate key update a=2;

call p_verify_status_increment(1, 0, 1, 0);

commit;

call p_verify_status_increment(1, 0, 1, 0);

--echo # 13. Read-write statement: INSERT IGNORE, change 0 rows. 

--echo #

insert ignore t1 set a=2;

call p_verify_status_increment(1, 0, 1, 0);

commit;

call p_verify_status_increment(1, 0, 1, 0);

--echo # 14. Read-write statement: INSERT IGNORE, change 1 row. 

--echo #

insert ignore t1 set a=1;

call p_verify_status_increment(2, 2, 2, 2);

commit;

call p_verify_status_increment(2, 2, 2, 2);

--echo # 15. Read-write statement: UPDATE IGNORE, change 0 rows. 

--echo #

update ignore t1 set a=2 where a=1;

call p_verify_status_increment(2, 2, 1, 0);

commit;

call p_verify_status_increment(2, 2, 1, 0);

--echo #

--echo # Create a stored function that modifies a

--echo # non-transactional table. Demonstrate that changes in

--echo # non-transactional tables do not affect the two phase commit

--echo # algorithm.

--echo #

delimiter |;

create function f1() returns int

begin

  insert t2 set a=2;

  return 2;

end|

delimiter ;|

call p_verify_status_increment(0, 0, 0, 0);

--echo # 16. A function changes non-trans-table.

--echo #

--echo # For row-based logging, there is an extra commit for the

--echo # non-transactional changes saved in the transaction cache to

--echo # the binary log. 

--echo #

select f1();

call p_verify_status_increment(0, 0, 1, 0);

commit;

call p_verify_status_increment(0, 0, 1, 0);

--echo # 17. Read-only statement, a function changes non-trans-table.

--echo #

--echo # For row-based logging, there is an extra commit for the

--echo # non-transactional changes saved in the transaction cache to

--echo # the binary log. 

--echo #

select f1() from t1;

call p_verify_status_increment(1, 0, 2, 0);

commit;

call p_verify_status_increment(1, 0, 2, 0);

--echo # 18. Read-write statement: UPDATE, change 0 (transactional) rows. 

--echo #

select count(*) from t2;

update t1 set a=2 where a=f1()+10;

select count(*) from t2;

call p_verify_status_increment(2, 0, 2, 0);

commit;

call p_verify_status_increment(2, 0, 2, 0);

--echo #

--echo # Replace the non-transactional table with a temporary

--echo # transactional table. Demonstrate that a change to a temporary

--echo # transactional table does not provoke 2-phase commit, although

--echo # does trigger a commit and a binlog write (in statement mode).

--echo #

drop table t2;

set sql_mode=no_engine_substitution;

create temporary table t2 (a int);

call p_verify_status_increment(0, 0, 0, 0);

set sql_mode=default;

--echo # 19. A function changes temp-trans-table.

--echo #

select f1();

--echo # Two commits because a binary log record is written

call p_verify_status_increment(2, 0, 1, 0);

commit;

call p_verify_status_increment(2, 0, 1, 0);

--echo # 20. Read-only statement, a function changes non-trans-table.

--echo #

select f1() from t1;

--echo # Two commits because a binary log record is written

call p_verify_status_increment(2, 0, 1, 0);

commit;

call p_verify_status_increment(2, 0, 1, 0);

--echo # 21. Read-write statement: UPDATE, change 0 (transactional) rows. 

--echo #

update t1 set a=2 where a=f1()+10;

call p_verify_status_increment(2, 0, 1, 0);

commit;

call p_verify_status_increment(2, 0, 1, 0);

--echo # 22. DDL: ALTER TEMPORARY TABLE, should not cause a 2pc

--echo #

alter table t2 add column b int default 5;

--echo # A commit is done internally by ALTER. 

call p_verify_status_increment(2, 0, 2, 0);

commit;

--echo # There is nothing left to commit

call p_verify_status_increment(0, 0, 0, 0);

--echo # 23. DDL: RENAME TEMPORARY TABLE, does not start a transaction

--echo

--echo # No test because of Bug#8729 "rename table fails on temporary table"

--echo # 24. DDL: TRUNCATE TEMPORARY TABLE, does not start a transaction

--echo

truncate table t2;

call p_verify_status_increment(2, 0, 2, 0);

commit;

--echo # There is nothing left to commit

call p_verify_status_increment(0, 0, 0, 0);

--echo # 25. Read-write statement: unqualified DELETE 

--echo

delete from t2;

call p_verify_status_increment(2, 0, 1, 0);

commit;

--echo # There is nothing left to commit

call p_verify_status_increment(2, 0, 1, 0);

--echo # 25. DDL: DROP TEMPORARY TABLE, does not start a transaction

--echo #

drop temporary table t2;

call p_verify_status_increment(0, 0, 0, 0);

commit;

call p_verify_status_increment(0, 0, 0, 0);

--echo # 26. Verify that SET AUTOCOMMIT issues an implicit commit

--echo #

insert t1 set a=3;

call p_verify_status_increment(2, 2, 2, 2);

set autocommit=1;

call p_verify_status_increment(2, 2, 2, 2);

rollback;

select a from t1 where a=3;

call p_verify_status_increment(1, 0, 1, 0);

delete from t1 where a=3;

call p_verify_status_increment(2, 2, 2, 2);

commit;