~drizzle-trunk/drizzle/development

set sql_mode=no_engine_substitution;

set storage_engine = InnoDB;

set autocommit=1;

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;

create table t1 (a int);

create table t2 (a int unique);

create table t3 (a int);

set sql_mode=default;

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

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

create function f2(x int) returns int

begin

insert into t2 (a) values (x);

insert into t2 (a) values (x);

return x;

end|

set autocommit=0;

flush status;

insert into t2 (a) values (1001);

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

ERROR 23000: Duplicate entry '1' for key 'a'

select * from t2;

a

1001

rollback;

select * from t2;

a

insert into t2 (a) values (1002);

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

ERROR 23000: Duplicate entry '2' for key 'a'

select * from t2;

a

1002

rollback;

select * from t2;

a

insert into t2 (a) values (1003);

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

ERROR 23000: Duplicate entry '3' for key 'a'

select * from t2;

a

1003

rollback;

select * from t2;

a

insert into t2 (a) values (1004);

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

ERROR 23000: Duplicate entry '4' for key 'a'

select * from t2;

a

1004

rollback;

select * from t2;

a

insert into t2 (a) values (1005);

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

ERROR 23000: Duplicate entry '5' for key 'a'

select * from t2;

a

1005

rollback;

select * from t2;

a

insert into t2 (a) values (1006);

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

ERROR 23000: Duplicate entry '6' for key 'a'

select * from t2;

a

1006

rollback;

select * from t2;

a

insert into t2 (a) values (1007);

replace t1 values (f2(7));

ERROR 23000: Duplicate entry '7' for key 'a'

select * from t2;

a

1007

rollback;

select * from t2;

a

insert into t2 (a) values (1008);

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

ERROR 23000: Duplicate entry '8' for key 'a'

select * from t2;

a

1008

rollback;

select * from t2;

a

insert into t2 (a) values (1009);

select f2(9) from t1 ;

ERROR 23000: Duplicate entry '9' for key 'a'

select * from t2;

a

1009

rollback;

select * from t2;

a

insert into t2 (a) values (1010);

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

ERROR 23000: Duplicate entry '10' for key 'a'

select * from t2;

a

1010

rollback;

select * from t2;

a

insert into t2 (a) values (1011);

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

ERROR 23000: Duplicate entry '11' for key 'a'

select * from t2;

a

1011

rollback;

select * from t2;

a

insert into t2 (a) values (1012);

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

ERROR 23000: Duplicate entry '12' for key 'a'

select * from t2;

a

1012

rollback;

select * from t2;

a

insert into t2 (a) values (1013);

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

ERROR 23000: Duplicate entry '13' for key 'a'

select * from t2;

a

1013

rollback;

select * from t2;

a

insert into t2 (a) values (1014);

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

ERROR 23000: Duplicate entry '14' for key 'a'

select * from t2;

a

1014

rollback;

select * from t2;

a

insert into t2 (a) values (1015);

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

ERROR 23000: Duplicate entry '15' for key 'a'

select * from t2;

a

1015

rollback;

select * from t2;

a

insert into t2 (a) values (1016);

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

ERROR 23000: Duplicate entry '16' for key 'a'

select * from t2;

a

1016

rollback;

select * from t2;

a

insert into t2 (a) values (1017);

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

ERROR 23000: Duplicate entry '17' for key 'a'

select * from t2;

a

1017

rollback;

select * from t2;

a

insert into t2 (a) values (1018);

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

ERROR 23000: Duplicate entry '18' for key 'a'

select * from t2;

a

1018

rollback;

select * from t2;

a

insert into t2 (a) values (1019);

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

ERROR 23000: Duplicate entry '19' for key 'a'

select * from t2;

a

1019

rollback;

select * from t2;

a

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

# evaluated

create procedure dummy() begin end;

insert into t2 (a) values (1020);

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

ERROR 23000: Duplicate entry '20' for key 'a'

select * from t2;

a

1020

rollback;

select * from t2;

a

drop procedure dummy;

insert into t2 (a) values (1021);

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

ERROR 23000: Duplicate entry '21' for key 'a'

select * from t2;

a

1021

rollback;

select * from t2;

a

insert into t2 (a) values (1022);

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

execute stmt;

ERROR 23000: Duplicate entry '22' for key 'a'

select * from t2;

a

1022

rollback;

select * from t2;

a

insert into t2 (a) values (1023);

do (f2(23));

Warnings:

Error	1062	Duplicate entry '23' for key 'a'

select * from t2;

a

1023

rollback;

select * from t2;

a

create procedure bug12713_call ()

begin

insert into t2 (a) values (24);

insert into t2 (a) values (24);

end|

insert into t2 (a) values (1024);

call bug12713_call();

ERROR 23000: Duplicate entry '24' for key 'a'

select * from t2;

a

24

1024

rollback;

select * from t2;

a

=======================================================================

Testing select_to_file

=======================================================================

insert into t2 (a) values (1025);

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

ERROR 23000: Duplicate entry '25' for key 'a'

select * from t2;

a

1025

rollback;

select * from t2;

a

insert into t2 (a) values (1026);

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

ERROR 23000: Duplicate entry '26' for key 'a'

select * from t2;

a

1026

rollback;

select * from t2;

a

=======================================================================

Testing select_dumpvar

=======================================================================

insert into t2 (a) values (1027);

select f2(27) into @foo;

ERROR 23000: Duplicate entry '27' for key 'a'

select * from t2;

a

1027

rollback;

select * from t2;

a

=======================================================================

Testing Select_fetch_into_spvars 

=======================================================================

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|

insert into t2 (a) values (1028);

call bug12713_dump_spvars ();

Exception trapped

Exception trapped

a

1028

rollback;

select * from t2;

a

=======================================================================

Cleanup

=======================================================================

set autocommit=default;

drop table t1;

drop table t2;

drop table t3;

drop function f2;

drop procedure bug12713_call;

drop procedure bug12713_dump_spvars;

#

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

# cause ROLLBACK of statem

#

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

# transactions.

#

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

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

# SELECT or SHOW statement.

#

set autocommit=0;

drop table if exists t1;

drop table if exists t2;

drop function if exists f1;

drop procedure if exists 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;

#

# An auxiliary procedure to track Handler_prepare and Handler_commit

# statistics.

#

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|

# Reset Handler_commit and Handler_prepare counters

flush status;

#

# 1. Read-only statement: SELECT

#

select * from t1;

a

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

SUCCESS

commit;

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

SUCCESS

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

#

insert into t1 (a) values (1);

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

SUCCESS

commit;

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

SUCCESS

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

#

update t1 set a=2;

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

SUCCESS

commit;

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

SUCCESS

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

#

update t1 set a=2;

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

SUCCESS

commit;

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

SUCCESS

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

#

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

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

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

# transaction is read-only.

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

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

#

update t1 set a=3 where a=1;

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

SUCCESS

commit;

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

SUCCESS

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

#

delete from t1 where a=1;

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

SUCCESS

commit;

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

SUCCESS

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

#

delete from t1 where a=2;

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

SUCCESS

commit;

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

SUCCESS

# 8. Read-write statement: unqualified DELETE

#

# In statement or mixed replication mode, we call

# handler::ha_delete_all_rows() and write statement text

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

# In row level replication mode, we do not call

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

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

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

delete from t1;

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

SUCCESS

commit;

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

SUCCESS

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

#

replace t1 set a=1;

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

SUCCESS

commit;

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

SUCCESS

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

#

replace t1 set a=1;

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

SUCCESS

commit;

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

SUCCESS

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

#

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

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

SUCCESS

select * from t1;

a

2

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

SUCCESS

commit;

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

SUCCESS

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

#

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

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

SUCCESS

commit;

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

SUCCESS

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

#

insert ignore t1 set a=2;

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

SUCCESS

commit;

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

SUCCESS

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

#

insert ignore t1 set a=1;

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

SUCCESS

commit;

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

SUCCESS

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

#

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

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

SUCCESS

commit;

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

SUCCESS

#

# Create a stored function that modifies a

# non-transactional table. Demonstrate that changes in

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

# algorithm.

#

create function f1() returns int

begin

insert t2 set a=2;

return 2;

end|

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

SUCCESS

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

#

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

# non-transactional changes saved in the transaction cache to

# the binary log. 

#

select f1();

f1()

2

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

SUCCESS

commit;

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

SUCCESS

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

#

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

# non-transactional changes saved in the transaction cache to

# the binary log. 

#

select f1() from t1;

f1()

2

2

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

SUCCESS

commit;

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

SUCCESS

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

#

select count(*) from t2;

count(*)

3

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

select count(*) from t2;

count(*)

5

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

SUCCESS

commit;

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

SUCCESS

#

# Replace the non-transactional table with a temporary

# transactional table. Demonstrate that a change to a temporary

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

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

#

drop table t2;

set sql_mode=no_engine_substitution;

create temporary table t2 (a int);

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

SUCCESS

set sql_mode=default;

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

#

select f1();

f1()

2

# Two commits because a binary log record is written

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

SUCCESS

commit;

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

SUCCESS

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

#

select f1() from t1;

f1()

2

2

# Two commits because a binary log record is written

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

SUCCESS

commit;

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

SUCCESS

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

#

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

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

SUCCESS

commit;

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

SUCCESS

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

#

alter table t2 add column b int default 5;

# A commit is done internally by ALTER. 

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

SUCCESS

commit;

# There is nothing left to commit

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

SUCCESS

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

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

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

truncate table t2;

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

SUCCESS

commit;

# There is nothing left to commit

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

SUCCESS

# 25. Read-write statement: unqualified DELETE 

delete from t2;

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

SUCCESS

commit;

# There is nothing left to commit

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

SUCCESS

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

#

drop temporary table t2;

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

SUCCESS

commit;

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

SUCCESS

# 26. Verify that SET AUTOCOMMIT issues an implicit commit

#

insert t1 set a=3;

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

SUCCESS

set autocommit=1;

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

SUCCESS

rollback;

select a from t1 where a=3;

a

3

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

SUCCESS

delete from t1 where a=3;

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

SUCCESS

commit;

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

SUCCESS

set autocommit=0;

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

SUCCESS

insert t1 set a=3;

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

SUCCESS

# Sic: not actually changing the value of autocommit

set autocommit=0;

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

SUCCESS

rollback;

select a from t1 where a=3;

a

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

SUCCESS

# 27. Savepoint management

#

insert t1 set a=3;

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

SUCCESS

savepoint a;

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

SUCCESS

insert t1 set a=4;

# Sic: a bug. Binlog did not register itself this time.

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

SUCCESS

release savepoint a;

rollback;

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

SUCCESS

select a from t1 where a=3;

a

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

SUCCESS

commit;

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

SUCCESS

# 28. Read-write statement: DO

#

create table t2 (a int);

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