diff options
Diffstat (limited to 'mysql-test/suite/innodb')
53 files changed, 1526 insertions, 123 deletions
diff --git a/mysql-test/suite/innodb/r/binlog_consistent.result b/mysql-test/suite/innodb/r/binlog_consistent.result new file mode 100644 index 00000000000..2e523c40a5b --- /dev/null +++ b/mysql-test/suite/innodb/r/binlog_consistent.result @@ -0,0 +1,103 @@ +RESET MASTER; +# Connection default +CREATE TABLE t1 (a INT, b VARCHAR(100), PRIMARY KEY (a,b)) ENGINE=innodb; +SHOW MASTER STATUS; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 380 +SHOW STATUS LIKE 'binlog_snapshot_%'; +Variable_name Value +binlog_snapshot_file master-bin.000001 +binlog_snapshot_position 380 +BEGIN; +INSERT INTO t1 VALUES (0, ""); +# Connection con1 +BEGIN; +INSERT INTO t1 VALUES (1, ""); +# Connection con2 +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=myisam; +BEGIN; +INSERT INTO t1 VALUES (2, "first"); +INSERT INTO t2 VALUES (2); +INSERT INTO t1 VALUES (2, "second"); +# Connection default +COMMIT; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +# Connection con3 +BEGIN; +INSERT INTO t1 VALUES (3, ""); +INSERT INTO t2 VALUES (3); +# Connection con4 +BEGIN; +INSERT INTO t1 VALUES (4, ""); +COMMIT; +# Connection default +SELECT * FROM t1 ORDER BY a,b; +a b +0 +SHOW STATUS LIKE 'binlog_snapshot_%'; +Variable_name Value +binlog_snapshot_file master-bin.000001 +binlog_snapshot_position 904 +SHOW MASTER STATUS; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000001 1316 +SELECT * FROM t2 ORDER BY a; +a +2 +3 +# Connection con1 +COMMIT; +# Connection con2 +COMMIT; +# Connection con3 +COMMIT; +FLUSH LOGS; +# Connection default +SELECT * FROM t1 ORDER BY a,b; +a b +0 +SHOW STATUS LIKE 'binlog_snapshot_%'; +Variable_name Value +binlog_snapshot_file master-bin.000001 +binlog_snapshot_position 904 +SHOW MASTER STATUS; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000002 245 +COMMIT; +SHOW STATUS LIKE 'binlog_snapshot_%'; +Variable_name Value +binlog_snapshot_file master-bin.000002 +binlog_snapshot_position 245 +SHOW MASTER STATUS; +File Position Binlog_Do_DB Binlog_Ignore_DB +master-bin.000002 245 +SHOW BINLOG EVENTS; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 4 Format_desc 1 245 Server ver: #, Binlog ver: # +master-bin.000001 245 Query 1 380 use `test`; CREATE TABLE t1 (a INT, b VARCHAR(100), PRIMARY KEY (a,b)) ENGINE=innodb +master-bin.000001 380 Query 1 492 use `test`; CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=myisam +master-bin.000001 492 Query 1 560 BEGIN +master-bin.000001 560 Query 1 648 use `test`; INSERT INTO t2 VALUES (2) +master-bin.000001 648 Query 1 717 COMMIT +master-bin.000001 717 Query 1 785 BEGIN +master-bin.000001 785 Query 1 877 use `test`; INSERT INTO t1 VALUES (0, "") +master-bin.000001 877 Xid 1 904 COMMIT /* XID */ +master-bin.000001 904 Query 1 972 BEGIN +master-bin.000001 972 Query 1 1060 use `test`; INSERT INTO t2 VALUES (3) +master-bin.000001 1060 Query 1 1129 COMMIT +master-bin.000001 1129 Query 1 1197 BEGIN +master-bin.000001 1197 Query 1 1289 use `test`; INSERT INTO t1 VALUES (4, "") +master-bin.000001 1289 Xid 1 1316 COMMIT /* XID */ +master-bin.000001 1316 Query 1 1384 BEGIN +master-bin.000001 1384 Query 1 1476 use `test`; INSERT INTO t1 VALUES (1, "") +master-bin.000001 1476 Xid 1 1503 COMMIT /* XID */ +master-bin.000001 1503 Query 1 1571 BEGIN +master-bin.000001 1571 Query 1 1668 use `test`; INSERT INTO t1 VALUES (2, "first") +master-bin.000001 1668 Query 1 1766 use `test`; INSERT INTO t1 VALUES (2, "second") +master-bin.000001 1766 Xid 1 1793 COMMIT /* XID */ +master-bin.000001 1793 Query 1 1861 BEGIN +master-bin.000001 1861 Query 1 1953 use `test`; INSERT INTO t1 VALUES (3, "") +master-bin.000001 1953 Xid 1 1980 COMMIT /* XID */ +master-bin.000001 1980 Rotate 1 2024 master-bin.000002;pos=4 +DROP TABLE t1,t2; diff --git a/mysql-test/suite/innodb/r/group_commit.result b/mysql-test/suite/innodb/r/group_commit.result new file mode 100644 index 00000000000..1009a83637f --- /dev/null +++ b/mysql-test/suite/innodb/r/group_commit.result @@ -0,0 +1,65 @@ +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) ENGINE=innodb; +SELECT variable_value INTO @commits FROM information_schema.global_status +WHERE variable_name = 'binlog_commits'; +SELECT variable_value INTO @group_commits FROM information_schema.global_status +WHERE variable_name = 'binlog_group_commits'; +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group1_running WAIT_FOR group2_queued"; +INSERT INTO t1 VALUES ("con1"); +set DEBUG_SYNC= "now WAIT_FOR group1_running"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con2"; +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group2_running"; +SET DEBUG_SYNC= "commit_after_release_LOCK_log WAIT_FOR group3_committed"; +SET DEBUG_SYNC= "commit_after_group_run_commit_ordered SIGNAL group2_visible WAIT_FOR group2_checked"; +INSERT INTO t1 VALUES ("con2"); +SET DEBUG_SYNC= "now WAIT_FOR group2_con2"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con3"; +INSERT INTO t1 VALUES ("con3"); +SET DEBUG_SYNC= "now WAIT_FOR group2_con3"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con4"; +INSERT INTO t1 VALUES ("con4"); +SET DEBUG_SYNC= "now WAIT_FOR group2_con4"; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT * FROM t1 ORDER BY a; +a +SET DEBUG_SYNC= "now SIGNAL group2_queued"; +SELECT * FROM t1 ORDER BY a; +a +con1 +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group3_con5"; +SET DEBUG_SYNC= "commit_after_get_LOCK_log SIGNAL con5_leader WAIT_FOR con6_queued"; +set DEBUG_SYNC= "now WAIT_FOR group2_running"; +INSERT INTO t1 VALUES ("con5"); +SET DEBUG_SYNC= "now WAIT_FOR con5_leader"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con6_queued"; +INSERT INTO t1 VALUES ("con6"); +SET DEBUG_SYNC= "now WAIT_FOR group3_con5"; +SELECT * FROM t1 ORDER BY a; +a +con1 +SET DEBUG_SYNC= "now SIGNAL group3_committed"; +SET DEBUG_SYNC= "now WAIT_FOR group2_visible"; +SELECT * FROM t1 ORDER BY a; +a +con1 +con2 +con3 +con4 +SET DEBUG_SYNC= "now SIGNAL group2_checked"; +SELECT * FROM t1 ORDER BY a; +a +con1 +con2 +con3 +con4 +con5 +con6 +SELECT variable_value - @commits FROM information_schema.global_status +WHERE variable_name = 'binlog_commits'; +variable_value - @commits +6 +SELECT variable_value - @group_commits FROM information_schema.global_status +WHERE variable_name = 'binlog_group_commits'; +variable_value - @group_commits +3 +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/group_commit_binlog_pos.result b/mysql-test/suite/innodb/r/group_commit_binlog_pos.result new file mode 100644 index 00000000000..79ade2acec7 --- /dev/null +++ b/mysql-test/suite/innodb/r/group_commit_binlog_pos.result @@ -0,0 +1,35 @@ +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb; +INSERT INTO t1 VALUES (0); +SET DEBUG_SYNC= "commit_after_get_LOCK_log SIGNAL con1_waiting WAIT_FOR con3_queued"; +SET DEBUG_SYNC= "commit_loop_entry_commit_ordered SIGNAL con1_loop WAIT_FOR con1_loop_cont EXECUTE 3"; +INSERT INTO t1 VALUES (1); +SET DEBUG_SYNC= "now WAIT_FOR con1_waiting"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con2_queued"; +INSERT INTO t1 VALUES (2); +SET DEBUG_SYNC= "now WAIT_FOR con2_queued"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con3_queued"; +INSERT INTO t1 VALUES (3); +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; +SET DEBUG_SYNC= "now SIGNAL con1_loop_cont"; +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; +SET DEBUG_SYNC= "now SIGNAL con1_loop_cont"; +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; +SELECT * FROM t1 ORDER BY a; +a +0 +1 +2 +SET SESSION debug="+d,crash_dispatch_command_before"; +SELECT 1; +Got one of the listed errors +Got one of the listed errors +Got one of the listed errors +SELECT * FROM t1 ORDER BY a; +a +0 +1 +2 +3 +InnoDB: Last MySQL binlog file position 0 906, file name ./master-bin.000001 +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/group_commit_binlog_pos_no_optimize_thread.result b/mysql-test/suite/innodb/r/group_commit_binlog_pos_no_optimize_thread.result new file mode 100644 index 00000000000..e6ea24a67bd --- /dev/null +++ b/mysql-test/suite/innodb/r/group_commit_binlog_pos_no_optimize_thread.result @@ -0,0 +1,36 @@ +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb; +INSERT INTO t1 VALUES (0); +SET DEBUG_SYNC= "commit_after_get_LOCK_log SIGNAL con1_waiting WAIT_FOR con3_queued"; +SET DEBUG_SYNC= "commit_loop_entry_commit_ordered SIGNAL con1_loop WAIT_FOR con1_loop_cont"; +INSERT INTO t1 VALUES (1); +SET DEBUG_SYNC= "now WAIT_FOR con1_waiting"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con2_queued"; +SET DEBUG_SYNC= "commit_loop_entry_commit_ordered SIGNAL con1_loop WAIT_FOR con1_loop_cont"; +INSERT INTO t1 VALUES (2); +SET DEBUG_SYNC= "now WAIT_FOR con2_queued"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con3_queued"; +SET DEBUG_SYNC= "commit_loop_entry_commit_ordered SIGNAL con1_loop WAIT_FOR con1_loop_cont"; +INSERT INTO t1 VALUES (3); +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; +SET DEBUG_SYNC= "now SIGNAL con1_loop_cont"; +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; +SET DEBUG_SYNC= "now SIGNAL con1_loop_cont"; +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; +SELECT * FROM t1 ORDER BY a; +a +0 +1 +2 +SET SESSION debug="+d,crash_dispatch_command_before"; +SELECT 1; +Got one of the listed errors +Got one of the listed errors +SELECT * FROM t1 ORDER BY a; +a +0 +1 +2 +3 +InnoDB: Last MySQL binlog file position 0 906, file name ./master-bin.000001 +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/group_commit_crash.result b/mysql-test/suite/innodb/r/group_commit_crash.result new file mode 100644 index 00000000000..a55d8f29dda --- /dev/null +++ b/mysql-test/suite/innodb/r/group_commit_crash.result @@ -0,0 +1,125 @@ +CREATE TABLE t1(a CHAR(255), +b CHAR(255), +c CHAR(255), +d CHAR(255), +id INT AUTO_INCREMENT, +PRIMARY KEY(id)) ENGINE=InnoDB; +create table t2 like t1; +create procedure setcrash(IN i INT) +begin +CASE i +WHEN 1 THEN SET SESSION debug="d,crash_commit_after_prepare"; +WHEN 2 THEN SET SESSION debug="d,crash_commit_after_log"; +WHEN 3 THEN SET SESSION debug="d,crash_commit_before_unlog"; +WHEN 4 THEN SET SESSION debug="d,crash_commit_after"; +WHEN 5 THEN SET SESSION debug="d,crash_commit_before"; +ELSE BEGIN END; +END CASE; +end // +FLUSH TABLES; +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +SET binlog_format= mixed; +RESET MASTER; +START TRANSACTION; +insert into t1 select * from t2; +call setcrash(5); +COMMIT; +Got one of the listed errors +SELECT * FROM t1 ORDER BY id; +a b c d id +SHOW BINLOG EVENTS LIMIT 2,1; +Log_name Pos Event_type Server_id End_log_pos Info +delete from t1; +SET binlog_format= mixed; +RESET MASTER; +START TRANSACTION; +insert into t1 select * from t2; +call setcrash(4); +COMMIT; +Got one of the listed errors +SELECT * FROM t1 ORDER BY id; +a b c d id +a b c d 1 +a b c d 2 +a b c d 3 +a b c d 4 +a b c d 5 +a b c d 6 +a b c d 7 +a b c d 8 +a b c d 9 +a b c d 10 +SHOW BINLOG EVENTS LIMIT 2,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; insert into t1 select * from t2 +delete from t1; +SET binlog_format= mixed; +RESET MASTER; +START TRANSACTION; +insert into t1 select * from t2; +call setcrash(3); +COMMIT; +Got one of the listed errors +SELECT * FROM t1 ORDER BY id; +a b c d id +a b c d 1 +a b c d 2 +a b c d 3 +a b c d 4 +a b c d 5 +a b c d 6 +a b c d 7 +a b c d 8 +a b c d 9 +a b c d 10 +SHOW BINLOG EVENTS LIMIT 2,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; insert into t1 select * from t2 +delete from t1; +SET binlog_format= mixed; +RESET MASTER; +START TRANSACTION; +insert into t1 select * from t2; +call setcrash(2); +COMMIT; +Got one of the listed errors +SELECT * FROM t1 ORDER BY id; +a b c d id +a b c d 1 +a b c d 2 +a b c d 3 +a b c d 4 +a b c d 5 +a b c d 6 +a b c d 7 +a b c d 8 +a b c d 9 +a b c d 10 +SHOW BINLOG EVENTS LIMIT 2,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; insert into t1 select * from t2 +delete from t1; +SET binlog_format= mixed; +RESET MASTER; +START TRANSACTION; +insert into t1 select * from t2; +call setcrash(1); +COMMIT; +Got one of the listed errors +SELECT * FROM t1 ORDER BY id; +a b c d id +SHOW BINLOG EVENTS LIMIT 2,1; +Log_name Pos Event_type Server_id End_log_pos Info +delete from t1; +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE setcrash; diff --git a/mysql-test/suite/innodb/r/group_commit_crash_no_optimize_thread.result b/mysql-test/suite/innodb/r/group_commit_crash_no_optimize_thread.result new file mode 100644 index 00000000000..a55d8f29dda --- /dev/null +++ b/mysql-test/suite/innodb/r/group_commit_crash_no_optimize_thread.result @@ -0,0 +1,125 @@ +CREATE TABLE t1(a CHAR(255), +b CHAR(255), +c CHAR(255), +d CHAR(255), +id INT AUTO_INCREMENT, +PRIMARY KEY(id)) ENGINE=InnoDB; +create table t2 like t1; +create procedure setcrash(IN i INT) +begin +CASE i +WHEN 1 THEN SET SESSION debug="d,crash_commit_after_prepare"; +WHEN 2 THEN SET SESSION debug="d,crash_commit_after_log"; +WHEN 3 THEN SET SESSION debug="d,crash_commit_before_unlog"; +WHEN 4 THEN SET SESSION debug="d,crash_commit_after"; +WHEN 5 THEN SET SESSION debug="d,crash_commit_before"; +ELSE BEGIN END; +END CASE; +end // +FLUSH TABLES; +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +SET binlog_format= mixed; +RESET MASTER; +START TRANSACTION; +insert into t1 select * from t2; +call setcrash(5); +COMMIT; +Got one of the listed errors +SELECT * FROM t1 ORDER BY id; +a b c d id +SHOW BINLOG EVENTS LIMIT 2,1; +Log_name Pos Event_type Server_id End_log_pos Info +delete from t1; +SET binlog_format= mixed; +RESET MASTER; +START TRANSACTION; +insert into t1 select * from t2; +call setcrash(4); +COMMIT; +Got one of the listed errors +SELECT * FROM t1 ORDER BY id; +a b c d id +a b c d 1 +a b c d 2 +a b c d 3 +a b c d 4 +a b c d 5 +a b c d 6 +a b c d 7 +a b c d 8 +a b c d 9 +a b c d 10 +SHOW BINLOG EVENTS LIMIT 2,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; insert into t1 select * from t2 +delete from t1; +SET binlog_format= mixed; +RESET MASTER; +START TRANSACTION; +insert into t1 select * from t2; +call setcrash(3); +COMMIT; +Got one of the listed errors +SELECT * FROM t1 ORDER BY id; +a b c d id +a b c d 1 +a b c d 2 +a b c d 3 +a b c d 4 +a b c d 5 +a b c d 6 +a b c d 7 +a b c d 8 +a b c d 9 +a b c d 10 +SHOW BINLOG EVENTS LIMIT 2,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; insert into t1 select * from t2 +delete from t1; +SET binlog_format= mixed; +RESET MASTER; +START TRANSACTION; +insert into t1 select * from t2; +call setcrash(2); +COMMIT; +Got one of the listed errors +SELECT * FROM t1 ORDER BY id; +a b c d id +a b c d 1 +a b c d 2 +a b c d 3 +a b c d 4 +a b c d 5 +a b c d 6 +a b c d 7 +a b c d 8 +a b c d 9 +a b c d 10 +SHOW BINLOG EVENTS LIMIT 2,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query 1 # use `test`; insert into t1 select * from t2 +delete from t1; +SET binlog_format= mixed; +RESET MASTER; +START TRANSACTION; +insert into t1 select * from t2; +call setcrash(1); +COMMIT; +Got one of the listed errors +SELECT * FROM t1 ORDER BY id; +a b c d id +SHOW BINLOG EVENTS LIMIT 2,1; +Log_name Pos Event_type Server_id End_log_pos Info +delete from t1; +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE setcrash; diff --git a/mysql-test/suite/innodb/r/group_commit_no_optimize_thread.result b/mysql-test/suite/innodb/r/group_commit_no_optimize_thread.result new file mode 100644 index 00000000000..34a638da2d5 --- /dev/null +++ b/mysql-test/suite/innodb/r/group_commit_no_optimize_thread.result @@ -0,0 +1,65 @@ +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) ENGINE=innodb; +SELECT variable_value INTO @commits FROM information_schema.global_status +WHERE variable_name = 'binlog_commits'; +SELECT variable_value INTO @group_commits FROM information_schema.global_status +WHERE variable_name = 'binlog_group_commits'; +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group1_running WAIT_FOR group2_queued"; +INSERT INTO t1 VALUES ("con1"); +set DEBUG_SYNC= "now WAIT_FOR group1_running"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con2"; +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group2_running"; +SET DEBUG_SYNC= "commit_after_release_LOCK_log WAIT_FOR group3_committed"; +INSERT INTO t1 VALUES ("con2"); +SET DEBUG_SYNC= "now WAIT_FOR group2_con2"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con3"; +INSERT INTO t1 VALUES ("con3"); +SET DEBUG_SYNC= "now WAIT_FOR group2_con3"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con4"; +SET DEBUG_SYNC= "commit_after_group_run_commit_ordered SIGNAL group2_visible WAIT_FOR group2_checked"; +INSERT INTO t1 VALUES ("con4"); +SET DEBUG_SYNC= "now WAIT_FOR group2_con4"; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT * FROM t1 ORDER BY a; +a +SET DEBUG_SYNC= "now SIGNAL group2_queued"; +SELECT * FROM t1 ORDER BY a; +a +con1 +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group3_con5"; +SET DEBUG_SYNC= "commit_after_get_LOCK_log SIGNAL con5_leader WAIT_FOR con6_queued"; +set DEBUG_SYNC= "now WAIT_FOR group2_running"; +INSERT INTO t1 VALUES ("con5"); +SET DEBUG_SYNC= "now WAIT_FOR con5_leader"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con6_queued"; +INSERT INTO t1 VALUES ("con6"); +SET DEBUG_SYNC= "now WAIT_FOR group3_con5"; +SELECT * FROM t1 ORDER BY a; +a +con1 +SET DEBUG_SYNC= "now SIGNAL group3_committed"; +SET DEBUG_SYNC= "now WAIT_FOR group2_visible"; +SELECT * FROM t1 ORDER BY a; +a +con1 +con2 +con3 +con4 +SET DEBUG_SYNC= "now SIGNAL group2_checked"; +SELECT * FROM t1 ORDER BY a; +a +con1 +con2 +con3 +con4 +con5 +con6 +SELECT variable_value - @commits FROM information_schema.global_status +WHERE variable_name = 'binlog_commits'; +variable_value - @commits +6 +SELECT variable_value - @group_commits FROM information_schema.global_status +WHERE variable_name = 'binlog_group_commits'; +variable_value - @group_commits +3 +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index 651ad391db0..5f37ae6e2c6 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -1,5 +1,4 @@ -set optimizer_switch='index_condition_pushdown=off'; -set @@optimizer_use_mrr=disable; +set optimizer_switch = 'mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; drop table if exists t1,t2,t3,t4; drop database if exists mysqltest; CREATE TABLE bug58912 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB; @@ -1227,11 +1226,11 @@ count(*) 623 explain select * from t1 where c between 1 and 2500; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range c c 5 NULL # Using where +1 SIMPLE t1 range c c 5 NULL # # update t1 set c=a; explain select * from t1 where c between 1 and 2500; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL c NULL NULL NULL # Using where +1 SIMPLE t1 ALL c NULL NULL NULL # # drop table t1,t2; create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb; insert into t1 (id) values (null),(null),(null),(null),(null); @@ -1915,7 +1914,7 @@ qq *a *a*a * explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v,v_2 # 13 const # Using where +1 SIMPLE t1 ref v,v_2 # 13 const # # select v,count(*) from t1 group by v limit 10; v count(*) a 1 @@ -2091,7 +2090,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 303 const # Using where; Using index explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where +1 SIMPLE t1 ref v v 303 const # # select v,count(*) from t1 group by v limit 10; v count(*) a 1 @@ -2171,7 +2170,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref v v 33 const # Using where explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 33 const # Using where +1 SIMPLE t1 ref v v 33 const # # select v,count(*) from t1 group by v limit 10; v count(*) a 1 diff --git a/mysql-test/suite/innodb/r/innodb_bug54044.result b/mysql-test/suite/innodb/r/innodb_bug54044.result index 90ab812f2ae..3f607e20ddf 100644 --- a/mysql-test/suite/innodb/r/innodb_bug54044.result +++ b/mysql-test/suite/innodb/r/innodb_bug54044.result @@ -1,3 +1,2 @@ CREATE TEMPORARY TABLE table_54044 ENGINE = INNODB AS SELECT IF(NULL IS NOT NULL, NULL, NULL); -ERROR HY000: Can't create table 'test.table_54044' (errno: -1) diff --git a/mysql-test/suite/innodb/r/innodb_bug56716.result b/mysql-test/suite/innodb/r/innodb_bug56716.result new file mode 100644 index 00000000000..50d83e8d87a --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug56716.result @@ -0,0 +1,4 @@ +CREATE TABLE bug56716 (a INT PRIMARY KEY,b INT,c INT,INDEX(b)) ENGINE=InnoDB; +SELECT * FROM bug56716 WHERE b<=42 ORDER BY b DESC FOR UPDATE; +a b c +DROP TABLE bug56716; diff --git a/mysql-test/suite/innodb/r/innodb_bug59307.result b/mysql-test/suite/innodb/r/innodb_bug59307.result deleted file mode 100644 index 0d726e83708..00000000000 --- a/mysql-test/suite/innodb/r/innodb_bug59307.result +++ /dev/null @@ -1,28 +0,0 @@ -CREATE TABLE t1 ( -t1_int INT, -t1_time TIME -) ENGINE=innodb; -CREATE TABLE t2 ( -t2_int int PRIMARY KEY, -t2_int2 INT -) ENGINE=INNODB; -INSERT INTO t2 VALUES (); -Warnings: -Warning 1364 Field 't2_int' doesn't have a default value -INSERT INTO t1 VALUES (); -SELECT * -FROM t1 AS t1a -WHERE NOT EXISTS -(SELECT * -FROM t1 AS t1b -WHERE t1b.t1_int NOT IN -(SELECT t2.t2_int -FROM t2 -WHERE t1b.t1_time LIKE t1b.t1_int -OR t1b.t1_time <> t2.t2_int2 -AND 6=7 -) -) -; -t1_int t1_time -DROP TABLE t1,t2; diff --git a/mysql-test/suite/innodb/r/innodb_bug59641.result b/mysql-test/suite/innodb/r/innodb_bug59641.result index 361172aa82b..e44fe1d69b4 100644 --- a/mysql-test/suite/innodb/r/innodb_bug59641.result +++ b/mysql-test/suite/innodb/r/innodb_bug59641.result @@ -1,3 +1,4 @@ +FLUSH TABLES; CREATE TABLE t(a INT PRIMARY KEY, b INT)ENGINE=InnoDB; INSERT INTO t VALUES(2,2),(4,4),(8,8),(16,16),(32,32); COMMIT; diff --git a/mysql-test/suite/innodb/r/innodb_bug60049.result b/mysql-test/suite/innodb/r/innodb_bug60049.result index bec0e05a897..a1788a8ab0a 100644 --- a/mysql-test/suite/innodb/r/innodb_bug60049.result +++ b/mysql-test/suite/innodb/r/innodb_bug60049.result @@ -1,3 +1,4 @@ +set @@global.innodb_fast_shutdown=0; CREATE TABLE t(a INT)ENGINE=InnoDB; RENAME TABLE t TO u; DROP TABLE u; diff --git a/mysql-test/suite/innodb/r/innodb_gis.result b/mysql-test/suite/innodb/r/innodb_gis.result index 9d015d91c0d..566b950d430 100644 --- a/mysql-test/suite/innodb/r/innodb_gis.result +++ b/mysql-test/suite/innodb/r/innodb_gis.result @@ -403,7 +403,7 @@ Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort -1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +1 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,overlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,disjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,intersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index deb84a87e93..8e41a951ab8 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -187,7 +187,7 @@ min(7) explain select min(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) select min(7) from t2i join t1i; min(7) NULL @@ -203,7 +203,7 @@ max(7) explain select max(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2i ALL NULL NULL NULL NULL 1 -1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) select max(7) from t2i join t1i; max(7) NULL @@ -679,8 +679,6 @@ INSERT INTO t1(b,c) SELECT b,c FROM t2; UPDATE t2 SET c='2007-01-03'; INSERT INTO t1(b,c) SELECT b,c FROM t2; set @@sort_buffer_size=8192; -Warnings: -Warning 1292 Truncated incorrect sort_buffer_size value: '8192' SELECT COUNT(*) FROM t1; COUNT(*) 3072 @@ -1605,7 +1603,17 @@ TRUNCATE t1; INSERT INTO t1 VALUES (1,'init'); CREATE PROCEDURE p1() BEGIN +# retry the UPDATE in case it times out the lock before con1 has time +# to COMMIT. +DECLARE do_retry INT DEFAULT 0; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1; +retry_loop:LOOP UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; +IF do_retry = 0 THEN +LEAVE retry_loop; +END IF; +SET do_retry = 0; +END LOOP; INSERT INTO t2 VALUES (); END| BEGIN; @@ -1740,8 +1748,8 @@ EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 5 5 Using filesort +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ref c3,c2 c3 5 const 2 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; @@ -1754,8 +1762,8 @@ EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 9 5 Using filesort +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ref c3,c2 c3 9 const 2 Using where; Using filesort DROP TABLE t1; CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), KEY (c3), KEY (c2, c3)) @@ -1769,8 +1777,8 @@ EXPLAIN SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL c3,c2 c3 7 5 Using filesort +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ref c3,c2 c3 7 const 2 Using where; Using filesort DROP TABLE t1; End of 5.1 tests # @@ -2342,6 +2350,28 @@ id select_type table type possible_keys key key_len ref rows Extra drop table t1,t2; # # +# Bug #39653: find_shortest_key in sql_select.cc does not consider +# clustered primary keys +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, +KEY (b,c)) ENGINE=INNODB; +INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3), +(4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6), +(7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9), +(11,11,11,11,11,11); +EXPLAIN SELECT COUNT(*) FROM t1; +id 1 +select_type SIMPLE +table t1 +type index +possible_keys NULL +key PRIMARY +key_len 4 +ref NULL +rows 10 +Extra Using index +DROP TABLE t1; +# # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may # corrupt definition at engine # @@ -2572,7 +2602,7 @@ INSERT INTO t1 VALUES (0); SET SQL_MODE='STRICT_ALL_TABLES'; CREATE TABLE t2 SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`; -ERROR 22007: Incorrect datetime value: '' for column 'NOW()' at row 2 +ERROR 22007: Incorrect datetime value: '' DROP TABLE t1; SET SQL_MODE=DEFAULT; # @@ -2654,6 +2684,13 @@ ref NULL rows 3 Extra Using index DROP TABLE t1; +# +# ALTER TABLE IGNORE didn't ignore duplicates for unique add index +# +create table t1 (a int primary key, b int) engine = innodb; +insert into t1 values (1,1),(2,1); +alter ignore table t1 add unique `main` (b); +drop table t1; End of 5.1 tests # # @@ -2665,7 +2702,7 @@ INSERT INTO t1 VALUES (0); SET SQL_MODE='STRICT_ALL_TABLES'; CREATE TABLE t2 SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`; -ERROR 22007: Incorrect datetime value: '' for column 'NOW()' at row 2 +ERROR 22007: Incorrect datetime value: '' DROP TABLE t1; SET SQL_MODE=DEFAULT; # @@ -2700,7 +2737,7 @@ SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1536 2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 1536 Using sort_union(idx,PRIMARY); Using where SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) @@ -2710,6 +2747,83 @@ COUNT(*) SET SESSION sort_buffer_size = DEFAULT; DROP TABLE t1; # +# Test for bug #39932 "create table fails if column for FK is in different +# case than in corr index". +# +drop tables if exists t1, t2; +create table t1 (pk int primary key) engine=InnoDB; +# Even although the below statement uses uppercased field names in +# foreign key definition it still should be able to find explicitly +# created supporting index. So it should succeed and should not +# create any additional supporting indexes. +create table t2 (fk int, key x (fk), +constraint x foreign key (FK) references t1 (PK)) engine=InnoDB; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `fk` int(11) DEFAULT NULL, + KEY `x` (`fk`), + CONSTRAINT `x` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t2, t1; +# +# Bug #663818: wrong result when BNLH is used +# +CREATE TABLE t1(pk int NOT NULL PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(1), (2), (11), (12), (13), (14), +(15), (16), (17), (18), (19); +CREATE TABLE t2(pk int NOT NULL PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t2 VALUES +(1), (10), (11), (12), (13), (14), +(15), (16), (17), (18), (19), (20), (21); +SET SESSION join_buffer_size=10000; +Warnings: +Warning 1292 Truncated incorrect join_buffer_size value: '10000' +SET SESSION join_cache_level=3; +EXPLAIN +SELECT t1.pk FROM t1,t2 +WHERE t1.pk = t2.pk AND t2.pk <> 8; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 11 Using where; Using index +1 SIMPLE t2 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.pk 13 Using join buffer (flat, BNLH join) +SELECT t1.pk FROM t1,t2 +WHERE t1.pk = t2.pk AND t2.pk <> 8; +pk +1 +11 +12 +13 +14 +15 +16 +17 +18 +19 +SET SESSION join_cache_level=1; +EXPLAIN +SELECT t1.pk FROM t1,t2 +WHERE t1.pk = t2.pk AND t2.pk <> 8; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 11 Using where; Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.pk 1 Using index +SELECT t1.pk FROM t1,t2 +WHERE t1.pk = t2.pk AND t2.pk <> 8; +pk +1 +11 +12 +13 +14 +15 +16 +17 +18 +19 +DROP TABLE t1,t2; +SET SESSION join_cache_level=DEFAULT; +SET SESSION join_buffer_size=DEFAULT; +# # Bug#668644: HAVING + ORDER BY # CREATE TABLE t1 ( @@ -2745,6 +2859,36 @@ f 1541734400 1541734400 DROP TABLE t1, t2; +# +# Test for bug #56619 - Assertion failed during +# ALTER TABLE RENAME, DISABLE KEYS +# +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 (a INT, INDEX(a)) engine=innodb; +ALTER TABLE t1 RENAME TO t2, DISABLE KEYS; +DROP TABLE IF EXISTS t1, t2; +# +# Bug#702322: HAVING with two ANDed predicates + ORDER BY +# +CREATE TABLE t1 (pk int PRIMARY KEY, a int, KEY (a)) ENGINE=InnoDB; +CREATE TABLE t2 (a int, KEY (a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(18,0),(9,10),(8,11),(2,15),(7,19),(1,20); +SET SESSION join_cache_level = 0; +EXPLAIN +SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a +WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11 +ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using filesort +1 SIMPLE t2 ref a a 5 test.t1.pk 1 Using index +SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a +WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11 +ORDER BY t1.a; +a +10 +19 +DROP TABLE t1,t2; End of 5.3 tests # # Test for bug #39932 "create table fails if column for FK is in different @@ -2818,3 +2962,4 @@ PACK_KEYS=0; CREATE INDEX a ON t1 (a); CREATE INDEX c on t1 (c); DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/suite/innodb/t/binlog_consistent.test b/mysql-test/suite/innodb/t/binlog_consistent.test new file mode 100644 index 00000000000..7502980d72a --- /dev/null +++ b/mysql-test/suite/innodb/t/binlog_consistent.test @@ -0,0 +1,87 @@ +--source include/have_log_bin.inc +--source include/have_binlog_format_mixed_or_statement.inc + +RESET MASTER; + +# Test that we get the correct binlog position from START TRANSACTION WITH +# CONSISTENT SNAPSHOT even when other transactions are active. + +connect(con1,localhost,root,,); +connect(con2,localhost,root,,); +connect(con3,localhost,root,,); +connect(con4,localhost,root,,); + +connection default; +--echo # Connection default + +CREATE TABLE t1 (a INT, b VARCHAR(100), PRIMARY KEY (a,b)) ENGINE=innodb; +SHOW MASTER STATUS; +SHOW STATUS LIKE 'binlog_snapshot_%'; +BEGIN; +INSERT INTO t1 VALUES (0, ""); + +connection con1; +--echo # Connection con1 +BEGIN; +INSERT INTO t1 VALUES (1, ""); + +connection con2; +--echo # Connection con2 +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=myisam; +BEGIN; +INSERT INTO t1 VALUES (2, "first"); +INSERT INTO t2 VALUES (2); +INSERT INTO t1 VALUES (2, "second"); + +connection default; +--echo # Connection default +COMMIT; + +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection con3; +--echo # Connection con3 +BEGIN; +INSERT INTO t1 VALUES (3, ""); +INSERT INTO t2 VALUES (3); + +connection con4; +--echo # Connection con4 +BEGIN; +INSERT INTO t1 VALUES (4, ""); +COMMIT; + +connection default; +--echo # Connection default +SELECT * FROM t1 ORDER BY a,b; +SHOW STATUS LIKE 'binlog_snapshot_%'; +SHOW MASTER STATUS; +SELECT * FROM t2 ORDER BY a; + +connection con1; +--echo # Connection con1 +COMMIT; + +connection con2; +--echo # Connection con2 +COMMIT; + +connection con3; +--echo # Connection con3 +COMMIT; +FLUSH LOGS; + +connection default; +--echo # Connection default +SELECT * FROM t1 ORDER BY a,b; +SHOW STATUS LIKE 'binlog_snapshot_%'; +SHOW MASTER STATUS; +COMMIT; +SHOW STATUS LIKE 'binlog_snapshot_%'; +SHOW MASTER STATUS; + +--replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/ +SHOW BINLOG EVENTS; + +DROP TABLE t1,t2; diff --git a/mysql-test/suite/innodb/t/group_commit.test b/mysql-test/suite/innodb/t/group_commit.test new file mode 100644 index 00000000000..f857658d643 --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit.test @@ -0,0 +1,116 @@ +--source include/have_debug_sync.inc +--source include/have_log_bin.inc + +# Test some group commit code paths by using debug_sync to do controlled +# commits of 6 transactions: first 1 alone, then 3 as a group, then 2 as a +# group. +# +# Group 3 is allowed to race as far as possible ahead before group 2 finishes +# to check some edge case for concurrency control. + +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) ENGINE=innodb; + +SELECT variable_value INTO @commits FROM information_schema.global_status + WHERE variable_name = 'binlog_commits'; +SELECT variable_value INTO @group_commits FROM information_schema.global_status + WHERE variable_name = 'binlog_group_commits'; + +connect(con1,localhost,root,,); +connect(con2,localhost,root,,); +connect(con3,localhost,root,,); +connect(con4,localhost,root,,); +connect(con5,localhost,root,,); +connect(con6,localhost,root,,); + +# Start group1 (with one thread) doing commit, waiting for +# group2 to queue up before finishing. + +connection con1; +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group1_running WAIT_FOR group2_queued"; +send INSERT INTO t1 VALUES ("con1"); + +# Make group2 (with three threads) queue up. +# Make sure con2 is the group commit leader for group2. +# Make group2 wait with running commit_ordered() until group3 has committed. + +connection con2; +set DEBUG_SYNC= "now WAIT_FOR group1_running"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con2"; +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group2_running"; +SET DEBUG_SYNC= "commit_after_release_LOCK_log WAIT_FOR group3_committed"; +SET DEBUG_SYNC= "commit_after_group_run_commit_ordered SIGNAL group2_visible WAIT_FOR group2_checked"; +send INSERT INTO t1 VALUES ("con2"); +connection con3; +SET DEBUG_SYNC= "now WAIT_FOR group2_con2"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con3"; +send INSERT INTO t1 VALUES ("con3"); +connection con4; +SET DEBUG_SYNC= "now WAIT_FOR group2_con3"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con4"; +send INSERT INTO t1 VALUES ("con4"); + +# When group2 is queued, let group1 continue and queue group3. + +connection default; +SET DEBUG_SYNC= "now WAIT_FOR group2_con4"; + +# At this point, trasaction 1 is still not visible as commit_ordered() has not +# been called yet. +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT * FROM t1 ORDER BY a; + +SET DEBUG_SYNC= "now SIGNAL group2_queued"; +connection con1; +reap; + +# Now transaction 1 is visible. +connection default; +SELECT * FROM t1 ORDER BY a; + +connection con5; +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group3_con5"; +SET DEBUG_SYNC= "commit_after_get_LOCK_log SIGNAL con5_leader WAIT_FOR con6_queued"; +set DEBUG_SYNC= "now WAIT_FOR group2_running"; +send INSERT INTO t1 VALUES ("con5"); + +connection con6; +SET DEBUG_SYNC= "now WAIT_FOR con5_leader"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con6_queued"; +send INSERT INTO t1 VALUES ("con6"); + +connection default; +SET DEBUG_SYNC= "now WAIT_FOR group3_con5"; +# Still only transaction 1 visible, as group2 have not yet run commit_ordered(). +SELECT * FROM t1 ORDER BY a; +SET DEBUG_SYNC= "now SIGNAL group3_committed"; +SET DEBUG_SYNC= "now WAIT_FOR group2_visible"; +# Now transactions 1-4 visible. +SELECT * FROM t1 ORDER BY a; +SET DEBUG_SYNC= "now SIGNAL group2_checked"; + +connection con2; +reap; + +connection con3; +reap; + +connection con4; +reap; + +connection con5; +reap; + +connection con6; +reap; + +connection default; +# Check all transactions finally visible. +SELECT * FROM t1 ORDER BY a; + +SELECT variable_value - @commits FROM information_schema.global_status + WHERE variable_name = 'binlog_commits'; +SELECT variable_value - @group_commits FROM information_schema.global_status + WHERE variable_name = 'binlog_group_commits'; + +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/group_commit_binlog_pos-master.opt b/mysql-test/suite/innodb/t/group_commit_binlog_pos-master.opt new file mode 100644 index 00000000000..425fda95086 --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit_binlog_pos-master.opt @@ -0,0 +1 @@ +--skip-stack-trace --skip-core-file diff --git a/mysql-test/suite/innodb/t/group_commit_binlog_pos.test b/mysql-test/suite/innodb/t/group_commit_binlog_pos.test new file mode 100644 index 00000000000..686425353f5 --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit_binlog_pos.test @@ -0,0 +1,88 @@ +--source include/have_debug_sync.inc +--source include/have_log_bin.inc +--source include/have_binlog_format_mixed_or_statement.inc + +# Need DBUG to crash the server intentionally +--source include/have_debug.inc +# Don't test this under valgrind, memory leaks will occur as we crash +--source include/not_valgrind.inc + +# The test case currently uses grep and tail, which may be unavailable on +# some windows systems. But see MWL#191 for how to remove the need for grep. +--source include/not_windows.inc + +# XtraDB stores the binlog position corresponding to the last commit, and +# prints it during crash recovery. +# Test that we get the correct position when we group commit several +# transactions together. + +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb; +INSERT INTO t1 VALUES (0); + +connect(con1,localhost,root,,); +connect(con2,localhost,root,,); +connect(con3,localhost,root,,); + +# Queue up three commits for group commit. + +connection con1; +SET DEBUG_SYNC= "commit_after_get_LOCK_log SIGNAL con1_waiting WAIT_FOR con3_queued"; +SET DEBUG_SYNC= "commit_loop_entry_commit_ordered SIGNAL con1_loop WAIT_FOR con1_loop_cont EXECUTE 3"; +send INSERT INTO t1 VALUES (1); + +connection con2; +SET DEBUG_SYNC= "now WAIT_FOR con1_waiting"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con2_queued"; +send INSERT INTO t1 VALUES (2); + +connection con3; +SET DEBUG_SYNC= "now WAIT_FOR con2_queued"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con3_queued"; +send INSERT INTO t1 VALUES (3); + +connection default; +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; +# At this point, no transactions are committed. +SET DEBUG_SYNC= "now SIGNAL con1_loop_cont"; +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; +# At this point, 1 transaction is committed. +SET DEBUG_SYNC= "now SIGNAL con1_loop_cont"; +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; + +# At this point, 2 transactions are committed. +SELECT * FROM t1 ORDER BY a; + +connection con2; +reap; + +# Now crash the server with 1+2 in-memory committed, 3 only prepared. +connection default; +system echo wait-group_commit_binlog_pos.test >> $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; +SET SESSION debug="+d,crash_dispatch_command_before"; +--error 2006,2013 +SELECT 1; + +connection con1; +--error 2006,2013 +reap; +connection con3; +--error 2006,2013 +reap; + +system echo restart-group_commit_binlog_pos.test >> $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; + +connection default; +--enable_reconnect +--source include/wait_until_connected_again.inc + +# Crash recovery should recover all three transactions. +SELECT * FROM t1 ORDER BY a; + +# Check that the binlog position reported by InnoDB is the correct one +# for the end of the second transaction (as can be checked with +# mysqlbinlog). +let $MYSQLD_DATADIR= `SELECT @@datadir`; +--exec grep 'InnoDB: Last MySQL binlog file position' $MYSQLD_DATADIR/../../log/mysqld.1.err | tail -1 + +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/group_commit_binlog_pos_no_optimize_thread-master.opt b/mysql-test/suite/innodb/t/group_commit_binlog_pos_no_optimize_thread-master.opt new file mode 100644 index 00000000000..18d43988ffd --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit_binlog_pos_no_optimize_thread-master.opt @@ -0,0 +1 @@ +--binlog-optimize-thread-scheduling=0 --skip-stack-trace --skip-core-file diff --git a/mysql-test/suite/innodb/t/group_commit_binlog_pos_no_optimize_thread.test b/mysql-test/suite/innodb/t/group_commit_binlog_pos_no_optimize_thread.test new file mode 100644 index 00000000000..bd6c4b721cc --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit_binlog_pos_no_optimize_thread.test @@ -0,0 +1,89 @@ +--source include/have_debug_sync.inc +--source include/have_log_bin.inc +--source include/have_binlog_format_mixed_or_statement.inc + +# Need DBUG to crash the server intentionally +--source include/have_debug.inc +# Don't test this under valgrind, memory leaks will occur as we crash +--source include/not_valgrind.inc + +# The test case currently uses grep and tail, which may be unavailable on +# some windows systems. But see MWL#191 for how to remove the need for grep. +--source include/not_windows.inc + +# XtraDB stores the binlog position corresponding to the last commit, and +# prints it during crash recovery. +# Test that we get the correct position when we group commit several +# transactions together. + +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb; +INSERT INTO t1 VALUES (0); + +connect(con1,localhost,root,,); +connect(con2,localhost,root,,); +connect(con3,localhost,root,,); + +# Queue up three commits for group commit. + +connection con1; +SET DEBUG_SYNC= "commit_after_get_LOCK_log SIGNAL con1_waiting WAIT_FOR con3_queued"; +SET DEBUG_SYNC= "commit_loop_entry_commit_ordered SIGNAL con1_loop WAIT_FOR con1_loop_cont"; +send INSERT INTO t1 VALUES (1); + +connection con2; +SET DEBUG_SYNC= "now WAIT_FOR con1_waiting"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con2_queued"; +SET DEBUG_SYNC= "commit_loop_entry_commit_ordered SIGNAL con1_loop WAIT_FOR con1_loop_cont"; +send INSERT INTO t1 VALUES (2); + +connection con3; +SET DEBUG_SYNC= "now WAIT_FOR con2_queued"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con3_queued"; +SET DEBUG_SYNC= "commit_loop_entry_commit_ordered SIGNAL con1_loop WAIT_FOR con1_loop_cont"; +send INSERT INTO t1 VALUES (3); + +connection default; +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; +# At this point, no transactions are committed. +SET DEBUG_SYNC= "now SIGNAL con1_loop_cont"; +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; +# At this point, 1 transaction is committed. +SET DEBUG_SYNC= "now SIGNAL con1_loop_cont"; +SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; + +# At this point, 2 transactions are committed. +SELECT * FROM t1 ORDER BY a; + +connection con1; +reap; +connection con2; +reap; + +# Now crash the server with 1+2 in-memory committed, 3 only prepared. +connection default; +system echo wait-group_commit_binlog_pos_no_optimize_thread.test >> $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; +SET SESSION debug="+d,crash_dispatch_command_before"; +--error 2006,2013 +SELECT 1; + +connection con3; +--error 2006,2013 +reap; + +system echo restart-group_commit_binlog_pos_no_optimize_thread.test >> $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; + +connection default; +--enable_reconnect +--source include/wait_until_connected_again.inc + +# Crash recovery should recover all three transactions. +SELECT * FROM t1 ORDER BY a; + +# Check that the binlog position reported by InnoDB is the correct one +# for the end of the second transaction (as can be checked with +# mysqlbinlog). +let $MYSQLD_DATADIR= `SELECT @@datadir`; +--exec grep 'InnoDB: Last MySQL binlog file position' $MYSQLD_DATADIR/../../log/mysqld.1.err | tail -1 + +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/group_commit_crash-master.opt b/mysql-test/suite/innodb/t/group_commit_crash-master.opt new file mode 100644 index 00000000000..425fda95086 --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit_crash-master.opt @@ -0,0 +1 @@ +--skip-stack-trace --skip-core-file diff --git a/mysql-test/suite/innodb/t/group_commit_crash.test b/mysql-test/suite/innodb/t/group_commit_crash.test new file mode 100644 index 00000000000..0d5eba6bd08 --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit_crash.test @@ -0,0 +1,81 @@ +# Testing group commit by crashing a few times. +# Test adapted from the Facebook patch: lp:mysqlatfacebook +--source include/not_embedded.inc +# Don't test this under valgrind, memory leaks will occur +--source include/not_valgrind.inc + +# Binary must be compiled with debug for crash to occur +--source include/have_debug.inc +--source include/have_log_bin.inc + +let $file_format_max=`SELECT @@innodb_file_format_max`; +CREATE TABLE t1(a CHAR(255), + b CHAR(255), + c CHAR(255), + d CHAR(255), + id INT AUTO_INCREMENT, + PRIMARY KEY(id)) ENGINE=InnoDB; +create table t2 like t1; +delimiter //; +create procedure setcrash(IN i INT) +begin + CASE i + WHEN 1 THEN SET SESSION debug="d,crash_commit_after_prepare"; + WHEN 2 THEN SET SESSION debug="d,crash_commit_after_log"; + WHEN 3 THEN SET SESSION debug="d,crash_commit_before_unlog"; + WHEN 4 THEN SET SESSION debug="d,crash_commit_after"; + WHEN 5 THEN SET SESSION debug="d,crash_commit_before"; + ELSE BEGIN END; + END CASE; +end // +delimiter ;// +# Avoid getting a crashed mysql.proc table. +FLUSH TABLES; + +let $numtests = 5; + +let $numinserts = 10; +while ($numinserts) +{ + dec $numinserts; + INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +} + +--enable_reconnect + +while ($numtests) +{ + SET binlog_format= mixed; + RESET MASTER; + + START TRANSACTION; + insert into t1 select * from t2; + # Write file to make mysql-test-run.pl expect crash + --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + + eval call setcrash($numtests); + + # Run the crashing query + --error 2006,2013 + COMMIT; + + # Poll the server waiting for it to be back online again. + --source include/wait_until_connected_again.inc + + # table and binlog should be in sync. + SELECT * FROM t1 ORDER BY id; +--replace_column 2 # 5 # + SHOW BINLOG EVENTS LIMIT 2,1; + + delete from t1; + + dec $numtests; +} + +# final cleanup +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE setcrash; +--disable_query_log +eval SET GLOBAL innodb_file_format_max=$file_format_max; +--enable_query_log diff --git a/mysql-test/suite/innodb/t/group_commit_crash_no_optimize_thread-master.opt b/mysql-test/suite/innodb/t/group_commit_crash_no_optimize_thread-master.opt new file mode 100644 index 00000000000..18d43988ffd --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit_crash_no_optimize_thread-master.opt @@ -0,0 +1 @@ +--binlog-optimize-thread-scheduling=0 --skip-stack-trace --skip-core-file diff --git a/mysql-test/suite/innodb/t/group_commit_crash_no_optimize_thread.test b/mysql-test/suite/innodb/t/group_commit_crash_no_optimize_thread.test new file mode 100644 index 00000000000..0d5eba6bd08 --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit_crash_no_optimize_thread.test @@ -0,0 +1,81 @@ +# Testing group commit by crashing a few times. +# Test adapted from the Facebook patch: lp:mysqlatfacebook +--source include/not_embedded.inc +# Don't test this under valgrind, memory leaks will occur +--source include/not_valgrind.inc + +# Binary must be compiled with debug for crash to occur +--source include/have_debug.inc +--source include/have_log_bin.inc + +let $file_format_max=`SELECT @@innodb_file_format_max`; +CREATE TABLE t1(a CHAR(255), + b CHAR(255), + c CHAR(255), + d CHAR(255), + id INT AUTO_INCREMENT, + PRIMARY KEY(id)) ENGINE=InnoDB; +create table t2 like t1; +delimiter //; +create procedure setcrash(IN i INT) +begin + CASE i + WHEN 1 THEN SET SESSION debug="d,crash_commit_after_prepare"; + WHEN 2 THEN SET SESSION debug="d,crash_commit_after_log"; + WHEN 3 THEN SET SESSION debug="d,crash_commit_before_unlog"; + WHEN 4 THEN SET SESSION debug="d,crash_commit_after"; + WHEN 5 THEN SET SESSION debug="d,crash_commit_before"; + ELSE BEGIN END; + END CASE; +end // +delimiter ;// +# Avoid getting a crashed mysql.proc table. +FLUSH TABLES; + +let $numtests = 5; + +let $numinserts = 10; +while ($numinserts) +{ + dec $numinserts; + INSERT INTO t2(a, b, c, d) VALUES ('a', 'b', 'c', 'd'); +} + +--enable_reconnect + +while ($numtests) +{ + SET binlog_format= mixed; + RESET MASTER; + + START TRANSACTION; + insert into t1 select * from t2; + # Write file to make mysql-test-run.pl expect crash + --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + + eval call setcrash($numtests); + + # Run the crashing query + --error 2006,2013 + COMMIT; + + # Poll the server waiting for it to be back online again. + --source include/wait_until_connected_again.inc + + # table and binlog should be in sync. + SELECT * FROM t1 ORDER BY id; +--replace_column 2 # 5 # + SHOW BINLOG EVENTS LIMIT 2,1; + + delete from t1; + + dec $numtests; +} + +# final cleanup +DROP TABLE t1; +DROP TABLE t2; +DROP PROCEDURE setcrash; +--disable_query_log +eval SET GLOBAL innodb_file_format_max=$file_format_max; +--enable_query_log diff --git a/mysql-test/suite/innodb/t/group_commit_no_optimize_thread-master.opt b/mysql-test/suite/innodb/t/group_commit_no_optimize_thread-master.opt new file mode 100644 index 00000000000..97d8c106816 --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit_no_optimize_thread-master.opt @@ -0,0 +1 @@ +--binlog-optimize-thread-scheduling=0 diff --git a/mysql-test/suite/innodb/t/group_commit_no_optimize_thread.test b/mysql-test/suite/innodb/t/group_commit_no_optimize_thread.test new file mode 100644 index 00000000000..1f821174326 --- /dev/null +++ b/mysql-test/suite/innodb/t/group_commit_no_optimize_thread.test @@ -0,0 +1,116 @@ +--source include/have_debug_sync.inc +--source include/have_log_bin.inc + +# Test some group commit code paths by using debug_sync to do controlled +# commits of 6 transactions: first 1 alone, then 3 as a group, then 2 as a +# group. +# +# Group 3 is allowed to race as far as possible ahead before group 2 finishes +# to check some edge case for concurrency control. + +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) ENGINE=innodb; + +SELECT variable_value INTO @commits FROM information_schema.global_status + WHERE variable_name = 'binlog_commits'; +SELECT variable_value INTO @group_commits FROM information_schema.global_status + WHERE variable_name = 'binlog_group_commits'; + +connect(con1,localhost,root,,); +connect(con2,localhost,root,,); +connect(con3,localhost,root,,); +connect(con4,localhost,root,,); +connect(con5,localhost,root,,); +connect(con6,localhost,root,,); + +# Start group1 (with one thread) doing commit, waiting for +# group2 to queue up before finishing. + +connection con1; +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group1_running WAIT_FOR group2_queued"; +send INSERT INTO t1 VALUES ("con1"); + +# Make group2 (with three threads) queue up. +# Make sure con2 is the group commit leader for group2. +# Make group2 wait with running commit_ordered() until group3 has committed. + +connection con2; +set DEBUG_SYNC= "now WAIT_FOR group1_running"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con2"; +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group2_running"; +SET DEBUG_SYNC= "commit_after_release_LOCK_log WAIT_FOR group3_committed"; +send INSERT INTO t1 VALUES ("con2"); +connection con3; +SET DEBUG_SYNC= "now WAIT_FOR group2_con2"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con3"; +send INSERT INTO t1 VALUES ("con3"); +connection con4; +SET DEBUG_SYNC= "now WAIT_FOR group2_con3"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con4"; +SET DEBUG_SYNC= "commit_after_group_run_commit_ordered SIGNAL group2_visible WAIT_FOR group2_checked"; +send INSERT INTO t1 VALUES ("con4"); + +# When group2 is queued, let group1 continue and queue group3. + +connection default; +SET DEBUG_SYNC= "now WAIT_FOR group2_con4"; + +# At this point, trasaction 1 is still not visible as commit_ordered() has not +# been called yet. +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT * FROM t1 ORDER BY a; + +SET DEBUG_SYNC= "now SIGNAL group2_queued"; +connection con1; +reap; + +# Now transaction 1 is visible. +connection default; +SELECT * FROM t1 ORDER BY a; + +connection con5; +SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL group3_con5"; +SET DEBUG_SYNC= "commit_after_get_LOCK_log SIGNAL con5_leader WAIT_FOR con6_queued"; +set DEBUG_SYNC= "now WAIT_FOR group2_running"; +send INSERT INTO t1 VALUES ("con5"); + +connection con6; +SET DEBUG_SYNC= "now WAIT_FOR con5_leader"; +SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con6_queued"; +send INSERT INTO t1 VALUES ("con6"); + +connection default; +SET DEBUG_SYNC= "now WAIT_FOR group3_con5"; +# Still only transaction 1 visible, as group2 have not yet run commit_ordered(). +SELECT * FROM t1 ORDER BY a; +SET DEBUG_SYNC= "now SIGNAL group3_committed"; +SET DEBUG_SYNC= "now WAIT_FOR group2_visible"; +# Now transactions 1-4 visible. +SELECT * FROM t1 ORDER BY a; +SET DEBUG_SYNC= "now SIGNAL group2_checked"; + +connection con2; +reap; + +connection con3; +reap; + +connection con4; +reap; + +connection con5; +reap; + +connection con6; +reap; + +connection default; +# Check all transactions finally visible. +SELECT * FROM t1 ORDER BY a; + +SELECT variable_value - @commits FROM information_schema.global_status + WHERE variable_name = 'binlog_commits'; +SELECT variable_value - @group_commits FROM information_schema.global_status + WHERE variable_name = 'binlog_group_commits'; + +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-autoinc-18274.test b/mysql-test/suite/innodb/t/innodb-autoinc-18274.test index de9f3e3d18b..9d9ea294cb4 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc-18274.test +++ b/mysql-test/suite/innodb/t/innodb-autoinc-18274.test @@ -1,4 +1,3 @@ --- source include/have_innodb.inc # embedded server ignores 'delayed', so skip this -- source include/not_embedded.inc diff --git a/mysql-test/suite/innodb/t/innodb-autoinc-56228-master.opt b/mysql-test/suite/innodb/t/innodb-autoinc-56228-master.opt index 0eed7aaadad..b21ee4fb85e 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc-56228-master.opt +++ b/mysql-test/suite/innodb/t/innodb-autoinc-56228-master.opt @@ -1 +1 @@ ---innodb_autoinc_lock_mode=0 +--loose-innodb_autoinc_lock_mode=0 diff --git a/mysql-test/suite/innodb/t/innodb-autoinc-56228.test b/mysql-test/suite/innodb/t/innodb-autoinc-56228.test index 626efcf9897..a0637620dea 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc-56228.test +++ b/mysql-test/suite/innodb/t/innodb-autoinc-56228.test @@ -1,5 +1,3 @@ --- source include/have_innodb.inc - ## # Bug #56228: dropping tables from within an active statement crashes server # diff --git a/mysql-test/suite/innodb/t/innodb-create-options.test b/mysql-test/suite/innodb/t/innodb-create-options.test index 3daa5f09e71..367a2f87dec 100644 --- a/mysql-test/suite/innodb/t/innodb-create-options.test +++ b/mysql-test/suite/innodb/t/innodb-create-options.test @@ -55,7 +55,6 @@ # # See InnoDB documentation page "SQL Compression Syntax Warnings and Errors" --- source include/have_innodb.inc SET storage_engine=InnoDB; --disable_query_log diff --git a/mysql-test/suite/innodb/t/innodb-truncate.test b/mysql-test/suite/innodb/t/innodb-truncate.test index 7629eb1a980..b01ff545e1a 100644 --- a/mysql-test/suite/innodb/t/innodb-truncate.test +++ b/mysql-test/suite/innodb/t/innodb-truncate.test @@ -1,5 +1,3 @@ ---source include/have_innodb.inc - --echo # --echo # TRUNCATE TABLE --echo # diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 1284f19b741..b8b507112b7 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -23,8 +23,7 @@ let $MYSQLD_DATADIR= `select @@datadir`; let collation=utf8_unicode_ci; --source include/have_collation.inc -set optimizer_switch='index_condition_pushdown=off'; -set @@optimizer_use_mrr=disable; +set optimizer_switch = 'mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; # Save the original values of some variables in order to be able to # estimate how much they have changed during the tests. Previously this @@ -963,10 +962,10 @@ insert into t1 (a) select b from t2; insert into t2 (a) select b from t1; insert into t1 (a) select b from t2; select count(*) from t1; ---replace_column 9 # +--replace_column 9 # 10 # explain select * from t1 where c between 1 and 2500; update t1 set c=a; ---replace_column 9 # +--replace_column 9 # 10 # explain select * from t1 where c between 1 and 2500; drop table t1,t2; diff --git a/mysql-test/suite/innodb/t/innodb_bug30423.test b/mysql-test/suite/innodb/t/innodb_bug30423.test index f2a3ee8d099..9e4156f1cf8 100644 --- a/mysql-test/suite/innodb/t/innodb_bug30423.test +++ b/mysql-test/suite/innodb/t/innodb_bug30423.test @@ -3,8 +3,6 @@ # Implemented InnoDB system variable "innodb_stats_method" with # "nulls_equal" (default), "nulls_unequal", and "nulls_ignored" options. --- source include/have_innodb.inc - let $innodb_stats_method_orig = `select @@innodb_stats_method`; # default setting for innodb_stats_method is "nulls_equal" diff --git a/mysql-test/suite/innodb/t/innodb_bug53046.test b/mysql-test/suite/innodb/t/innodb_bug53046.test index 77f0a638728..d531a9d2284 100644 --- a/mysql-test/suite/innodb/t/innodb_bug53046.test +++ b/mysql-test/suite/innodb/t/innodb_bug53046.test @@ -8,8 +8,6 @@ # correctness. # --- source include/have_innodb.inc - CREATE TABLE bug53046_1 (c1 INT PRIMARY KEY) ENGINE=INNODB; CREATE TABLE bug53046_2 (c2 INT PRIMARY KEY, FOREIGN KEY (c2) REFERENCES bug53046_1(c1) diff --git a/mysql-test/suite/innodb/t/innodb_bug53756.test b/mysql-test/suite/innodb/t/innodb_bug53756.test index ae23e9d41a3..b0e15c1686e 100644 --- a/mysql-test/suite/innodb/t/innodb_bug53756.test +++ b/mysql-test/suite/innodb/t/innodb_bug53756.test @@ -13,9 +13,6 @@ # # Don't test this under valgrind, memory leaks will occur. --source include/not_valgrind.inc -# -# This test case needs InnoDB. --- source include/have_innodb.inc # Avoid CrashReporter popup on Mac --source include/not_crashrep.inc diff --git a/mysql-test/suite/innodb/t/innodb_bug54044.test b/mysql-test/suite/innodb/t/innodb_bug54044.test index 1846cf22544..46a32921c5b 100644 --- a/mysql-test/suite/innodb/t/innodb_bug54044.test +++ b/mysql-test/suite/innodb/t/innodb_bug54044.test @@ -2,9 +2,6 @@ # during create table, so it will not trigger assertion failure. -# This 'create table' operation should fail because of -# using NULL datatype ---error ER_CANT_CREATE_TABLE CREATE TEMPORARY TABLE table_54044 ENGINE = INNODB AS SELECT IF(NULL IS NOT NULL, NULL, NULL); diff --git a/mysql-test/suite/innodb/t/innodb_bug56143.test b/mysql-test/suite/innodb/t/innodb_bug56143.test index c21de09892c..ef75ab8281f 100644 --- a/mysql-test/suite/innodb/t/innodb_bug56143.test +++ b/mysql-test/suite/innodb/t/innodb_bug56143.test @@ -3,8 +3,6 @@ # http://bugs.mysql.com/56143 # --- source include/have_innodb.inc - -- disable_query_log -- disable_result_log diff --git a/mysql-test/suite/innodb/t/innodb_bug56680.test b/mysql-test/suite/innodb/t/innodb_bug56680.test index 48723195141..19ab5cfff21 100644 --- a/mysql-test/suite/innodb/t/innodb_bug56680.test +++ b/mysql-test/suite/innodb/t/innodb_bug56680.test @@ -1,8 +1,6 @@ # # Bug #56680 InnoDB may return wrong results from a case-insensitive index # --- source include/have_innodb.inc - -- disable_query_log SET @tx_isolation_orig = @@tx_isolation; SET @innodb_file_per_table_orig = @@innodb_file_per_table; diff --git a/mysql-test/suite/innodb/t/innodb_bug56716.test b/mysql-test/suite/innodb/t/innodb_bug56716.test new file mode 100644 index 00000000000..e297b627dcb --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug56716.test @@ -0,0 +1,8 @@ +# +# Bug #56716 InnoDB locks a record gap without locking the table +# +CREATE TABLE bug56716 (a INT PRIMARY KEY,b INT,c INT,INDEX(b)) ENGINE=InnoDB; + +SELECT * FROM bug56716 WHERE b<=42 ORDER BY b DESC FOR UPDATE; + +DROP TABLE bug56716; diff --git a/mysql-test/suite/innodb/t/innodb_bug56947.test b/mysql-test/suite/innodb/t/innodb_bug56947.test index e11f39b97a8..b1aca4efd4f 100644 --- a/mysql-test/suite/innodb/t/innodb_bug56947.test +++ b/mysql-test/suite/innodb/t/innodb_bug56947.test @@ -1,8 +1,6 @@ # # Bug #56947 valgrind reports a memory leak in innodb-plugin.innodb-index # --- source include/have_innodb.inc - SET @old_innodb_file_per_table=@@innodb_file_per_table; # avoid a message about filed *.ibd file creation in the error log SET GLOBAL innodb_file_per_table=0; diff --git a/mysql-test/suite/innodb/t/innodb_bug57252.test b/mysql-test/suite/innodb/t/innodb_bug57252.test index 04c3ed0cea7..996533feabe 100644 --- a/mysql-test/suite/innodb/t/innodb_bug57252.test +++ b/mysql-test/suite/innodb/t/innodb_bug57252.test @@ -3,8 +3,6 @@ # http://bugs.mysql.com/57252 # --- source include/have_innodb.inc - -- disable_query_log -- disable_result_log diff --git a/mysql-test/suite/innodb/t/innodb_bug57904.test b/mysql-test/suite/innodb/t/innodb_bug57904.test index 1131e24844d..0669a2e53a5 100755 --- a/mysql-test/suite/innodb/t/innodb_bug57904.test +++ b/mysql-test/suite/innodb/t/innodb_bug57904.test @@ -1,8 +1,6 @@ # # Bug #57904 Missing constraint from information schema REFERENTIAL_CONSTRAINTS table # --- source include/have_innodb.inc - CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id)) ENGINE=INNODB; CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; diff --git a/mysql-test/suite/innodb/t/innodb_bug59307.test b/mysql-test/suite/innodb/t/innodb_bug59307.test deleted file mode 100644 index 31841fa6018..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug59307.test +++ /dev/null @@ -1,32 +0,0 @@ --- source include/have_innodb.inc -# Bug #59307 uninitialized value in rw_lock_set_writer_id_and_recursion_flag() -# when Valgrind instrumentation (UNIV_DEBUG_VALGRIND) is not enabled - -CREATE TABLE t1 ( - t1_int INT, - t1_time TIME -) ENGINE=innodb; - -CREATE TABLE t2 ( - t2_int int PRIMARY KEY, - t2_int2 INT -) ENGINE=INNODB; - -INSERT INTO t2 VALUES (); -INSERT INTO t1 VALUES (); - -SELECT * -FROM t1 AS t1a -WHERE NOT EXISTS - (SELECT * - FROM t1 AS t1b - WHERE t1b.t1_int NOT IN - (SELECT t2.t2_int - FROM t2 - WHERE t1b.t1_time LIKE t1b.t1_int - OR t1b.t1_time <> t2.t2_int2 - AND 6=7 - ) -) -; -DROP TABLE t1,t2; diff --git a/mysql-test/suite/innodb/t/innodb_bug59410.test b/mysql-test/suite/innodb/t/innodb_bug59410.test index 30bb0642679..89d3f1a0637 100644 --- a/mysql-test/suite/innodb/t/innodb_bug59410.test +++ b/mysql-test/suite/innodb/t/innodb_bug59410.test @@ -1,8 +1,6 @@ # # Bug#59410 read uncommitted: unlock row could not find a 3 mode lock on the record # --- source include/have_innodb.inc - # only interested that the following do not produce something like # InnoDB: Error: unlock row could not find a 2 mode lock on the record # in the error log diff --git a/mysql-test/suite/innodb/t/innodb_bug59641.test b/mysql-test/suite/innodb/t/innodb_bug59641.test index b933abd1d14..633f917692f 100644 --- a/mysql-test/suite/innodb/t/innodb_bug59641.test +++ b/mysql-test/suite/innodb/t/innodb_bug59641.test @@ -1,7 +1,8 @@ # Bug #59641 Prepared XA transaction causes shutdown hang after a crash -- source include/not_embedded.inc --- source include/have_innodb.inc + +FLUSH TABLES; CREATE TABLE t(a INT PRIMARY KEY, b INT)ENGINE=InnoDB; INSERT INTO t VALUES(2,2),(4,4),(8,8),(16,16),(32,32); diff --git a/mysql-test/suite/innodb/t/innodb_bug60049-master.opt b/mysql-test/suite/innodb/t/innodb_bug60049-master.opt index 22a5d4ed221..741d8685459 100644 --- a/mysql-test/suite/innodb/t/innodb_bug60049-master.opt +++ b/mysql-test/suite/innodb/t/innodb_bug60049-master.opt @@ -1 +1 @@ ---innodb_fast_shutdown=0 +--loose-innodb-fast-shutdown=0 diff --git a/mysql-test/suite/innodb/t/innodb_bug60049.test b/mysql-test/suite/innodb/t/innodb_bug60049.test index ec4e3b8de7e..10a6f3032ba 100644 --- a/mysql-test/suite/innodb/t/innodb_bug60049.test +++ b/mysql-test/suite/innodb/t/innodb_bug60049.test @@ -3,7 +3,11 @@ # This was a suspected bug (not a bug). -- source include/not_embedded.inc --- source include/have_innodb.inc + +# +# This test will not work if we don't do full shutdown of innodb +# +set @@global.innodb_fast_shutdown=0; CREATE TABLE t(a INT)ENGINE=InnoDB; RENAME TABLE t TO u; diff --git a/mysql-test/suite/innodb/t/innodb_bug60196.test b/mysql-test/suite/innodb/t/innodb_bug60196.test index ea85653f1af..6e2974fd4f8 100755 --- a/mysql-test/suite/innodb/t/innodb_bug60196.test +++ b/mysql-test/suite/innodb/t/innodb_bug60196.test @@ -7,7 +7,6 @@ --source include/not_embedded.inc --source include/have_lowercase2.inc --source include/have_case_insensitive_file_system.inc ---source include/have_innodb.inc # # Create test data. diff --git a/mysql-test/suite/innodb/t/innodb_index_large_prefix.test b/mysql-test/suite/innodb/t/innodb_index_large_prefix.test index 3ed8aa6e096..b43ff033903 100644 --- a/mysql-test/suite/innodb/t/innodb_index_large_prefix.test +++ b/mysql-test/suite/innodb/t/innodb_index_large_prefix.test @@ -1,7 +1,5 @@ # Testcase for worklog #5743: Lift the limit of index key prefixes ---source include/have_innodb.inc - let $innodb_file_format_orig=`select @@innodb_file_format`; let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; let $innodb_file_format_max_orig=`select @@innodb_file_format_max`; diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test index 2f983436177..d379e63f505 100644 --- a/mysql-test/suite/innodb/t/innodb_mysql.test +++ b/mysql-test/suite/innodb/t/innodb_mysql.test @@ -504,7 +504,11 @@ INSERT INTO t2 VALUES (),(); CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2 WHERE b =(SELECT a FROM t1 LIMIT 1); +--disable_query_log +--disable_result_log CONNECT (con1, localhost, root,,); +--enable_query_log +--enable_result_log CONNECTION default; DELIMITER |; @@ -559,6 +563,23 @@ drop table t1,t2; --echo # +--echo # Bug #39653: find_shortest_key in sql_select.cc does not consider +--echo # clustered primary keys +--echo # + +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT, + KEY (b,c)) ENGINE=INNODB; + +INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3), + (4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6), + (7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9), + (11,11,11,11,11,11); + +--query_vertical EXPLAIN SELECT COUNT(*) FROM t1 + +DROP TABLE t1; + +--echo # --echo # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may --echo # corrupt definition at engine --echo # @@ -839,6 +860,15 @@ CREATE INDEX b ON t1(a,b,c,d); DROP TABLE t1; +--echo # +--echo # ALTER TABLE IGNORE didn't ignore duplicates for unique add index +--echo # + +create table t1 (a int primary key, b int) engine = innodb; +insert into t1 values (1,1),(2,1); +alter ignore table t1 add unique `main` (b); +drop table t1; + --echo End of 5.1 tests --echo # @@ -901,6 +931,57 @@ SET SESSION sort_buffer_size = DEFAULT; DROP TABLE t1; --echo # +--echo # Test for bug #39932 "create table fails if column for FK is in different +--echo # case than in corr index". +--echo # +--disable_warnings +drop tables if exists t1, t2; +--enable_warnings +create table t1 (pk int primary key) engine=InnoDB; +--echo # Even although the below statement uses uppercased field names in +--echo # foreign key definition it still should be able to find explicitly +--echo # created supporting index. So it should succeed and should not +--echo # create any additional supporting indexes. +create table t2 (fk int, key x (fk), + constraint x foreign key (FK) references t1 (PK)) engine=InnoDB; +show create table t2; +drop table t2, t1; + +--echo # +--echo # Bug #663818: wrong result when BNLH is used +--echo # + +CREATE TABLE t1(pk int NOT NULL PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES + (1), (2), (11), (12), (13), (14), + (15), (16), (17), (18), (19); +CREATE TABLE t2(pk int NOT NULL PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t2 VALUES + (1), (10), (11), (12), (13), (14), + (15), (16), (17), (18), (19), (20), (21); + +SET SESSION join_buffer_size=10000; + +SET SESSION join_cache_level=3; +EXPLAIN +SELECT t1.pk FROM t1,t2 + WHERE t1.pk = t2.pk AND t2.pk <> 8; +SELECT t1.pk FROM t1,t2 + WHERE t1.pk = t2.pk AND t2.pk <> 8; + +SET SESSION join_cache_level=1; +EXPLAIN +SELECT t1.pk FROM t1,t2 + WHERE t1.pk = t2.pk AND t2.pk <> 8; +SELECT t1.pk FROM t1,t2 + WHERE t1.pk = t2.pk AND t2.pk <> 8; + +DROP TABLE t1,t2; + +SET SESSION join_cache_level=DEFAULT; +SET SESSION join_buffer_size=DEFAULT; + +--echo # --echo # Bug#668644: HAVING + ORDER BY --echo # @@ -934,6 +1015,41 @@ SELECT t1 .i AS f FROM t1, t2 DROP TABLE t1, t2; +--echo # +--echo # Test for bug #56619 - Assertion failed during +--echo # ALTER TABLE RENAME, DISABLE KEYS +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings +CREATE TABLE t1 (a INT, INDEX(a)) engine=innodb; +--disable_warnings +ALTER TABLE t1 RENAME TO t2, DISABLE KEYS; +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +--echo # +--echo # Bug#702322: HAVING with two ANDed predicates + ORDER BY +--echo # + +CREATE TABLE t1 (pk int PRIMARY KEY, a int, KEY (a)) ENGINE=InnoDB; +CREATE TABLE t2 (a int, KEY (a)) ENGINE=InnoDB; + +INSERT INTO t1 VALUES + (18,0),(9,10),(8,11),(2,15),(7,19),(1,20); + +SET SESSION join_cache_level = 0; + +EXPLAIN +SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a + WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11 + ORDER BY t1.a; +SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a + WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11 + ORDER BY t1.a; + +DROP TABLE t1,t2; --echo End of 5.3 tests @@ -1027,3 +1143,5 @@ CREATE INDEX a ON t1 (a); CREATE INDEX c on t1 (c); DROP TABLE t1; + +--echo End of 5.1 tests diff --git a/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test b/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test index 2bc89bf05d2..2ec048a0614 100644 --- a/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test +++ b/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test @@ -14,7 +14,6 @@ ###################################################################### ---source include/have_innodb.inc # Save innodb variables let $innodb_file_format_orig=`select @@innodb_file_format`; let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; diff --git a/mysql-test/suite/innodb/t/innodb_prefix_index_restart_server.test b/mysql-test/suite/innodb/t/innodb_prefix_index_restart_server.test index 587e6fe1f6b..eb9f3e454a2 100644 --- a/mysql-test/suite/innodb/t/innodb_prefix_index_restart_server.test +++ b/mysql-test/suite/innodb/t/innodb_prefix_index_restart_server.test @@ -14,7 +14,6 @@ # Test restart the server and "shutdown_server" looks for pid file # which is not there with embedded mode --source include/not_embedded.inc ---source include/have_innodb.inc # Save innodb variables let $innodb_file_format_orig=`select @@innodb_file_format`; let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; |