Edit File: 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 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/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(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; 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(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(1, 0, 1, 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(4, 0, 4, 0); ERROR Expected commit increment: 4 actual: 2 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(2, 0, 2, 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(2, 0, 2, 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(2, 0, 2, 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(6, 0, 6, 0); SUCCESS commit; call p_verify_status_increment(0, 0, 0, 0); SUCCESS drop view v1; call p_verify_status_increment(0, 0, 0, 0); SUCCESS # # Cleanup # drop table t1, t2, t3; drop procedure p_verify_status_increment; drop function f1;
Back to File Manager