blob: 24d7d6cebf74679cb9cd877a6f5578be315e7f80 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
include/master-slave.inc
[connection master]
==== Initialize ====
connection master;
CREATE TABLE t1 (a CHAR(48));
CREATE TEMPORARY TABLE t1_tmp1(a INT);
INSERT INTO t1 VALUES (UUID());
connection slave;
==== Verify results on slave ====
SHOW STATUS LIKE "Slave_open_temp_tables";
Variable_name Value
Slave_open_temp_tables 1
connection master;
disconnect master;
connection master1;
connection slave;
==== Verify results on slave ====
SHOW STATUS LIKE "Slave_open_temp_tables";
Variable_name Value
Slave_open_temp_tables 0
==== Clean up ====
include/rpl_connect.inc [creating master]
connection master;
DROP TABLE t1;
connection slave;
include/rpl_reset.inc
connection master;
CREATE TABLE t1 (a int) engine=innodb;
CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ();
CREATE TEMPORARY TABLE t1_tmp (i1 int);
ALTER TABLE t1_tmp ADD COLUMN b INT;
INSERT INTO t1 values(1);
INSERT INTO t2 (i1) select * from t1;
CREATE TEMPORARY TABLE t2_tmp (a int);
ALTER TABLE t1_tmp ADD COLUMN c INT;
### assertion: assert that there is one open temp table on slave
connection slave;
SHOW STATUS LIKE 'Slave_open_temp_tables';
Variable_name Value
Slave_open_temp_tables 1
connection master;
DROP TABLE t1_tmp, t2;
INSERT INTO t1 VALUES (1);
DROP TEMPORARY TABLE t2_tmp;
INSERT INTO t1 VALUES (2);
connection slave;
### assertion: assert that slave has no temporary tables opened
SHOW STATUS LIKE 'Slave_open_temp_tables';
Variable_name Value
Slave_open_temp_tables 0
connection master;
DROP TABLE t3, t1;
connection slave;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
slave-bin.000001 # Gtid # # GTID #-#-#
slave-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a int) engine=innodb
slave-bin.000001 # Gtid # # GTID #-#-#
slave-bin.000001 # Query # # use `test`; CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) )
slave-bin.000001 # Gtid # # GTID #-#-#
slave-bin.000001 # Query # # use `test`; CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) )
slave-bin.000001 # Gtid # # GTID #-#-#
slave-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ()
slave-bin.000001 # Gtid # # GTID #-#-#
slave-bin.000001 # Query # # use `test`; CREATE TEMPORARY TABLE t1_tmp (i1 int)
slave-bin.000001 # Gtid # # GTID #-#-#
slave-bin.000001 # Query # # use `test`; ALTER TABLE t1_tmp ADD COLUMN b INT
slave-bin.000001 # Gtid # # BEGIN GTID #-#-#
slave-bin.000001 # Query # # use `test`; INSERT INTO t1 values(1)
slave-bin.000001 # Xid # # COMMIT /* XID */
slave-bin.000001 # Gtid # # BEGIN GTID #-#-#
slave-bin.000001 # Annotate_rows # # INSERT INTO t2 (i1) select * from t1
slave-bin.000001 # Table_map # # table_id: # (test.t2)
slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
slave-bin.000001 # Query # # COMMIT
slave-bin.000001 # Gtid # # GTID #-#-#
slave-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t1_tmp` /* generated by server */
slave-bin.000001 # Gtid # # GTID #-#-#
slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */
slave-bin.000001 # Gtid # # BEGIN GTID #-#-#
slave-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (1)
slave-bin.000001 # Table_map # # table_id: # (test.t1)
slave-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
slave-bin.000001 # Xid # # COMMIT /* XID */
slave-bin.000001 # Gtid # # BEGIN GTID #-#-#
slave-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (2)
slave-bin.000001 # Xid # # COMMIT /* XID */
slave-bin.000001 # Gtid # # GTID #-#-#
slave-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t3`,`t1` /* generated by server */
# Bug#55478 Row events wrongly apply on the temporary table of the same name
# ==========================================================================
connection master;
# The statement should be binlogged
CREATE TEMPORARY TABLE t1(c1 INT) ENGINE=InnoDB;
# Case 1: CREATE TABLE t1 ... SELECT
# ----------------------------------
# The statement generates row events on t1. And the rows events should
# be inserted into the base table on slave.
CREATE TABLE t1 ENGINE=MyISAM SELECT rand();
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # use `test`; CREATE TABLE `t1` (
`rand()` double NOT NULL
) ENGINE=MyISAM
master-bin.000001 # Annotate_rows # # CREATE TABLE t1 ENGINE=MyISAM SELECT rand()
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
master-bin.000001 # Query # # COMMIT
# Case 2: DROP TEMPORARY TABLE in a transacation
# ----------------------------------------------
BEGIN;
DROP TEMPORARY TABLE t1;
# The rows event will binlogged after 'INSERT INTO t1 VALUES(1)'
INSERT IGNORE INTO t1 VALUES(uuid()+0);
COMMIT;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Annotate_rows # # INSERT IGNORE INTO t1 VALUES(uuid()+0)
master-bin.000001 # Table_map # # table_id: # (test.t1)
master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Gtid # # BEGIN GTID #-#-#
master-bin.000001 # Query # # DROP TEMPORARY TABLE IF EXISTS `test`.`t1` /* generated by server */
master-bin.000001 # Query # # COMMIT
connection slave;
# Compare the base table.
include/diff_tables.inc [master:t1, slave:t1]
connection master;
DROP TABLE t1;
include/rpl_end.inc
|
