rpl_row_create_table.test 7.41 KB
Newer Older
1 2 3 4
# Testing table creations for row-based replication.

--source include/have_binlog_format_row.inc
--source include/master-slave.inc
5 6 7 8
--source include/have_innodb.inc
connection slave;
--source include/have_innodb.inc
connection master;
9

10 11 12 13 14
# Bug#18326: Do not lock table for writing during prepare of statement
# The use of the ps protocol causes extra table maps in the binlog, so
# we disable the ps-protocol for this statement.
--disable_ps_protocol

15 16 17 18 19 20 21 22 23
--disable_query_log
--disable_warnings
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9;
--enable_warnings
--enable_query_log

# Set the default storage engine to different values on master and
# slave. We need to stop the slave for the server variable to take
# effect, since the variable is only read on start-up.
24
sync_slave_with_master;
25 26 27 28 29 30 31 32 33 34 35 36
--disable_query_log
set @storage_engine = @@global.storage_engine;
STOP SLAVE;
SET GLOBAL storage_engine=memory;
START SLAVE;
--enable_query_log

connection master;
CREATE TABLE t1 (a INT, b INT);
CREATE TABLE t2 (a INT, b INT) ENGINE=Merge;
CREATE TABLE t3 (a INT, b INT) CHARSET=utf8;
CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8;
37
--replace_column 1 # 4 #
38
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/
39
--query_vertical SHOW BINLOG EVENTS FROM 216
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
--echo **** On Master ****
--query_vertical SHOW CREATE TABLE t1
--query_vertical SHOW CREATE TABLE t2
--query_vertical SHOW CREATE TABLE t3
sync_slave_with_master;
--echo **** On Slave ****
--query_vertical SHOW CREATE TABLE t1
--query_vertical SHOW CREATE TABLE t2
--query_vertical SHOW CREATE TABLE t3

connection master;
CREATE TABLE t5 (b INT, c INT) SELECT * FROM t3;

CREATE TEMPORARY TABLE tt3 (a INT, b INT);
INSERT INTO tt3 VALUES (1,2), (2,4), (3,6), (4,2), (5,10), (6,12);
CREATE TABLE t6 (b INT, c INT) SELECT * FROM tt3;
--echo **** On Master ****
--query_vertical SHOW CREATE TABLE t5
SELECT * FROM t5 ORDER BY a,b,c;
--query_vertical SHOW CREATE TABLE t6
SELECT * FROM t6 ORDER BY a,b,c;
sync_slave_with_master;
--echo **** On Slave ****
--query_vertical SHOW CREATE TABLE t5
SELECT * FROM t5 ORDER BY a,b,c;
--query_vertical SHOW CREATE TABLE t6
SELECT * FROM t6 ORDER BY a,b,c;

connection master;
# Test for erroneous constructions
70
--error ER_DUP_ENTRY
71 72
CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3;
# Shouldn't be written to the binary log
73
--replace_column 1 # 4 #
74
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/
75
SHOW BINLOG EVENTS FROM 1374;
76 77 78

# Test that INSERT-SELECT works the same way as for SBR.
CREATE TABLE t7 (a INT, b INT UNIQUE);
79
--error ER_DUP_ENTRY
80 81 82
INSERT INTO t7 SELECT a,b FROM tt3;
SELECT * FROM t7 ORDER BY a,b;
# Should be written to the binary log
83
--replace_column 1 # 4 #
84
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/
85
SHOW BINLOG EVENTS FROM 1374;
86 87 88 89 90 91 92 93 94
sync_slave_with_master;
SELECT * FROM t7 ORDER BY a,b;

connection master;
CREATE TEMPORARY TABLE tt4 (a INT, b INT);
INSERT INTO tt4 VALUES (4,8), (5,10), (6,12);
BEGIN;
INSERT INTO t7 SELECT a,b FROM tt4;
ROLLBACK;
95
--replace_column 1 # 4 #
96
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/
97
SHOW BINLOG EVENTS FROM 1711;
98 99 100 101 102 103 104 105 106
SELECT * FROM t7 ORDER BY a,b;
sync_slave_with_master;
SELECT * FROM t7 ORDER BY a,b;

connection master;
CREATE TABLE t8 LIKE t4;
CREATE TABLE t9 LIKE tt4;
CREATE TEMPORARY TABLE tt5 LIKE t4;
CREATE TEMPORARY TABLE tt6 LIKE tt4;
107
CREATE TEMPORARY TABLE tt7 SELECT 1;
108 109 110
--echo **** On Master ****
--query_vertical SHOW CREATE TABLE t8
--query_vertical SHOW CREATE TABLE t9
111
--replace_column 1 # 4 #
112
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/
113
SHOW BINLOG EVENTS FROM 1946;
114 115 116 117 118 119 120 121 122 123 124 125
sync_slave_with_master;
--echo **** On Slave ****
--query_vertical SHOW CREATE TABLE t8
--query_vertical SHOW CREATE TABLE t9

connection master;
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9;
sync_slave_with_master;
# Here we reset the value of the default storage engine
STOP SLAVE;
SET GLOBAL storage_engine=@storage_engine;
START SLAVE;
126
--enable_ps_protocol
127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162

# BUG#22864 (Rollback following CREATE ... SELECT discards 'CREATE
# table' from log):
--echo ================ BUG#22864 ================
connection slave;
STOP SLAVE;
RESET SLAVE;
connection master;
RESET MASTER;
connection slave;
START SLAVE;
connection master;
SET AUTOCOMMIT=0;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);

CREATE TABLE t2 ENGINE=INNODB SELECT * FROM t1;
ROLLBACK;

CREATE TABLE t3 ENGINE=INNODB SELECT * FROM t1;
INSERT INTO t3 VALUES (4),(5),(6);
ROLLBACK;

CREATE TABLE t4 ENGINE=INNODB SELECT * FROM t1;
INSERT INTO t1 VALUES (4),(5),(6);
ROLLBACK;

SHOW TABLES;
SELECT   TABLE_NAME,ENGINE
  FROM   INFORMATION_SCHEMA.TABLES
 WHERE   TABLE_NAME LIKE 't_'
ORDER BY TABLE_NAME;
SELECT * FROM t1 ORDER BY a;
SELECT * FROM t2 ORDER BY a;
SELECT * FROM t3 ORDER BY a;
SELECT * FROM t4 ORDER BY a;
163
--replace_column 1 # 4 #
164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/
SHOW BINLOG EVENTS;
sync_slave_with_master;
SHOW TABLES;
SELECT   TABLE_NAME,ENGINE
  FROM   INFORMATION_SCHEMA.TABLES
 WHERE   TABLE_NAME LIKE 't_'
ORDER BY TABLE_NAME;
SELECT * FROM t1 ORDER BY a;
SELECT * FROM t2 ORDER BY a;
SELECT * FROM t3 ORDER BY a;
SELECT * FROM t4 ORDER BY a;

connection master;
DROP TABLE IF EXISTS t1,t2,t3,t4;
SET AUTOCOMMIT=1;
sync_slave_with_master;

# Some tests with temporary tables
connection slave;
STOP SLAVE;
RESET SLAVE;

connection master;
RESET MASTER;

connection slave;
START SLAVE;

connection master;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);

CREATE TABLE t2 (a INT) ENGINE=INNODB;

BEGIN;
INSERT INTO t2 SELECT a*a FROM t1;
CREATE TEMPORARY TABLE tt1
SELECT a+1 AS a
  FROM t1
 WHERE a MOD 2 = 1;
INSERT INTO t2 SELECT a+2 FROM tt1;
COMMIT;

SELECT * FROM t2 ORDER BY a;
209
--replace_column 1 # 4 #
210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/
SHOW BINLOG EVENTS;
sync_slave_with_master;
SELECT * FROM t2 ORDER BY a;

connection master;
TRUNCATE TABLE t2;

BEGIN;
INSERT INTO t2 SELECT a*a FROM t1;
CREATE TEMPORARY TABLE tt2
SELECT a+1 AS a
  FROM t1
 WHERE a MOD 2 = 1;
INSERT INTO t2 SELECT a+2 FROM tt2;
ROLLBACK;

SELECT * FROM t2 ORDER BY a;
228
--replace_column 1 # 4 #
229
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/
230
SHOW BINLOG EVENTS FROM 949;
231 232 233 234 235 236
sync_slave_with_master;
SELECT * FROM t2 ORDER BY a;

connection master;
DROP TABLE t1,t2;
sync_slave_with_master;
237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261

#
# bug#35762 Failing CREATE-SELECT produces bad binlog in row mode
#

connection master;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(1);
--error ER_DUP_ENTRY
CREATE TABLE t2 (a INT UNIQUE) ENGINE=INNODB SELECT * FROM t1;
INSERT INTO t1 VALUES (2);

sync_slave_with_master;
# connection slave;

--echo *** the proof of the fix:
--echo     select must show that the last insert performed on the slave ***
SELECT * FROM t1;

connection master;
DROP TABLE t1;
sync_slave_with_master;

262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278
#
# BUG#34707: Row based replication: slave creates table within wrong database
#

source include/master-slave-reset.inc;

connection master;
CREATE DATABASE mysqltest1;

CREATE TABLE mysqltest1.without_select (f1 BIGINT);
CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1;
source include/show_binlog_events.inc;
sync_slave_with_master;

connection master;
DROP DATABASE mysqltest1;
sync_slave_with_master;
279 280

--echo end of the tests