MWL#136: Cross-engine consistency for START TRANSACTION WITH CONSISTENT SNAPSHOT

Make the binlog handlerton participate in START TRANSACTION WITH CONSISTENT
SNAPSHOT, recording the binlog position corresponding to the snapshot taken
in other MVCC storage engines.

Expose this consistent binlog position as the new status variables
binlog_trx_file and binlog_trx_position. This enables to get a fully
non-locking snapshot of the database (including binlog position for
slave provisioning), avoiding the need for FLUSH TABLES WITH READ LOCK.

Modify mysqldump to detect if the server supports this new feature, and
if so, avoid FLUSH TABLES WITH READ LOCK for --single-transaction
--master-data snapshot backups.

4 files changed, 324 insertions, 0 deletions

diff --git a/mysql-test/r/mysqldump-max.result b/mysql-test/r/mysqldump-max.result
index c300f3d7996..57c9180691a 100644
--- a/mysql-test/r/mysqldump-max.result
+++ b/mysql-test/r/mysqldump-max.result
@@ -290,3 +290,59 @@ COUNT(*)
 DROP VIEW  v1;
 DROP TABLE t1;
 SET GLOBAL storage_engine=@old_engine;
+# Connection default
+RESET MASTER;
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,0), (2,0);
+SELECT GET_LOCK("block_queries_1", 120);
+GET_LOCK("block_queries_1", 120)
+1
+# Connection c3
+SELECT GET_LOCK("block_queries_2", 120);
+GET_LOCK("block_queries_2", 120)
+1
+# Connection c1
+SET @c= 0;
+SELECT IF(@c<1, @c:=@c+1, GET_LOCK("block_queries_1", 120)) FROM t1 ORDER BY a;
+# Connection c2
+SET binlog_format="row";
+SET @d= 10;
+UPDATE t2 SET b=IF(@d<=10, @d:=@d+1, GET_LOCK("block_queries_2", 120)) ORDER BY a;
+# Connection default
+# Make sure other queries are running (and waiting).
+SELECT RELEASE_LOCK("block_queries_1");
+RELEASE_LOCK("block_queries_1")
+1
+# Connection c3
+SELECT RELEASE_LOCK("block_queries_2");
+RELEASE_LOCK("block_queries_2")
+1
+# Connection c1
+IF(@c<1, @c:=@c+1, GET_LOCK("block_queries_1", 120))
+1
+1
+# Connection c2
+# Connection default
+SELECT * FROM t2 ORDER BY a;
+a	b
+1	11
+2	1
+DROP TABLE t1;
+DROP TABLE t2;
+SHOW BINLOG EVENTS LIMIT 6,3;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	524	Query	1	592	BEGIN
+master-bin.000001	592	Query	1	689	use `test`; INSERT INTO t2 VALUES (1,0), (2,0)
+master-bin.000001	689	Xid	1	716	COMMIT /* XID */
+-- CHANGE MASTER TO MASTER_LOG_FILE='./master-bin.000001', MASTER_LOG_POS=716;
+SELECT * FROM t1 ORDER BY a;
+a
+1
+2
+SELECT * FROM t2 ORDER BY a;
+a	b
+1	0
+2	0
+DROP TABLE t1,t2;
diff --git a/mysql-test/suite/binlog/r/binlog_consistent.result b/mysql-test/suite/binlog/r/binlog_consistent.result
new file mode 100644
index 00000000000..dc479ebe29c
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_consistent.result
@@ -0,0 +1,99 @@
+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	241		
+SHOW STATUS LIKE 'binlog_trx_%';
+Variable_name	Value
+binlog_trx_file	./master-bin.000001
+binlog_trx_position	241
+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 READ COMMITTED;
+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_trx_%';
+Variable_name	Value
+binlog_trx_file	./master-bin.000001
+binlog_trx_position	540
+SHOW MASTER STATUS;
+File	Position	Binlog_Do_DB	Binlog_Ignore_DB
+master-bin.000001	727		
+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_trx_%';
+Variable_name	Value
+binlog_trx_file	./master-bin.000001
+binlog_trx_position	540
+SHOW MASTER STATUS;
+File	Position	Binlog_Do_DB	Binlog_Ignore_DB
+master-bin.000002	106		
+COMMIT;
+SHOW STATUS LIKE 'binlog_trx_%';
+Variable_name	Value
+binlog_trx_file	./master-bin.000002
+binlog_trx_position	106
+SHOW MASTER STATUS;
+File	Position	Binlog_Do_DB	Binlog_Ignore_DB
+master-bin.000002	106		
+SHOW BINLOG EVENTS;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	4	Format_desc	1	106	Server ver: #, Binlog ver: #
+master-bin.000001	106	Query	1	241	use `test`; CREATE TABLE t1 (a INT, b VARCHAR(100), PRIMARY KEY (a,b)) ENGINE=innodb
+master-bin.000001	241	Query	1	353	use `test`; CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=myisam
+master-bin.000001	353	Query	1	421	BEGIN
+master-bin.000001	421	Query	1	513	use `test`; INSERT INTO t1 VALUES (0, "")
+master-bin.000001	513	Xid	1	540	COMMIT /* XID */
+master-bin.000001	540	Query	1	608	BEGIN
+master-bin.000001	608	Query	1	700	use `test`; INSERT INTO t1 VALUES (4, "")
+master-bin.000001	700	Xid	1	727	COMMIT /* XID */
+master-bin.000001	727	Query	1	795	BEGIN
+master-bin.000001	795	Query	1	887	use `test`; INSERT INTO t1 VALUES (1, "")
+master-bin.000001	887	Xid	1	914	COMMIT /* XID */
+master-bin.000001	914	Query	1	982	BEGIN
+master-bin.000001	982	Query	1	1079	use `test`; INSERT INTO t1 VALUES (2, "first")
+master-bin.000001	1079	Query	1	1167	use `test`; INSERT INTO t2 VALUES (2)
+master-bin.000001	1167	Query	1	1265	use `test`; INSERT INTO t1 VALUES (2, "second")
+master-bin.000001	1265	Xid	1	1292	COMMIT /* XID */
+master-bin.000001	1292	Query	1	1360	BEGIN
+master-bin.000001	1360	Query	1	1452	use `test`; INSERT INTO t1 VALUES (3, "")
+master-bin.000001	1452	Query	1	1540	use `test`; INSERT INTO t2 VALUES (3)
+master-bin.000001	1540	Xid	1	1567	COMMIT /* XID */
+master-bin.000001	1567	Rotate	1	1611	master-bin.000002;pos=4
+DROP TABLE t1,t2;
diff --git a/mysql-test/suite/binlog/t/binlog_consistent.test b/mysql-test/suite/binlog/t/binlog_consistent.test
new file mode 100644
index 00000000000..b1df7e45678
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_consistent.test
@@ -0,0 +1,88 @@
+--source include/have_log_bin.inc
+--source include/have_binlog_format_mixed_or_statement.inc
+--source include/have_innodb.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_trx_%';
+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 READ COMMITTED;
+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_trx_%';
+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_trx_%';
+SHOW MASTER STATUS;
+COMMIT;
+SHOW STATUS LIKE 'binlog_trx_%';
+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/t/mysqldump-max.test b/mysql-test/t/mysqldump-max.test
index 1e8b9647503..0a489b7eac7 100644
--- a/mysql-test/t/mysqldump-max.test
+++ b/mysql-test/t/mysqldump-max.test
@@ -2,6 +2,7 @@
 --source include/not_embedded.inc
 --source include/have_innodb.inc
 --source include/have_archive.inc
+--source include/have_log_bin.inc
 
 --disable_warnings
 drop table if exists t1, t2, t3, t4, t5, t6;
@@ -1124,3 +1125,83 @@ DROP VIEW  v1;
 DROP TABLE t1;
 
 SET GLOBAL storage_engine=@old_engine;
+
+# Test fully non-locking mysqldump with consistent binlog position (MWL#136).
+
+connect(c1,127.0.0.1,root,,test,$MASTER_MYPORT,);
+connect(c2,127.0.0.1,root,,test,$MASTER_MYPORT,);
+connect(c3,127.0.0.1,root,,test,$MASTER_MYPORT,);
+
+connection default;
+--echo # Connection default
+RESET MASTER;
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,0), (2,0);
+SELECT GET_LOCK("block_queries_1", 120);
+
+connection c3;
+--echo # Connection c3
+SELECT GET_LOCK("block_queries_2", 120);
+
+# Start two queries that will be running on the tables during mysqldump
+connection c1;
+--echo # Connection c1
+SET @c= 0;
+send SELECT IF(@c<1, @c:=@c+1, GET_LOCK("block_queries_1", 120)) FROM t1 ORDER BY a;
+
+connection c2;
+--echo # Connection c2
+SET binlog_format="row";
+SET @d= 10;
+send UPDATE t2 SET b=IF(@d<=10, @d:=@d+1, GET_LOCK("block_queries_2", 120)) ORDER BY a;
+
+connection default;
+--echo # Connection default
+--echo # Make sure other queries are running (and waiting).
+let $wait_condition=
+  SELECT COUNT(*) FROM information_schema.processlist
+   WHERE state = "User lock" AND info LIKE 'SELECT%block_queries_1%';
+--source include/wait_condition.inc
+let $wait_condition=
+  SELECT COUNT(*) FROM information_schema.processlist
+   WHERE state = "User lock" AND info LIKE 'UPDATE%block_queries_2%';
+--source include/wait_condition.inc
+
+--exec $MYSQL_DUMP --master-data=2 --single-transaction test t1 t2 > $MYSQLTEST_VARDIR/tmp/mwl136.sql
+
+SELECT RELEASE_LOCK("block_queries_1");
+
+connection c3;
+--echo # Connection c3
+SELECT RELEASE_LOCK("block_queries_2");
+
+connection c1;
+--echo # Connection c1
+reap;
+
+connection c2;
+--echo # Connection c2
+reap;
+
+connection default;
+--echo # Connection default
+SELECT * FROM t2 ORDER BY a;
+DROP TABLE t1;
+DROP TABLE t2;
+--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/mwl136.sql
+
+--replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/
+SHOW BINLOG EVENTS LIMIT 6,3;
+--perl
+my $f= "$ENV{MYSQLTEST_VARDIR}/tmp/mwl136.sql";
+open F, '<', $f or die "Failed to open $f: $!\n";
+while (<F>) {
+   print if /CHANGE MASTER TO/;
+}
+EOF
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+
+DROP TABLE t1,t2;