From 526798dbb55c79b62e795a1cf40062030e2342bd Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 19 Feb 2008 14:43:01 +0300 Subject: A fix and a test case for Bug#12713 "Error in a stored function called from a SELECT doesn't cause ROLLBACK of statem". The idea of the fix is to ensure that we always commit the current statement at the end of dispatch_command(). In order to not issue redundant disc syncs, an optimization of the two-phase commit protocol is implemented to bypass the two phase commit if the transaction is read-only. mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result: Update test results. mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result: Update test results. mysql-test/suite/rpl_ndb/t/disabled.def: Disable the tests, for which this changeset reveals a bug: the injector thread does not always add 'statement commit' to the rows injected in circular replication set up. To be investigated separately. sql/ha_ndbcluster_binlog.cc: Add close_thread_tables() to run_query: this ensures that all tables are closed and there is no pending statement transaction. sql/handler.cc: Implement optimisation of read-only transactions. If a transaction consists only of DML statements that do not change data, we do not perform a two-phase commit for it (run one phase commit only). sql/handler.h: Implement optimisation of read-only transactions. If a transaction consists only of DML statements that do not change data, we do not perform a two-phase commit for it (run one phase commit only). sql/log.cc: Mark the binlog transaction read-write whenever it's started. We never read from binlog, so it's safe and least intrusive to add this mark up here. sql/log_event.cc: Update to the new layout of thd->transaction. sql/rpl_injector.cc: Always commit statement transaction before committing the global one. sql/sp.cc: Ad comments. sql/sp_head.cc: Add comments. sql/sql_base.cc: Commit transaction at the end of the statement. Always. sql/sql_class.cc: Update thd_ha_data to return the right pointer in the new layout. Fix select_dumpvar::send_data to properly return operation status. A test case from commit.inc would lead to an assertion failure in the diagnostics area (double assignment). Not test otherwise by the test suite. sql/sql_class.h: Implement a new layout of storage engine transaction info in which it is easy to access all members related to the handlerton only based on ht->slot. sql/sql_cursor.cc: Update to the new layout of thd->transaction. sql/sql_delete.cc: Remove wrong and now redundant calls to ha_autocommit_or_rollback. The transaction is committed in one place, at the end of the statement. Remove calls to mysql_unlock_tables, since some engines count locks and commit statement transaction in unlock_tables(), which essentially equates mysql_unlock_tables to ha_autocommit_or_rollback. Previously it was necessary to unlock tables soon because we wanted to avoid sending of 'ok' packet to the client under locked tables. This is no longer necessary, since OK packet is also sent from one place at the end of transaction. sql/sql_do.cc: Add DO always clears the error, we must rollback the current statement before this happens. Otherwise the statement will be committed, and not rolled back in the end. sql/sql_insert.cc: Remove wrong and now redundant calls to ha_autocommit_or_rollback. The transaction is committed in one place, at the end of the statement. Remove calls to mysql_unlock_tables, since some engines count locks and commit statement transaction in unlock_tables(), which essentially equates mysql_unlock_tables to ha_autocommit_or_rollback. Previously it was necessary to unlock tables soon because we wanted to avoid sending of 'ok' packet to the client under locked tables. This is no longer necessary, since OK packet is also sent from one place at the end of transaction. sql/sql_load.cc: Remove wrong and now redundant calls to ha_autocommit_or_rollback. The transaction is committed in one place, at the end of the statement. Remove calls to mysql_unlock_tables, since some engines count locks and commit statement transaction in unlock_tables(), which essentially equates mysql_unlock_tables to ha_autocommit_or_rollback. Previously it was necessary to unlock tables soon because we wanted to avoid sending of 'ok' packet to the client under locked tables. This is no longer necessary, since OK packet is also sent from one place at the end of transaction. sql/sql_parse.cc: Implement optimisation of read-only transactions: bypass 2-phase commit for them. Always commit statement transaction before commiting the global one. Fix an unrelated crash in check_table_access, when called from information_schema. sql/sql_partition.cc: Partitions commit at the end of a DDL operation. Make sure that send_ok() is done only if the commit has succeeded. sql/sql_table.cc: Use ha_autocommit_or_rollback and end_active_trans everywhere. Add end_trans to mysql_admin_table, so that it leaves no pending transaction. sql/sql_udf.cc: Remvove a redundant call to close_thread_tables() sql/sql_update.cc: Remove wrong and now redundant calls to ha_autocommit_or_rollback. The transaction is committed in one place, at the end of the statement. Remove calls to mysql_unlock_tables, since some engines count locks and commit statement transaction in unlock_tables(), which essentially equates mysql_unlock_tables to ha_autocommit_or_rollback. Previously it was necessary to unlock tables soon because we wanted to avoid sending of 'ok' packet to the client under locked tables. This is no longer necessary, since OK packet is also sent from one place at the end of transaction. mysql-test/include/commit.inc: New BitKeeper file ``mysql-test/include/commit.inc'' mysql-test/r/commit_1innodb.result: New BitKeeper file ``mysql-test/r/commit_1innodb.result'' mysql-test/t/commit_1innodb.test: New BitKeeper file ``mysql-test/t/commit_1innodb.test'' --- mysql-test/r/commit_1innodb.result | 888 +++++++++++++++++++++++++++++++++++++ 1 file changed, 888 insertions(+) create mode 100644 mysql-test/r/commit_1innodb.result (limited to 'mysql-test/r/commit_1innodb.result') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result new file mode 100644 index 00000000000..87f4fc32bc8 --- /dev/null +++ b/mysql-test/r/commit_1innodb.result @@ -0,0 +1,888 @@ +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 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 @@global.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 +# +# Note the wrong Handler_prepare/Handler_commit count is due to +# Bug#29157 "UPDATE, changed rows incorrect" and +# Bug#Bug #33846 UPDATE word:Wrong 'Changed rows' if InnoDB, unique +# key and no rows qualify WHERE +# +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 + +# 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, 2, 2); +SUCCESS + +commit; +call p_verify_status_increment(2, 2, 2, 2); +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. +# +select f1(); +f1() +2 +call p_verify_status_increment(0, 0, 0, 0); +SUCCESS + +commit; +call p_verify_status_increment(0, 0, 0, 0); +SUCCESS + +# 17. Read-only statement, a function changes non-trans-table. +# +select f1() from t1; +f1() +2 +2 +call p_verify_status_increment(1, 0, 1, 0); +SUCCESS + +commit; +call p_verify_status_increment(1, 0, 1, 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); +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(4, 4, 4, 4); +SUCCESS + +create table t3 select a from t2; +call p_verify_status_increment(4, 4, 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(1, 0, 1, 0); +SUCCESS + +rename table t4 to t3; +call p_verify_status_increment(1, 0, 1, 0); +SUCCESS + +truncate table t3; +call p_verify_status_increment(2, 2, 2, 2); +SUCCESS + +create view v1 as select * from t2; +call p_verify_status_increment(1, 0, 1, 0); +SUCCESS + +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +call p_verify_status_increment(3, 0, 3, 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; +drop procedure p_verify_status_increment; +drop function f1; -- cgit v1.2.1 From 6dd9666d0f8bc40dbba44688c87a5dec1f08cef4 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 27 Feb 2008 18:38:17 +0300 Subject: Update Bug#12713 test results to take into account fixed bugs (29157, 33846) --- mysql-test/r/commit_1innodb.result | 13 ++++--------- 1 file changed, 4 insertions(+), 9 deletions(-) (limited to 'mysql-test/r/commit_1innodb.result') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 87f4fc32bc8..8598e231861 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -416,17 +416,12 @@ SUCCESS # 4. Read-write statement: UPDATE, update 0 rows, 1 row matches WHERE # -# Note the wrong Handler_prepare/Handler_commit count is due to -# Bug#29157 "UPDATE, changed rows incorrect" and -# Bug#Bug #33846 UPDATE word:Wrong 'Changed rows' if InnoDB, unique -# key and no rows qualify WHERE -# update t1 set a=2; -call p_verify_status_increment(2, 2, 2, 2); +call p_verify_status_increment(2, 2, 1, 0); SUCCESS commit; -call p_verify_status_increment(2, 2, 2, 2); +call p_verify_status_increment(2, 2, 1, 0); SUCCESS # 5. Read-write statement: UPDATE, update 0 rows, 0 rows match WHERE @@ -496,11 +491,11 @@ SUCCESS # 10. Read-write statement: REPLACE, change 0 rows. # replace t1 set a=1; -call p_verify_status_increment(2, 2, 2, 2); +call p_verify_status_increment(2, 2, 1, 0); SUCCESS commit; -call p_verify_status_increment(2, 2, 2, 2); +call p_verify_status_increment(2, 2, 1, 0); SUCCESS # 11. Read-write statement: IODKU, change 1 row. -- cgit v1.2.1 From 875ad6d8b8f89eed171325a1e8b31816f7edef12 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 7 Mar 2008 13:59:36 +0100 Subject: BUG#31168: @@hostname does not replicate Problem: in mixed and statement mode, a query that refers to a system variable will use the slave's value when replayed on slave. So if the value of a system variable is inserted into a table, the slave will differ from the master. Fix: mark statements that refer to a system variable as "unsafe", meaning they will be replicated by row in mixed mode and produce a warning in statement mode. There are some exceptions: some variables are actually replicated. Those should *not* be marked as unsafe. BUG#34732: mysqlbinlog does not print default values for auto_increment variables Problem: mysqlbinlog does not print default values for some variables, including auto_increment_increment and others. So if a client executing the output of mysqlbinlog has different default values, replication will be wrong. Fix: Always print default values for all variables that are replicated. I need to fix the two bugs at the same time, because the test cases would fail if I only fixed one of them. include/m_ctype.h: Added definition of ILLEGAL_CHARSET_INFO_NUMBER. We just need a symbol for a number that will never be used by any charset. ~0U should be safe since charset numbers are sequential, starting from 0. mysql-test/include/commit.inc: Upated test to avoid making statements unsafe. mysql-test/r/commit_1innodb.result: Updated test needs updated result file. mysql-test/r/mysqlbinlog.result: Updated result file. mysql-test/r/mysqlbinlog2.result: Updated result file. mysql-test/r/user_var-binlog.result: Updated result file. mysql-test/suite/binlog/r/binlog_base64_flag.result: Updated result file. mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result: Updated result file. mysql-test/suite/binlog/r/binlog_unsafe.result: Modified test file needs modified result file. mysql-test/suite/binlog/t/binlog_base64_flag.test: Need to filter out pseudo_thread_id from result since it is nondeterministic. mysql-test/suite/binlog/t/binlog_unsafe.test: Add tests that using variables is unsafe. The 'CREATE VIEW' tests didn't make sense, so I removed them. SHOW WARNINGS is not necessary either, because we get warnings for each statement in the result file. mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result: Updated result file. mysql-test/suite/rpl/r/rpl_skip_error.result: Updated result file. mysql-test/suite/rpl/t/rpl_skip_error.test: The test used @@server_id, which is not safe to replicate, so it would have given a warning. The way it used @@server_id was hackish (issue a query on master that removes rows only on master), so I replaced it by a more robust way to do the same thing (connect to slave and insert the rows only there). Also clarified what the test case does. mysql-test/t/mysqlbinlog2.test: Use --short-form instead of manually filtering out nondeterministic stuff from mysqlbinlog (because we added the nondeterministic @@pseudo_thread_id to the output). sql/item_func.cc: Added method of Item_func_get_system_var that indicates whether the given system variable will be written to the binlog or not. sql/item_func.h: Added method of Item_func_get_system_var that indicates whether the given system variable will be written to the binlog or not. sql/log_event.cc: - auto_increment_offset was not written to the binlog if auto_increment_increment=1 - mysqlbinlog did not output default values for some variables (BUG#34732). In st_print_event_info, we remember for each variable whether it has been printed or not. This is achieved in different ways for different variables: - For auto_increment_*, lc_time_names, charset_database_number, we set the default values in st_print_event_info to something illegal, so that it will look like they have changed the first time they are seen. - For charset, sql_mode, pseudo_thread_id, we add a flag to st_print_event_info which indicates whether the variable has been printed. - Since pseudo_thread_id is now printed more often, and its value is not guaranteed to be constant across different runs of the same test script, I replaced it by a constant if --short-form is used. - Moved st_print_event_info constructor from log_event.h to log_event.cc, since it now depends on ILLEGAL_CHARSET_NUMBER, which is defined in m_ctype.h, which is better to include from a .cc file than from a header file. sql/log_event.h: Added fields to st_print_event_info that indicate whether some of the variables have been written or not. Since the initialization of charset_database_number now depends on ILLEGAL_CHARSET_INFO_NUMBER, which is defined in a header file, which we'd better not include from this header file -- I moved the constructor from here to log_event.cc. sql/set_var.cc: System variables now have a flag binlog_status, which indicates if they are written to the binlog. If nothing is specified, all variables are marked as not written to the binlog (NOT_IN_BINLOG) when created. In this file, the variables that are written to the binlog are marked with SESSION_VARIABLE_IN_BINLOG. sql/set_var.h: Added flag binlog_status to class sys_var. Added a getter and a constructor parameter that sets it. Since I had to change sys_var_thd_enum constructor anyways, I simplified it to use default values of arguments instead of three copies of the constructor. sql/sql_yacc.yy: Mark statements that refer to a system variable as "unsafe", meaning they will be replicated by row in mixed mode. Added comment to explain strange piece of code just above. mysql-test/include/diff_tables.inc: New auxiliary test file that tests whether two tables (possibly one on master and one on slave) differ. mysql-test/suite/rpl/r/rpl_variables.result: New test case needs new result file. mysql-test/suite/rpl/r/rpl_variables_stm.result: New test file needs new result file. mysql-test/suite/rpl/t/rpl_variables.test: Test that INSERT of @@variables is replicated correctly (by switching to row-based mode). mysql-test/suite/rpl/t/rpl_variables_stm.test: Test that replication of @@variables which are replicated explicitly works as expected in statement mode (without giving warnings). --- mysql-test/r/commit_1innodb.result | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'mysql-test/r/commit_1innodb.result') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 8598e231861..76eee3874d6 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -331,6 +331,7 @@ 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; @@ -355,7 +356,7 @@ select variable_value from information_schema.session_status where variable_name='Handler_commit' or variable_name='Handler_prepare' order by variable_name; -if @@global.binlog_format = 'ROW' then +if @binlog_format = 'ROW' then set commit_inc= commit_inc_row; set prepare_inc= prepare_inc_row; else -- cgit v1.2.1 From 3155a88d6cffc2222c711dc7136db085c3e64faa Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 12 Mar 2008 16:52:29 +0300 Subject: Fixed test failures caused by insufficient cleanups in the tests for bug12713. --- mysql-test/r/commit_1innodb.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r/commit_1innodb.result') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 8598e231861..196d65a48ad 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -878,6 +878,6 @@ SUCCESS # # Cleanup # -drop table t1; +drop table t1, t2, t3; drop procedure p_verify_status_increment; drop function f1; -- cgit v1.2.1 From 600a664a5a220f056b61139a761093cd1aed6355 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 31 Mar 2008 11:29:03 +0200 Subject: Updating commit.inc since the number of commits done for non-transactional tables is not zero any more. For row-based logging, there is an extra commit for sending rows changed by the statement to the binary log. mysql-test/include/commit.inc: For row-based logging, an extra commit is done for each statement to commit non-transactional changes to the binary log. mysql-test/r/commit_1innodb.result: Result change. --- mysql-test/r/commit_1innodb.result | 16 ++++++++++++---- 1 file changed, 12 insertions(+), 4 deletions(-) (limited to 'mysql-test/r/commit_1innodb.result') diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index ed0034cfc99..a2c1eb9a82e 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -571,27 +571,35 @@ 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, 0, 0); +call p_verify_status_increment(0, 0, 1, 0); SUCCESS commit; -call p_verify_status_increment(0, 0, 0, 0); +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, 1, 0); +call p_verify_status_increment(1, 0, 2, 0); SUCCESS commit; -call p_verify_status_increment(1, 0, 1, 0); +call p_verify_status_increment(1, 0, 2, 0); SUCCESS # 18. Read-write statement: UPDATE, change 0 (transactional) rows. -- cgit v1.2.1