diff options
| author | Sergey Vojtovich <svoj@mariadb.org> | 2016-03-25 20:51:22 +0400 |
|---|---|---|
| committer | Sergey Vojtovich <svoj@mariadb.org> | 2016-03-31 10:11:16 +0400 |
| commit | 282497dd6d1049b4fb963641504c2733752845a7 (patch) | |
| tree | 7288d17c29fbbe9ac47ec51f6988fb954f59a361 /mysql-test/suite/innodb/r | |
| parent | 5052e2479e873461bebfcedbc674bbaf57d3c968 (diff) | |
| download | mariadb-git-282497dd6d1049b4fb963641504c2733752845a7.tar.gz | |
MDEV-6720 - enable connection log in mysqltest by default
Diffstat (limited to 'mysql-test/suite/innodb/r')
44 files changed, 615 insertions, 112 deletions
diff --git a/mysql-test/suite/innodb/r/auto_increment_dup.result b/mysql-test/suite/innodb/r/auto_increment_dup.result index def975af6dd..25a0a072c24 100644 --- a/mysql-test/suite/innodb/r/auto_increment_dup.result +++ b/mysql-test/suite/innodb/r/auto_increment_dup.result @@ -4,15 +4,13 @@ id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, k INT, c CHAR(1), UNIQUE KEY(k)) ENGINE=InnoDB; -# -# Connection 1 -# +connect con1, localhost, root; +connect con2, localhost, root; +connection con1; SET DEBUG_SYNC='ha_write_row_end SIGNAL continue2 WAIT_FOR continue1'; affected rows: 0 INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'; -# -# Connection 2 -# +connection con2; SET DEBUG_SYNC='ha_write_row_start WAIT_FOR continue2'; affected rows: 0 SET DEBUG_SYNC='after_mysql_insert SIGNAL continue1'; @@ -20,6 +18,7 @@ affected rows: 0 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2'; affected rows: 3 info: Records: 3 Duplicates: 0 Warnings: 0 +connection con1; affected rows: 4 info: Records: 3 Duplicates: 1 Warnings: 0 SET DEBUG_SYNC='RESET'; @@ -30,4 +29,7 @@ id k c 2 3 NULL 5 4 NULL 6 5 NULL +disconnect con1; +disconnect con2; +connection default; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/binlog_consistent.result b/mysql-test/suite/innodb/r/binlog_consistent.result index 1e095c6596e..0562a92ef68 100644 --- a/mysql-test/suite/innodb/r/binlog_consistent.result +++ b/mysql-test/suite/innodb/r/binlog_consistent.result @@ -1,5 +1,9 @@ RESET MASTER; -# Connection default +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connect con3,localhost,root,,; +connect con4,localhost,root,,; +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 @@ -10,28 +14,28 @@ Binlog_snapshot_file master-bin.000001 Binlog_snapshot_position <pos> BEGIN; INSERT INTO t1 VALUES (0, ""); -# Connection con1 +connection con1; BEGIN; INSERT INTO t1 VALUES (1, ""); -# Connection con2 +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 +connection default; COMMIT; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION WITH CONSISTENT SNAPSHOT; -# Connection con3 +connection con3; BEGIN; INSERT INTO t1 VALUES (3, ""); INSERT INTO t2 VALUES (3); -# Connection con4 +connection con4; BEGIN; INSERT INTO t1 VALUES (4, ""); COMMIT; -# Connection default +connection default; SELECT * FROM t1 ORDER BY a,b; a b 0 @@ -46,14 +50,14 @@ SELECT * FROM t2 ORDER BY a; a 2 3 -# Connection con1 +connection con1; COMMIT; -# Connection con2 +connection con2; COMMIT; -# Connection con3 +connection con3; COMMIT; FLUSH LOGS; -# Connection default +connection default; SELECT * FROM t1 ORDER BY a,b; a b 0 diff --git a/mysql-test/suite/innodb/r/group_commit.result b/mysql-test/suite/innodb/r/group_commit.result index 5321715df36..e2fb89767d4 100644 --- a/mysql-test/suite/innodb/r/group_commit.result +++ b/mysql-test/suite/innodb/r/group_commit.result @@ -3,35 +3,51 @@ 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,,; +connection con1; SET DEBUG_SYNC= "commit_before_get_LOCK_after_binlog_sync SIGNAL group1_running WAIT_FOR group2_queued"; INSERT INTO t1 VALUES ("con1"); +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_after_binlog_sync 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"); +connection con3; SET DEBUG_SYNC= "now WAIT_FOR group2_con2"; SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL group2_con3"; 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"; INSERT INTO t1 VALUES ("con4"); +connection default; 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"; +connection con1; +connection default; SELECT * FROM t1 ORDER BY a; a con1 +connection con5; SET DEBUG_SYNC= "commit_before_get_LOCK_after_binlog_sync 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"); +connection con6; SET DEBUG_SYNC= "now WAIT_FOR con5_leader"; SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con6_queued"; INSERT INTO t1 VALUES ("con6"); +connection default; SET DEBUG_SYNC= "now WAIT_FOR group3_con5"; SELECT * FROM t1 ORDER BY a; a @@ -45,6 +61,12 @@ con2 con3 con4 SET DEBUG_SYNC= "now SIGNAL group2_checked"; +connection con2; +connection con3; +connection con4; +connection con5; +connection con6; +connection default; SELECT * FROM t1 ORDER BY a; a con1 diff --git a/mysql-test/suite/innodb/r/group_commit_binlog_pos.result b/mysql-test/suite/innodb/r/group_commit_binlog_pos.result index 8a0eed2bf3c..616790c8b37 100644 --- a/mysql-test/suite/innodb/r/group_commit_binlog_pos.result +++ b/mysql-test/suite/innodb/r/group_commit_binlog_pos.result @@ -1,15 +1,22 @@ SET GLOBAL innodb_flush_log_at_trx_commit=3; 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,,; +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"; 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"; 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"; INSERT INTO t1 VALUES (3); +connection default; SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; SET DEBUG_SYNC= "now SIGNAL con1_loop_cont"; SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; @@ -20,11 +27,16 @@ a 0 1 2 +connection con2; +connection default; SET SESSION debug_dbug="+d,crash_dispatch_command_before"; SELECT 1; Got one of the listed errors +connection con1; Got one of the listed errors +connection con3; Got one of the listed errors +connection default; SELECT * FROM t1 ORDER BY a; a 0 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 index d85b6681360..f338761aea8 100644 --- 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 @@ -1,17 +1,24 @@ SET GLOBAL innodb_flush_log_at_trx_commit=3; 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,,; +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"; 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"; 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"; INSERT INTO t1 VALUES (3); +connection default; SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; SET DEBUG_SYNC= "now SIGNAL con1_loop_cont"; SET DEBUG_SYNC= "now WAIT_FOR con1_loop"; @@ -22,10 +29,15 @@ a 0 1 2 +connection con1; +connection con2; +connection default; SET SESSION debug_dbug="+d,crash_dispatch_command_before"; SELECT 1; Got one of the listed errors +connection con3; Got one of the listed errors +connection default; SELECT * FROM t1 ORDER BY a; a 0 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 index dd152fd3c9c..41e9f6a9d08 100644 --- a/mysql-test/suite/innodb/r/group_commit_no_optimize_thread.result +++ b/mysql-test/suite/innodb/r/group_commit_no_optimize_thread.result @@ -3,35 +3,51 @@ 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,,; +connection con1; SET DEBUG_SYNC= "commit_before_get_LOCK_after_binlog_sync SIGNAL group1_running WAIT_FOR group2_queued"; INSERT INTO t1 VALUES ("con1"); +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_after_binlog_sync SIGNAL group2_running"; SET DEBUG_SYNC= "commit_after_release_LOCK_log WAIT_FOR group3_committed"; 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"; 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"; INSERT INTO t1 VALUES ("con4"); +connection default; 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"; +connection con1; +connection default; SELECT * FROM t1 ORDER BY a; a con1 +connection con5; SET DEBUG_SYNC= "commit_before_get_LOCK_after_binlog_sync 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"); +connection con6; SET DEBUG_SYNC= "now WAIT_FOR con5_leader"; SET DEBUG_SYNC= "commit_after_prepare_ordered SIGNAL con6_queued"; INSERT INTO t1 VALUES ("con6"); +connection default; SET DEBUG_SYNC= "now WAIT_FOR group3_con5"; SELECT * FROM t1 ORDER BY a; a @@ -45,6 +61,12 @@ con2 con3 con4 SET DEBUG_SYNC= "now SIGNAL group2_checked"; +connection con2; +connection con3; +connection con4; +connection con5; +connection con6; +connection default; SELECT * FROM t1 ORDER BY a; a con1 diff --git a/mysql-test/suite/innodb/r/innodb-autoinc-61209.result b/mysql-test/suite/innodb/r/innodb-autoinc-61209.result index 9dd654702e2..f01008a80ae 100644 --- a/mysql-test/suite/innodb/r/innodb-autoinc-61209.result +++ b/mysql-test/suite/innodb/r/innodb-autoinc-61209.result @@ -1,28 +1,22 @@ DROP TABLE IF EXISTS bug_61209; CREATE TABLE bug_61209 (a INT auto_increment, PRIMARY KEY(a)) ENGINE=InnoDB; INSERT INTO bug_61209 VALUES (), (), (), (); - -# Connect con1 - -# Connect con2 - -# Connection con1 +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; SET SESSION auto_increment_increment=3; SET SESSION auto_increment_offset=2; SELECT GET_LOCK('a', 9); GET_LOCK('a', 9) 1 - -# Connection con2 +connection con2; SET SESSION auto_increment_increment=3; SET SESSION auto_increment_offset=2; INSERT INTO bug_61209 (a) VALUES (NULL), (NULL), (NULL + GET_LOCK('a', 10)); - -# Connection con1 reap +connection con1; INSERT INTO bug_61209 (a) VALUES (NULL), (NULL), (NULL); SELECT RELEASE_LOCK('a'); RELEASE_LOCK('a') 1 - -# Connection con2 reap +connection con2; DROP TABLE bug_61209; diff --git a/mysql-test/suite/innodb/r/innodb-blob.result b/mysql-test/suite/innodb/r/innodb-blob.result index a90f83945e0..ec5a4a8b0ac 100644 --- a/mysql-test/suite/innodb/r/innodb-blob.result +++ b/mysql-test/suite/innodb/r/innodb-blob.result @@ -8,17 +8,22 @@ UPDATE t1 SET a=a+2; ROLLBACK; BEGIN; UPDATE t1 SET b=CONCAT(b,'foo'); +connect con1,localhost,root,,; SET DEBUG_SYNC='now WAIT_FOR have_latch'; SELECT a, RIGHT(b,20) FROM t1; +connect con2,localhost,root,,; SET DEBUG_SYNC='now SIGNAL go1'; +connection con1; a RIGHT(b,20) 1 aaaaaaaaaaaaaaaaaaaa 2 bbbbbbbbbbbbbbbbbbbb +connection default; SET DEBUG_DBUG='+d,row_ins_extern_checkpoint'; SET DEBUG_SYNC='before_row_ins_extern_latch SIGNAL rec_not_blob WAIT_FOR crash'; ROLLBACK; BEGIN; INSERT INTO t1 VALUES (3,REPEAT('c',50000)); +connection con1; SET DEBUG_SYNC='now WAIT_FOR rec_not_blob'; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @@tx_isolation; @@ -36,6 +41,9 @@ a SET DEBUG_DBUG='+d,crash_commit_before'; INSERT INTO t2 VALUES (42); ERROR HY000: Lost connection to MySQL server during query +disconnect con1; +disconnect con2; +connection default; ERROR HY000: Lost connection to MySQL server during query CHECK TABLE t1; Table Op Msg_type Msg_text @@ -45,26 +53,34 @@ INSERT INTO t3 VALUES (2,REPEAT('g',7000),REPEAT('h',100)); SET DEBUG_SYNC='before_row_upd_extern SIGNAL have_latch WAIT_FOR go'; UPDATE t3 SET c=REPEAT('f',3000) WHERE a=1; +connect con1,localhost,root,,; SET DEBUG_SYNC='now WAIT_FOR have_latch'; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @@tx_isolation; @@tx_isolation READ-UNCOMMITTED SELECT a, RIGHT(b,20), RIGHT(c,20) FROM t3; +connect con2,localhost,root,,; SET DEBUG_SYNC='now SIGNAL go'; +connection con1; a RIGHT(b,20) RIGHT(c,20) 1 dddddddddddddddddddd ffffffffffffffffffff 2 gggggggggggggggggggg hhhhhhhhhhhhhhhhhhhh +disconnect con1; +connection default; 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 +connection con2; BEGIN; INSERT INTO t2 VALUES (347); +connection default; SET DEBUG_DBUG='+d,row_upd_extern_checkpoint'; SET DEBUG_SYNC='before_row_upd_extern SIGNAL have_latch WAIT_FOR crash'; UPDATE t3 SET c=REPEAT('i',3000) WHERE a=2; +connection con2; SET DEBUG_SYNC='now WAIT_FOR have_latch'; SELECT info FROM information_schema.processlist WHERE state = 'debug sync point: before_row_upd_extern'; @@ -73,6 +89,8 @@ UPDATE t3 SET c=REPEAT('i',3000) WHERE a=2 SET DEBUG_DBUG='+d,crash_commit_before'; COMMIT; ERROR HY000: Lost connection to MySQL server during query +disconnect con2; +connection default; ERROR HY000: Lost connection to MySQL server during query CHECK TABLE t1,t2,t3; Table Op Msg_type Msg_text @@ -87,11 +105,14 @@ SELECT a FROM t3; a 1 2 +connect con2,localhost,root,,; BEGIN; INSERT INTO t2 VALUES (33101); +connection default; SET DEBUG_DBUG='+d,row_upd_extern_checkpoint'; SET DEBUG_SYNC='after_row_upd_extern SIGNAL have_latch WAIT_FOR crash'; UPDATE t3 SET c=REPEAT('j',3000) WHERE a=2; +connection con2; SET DEBUG_SYNC='now WAIT_FOR have_latch'; SELECT info FROM information_schema.processlist WHERE state = 'debug sync point: after_row_upd_extern'; @@ -100,6 +121,8 @@ UPDATE t3 SET c=REPEAT('j',3000) WHERE a=2 SET DEBUG_DBUG='+d,crash_commit_before'; COMMIT; ERROR HY000: Lost connection to MySQL server during query +disconnect con2; +connection default; ERROR HY000: Lost connection to MySQL server during query CHECK TABLE t1,t2,t3; Table Op Msg_type Msg_text diff --git a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result index 96d2a3d462c..508d578193f 100644 --- a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result +++ b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result @@ -30,6 +30,8 @@ b x x x +connect con1,localhost,root,,; +connection con1; BEGIN; DELETE FROM t1 WHERE a=1; INSERT INTO t1 VALUES(1,'X',1); diff --git a/mysql-test/suite/innodb/r/innodb-consistent.result b/mysql-test/suite/innodb/r/innodb-consistent.result index 9115791b99c..c959dc1adb9 100644 --- a/mysql-test/suite/innodb/r/innodb-consistent.result +++ b/mysql-test/suite/innodb/r/innodb-consistent.result @@ -1,4 +1,7 @@ drop table if exists t1; +connect a,localhost,root,,; +connect b,localhost,root,,; +connection a; set session transaction isolation level read committed; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; create table t2 like t1; @@ -6,21 +9,25 @@ insert into t2 values (1),(2),(3),(4),(5),(6),(7); set autocommit=0; begin; replace into t1 select * from t2; +connection b; set session transaction isolation level read committed; set autocommit=0; delete from t2 where a=5; commit; delete from t2; commit; +connection a; commit; begin; insert into t1 select * from t2; +connection b; set session transaction isolation level read committed; set autocommit=0; delete from t2 where a=5; commit; delete from t2; commit; +connection a; commit; select * from t1; a @@ -33,3 +40,6 @@ a 7 drop table t1; drop table t2; +connection default; +disconnect a; +disconnect b; diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result index b151e20d321..d7aa6c46747 100644 --- a/mysql-test/suite/innodb/r/innodb-index.result +++ b/mysql-test/suite/innodb/r/innodb-index.result @@ -1055,6 +1055,9 @@ t2 CREATE TABLE `t2` ( affected rows: 1 DROP TABLE t2; DROP TABLE t1; +connect a,localhost,root,,; +connect b,localhost,root,,; +connection a; CREATE TABLE t1 (a INT, b CHAR(1)) ENGINE=InnoDB; INSERT INTO t1 VALUES (3,'a'),(3,'b'),(1,'c'),(0,'d'),(1,'e'); CREATE TABLE t2 (a INT, b CHAR(1)) ENGINE=InnoDB; @@ -1063,6 +1066,7 @@ CREATE TABLE t2c (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB; INSERT INTO t2 SELECT * FROM t1; INSERT INTO t2i SELECT * FROM t1; INSERT INTO t2c SELECT * FROM t1; +connection b; BEGIN; SELECT * FROM t1; a b @@ -1071,6 +1075,7 @@ a b 1 c 0 d 1 e +connection a; SET lock_wait_timeout=1; CREATE INDEX t1a ON t1(a); ERROR HY000: Lock wait timeout exceeded; try restarting transaction @@ -1086,6 +1091,7 @@ set @@sql_mode = @old_sql_mode; ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY; affected rows: 5 info: Records: 5 Duplicates: 0 Warnings: 0 +connection b; SELECT * FROM t2i; ERROR HY000: Table definition has changed, please retry transaction SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a; @@ -1153,6 +1159,9 @@ a b 1 e 3 a 3 b +connection default; +disconnect a; +disconnect b; alter table t2 add index t2a(b); ERROR 42000: Duplicate key name 't2a' alter table t2 drop index t2a, add index t2a(b); diff --git a/mysql-test/suite/innodb/r/innodb-lock.result b/mysql-test/suite/innodb/r/innodb-lock.result index 7191bcd238a..6604f2396a3 100644 --- a/mysql-test/suite/innodb/r/innodb-lock.result +++ b/mysql-test/suite/innodb/r/innodb-lock.result @@ -2,24 +2,31 @@ set global innodb_table_locks=1; select @@innodb_table_locks; @@innodb_table_locks 1 +connect con1,localhost,root,,; +connect con2,localhost,root,,; drop table if exists t1; set @@innodb_table_locks=1; +connection con1; create table t1 (id integer, x integer) engine=INNODB; insert into t1 values(0, 0); set autocommit=0; SELECT * from t1 where id = 0 FOR UPDATE; id x 0 0 +connection con2; set autocommit=0; lock table t1 write; +connection con1; update t1 set x=1 where id = 0; select * from t1; id x 0 1 commit; +connection con2; update t1 set x=2 where id = 0; commit; unlock tables; +connection con1; select * from t1; id x 0 2 @@ -39,7 +46,7 @@ commit; SELECT * from t1 where id = 0 FOR UPDATE; id x 0 0 -# Connection 'con2'. +connection con2; set autocommit=0; set @@innodb_table_locks=0; # The following statement should block because SQL-level lock @@ -47,7 +54,7 @@ set @@innodb_table_locks=0; # is commited. # Sending: lock table t1 write;; -# Connection 'con1'. +connection con1; # Wait until LOCK TABLE is blocked on SQL-level lock. # We should be able to do UPDATEs and SELECTs within transaction. update t1 set x=1 where id = 0; @@ -58,14 +65,14 @@ id x 2 2 # Unblock LOCK TABLE. commit; -# Connection 'con2'. +connection con2; # Reap LOCK TABLE. unlock tables; -# Connection 'con1'. +connection con1; select * from t1 where id = 0 for update; id x 0 1 -# Connection 'con2'. +connection con2; # The below statement should not be blocked as LOCK TABLES ... READ # does not take strong SQL-level lock on t1. SELECTs which do not # conflict with transaction in the first connections should not be @@ -86,7 +93,7 @@ id x 1 1 2 2 commit; -# Connection 'con1'. +connection con1; commit; drop table t1; # @@ -94,19 +101,27 @@ drop table t1; #fixed by re-fixing Bug#7975 #aka Bug#11759688 52020: InnoDB can still deadlock on just INSERT... # +connection default; CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; INSERT INTO t1 VALUES(3,1); BEGIN; INSERT IGNORE INTO t1 VALUES(3,14); Warnings: Warning 1062 Duplicate entry '3' for key 'PRIMARY' +connection con1; BEGIN; INSERT IGNORE INTO t1 VALUES(3,23); Warnings: Warning 1062 Duplicate entry '3' for key 'PRIMARY' SELECT * FROM t1 FOR UPDATE; +connection con2; +connection default; COMMIT; +connection con1; a b 3 1 COMMIT; +disconnect con1; +disconnect con2; +connection default; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-semi-consistent.result b/mysql-test/suite/innodb/r/innodb-semi-consistent.result index 78ef4a23149..f4541b589f7 100644 --- a/mysql-test/suite/innodb/r/innodb-semi-consistent.result +++ b/mysql-test/suite/innodb/r/innodb-semi-consistent.result @@ -1,4 +1,7 @@ drop table if exists t1,t2; +connect a,localhost,root,,; +connect b,localhost,root,,; +connection a; set binlog_format=mixed; set session transaction isolation level repeatable read; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; @@ -7,28 +10,36 @@ set autocommit=0; select * from t1 where a=3 lock in share mode; a 3 +connection b; set binlog_format=mixed; set session transaction isolation level repeatable read; set autocommit=0; update t1 set a=10 where a=5; ERROR HY000: Lock wait timeout exceeded; try restarting transaction commit; +connection a; commit; +connection b; set session transaction isolation level read committed; update t1 set a=10 where a=5; +connection a; select * from t1 where a=2 for update; ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1 where a=2 limit 1 for update; a 2 +connection b; update t1 set a=11 where a=6; update t1 set a=12 where a=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction update t1 set a=13 where a=1; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection a; commit; +connection b; update t1 set a=14 where a=1; commit; +connection a; select * from t1; a 14 @@ -39,10 +50,17 @@ a 11 7 drop table t1; +connection default; +disconnect a; +disconnect b; create table t1 (a int, b int) engine=myisam; create table t2 (c int, d int, key (c)) engine=innodb; insert into t1 values (1,1); insert into t2 values (1,2); +connect a,localhost,root,,; +connection a; set session transaction isolation level read committed; delete from t1 using t1 join t2 on t1.a = t2.c where t2.d in (1); +connection default; +disconnect a; drop table t1, t2; diff --git a/mysql-test/suite/innodb/r/innodb-timeout.result b/mysql-test/suite/innodb/r/innodb-timeout.result index bb71ba5cb3d..f252ebac314 100644 --- a/mysql-test/suite/innodb/r/innodb-timeout.result +++ b/mysql-test/suite/innodb/r/innodb-timeout.result @@ -1,4 +1,7 @@ set global innodb_lock_wait_timeout=42; +connect a,localhost,root,,; +connect b,localhost,root,,; +connection a; select @@innodb_lock_wait_timeout; @@innodb_lock_wait_timeout 42 @@ -6,6 +9,7 @@ set innodb_lock_wait_timeout=1; select @@innodb_lock_wait_timeout; @@innodb_lock_wait_timeout 1 +connection b; select @@innodb_lock_wait_timeout; @@innodb_lock_wait_timeout 42 @@ -17,24 +21,39 @@ set innodb_lock_wait_timeout=10; select @@innodb_lock_wait_timeout; @@innodb_lock_wait_timeout 10 +connect c,localhost,root,,; +connection c; select @@innodb_lock_wait_timeout; @@innodb_lock_wait_timeout 347 +disconnect c; +connection a; SET @connection_b_id = <connection_b_id>; create table t1(a int primary key)engine=innodb; begin; insert into t1 values(1),(2),(3); +connection b; select * from t1 for update; +connection a; commit; +connection b; a 1 2 3 +connection a; begin; insert into t1 values(4); +connection b; set innodb_lock_wait_timeout=3; select * from t1 for update; +connection a; commit; +connection b; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +disconnect b; +connection a; +disconnect a; +connection default; drop table t1; set global innodb_lock_wait_timeout=<initial_timeout>; diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index 3813cfcda14..73920134185 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -2492,34 +2492,46 @@ SET FOREIGN_KEY_CHECKS=1; INSERT INTO t2 VALUES(3); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`)) DROP TABLE t2; +connect a,localhost,root,,; +connect b,localhost,root,,; +connection a; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; insert into t1 values (1),(2); set autocommit=0; checksum table t1; Table Checksum test.t1 1531596814 +connection b; insert into t1 values(3); +connection a; checksum table t1; Table Checksum test.t1 1531596814 +connection a; commit; checksum table t1; Table Checksum test.t1 2050879373 commit; drop table t1; +connection a; create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; insert into t1 values (1),(2); set autocommit=1; checksum table t1; Table Checksum test.t1 1531596814 +connection b; set autocommit=1; insert into t1 values(3); +connection a; checksum table t1; Table Checksum test.t1 2050879373 drop table t1; +connection default; +disconnect a; +disconnect b; set foreign_key_checks=0; create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb; create table t1(a char(10) primary key, b varchar(20)) engine = innodb; @@ -2784,6 +2796,9 @@ select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id) where mm.id is null lock in share mode; id f_id f drop table t1,t2; +connect a,localhost,root,,; +connect b,localhost,root,,; +connection a; create table t1(a int not null, b int, primary key(a)) engine=innodb; insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); commit; @@ -2791,15 +2806,24 @@ SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; update t1 set b = 5 where b = 1; +connection b; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; select * from t1 where a = 7 and b = 3 for update; a b 7 3 +connection a; commit; +connection b; commit; drop table t1; +connection default; +disconnect a; +disconnect b; +connect a,localhost,root,,; +connect b,localhost,root,,; +connection a; create table t1(a int not null, b int, primary key(a)) engine=innodb; insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2); commit; @@ -2813,12 +2837,21 @@ a b 5 1 6 2 update t1 set b = 5 where b = 1; +connection b; set autocommit = 0; select * from t1 where a = 2 and b = 2 for update; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection a; commit; +connection b; commit; +connection default; +disconnect a; +disconnect b; drop table t1; +connect a,localhost,root,,; +connect b,localhost,root,,; +connection a; create table t1(a int not null, b int, primary key(a)) engine=innodb; insert into t1 values (1,2),(5,3),(4,2); create table t2(d int not null, e int, primary key(d)) engine=innodb; @@ -2830,6 +2863,7 @@ d e 3 1 8 6 12 1 +connection b; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; @@ -2838,8 +2872,23 @@ update t1 set b = (select e from t2 where a = d); create table t3(d int not null, e int, primary key(d)) engine=innodb select * from t2; commit; +connection a; commit; +connection default; +disconnect a; +disconnect b; drop table t1, t2, t3; +connect a,localhost,root,,; +connect b,localhost,root,,; +connect c,localhost,root,,; +connect d,localhost,root,,; +connect e,localhost,root,,; +connect f,localhost,root,,; +connect g,localhost,root,,; +connect h,localhost,root,,; +connect i,localhost,root,,; +connect j,localhost,root,,; +connection a; create table t1(a int not null, b int, primary key(a)) engine=innodb; insert into t1 values (1,2),(5,3),(4,2); create table t2(a int not null, b int, primary key(a)) engine=innodb; @@ -2861,52 +2910,82 @@ a b 3 1 8 6 12 1 +connection b; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; insert into t1 select * from t2; +connection c; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; update t3 set b = (select b from t2 where a = d); +connection d; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2; +connection e; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into t5 (select * from t2 lock in share mode); +connection f; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; update t6 set e = (select b from t2 where a = d lock in share mode); +connection g; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode; +connection h; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; insert into t8 (select * from t2 for update); +connection i; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; update t9 set e = (select b from t2 where a = d for update); +connection j; SET binlog_format='MIXED'; set autocommit = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update; +connection b; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection c; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection d; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection e; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection f; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection g; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection h; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection i; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection j; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection a; commit; +connection default; +disconnect a; +disconnect b; +disconnect c; +disconnect d; +disconnect e; +disconnect f; +disconnect g; +disconnect h; +disconnect i; +disconnect j; drop table t1, t2, t3, t5, t6, t8, t9; CREATE TABLE t1 (DB_ROW_ID int) engine=innodb; ERROR 42000: Incorrect column name 'DB_ROW_ID' @@ -3044,6 +3123,9 @@ id -10 1 DROP TABLE t1; +CONNECT c1,localhost,root,,; +CONNECT c2,localhost,root,,; +connection c1; SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; @@ -3055,24 +3137,33 @@ CREATE TABLE t1 ( a int ) ENGINE=InnoDB; CREATE TABLE t2 LIKE t1; SELECT * FROM t2; a +connection c2; SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (1); COMMIT; +connection c1; SELECT * FROM t1 WHERE a=1; a 1 +disconnect c1; +disconnect c2; +CONNECT c1,localhost,root,,; +CONNECT c2,localhost,root,,; +connection c1; SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; SELECT * FROM t2; a +connection c2; SET binlog_format='MIXED'; SET TX_ISOLATION='read-committed'; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (2); COMMIT; +connection c1; SELECT * FROM t1 WHERE a=2; a 2 @@ -3081,6 +3172,9 @@ a 2 DROP TABLE t1; DROP TABLE t2; +disconnect c1; +disconnect c2; +connection default; create table t1 (i int, j int) engine=innodb; insert into t1 (i, j) values (1, 1), (2, 2); update t1 set j = 2; @@ -3094,6 +3188,7 @@ where table_schema='test' and table_name = 't1'; table_comment data_free_is_set this is a comment 1 drop table t1; +connection default; CREATE TABLE t1 ( c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, c2 VARCHAR(128) NOT NULL, @@ -3113,6 +3208,7 @@ AUTO_INCREMENT 200 DROP TABLE t2; DROP TABLE t1; +connection default; CREATE TABLE t1 (c1 int default NULL, c2 int default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; diff --git a/mysql-test/suite/innodb/r/innodb_blob_unrecoverable_crash.result b/mysql-test/suite/innodb/r/innodb_blob_unrecoverable_crash.result index 9f6b7ca6a23..c467193bca7 100644 --- a/mysql-test/suite/innodb/r/innodb_blob_unrecoverable_crash.result +++ b/mysql-test/suite/innodb/r/innodb_blob_unrecoverable_crash.result @@ -1,6 +1,7 @@ call mtr.add_suppression("InnoDB: The total blob data length"); SET GLOBAL max_allowed_packet = 100*1024*1024; -# Connection big_packets: +connect big_packets,localhost,root,,; +connection big_packets; CREATE TABLE t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB; INSERT INTO t1 (a, b) VALUES (1, '1'); INSERT INTO t1 (a, b) VALUES (2, '2'); @@ -10,9 +11,9 @@ INSERT INTO t1 (a, b) VALUES (5, '5'); start transaction; INSERT INTO t1 (a, b) VALUES (6, REPEAT('a', 20*1024*1024)); ERROR 42000: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size. -# Connection default: +connection default; # Quick shutdown and restart server -# Connection default: +connection default; SELECT a FROM t1; a 1 diff --git a/mysql-test/suite/innodb/r/innodb_bug12400341.result b/mysql-test/suite/innodb/r/innodb_bug12400341.result index c382bd12616..31a064e624d 100644 --- a/mysql-test/suite/innodb/r/innodb_bug12400341.result +++ b/mysql-test/suite/innodb/r/innodb_bug12400341.result @@ -16,11 +16,13 @@ CREATE TABLE mysqltest.transtable (id int unsigned NOT NULL PRIMARY KEY, val int select count(*) from information_schema.processlist; count(*) 33 +connection default; CREATE TABLE mysqltest.testtable (id int unsigned not null primary key) ENGINE=InnoDB; ERROR HY000: Can't create table `mysqltest`.`testtable` (errno: 177 "Too many active concurrent transactions") select count(*) from information_schema.processlist; count(*) 33 +connection default; select count(*) from information_schema.processlist; count(*) 33 diff --git a/mysql-test/suite/innodb/r/innodb_bug14007649.result b/mysql-test/suite/innodb/r/innodb_bug14007649.result index 50de5868be4..659881b55a6 100644 --- a/mysql-test/suite/innodb/r/innodb_bug14007649.result +++ b/mysql-test/suite/innodb/r/innodb_bug14007649.result @@ -14,7 +14,11 @@ t1 CREATE TABLE `t1` ( KEY `i2` (`f2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 insert into `t1` (rowid, f1, f2) values (1, 1, 10), (2, 1, NULL); +connect a,localhost,root,,; +connect b,localhost,root,,; +connection a; start transaction with consistent snapshot; +connection b; start transaction; update t1 set f2 = 4 where f1 = 1 and f2 is null; (b) Number of rows updated: @@ -29,6 +33,7 @@ rowid f1 f2 2 1 4 3 1 NULL commit; +connection a; (a) Before the update statement is executed. select rowid, f1, f2 from t1; rowid f1 f2 @@ -53,4 +58,7 @@ rowid f1 f2 1 1 10 2 1 4 3 1 6 +connection default; +disconnect a; +disconnect b; drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb_bug14704286.result b/mysql-test/suite/innodb/r/innodb_bug14704286.result index 9de42cb01c8..f84d5206e07 100644 --- a/mysql-test/suite/innodb/r/innodb_bug14704286.result +++ b/mysql-test/suite/innodb/r/innodb_bug14704286.result @@ -6,8 +6,13 @@ insert into t1 values (10,10,10,10),(11,11,11,11),(12,12,12,12),(13,13,13,13),(14,14,14,14), (15,15,15,15),(16,16,16,16),(17,17,17,17),(18,18,18,18),(19,19,19,19), (20,20,20,20); +connect conn1, localhost, root,,; +connect conn2, localhost, root,,; +connect conn3, localhost, root,,; +connection conn1; use test; start transaction with consistent snapshot; +connection conn2; use test; CREATE PROCEDURE update_t1() BEGIN @@ -46,8 +51,15 @@ id value value2 value3 18 18 18 18 19 19 19 19 20 20 20 20 +connection conn1; select * from t1 force index(value) where value=12; +connection conn3; kill query @id; +connection conn1; ERROR 70100: Query execution was interrupted +connection default; +disconnect conn1; +disconnect conn2; +disconnect conn3; drop procedure if exists update_t1; drop table if exists t1; diff --git a/mysql-test/suite/innodb/r/innodb_bug38231.result b/mysql-test/suite/innodb/r/innodb_bug38231.result index 195775f74c8..0ecdc13b048 100644 --- a/mysql-test/suite/innodb/r/innodb_bug38231.result +++ b/mysql-test/suite/innodb/r/innodb_bug38231.result @@ -1 +1,2 @@ SET storage_engine=InnoDB; +connection default; diff --git a/mysql-test/suite/innodb/r/innodb_bug42419.result b/mysql-test/suite/innodb/r/innodb_bug42419.result index f304bb634cb..cf10ef3e12a 100644 --- a/mysql-test/suite/innodb/r/innodb_bug42419.result +++ b/mysql-test/suite/innodb/r/innodb_bug42419.result @@ -1,3 +1,4 @@ +connection default; CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b INT) ENGINE = InnoDB; INSERT INTO t1 VALUES (1,1),(2,2),(3,3); COMMIT; @@ -5,13 +6,18 @@ SET AUTOCOMMIT = 0; CREATE TEMPORARY TABLE t1_tmp ( b INT ); INSERT INTO t1_tmp (b) SELECT b FROM t1 WHERE a = 3; INSERT INTO t1_tmp (b) SELECT b FROM t1 WHERE a = 2; +connect user2,localhost,root,,,$MASTER_MYPORT,$MASTER_MYSOCK; SET AUTOCOMMIT = 0; CREATE TEMPORARY TABLE t2_tmp ( a int, new_a int ); INSERT INTO t2_tmp VALUES (1,51),(2,52),(3,53); UPDATE t1 SET a = (SELECT new_a FROM t2_tmp WHERE t2_tmp.a = t1.a) WHERE a = 1; UPDATE t1 SET a = (SELECT new_a FROM t2_tmp WHERE t2_tmp.a = t1.a) WHERE a = 2; +connection default; INSERT INTO t1_tmp (b) SELECT b FROM t1 WHERE a = 1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +connection user2; Reap the server message for connection user2 UPDATE t1 ... UPDATE t1 SET a = (SELECT new_a FROM t2_tmp WHERE t2_tmp.a = t1.a) WHERE a = 3; +connection default; +disconnect user2; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_bug47622.result b/mysql-test/suite/innodb/r/innodb_bug47622.result index f5d13711c52..e520d1491ed 100644 --- a/mysql-test/suite/innodb/r/innodb_bug47622.result +++ b/mysql-test/suite/innodb/r/innodb_bug47622.result @@ -1,3 +1,5 @@ +connect a,localhost,root,,; +connect b,localhost,root,,; CREATE TABLE bug47622( `rule_key` int(11) NOT NULL DEFAULT '0', `seq` smallint(6) NOT NULL DEFAULT '0', @@ -6,8 +8,10 @@ CREATE TABLE bug47622( `else_ind` TINYINT NOT NULL, KEY IDX_A (`arg_id`) ) ENGINE=InnoDB; +connection a; ALTER TABLE bug47622 ADD UNIQUE IDX_B (rule_key,else_ind,seq,action,arg_id); drop index IDX_B on bug47622; +connection b; create index idx on bug47622(seq, arg_id); ALTER TABLE bug47622 ADD UNIQUE IDX_X (rule_key,else_ind,seq,action); drop table bug47622; diff --git a/mysql-test/suite/innodb/r/innodb_bug49164.result b/mysql-test/suite/innodb/r/innodb_bug49164.result index 9456702e1d0..857cd2851f2 100644 --- a/mysql-test/suite/innodb/r/innodb_bug49164.result +++ b/mysql-test/suite/innodb/r/innodb_bug49164.result @@ -1,3 +1,6 @@ +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; SET tx_isolation = 'READ-COMMITTED'; CREATE TABLE bug49164 (a INT, b BIGINT, c TINYINT, PRIMARY KEY (a, b)) ENGINE=InnoDB; @@ -17,6 +20,7 @@ a b c 3 3 3 begin; update bug49164 set c=7; +connection con2; SET tx_isolation = 'READ-COMMITTED'; begin; select * from bug49164; @@ -27,16 +31,22 @@ a b c commit; begin; update bug49164 set c=6 where a=1 and b=1; +connection con1; rollback; select * from bug49164; a b c 1 1 1 2 2 2 3 3 3 +connection con2; commit; +connection con1; select * from bug49164; a b c 1 1 6 2 2 2 3 3 3 +connection default; +disconnect con1; +disconnect con2; drop table bug49164; diff --git a/mysql-test/suite/innodb/r/innodb_bug51920.result b/mysql-test/suite/innodb/r/innodb_bug51920.result index 7ded141c239..ddb9e29fab2 100644 --- a/mysql-test/suite/innodb/r/innodb_bug51920.result +++ b/mysql-test/suite/innodb/r/innodb_bug51920.result @@ -4,10 +4,16 @@ BEGIN; SELECT * FROM bug51920 FOR UPDATE; i 1 +connect con1,localhost,root,,; +connection con1; UPDATE bug51920 SET i=2; +connection default; SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO="UPDATE bug51920 SET i=2" INTO @thread_id; KILL @thread_id; +connection con1; Got one of the listed errors +connection default; DROP TABLE bug51920; +disconnect con1; diff --git a/mysql-test/suite/innodb/r/innodb_bug52663.result b/mysql-test/suite/innodb/r/innodb_bug52663.result index 89add18617b..348a7b21022 100644 --- a/mysql-test/suite/innodb/r/innodb_bug52663.result +++ b/mysql-test/suite/innodb/r/innodb_bug52663.result @@ -3,23 +3,30 @@ create table innodb_bug52663 (what varchar(5), id integer, count integer, primar (what, id)) engine=innodb; insert into innodb_bug52663 values ('total', 0, 0); begin; +connect addconroot, localhost, root,,; +connection addconroot; set session transaction isolation level read committed; begin; +connection default; update innodb_bug52663 set count = count + 1 where what = 'total' and id = 0; select * from innodb_bug52663; what id count total 0 1 +connection addconroot; update innodb_bug52663 set count = count + 1 where what = 'total' and id = 0; ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from innodb_bug52663; what id count total 0 0 +connection default; commit; +connection addconroot; update innodb_bug52663 set count = count + 1 where what = 'total' and id = 0; commit; select * from innodb_bug52663; what id count total 0 2 +connection default; select * from innodb_bug52663; what id count total 0 2 diff --git a/mysql-test/suite/innodb/r/innodb_bug53756.result b/mysql-test/suite/innodb/r/innodb_bug53756.result index 2a2a30e2482..6b44479677f 100644 --- a/mysql-test/suite/innodb/r/innodb_bug53756.result +++ b/mysql-test/suite/innodb/r/innodb_bug53756.result @@ -19,75 +19,64 @@ pk c1 2 22 3 33 4 44 - -# connection con1 deletes row 1 +connect con1,localhost,root,,; START TRANSACTION; SELECT @@tx_isolation; @@tx_isolation READ-COMMITTED DELETE FROM bug_53756 WHERE pk=1; - -# connection con2 deletes row 2 +connect con2,localhost,root,,; START TRANSACTION; SELECT @@tx_isolation; @@tx_isolation READ-COMMITTED DELETE FROM bug_53756 WHERE pk=2; - -# connection con3 updates row 3 +connect con3,localhost,root,,; START TRANSACTION; SELECT @@tx_isolation; @@tx_isolation READ-COMMITTED UPDATE bug_53756 SET c1=77 WHERE pk=3; - -# connection con4 updates row 4 +connect con4,localhost,root,,; START TRANSACTION; SELECT @@tx_isolation; @@tx_isolation READ-COMMITTED UPDATE bug_53756 SET c1=88 WHERE pk=4; - -# connection con5 inserts row 5 +connect con5,localhost,root,,; START TRANSACTION; SELECT @@tx_isolation; @@tx_isolation READ-COMMITTED INSERT INTO bug_53756 VALUES(5, 55); - -# connection con6 inserts row 6 +connect con6,localhost,root,,; START TRANSACTION; SELECT @@tx_isolation; @@tx_isolation READ-COMMITTED INSERT INTO bug_53756 VALUES(6, 66); - -# connection con1 commits. +connection con1; COMMIT; - -# connection con3 commits. +connection con3; COMMIT; - -# connection con4 rolls back. +connection con4; ROLLBACK; - -# connection con6 rolls back. +connection con6; ROLLBACK; # The connections 2 and 5 stay open. -# connection default selects resulting data. # Delete of row 1 was committed. # Update of row 3 was committed. # Due to isolation level read committed, these should be included. # All other changes should not be included. +connection default; SELECT * FROM bug_53756; pk c1 2 22 3 77 4 44 - -# connection default +connection default; # # Crash server. START TRANSACTION; @@ -96,8 +85,12 @@ SET SESSION debug_dbug="+d,crash_commit_before"; COMMIT; ERROR HY000: Lost connection to MySQL server during query -# -# disconnect con1, con2, con3, con4, con5, con6. +disconnect con1; +disconnect con2; +disconnect con3; +disconnect con4; +disconnect con5; +disconnect con6; # # Restart server. diff --git a/mysql-test/suite/innodb/r/innodb_bug59641.result b/mysql-test/suite/innodb/r/innodb_bug59641.result index 9c5d363e5fe..f22d39654de 100644 --- a/mysql-test/suite/innodb/r/innodb_bug59641.result +++ b/mysql-test/suite/innodb/r/innodb_bug59641.result @@ -7,15 +7,21 @@ XA START '123'; INSERT INTO t VALUES(1,1); XA END '123'; XA PREPARE '123'; +CONNECT con1,localhost,root,,; +connection con1; XA START '456'; INSERT INTO t VALUES(3,47),(5,67); UPDATE t SET b=2*b WHERE a BETWEEN 5 AND 8; XA END '456'; XA PREPARE '456'; +CONNECT con2,localhost,root,,; +connection con2; XA START '789'; UPDATE t SET b=4*a WHERE a=32; XA END '789'; XA PREPARE '789'; +CONNECT con3,localhost,root,,; +connection con3; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM t; a b diff --git a/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result b/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result index d3ad9cc9065..ff32bf694cb 100644 --- a/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result +++ b/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result @@ -4,6 +4,11 @@ select @@global.innodb_stats_persistent; 0 set global innodb_defragment_stats_accuracy = 80; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), c INT, KEY second(a, b),KEY third(c)) ENGINE=INNODB; +connect con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect con3,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connect con4,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection default; SET @@global.innodb_defragment_n_pages = 20; select count(*) from t1; count(*) @@ -32,11 +37,25 @@ count(stat_value) > 0 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); count(stat_value) > 0 1 +connection con1; optimize table t1;; +connection default; INSERT INTO t1 VALUES (400000, REPEAT('A', 256),300000);; +connection con2; INSERT INTO t1 VALUES (500000, REPEAT('A', 256),400000);; +connection con3; DELETE FROM t1 where a between 1 and 100;; +connection con4; UPDATE t1 SET c = c + 1 where c between 2000 and 8000;; +connection con1; +connection con2; +connection con3; +connection con4; +connection default; +disconnect con1; +disconnect con2; +disconnect con3; +disconnect con4; optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK diff --git a/mysql-test/suite/innodb/r/innodb_defragment.result b/mysql-test/suite/innodb/r/innodb_defragment.result index 5f3fd523946..c42cbd972f8 100644 --- a/mysql-test/suite/innodb/r/innodb_defragment.result +++ b/mysql-test/suite/innodb/r/innodb_defragment.result @@ -31,7 +31,13 @@ count(stat_value) select count(*) from t1; count(*) 10004 +connect con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; +connection con1; call defragment(); +connection default; +connection con1; +connection default; +disconnect con1; optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK diff --git a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result index 26ed20ca730..3a17d4a273b 100644 --- a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result @@ -13,6 +13,8 @@ BEGIN; SELECT b FROM t2 WHERE b=7 FOR UPDATE; b 7 +CONNECT addconroot, localhost, root,,; +connection addconroot; BEGIN; SELECT b FROM t2 WHERE b=7 FOR UPDATE; ERROR HY000: Lock wait timeout exceeded; try restarting transaction @@ -25,6 +27,8 @@ ERROR HY000: Lock wait timeout exceeded; try restarting transaction SELECT * FROM t1; a b 1070109 99 +connection default; +disconnect addconroot; DROP TABLE t2, t1; # End of 5.0 tests # @@ -40,10 +44,14 @@ begin; select * from t1 where b=5 for update; a b 5 5 +connect con1, localhost, root,,; +connection con1; insert ignore into t1 (b) select a as b from t1; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection default; # Cleanup # +disconnect con1; commit; set autocommit=default; drop table t1; @@ -51,19 +59,27 @@ drop table t1; # Bug #37183 insert ignore into .. select ... hangs # after deadlock was encountered # +connect con1,localhost,root,,; create table t1(id int primary key,v int)engine=innodb; insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); create table t2 like t1; +connection con1; begin; update t1 set v=id*2 where id=1; +connection default; begin; update t1 set v=id*2 where id=2; +connection con1; update t1 set v=id*2 where id=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection default; insert ignore into t2 select * from t1 where id=1; ERROR HY000: Lock wait timeout exceeded; try restarting transaction rollback; +connection con1; rollback; +connection default; +disconnect con1; drop table t1, t2; # # Bug#41756 Strange error messages about locks from InnoDB @@ -162,8 +178,8 @@ begin; select 1 from t1 natural join (select 2 as a, 1 as b union all select 2 as a, 2 as b) as t2 for update; 1 -# -# Switching to connection con1 +connect con1,localhost,root,,; +connection con1; # We should be able to delete all records from t1 except (2, null), # since they were not locked. begin; @@ -184,8 +200,7 @@ delete from t1 where a in (19,20); delete from t1; ERROR HY000: Lock wait timeout exceeded; try restarting transaction rollback; -# -# Switching to connection default +connection default; # # Show that the original contents of t1 is intact: select * from t1; @@ -247,8 +262,7 @@ a b 18 1 19 1 20 1 -# -# Switching to connection con1 +connection con1; begin; # Since there is another distinct record in the derived table # the previous matching record in t1 -- (2,null) -- was unlocked. @@ -278,8 +292,7 @@ a b 19 1 20 1 commit; -# -# Switching to connection default +connection default; rollback; begin; # @@ -339,15 +352,16 @@ select 1 from t1 natural join (select 3 as a, 2 as b union all select 3 as a, 1 as b) as t2 for update; 1 1 -# Switching to connection con1 +connection con1; # # We should not be able to delete record (3,1) from t1, # (previously it was possible). # delete from t1 where a=3; ERROR HY000: Lock wait timeout exceeded; try restarting transaction -# Switching to connection default +connection default; commit; +disconnect con1; set @@session.tx_isolation=default; drop table t1; # diff --git a/mysql-test/suite/innodb/r/innodb_mutexes.result b/mysql-test/suite/innodb/r/innodb_mutexes.result index 129d7f0762b..ff7bfa6f32a 100644 --- a/mysql-test/suite/innodb/r/innodb_mutexes.result +++ b/mysql-test/suite/innodb/r/innodb_mutexes.result @@ -1,3 +1,6 @@ +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connect con3,localhost,root,,; create table t1(a int not null primary key, b int, c int,d CHAR(100)) engine=innodb; create procedure innodb_insert_proc (repeat_count int) begin @@ -13,9 +16,20 @@ set autocommit=0; call innodb_insert_proc(20000); commit; set autocommit=1; +connection con1; delete from t1 where a between 1000 and 1300; +connection con2; update t1 set b=b+1 where a between 2000 and 2600; +connection con3; insert into t1 select a+30000,b,c,d from t1 where a between 3000 and 4000; +connection default; delete from t1 where a between 6000 and 7000; +connection con1; +connection con2; +connection con3; +connection default; +disconnect con1; +disconnect con2; +disconnect con3; drop procedure innodb_insert_proc; drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 61eec8d4155..1f40887b9fc 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -109,12 +109,19 @@ id1 2 DROP TABLE t1, t2; create table t1 (c1 int) engine=innodb; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con2; handler t1 open; handler t1 read first; c1 +disconnect con2; +connection con1; Before and after comparison 0 +connection default; drop table t1; +disconnect con1; CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1) ENGINE=INNODB CHARACTER SET UTF8; INSERT INTO t1 (c1) VALUES ('1a'); @@ -399,12 +406,16 @@ innodb_rollback_on_timeout OFF create table t1 (a int unsigned not null primary key) engine = innodb; insert into t1 values (1); commit; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con2; begin work; insert into t1 values (2); select * from t1; a 1 2 +connection con1; begin work; insert into t1 values (5); select * from t1; @@ -418,21 +429,27 @@ a 1 5 commit; +connection con2; select * from t1; a 1 2 commit; +connection default; select * from t1; a 1 2 5 drop table t1; +disconnect con1; +disconnect con2; set @save_qcache_size=@@global.query_cache_size; set @save_qcache_type=@@global.query_cache_type; set global query_cache_size=10*1024*1024; set global query_cache_type=1; +connect con1,localhost,root,,; +connection con1; drop table if exists `test`; Warnings: Note 1051 Unknown table 'test.test' @@ -440,6 +457,9 @@ CREATE TABLE `test` (`test1` varchar(3) NOT NULL, `test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678'); +disconnect con1; +connect con2,localhost,root,,; +connection con2; select * from test; test1 test2 tes 5678 @@ -452,6 +472,8 @@ flush tables; select * from test; test1 test2 tes 1234 +disconnect con2; +connection default; drop table test; set global query_cache_type=@save_qcache_type; set global query_cache_size=@save_qcache_size; @@ -462,12 +484,16 @@ innodb_rollback_on_timeout OFF create table t1 (a int unsigned not null primary key) engine = innodb; insert into t1 values (1); commit; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con2; begin work; insert into t1 values (2); select * from t1; a 1 2 +connection con1; begin work; insert into t1 values (5); select * from t1; @@ -481,17 +507,21 @@ a 1 5 commit; +connection con2; select * from t1; a 1 2 commit; +connection default; select * from t1; a 1 2 5 drop table t1; +disconnect con1; +disconnect con2; create table t1( id int auto_increment, c char(1) not null, @@ -637,18 +667,22 @@ Warnings: Warning 1932 Table 'test.bug29807' doesn't exist in engine CREATE TABLE t1 (a INT) ENGINE=InnoDB; CREATE TABLE t2 (a INT) ENGINE=InnoDB; -switch to connection c1 +CONNECT c1,localhost,root,,; +CONNECT c2,localhost,root,,; +connection c1; SET AUTOCOMMIT=0; INSERT INTO t2 VALUES (1); -switch to connection c2 +connection c2; SET AUTOCOMMIT=0; LOCK TABLES t1 READ, t2 READ; ERROR HY000: Lock wait timeout exceeded; try restarting transaction -switch to connection c1 +connection c1; COMMIT; INSERT INTO t1 VALUES (1); -switch to connection default +connection default; SET AUTOCOMMIT=default; +disconnect c1; +disconnect c2; DROP TABLE t1,t2; CREATE TABLE t1 ( id int NOT NULL auto_increment PRIMARY KEY, @@ -790,12 +824,16 @@ innodb_rollback_on_timeout OFF create table t1 (a int unsigned not null primary key) engine = innodb; insert into t1 values (1); commit; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con2; begin work; insert into t1 values (2); select * from t1; a 1 2 +connection con1; begin work; insert into t1 values (5); select * from t1; @@ -809,17 +847,21 @@ a 1 5 commit; +connection con2; select * from t1; a 1 2 commit; +connection default; select * from t1; a 1 2 5 drop table t1; +disconnect con1; +disconnect con2; drop table if exists t1; create table t1 (a int) engine=innodb; alter table t1 alter a set default 1; @@ -1117,9 +1159,19 @@ SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE; END WHILE; COMMIT; END;| +CONNECT con1,localhost,root,,; +CONNECT con2,localhost,root,,; +connection con1; CALL p1(); +connection con2; CALL p1(); +connection default; CALL p1(); +connection con1; +connection con2; +connection default; +disconnect con1; +disconnect con2; DROP PROCEDURE p1; DROP TABLE t1; create table t1(a text) engine=innodb default charset=utf8; @@ -1144,12 +1196,15 @@ START TRANSACTION; SELECT * FROM t1 WHERE b=20 FOR UPDATE; a b 2 20 +connect conn2, localhost, root,,test; START TRANSACTION; SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE; a b 1 10 2 10 ROLLBACK; +disconnect conn2; +connection default; ROLLBACK; DROP TABLE t1; CREATE TABLE t1( @@ -1574,10 +1629,12 @@ select if(@a=@b,"ok","wrong"); if(@a=@b,"ok","wrong") ok drop table t1; +connect con1,localhost,root,,; +connect con2,localhost,root,,; SET SESSION AUTOCOMMIT = 0; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; set binlog_format=mixed; -# Switch to connection con1 +connection con1; CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) ENGINE = InnoDB; INSERT INTO t1 VALUES (1,2); @@ -1589,17 +1646,17 @@ info: Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM t1; a b 1 12 -# Switch to connection con2 +connection con2; UPDATE t1 SET b = 21 WHERE a = 1; ERROR HY000: Lock wait timeout exceeded; try restarting transaction -# Switch to connection con1 +connection con1; SELECT * FROM t1; a b 1 12 ROLLBACK; -# Switch to connection con2 +connection con2; ROLLBACK; -# Switch to connection con1 +connection con1; # 2. test for serialized update: CREATE TABLE t2 (a INT); TRUNCATE t1; @@ -1626,9 +1683,9 @@ info: Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM t1; a b 1 init+con1 -# Switch to connection con2 +connection con2; CALL p1;; -# Switch to connection con1 +connection con1; SELECT * FROM t1; a b 1 init+con1 @@ -1636,12 +1693,12 @@ COMMIT; SELECT * FROM t1; a b 1 init+con1 -# Switch to connection con2 +connection con2; SELECT * FROM t1; a b 1 init+con1+con2 COMMIT; -# Switch to connection con1 +connection con1; # 3. test for updated key column: TRUNCATE t1; TRUNCATE t2; @@ -1653,9 +1710,9 @@ info: Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM t1; a b 2 init+con1 -# Switch to connection con2 +connection con2; CALL p1;; -# Switch to connection con1 +connection con1; SELECT * FROM t1; a b 2 init+con1 @@ -1663,10 +1720,13 @@ COMMIT; SELECT * FROM t1; a b 2 init+con1 -# Switch to connection con2 +connection con2; SELECT * FROM t1; a b 2 init+con1 +connection default; +disconnect con1; +disconnect con2; DROP PROCEDURE p1; DROP TABLE t1, t2; CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb; @@ -1826,21 +1886,21 @@ a 1 2 3 -# Connection con1 +connect con1, localhost, root,,; TRUNCATE TABLE t1;; -# Connection default +connection default; SELECT * FROM t1 ORDER BY a; a 1 2 3 ROLLBACK; -# Connection con1 +connection con1; # Reaping TRUNCATE TABLE SELECT * FROM t1; a -# Disconnect con1 -# Connection default +disconnect con1; +connection default; DROP TABLE t1; drop table if exists t1, t2, t3; # @@ -2370,6 +2430,8 @@ INSERT INTO t1 VALUES (),(); INSERT INTO t2 VALUES (),(); CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2 WHERE b =(SELECT a FROM t1 LIMIT 1); +CONNECT con1, localhost, root,,; +connection default; CREATE PROCEDURE p1(num INT) BEGIN DECLARE i INT DEFAULT 0; @@ -2380,6 +2442,7 @@ UNTIL i>num END REPEAT; END| # Should not crash # Should not crash +disconnect con1; DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1,t2; @@ -2774,19 +2837,18 @@ CREATE TABLE t1(x INT PRIMARY KEY, y INT) ENGINE=innodb; INSERT INTO t1 VALUES (1, 0), (2, 0); CREATE FUNCTION f1(z INT) RETURNS INT READS SQL DATA RETURN (SELECT x FROM t1 WHERE x = z); -# Connection default START TRANSACTION; SELECT f1(1); f1(1) 1 -# Connection con2 START TRANSACTION; SELECT f1(1); f1(1) 1 UPDATE t1 SET y = 1 WHERE x = 1; COMMIT; -# Connection default +disconnect con2; +connection default; COMMIT; DROP TABLE t1; DROP FUNCTION f1; diff --git a/mysql-test/suite/innodb/r/innodb_mysql_rbk.result b/mysql-test/suite/innodb/r/innodb_mysql_rbk.result index 21ac4295325..64f508a501c 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql_rbk.result +++ b/mysql-test/suite/innodb/r/innodb_mysql_rbk.result @@ -1,21 +1,30 @@ CREATE TABLE t1(a INT, b INT NOT NULL, PRIMARY KEY (a)) ENGINE=innodb DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); +CONNECT con1,localhost,root,,; +CONNECT con2,localhost,root,,; +connection con1; START TRANSACTION; SELECT * FROM t1 WHERE b=3 LIMIT 1 FOR UPDATE; a b 3 3 +connection con2; START TRANSACTION; UPDATE t1 SET b=b+12 WHERE a > 2 ORDER BY a; ERROR HY000: Lock wait timeout exceeded; try restarting transaction ROLLBACK; +connection con1; START TRANSACTION; SELECT * FROM t1 WHERE b=3 LIMIT 1 FOR UPDATE; a b 3 3 +connection con2; START TRANSACTION; UPDATE t1 SET b=10 WHERE a > 1 ORDER BY a; ERROR HY000: Lock wait timeout exceeded; try restarting transaction SELECT * FROM t1 WHERE b = 10; a b +connection default; +disconnect con1; +disconnect con2; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_notembedded.result b/mysql-test/suite/innodb/r/innodb_notembedded.result index af332aba38a..b8ff28a10b5 100644 --- a/mysql-test/suite/innodb/r/innodb_notembedded.result +++ b/mysql-test/suite/innodb/r/innodb_notembedded.result @@ -1,5 +1,7 @@ drop table if exists t1; SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; +connect a,localhost,root,,; +connect b,localhost,root,,; SET GLOBAL log_bin_trust_function_creators = 1; create table t1 (col1 integer primary key, col2 integer) engine=innodb; insert t1 values (1,100); @@ -12,12 +14,21 @@ start transaction; select f1(); f1() 100 +connection b; update t1 set col2=0 where col1=1; +connection default; select * from t1; col1 col2 1 100 +connection a; rollback; +connection b; rollback; +connection a; +disconnect a; +connection b; +disconnect b; +connection default; drop table t1; drop function f1; SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; diff --git a/mysql-test/suite/innodb/r/innodb_prefix_index_restart_server.result b/mysql-test/suite/innodb/r/innodb_prefix_index_restart_server.result index e1f6f8608e1..cf5d77a679c 100644 --- a/mysql-test/suite/innodb/r/innodb_prefix_index_restart_server.result +++ b/mysql-test/suite/innodb/r/innodb_prefix_index_restart_server.result @@ -11,7 +11,7 @@ SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM worklog5743; col_1_text = REPEAT("a", 3500) col_2_text = REPEAT("o", 3500) 1 1 -"In connection 1" +connect con1,localhost,root,,; SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM worklog5743; col_1_text = REPEAT("a", 3500) col_2_text = REPEAT("o", 3500) @@ -19,10 +19,10 @@ col_1_text = REPEAT("a", 3500) col_2_text = REPEAT("o", 3500) SELECT COUNT(*) FROM worklog5743; COUNT(*) 1 -"In connection 2" +connect con2,localhost,root,,; START TRANSACTION; INSERT INTO worklog5743 VALUES(REPEAT("b", 3500) , REPEAT("o", 3500)); -"In connection 1" +connection con1; SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM worklog5743; col_1_text = REPEAT("a", 3500) col_2_text = REPEAT("o", 3500) @@ -31,7 +31,7 @@ SELECT COUNT(*) FROM worklog5743; COUNT(*) 1 START TRANSACTION; -"In connection default ....restarting the server" +connection default; SELECT COUNT(*) FROM worklog5743; COUNT(*) 1 @@ -39,7 +39,9 @@ SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM worklog5743; col_1_text = REPEAT("a", 3500) col_2_text = REPEAT("o", 3500) 1 1 -"In connection 1" +disconnect con1; +disconnect con2; +connect con1,localhost,root,,; SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM worklog5743; col_1_text = REPEAT("a", 3500) col_2_text = REPEAT("o", 3500) @@ -54,7 +56,7 @@ SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM worklog5743; col_1_text = REPEAT("a", 3500) col_2_text = REPEAT("o", 3500) 1 1 -"In connection default ....restarting the server" +connection default; SELECT COUNT(*) FROM worklog5743; COUNT(*) 1 @@ -62,7 +64,8 @@ SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM worklog5743; col_1_text = REPEAT("a", 3500) col_2_text = REPEAT("o", 3500) 1 1 -"In connection 1" +disconnect con1; +connect con2,localhost,root,,; SELECT col_1_text = REPEAT("a", 3500) , col_2_text = REPEAT("o", 3500) FROM worklog5743; col_1_text = REPEAT("a", 3500) col_2_text = REPEAT("o", 3500) @@ -76,7 +79,7 @@ SELECT col_1_text = REPEAT("b", 3500) , col_2_text = REPEAT("o", 3500) FROM worklog5743; col_1_text = REPEAT("b", 3500) col_2_text = REPEAT("o", 3500) 1 1 -"In connection default ....restarting the server" +connection default; SELECT COUNT(*) FROM worklog5743; COUNT(*) 1 diff --git a/mysql-test/suite/innodb/r/innodb_stats_drop_locked.result b/mysql-test/suite/innodb/r/innodb_stats_drop_locked.result index 13cc45e0ef9..b8f312ccd63 100644 --- a/mysql-test/suite/innodb/r/innodb_stats_drop_locked.result +++ b/mysql-test/suite/innodb/r/innodb_stats_drop_locked.result @@ -17,6 +17,8 @@ innodb_stats_drop_locked innodb_stats_drop_locked innodb_stats_drop_locked innodb_stats_drop_locked +connect con1,localhost,root,,; +connection con1; ALTER TABLE innodb_stats_drop_locked DROP INDEX c_key; Warnings: Warning 1205 Unable to delete statistics for index c_key from mysql.innodb_index_stats because the rows are locked: Lock wait timeout. They can be deleted later using DELETE FROM mysql.innodb_index_stats WHERE database_name = 'test' AND table_name = 'innodb_stats_drop_locked' AND index_name = 'c_key'; @@ -28,6 +30,8 @@ innodb_stats_drop_locked CREATE TABLE `innodb_stats_drop_locked` ( DROP TABLE innodb_stats_drop_locked; SHOW TABLES; Tables_in_test +connection default; +disconnect con1; COMMIT; SELECT table_name FROM mysql.innodb_table_stats WHERE table_name='innodb_stats_drop_locked'; diff --git a/mysql-test/suite/innodb/r/innodb_sys_semaphore_waits.result b/mysql-test/suite/innodb/r/innodb_sys_semaphore_waits.result index 4164e043b2c..5f14ad9b0d7 100644 --- a/mysql-test/suite/innodb/r/innodb_sys_semaphore_waits.result +++ b/mysql-test/suite/innodb/r/innodb_sys_semaphore_waits.result @@ -1,7 +1,7 @@ -# Establish connection con1 (user=root) -# Establish connection con2 (user=root) +connect con1,localhost,root,,; +connect con2,localhost,root,,; drop table if exists t1; -# Switch to connection con1 +connection con1; create table t1 (id integer, x integer) engine = InnoDB; insert into t1 values(0, 0); set DEBUG_DBUG='+d,fatal-semaphore-timeout'; @@ -9,13 +9,13 @@ set autocommit=0; # Sending query on con1, # the session will hold lock table mutex and sleep SELECT * from t1 where id = 0 FOR UPDATE; -# Switch to connection con2 +connection con2; set autocommit=0; # Sending query on con2, # the session will be blocked on the lock table mutex and # thus be put into sync arry SELECT * from t1 where id = 0 FOR UPDATE; -# Switched to the default connection +connection default; # Waitting for mysqld to crash # Mysqld crash was detected # Waitting for reconnect after mysqld restarts diff --git a/mysql-test/suite/innodb/r/innodb_timeout_rollback.result b/mysql-test/suite/innodb/r/innodb_timeout_rollback.result index e2da6ba8af7..30db5a21a4d 100644 --- a/mysql-test/suite/innodb/r/innodb_timeout_rollback.result +++ b/mysql-test/suite/innodb/r/innodb_timeout_rollback.result @@ -5,12 +5,16 @@ innodb_rollback_on_timeout ON create table t1 (a int unsigned not null primary key) engine = innodb; insert into t1 values (1); commit; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con2; begin work; insert into t1 values (2); select * from t1; a 1 2 +connection con1; begin work; insert into t1 values (5); select * from t1; @@ -23,14 +27,18 @@ select * from t1; a 1 commit; +connection con2; select * from t1; a 1 2 commit; +connection default; select * from t1; a 1 2 drop table t1; +disconnect con1; +disconnect con2; End of 5.0 tests diff --git a/mysql-test/suite/innodb/r/innodb_uninstall.result b/mysql-test/suite/innodb/r/innodb_uninstall.result index b1f618c5221..f3b166c5736 100644 --- a/mysql-test/suite/innodb/r/innodb_uninstall.result +++ b/mysql-test/suite/innodb/r/innodb_uninstall.result @@ -2,15 +2,23 @@ install plugin innodb soname 'ha_innodb'; Warnings: Warning 1105 Cannot enable tc-log at run-time. XA features of InnoDB are disabled create table t1(a int not null primary key) engine=innodb; +connect con1, localhost, root; +connection con1; begin; insert into t1 values(1); +connection default; flush tables; uninstall plugin innodb; +connection con1; select sleep(1); sleep(1) 0 +disconnect con1; +connection default; Warnings: Warning 1620 Plugin is busy and will be uninstalled on shutdown +connect con1, localhost, root; +connection con1; install plugin innodb soname 'ha_innodb'; Warnings: Warning 1105 Cannot enable tc-log at run-time. XA features of InnoDB are disabled @@ -18,9 +26,13 @@ drop table t1; create table t2(a int not null primary key) engine=innodb; insert into t2 values(1); drop table t2; +connection default; uninstall plugin innodb; +connection con1; select sleep(1); sleep(1) 0 +disconnect con1; +connection default; Warnings: Warning 1620 Plugin is busy and will be uninstalled on shutdown diff --git a/mysql-test/suite/innodb/r/mdev-117.result b/mysql-test/suite/innodb/r/mdev-117.result index 40c63b5ae38..997470fea6f 100644 --- a/mysql-test/suite/innodb/r/mdev-117.result +++ b/mysql-test/suite/innodb/r/mdev-117.result @@ -1,12 +1,17 @@ SET GLOBAL innodb_lock_wait_timeout=3; CREATE TABLE t1 (col_int_key INT, KEY (col_int_key)) ENGINE=InnoDB; INSERT INTO t1 VALUES (6); +connect con1,localhost,root,,test; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET AUTOCOMMIT=OFF; SELECT col_int_key FROM t1; col_int_key 6 +connection default; DELETE IGNORE FROM t1;; +connection con1; DELETE FROM t1 WHERE col_int_key IN (1, 40000000); +connection default; +disconnect con1; drop table t1; SET GLOBAL innodb_lock_wait_timeout=default; diff --git a/mysql-test/suite/innodb/r/row_lock.result b/mysql-test/suite/innodb/r/row_lock.result index 7344927a587..7ea09fa3bad 100644 --- a/mysql-test/suite/innodb/r/row_lock.result +++ b/mysql-test/suite/innodb/r/row_lock.result @@ -4,10 +4,18 @@ CREATE TABLE t2 (c INT, d INT) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,1), (2,2); CREATE TABLE t3 (e INT) ENGINE=InnoDB; CREATE TABLE t4 ENGINE=InnoDB AS SELECT * FROM t2; +connect con11,localhost,root,,test; BEGIN; UPDATE t1 SET a = 0 WHERE a = ( SELECT e FROM t3 ); +connect con12,localhost,root,,test; UPDATE t4 SET d = 1 WHERE d in ( SELECT a FROM t1 ) ORDER BY c LIMIT 6; +connection con11; UPDATE t4 SET d = 9; +connection con12; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +connection con11; commit; +connection default; +disconnect con12; +disconnect con11; drop table t1,t2,t3,t4; diff --git a/mysql-test/suite/innodb/r/snapshot.result b/mysql-test/suite/innodb/r/snapshot.result index dcb3d242fc2..fa4ec2bdc21 100644 --- a/mysql-test/suite/innodb/r/snapshot.result +++ b/mysql-test/suite/innodb/r/snapshot.result @@ -1,5 +1,7 @@ START TRANSACTION WITH CONSISTENT SNAPSHOT; +connect con1,localhost,root,,test; CREATE TABLE IF NOT EXISTS t1 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=InnoDB; +connection default; UPDATE t1 SET i = 0; ERROR HY000: Table definition has changed, please retry transaction UPDATE t1 SET pk = 0; diff --git a/mysql-test/suite/innodb/r/tmpdir.result b/mysql-test/suite/innodb/r/tmpdir.result index f9475c1b7f8..a277883a975 100644 --- a/mysql-test/suite/innodb/r/tmpdir.result +++ b/mysql-test/suite/innodb/r/tmpdir.result @@ -10,11 +10,13 @@ Variable_name Value innodb_tmpdir alter table t1 add column b int not null; set global innodb_tmpdir=NULL; -# Connection con1 +connect con1,localhost,root; show session variables like 'innodb_tmpdir'; Variable_name Value innodb_tmpdir alter table t1 add key(b); +connection default; +disconnect con1; drop table t1; # innodb_tmpdir with invalid path. create table t1(a int primary key)engine=innodb; @@ -45,7 +47,9 @@ set global innodb_tmpdir = @tmpdir; show session variables like 'innodb_tmpdir'; Variable_name Value innodb_tmpdir -# Connection con3 +connect con3,localhost,root; alter table t1 add fulltext(b); +disconnect con3; +connection default; set global innodb_tmpdir=NULL; drop table t1; diff --git a/mysql-test/suite/innodb/r/xa_recovery.result b/mysql-test/suite/innodb/r/xa_recovery.result index 84cb37ef7c9..0ddfd9c3298 100644 --- a/mysql-test/suite/innodb/r/xa_recovery.result +++ b/mysql-test/suite/innodb/r/xa_recovery.result @@ -1,11 +1,17 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); +connect con1,localhost,root; XA START 'x'; UPDATE t1 set a=2; XA END 'x'; XA PREPARE 'x'; +connection default; call mtr.add_suppression("Found 1 prepared XA transactions"); +disconnect con1; +connect con1,localhost,root; SELECT * FROM t1 LOCK IN SHARE MODE; +connection default; +disconnect con1; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM t1; a |