GTT: SBR and MBR fall back to ROW events for all DML using GTT and base tables + binlog Bloat

--source include/have_innodb.inc

--source include/have_binlog_format_statement.inc

--source include/master-slave.inc

--connection master

CREATE TABLE t1 (c INT) ENGINE=InnoDB;

CREATE GLOBAL TEMPORARY TABLE gtt1 (c INT) ENGINE=InnoDB ON COMMIT PRESERVE ROWS;

INSERT INTO gtt1 VALUES (1),(2);

INSERT INTO t1 SELECT c FROM gtt1;

SELECT * FROM t1;  # 1

--source include/sync_slave_sql_with_master.inc

--connection slave

SELECT * FROM t1;  # 2

--connection master

SHOW BINLOG EVENTS;

TRUNCATE TABLE gtt1;

DROP TABLE t1, gtt1;

--source include/rpl_end.inc

SELECT * FROM t1;

c

1

2

include/sync_slave_sql_with_master.inc

connection slave;

SELECT * FROM t1;

c

1

2

...

master-bin.000001	724	Annotate_rows	1	0	INSERT INTO t1 SELECT c FROM gtt1

master-bin.000001	780	Table_map	1	0	table_id: 34 (test.t1)

master-bin.000001	825	Write_rows_v1	1	0	table_id: 34 flags: STMT_END_F

mariadb-test$ ../bin/mariadb-binlog --base64-output=decode-rows -vvv var/mysqld.1/data/master-bin.00[0-9][0-9][0-9][0-9] | grep '^###'

### INSERT INTO `test`.`t1`

### SET

###   @1=1 /* INT meta=0 nullable=1 is_null=0 */

### INSERT INTO `test`.`t1`

### SET

###   @1=2 /* INT meta=0 nullable=1 is_null=0 */

--source include/master-slave.inc

--source include/have_innodb.inc

--source include/have_binlog_format_statement.inc

--source include/have_sequence.inc

CREATE TABLE t  (id INT PRIMARY KEY) ENGINE=InnoDB;

CREATE TABLE t1 (id INT PRIMARY KEY, data CHAR(200)) ENGINE=InnoDB;

CREATE TABLE t2 (id INT PRIMARY KEY, data CHAR(200)) ENGINE=InnoDB;

CREATE GLOBAL TEMPORARY TABLE gtt (id INT PRIMARY KEY) ENGINE=InnoDB ON COMMIT PRESERVE ROWS;

INSERT INTO t  SELECT seq FROM seq_1_to_1000;

INSERT INTO t1 SELECT seq, REPEAT('x', 200) FROM seq_1_to_1000;

INSERT INTO t2 SELECT seq, REPEAT('x', 200) FROM seq_1_to_1000;

INSERT INTO gtt SELECT seq FROM seq_1_to_1000;

FLUSH BINARY LOGS;

--let $start= query_get_value(SHOW MASTER STATUS, Position, 1)

UPDATE t1 JOIN t ON t1.id = t.id SET t1.data = REPEAT('y', 200);

--let $end= query_get_value(SHOW MASTER STATUS, Position, 1)

--let $sbr= `SELECT $end - $start`

FLUSH BINARY LOGS;

--let $start= query_get_value(SHOW MASTER STATUS, Position, 1)

--disable_warnings

UPDATE t2 JOIN gtt ON t2.id = gtt.id SET t2.data = REPEAT('z', 200);

--enable_warnings

--let $end= query_get_value(SHOW MASTER STATUS, Position, 1)

--let $row= `SELECT $end - $start`

--let $ratio= `SELECT $row DIV $sbr`

--echo # Bloat Factor: $ratio x larger

--connection master

TRUNCATE TABLE gtt;

DROP TABLE t, t1, t2, gtt;

--source include/rpl_end.inc