mysql-test/main/commit_1innodb.result

call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
set sql_mode=no_engine_substitution;
set default_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'
Note	4094	At line 4 in test.f2
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/words.dat" ignore 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;
#
# Count of reading of p_verify_status_increment() from mysql.proc
call p_verify_status_increment(2, 0, 2, 0);
SUCCESS

#
# 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, 0, 1, 0);
SUCCESS

commit;
call p_verify_status_increment(2, 0, 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(2, 2, 1, 0);
SUCCESS

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

# 13. Read-write statement: INSERT IGNORE, change 0 rows.
#
insert ignore t1 set a=2;
Warnings:
Warning	1062	Duplicate entry '2' for key 'a'
call p_verify_status_increment(2, 2, 1, 0);
SUCCESS

commit;
call p_verify_status_increment(2, 2, 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;
SUCCESS

commit;
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(4, 0, 4, 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(3, 0, 3, 0);
SUCCESS

commit;
call p_verify_status_increment(1, 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(2, 0, 2, 0);
SUCCESS

commit;
call p_verify_status_increment(2, 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(1, 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

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(1, 0, 1, 0);
SUCCESS

commit;
call p_verify_status_increment(1, 0, 1, 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(1, 0, 1, 0);
SUCCESS

insert t1 set a=4;
call p_verify_status_increment(2, 2, 2, 2);
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);
SUCCESS

do (select f1() 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

# 29. Read-write statement: MULTI-DELETE
#
delete t1, t2 from t1 join t2 on (t1.a=t2.a) where t1.a=2;
commit;
call p_verify_status_increment(4, 4, 4, 4);
SUCCESS

# 30. Read-write statement: INSERT-SELECT, MULTI-UPDATE, REPLACE-SELECT
#
insert into t2 select a from t1;
commit;
replace into t2 select a from t1;
commit;
call p_verify_status_increment(8, 8, 8, 8);
SUCCESS

update t1, t2 set t1.a=4, t2.a=8 where t1.a=t2.a and t1.a=1;
commit;
call p_verify_status_increment(4, 4, 4, 4);
SUCCESS

# 31. DDL: various DDL with transactional tables
#
# Sic: no table is created.
create table if not exists t2 (a int) select 6 union select 7;
Warnings:
Note	1050	Table 't2' already exists
# Sic: first commits the statement, and then the transaction.
call p_verify_status_increment(0, 0, 0, 0);
SUCCESS

create table t3 select a from t2;
call p_verify_status_increment(2, 0, 4, 4);
SUCCESS

alter table t3 add column (b int);
call p_verify_status_increment(2, 0, 2, 0);
SUCCESS

alter table t3 rename t4;
call p_verify_status_increment(0, 0, 0, 0);
SUCCESS

rename table t4 to t3;
call p_verify_status_increment(0, 0, 0, 0);
SUCCESS

truncate table t3;
call p_verify_status_increment(2, 0, 2, 0);
SUCCESS

create view v1 as select * from t2;
call p_verify_status_increment(4, 0, 4, 0);
SUCCESS

check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
call p_verify_status_increment(2, 0, 2, 0);
SUCCESS

# Sic: after this bug is fixed, CHECK leaves no pending transaction
commit;
call p_verify_status_increment(0, 0, 0, 0);
SUCCESS

check table t1, t2, t3;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
test.t2	check	status	OK
test.t3	check	status	OK
call p_verify_status_increment(4, 0, 4, 0);
SUCCESS

commit;
call p_verify_status_increment(0, 0, 0, 0);
SUCCESS

drop view v1;
call p_verify_status_increment(2, 0, 2, 0);
SUCCESS

#
# Cleanup
#
drop table t1, t2, t3;
drop procedure p_verify_status_increment;
drop function f1;