rpl_row_tabledefs.test 7.32 KB
Newer Older
1 2 3 4 5
# Test how replication of tables work when the definition on the
# master and slave differs.

# Consider making these part of the basic RBR tests.

6 7 8 9 10 11 12 13
connection master;
--disable_warnings
--disable_query_log
DROP TABLE IF EXISTS t1_int,t1_bit,t1_char,t1_nodef;
DROP TABLE IF EXISTS t2,t3,t4,t5,t6,t9;
--enable_query_log
--enable_warnings
sync_slave_with_master;
14 15 16
STOP SLAVE;
SET GLOBAL SQL_MODE='STRICT_ALL_TABLES';
START SLAVE;
17 18

connection master;
19 20 21 22
eval CREATE TABLE t1_int (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
eval CREATE TABLE t1_bit (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
eval CREATE TABLE t1_char (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
eval CREATE TABLE t1_nodef (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
23 24 25 26 27
eval CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
eval CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=$engine_type;
eval CREATE TABLE t4 (a INT) ENGINE=$engine_type;
eval CREATE TABLE t5 (a INT, b INT, c INT) ENGINE=$engine_type;
eval CREATE TABLE t6 (a INT, b INT, c INT) ENGINE=$engine_type;
28 29
eval CREATE TABLE t7 (a INT NOT NULL) ENGINE=$engine_type;
eval CREATE TABLE t8 (a INT NOT NULL) ENGINE=$engine_type;
30 31

# Table used to detect that slave is running
32
eval CREATE TABLE t9 (a INT) ENGINE=$engine_type;
33 34

sync_slave_with_master;
35 36 37

# On the slave, we add one INT column last in table 't1_int',
ALTER TABLE t1_int ADD x INT DEFAULT 42;
38 39 40 41 42 43
# ... and add BIT columns last in table 't1_bit' to ensure that we
# have at least one extra null byte on the slave,
ALTER TABLE t1_bit
  ADD x BIT(3) DEFAULT b'011',
  ADD y BIT(5) DEFAULT b'10101',
  ADD z BIT(2) DEFAULT b'10';
44 45 46 47 48 49 50
# ... and add one CHAR column last in table 't1_char',
ALTER TABLE t1_char ADD x CHAR(20) DEFAULT 'Just a test';
# ... and add one non-nullable INT column last in table 't1_text'
#     with no default,
ALTER TABLE t1_nodef ADD x INT NOT NULL;
# ... and remove the last column in t2
ALTER TABLE t2 DROP b;
51 52 53 54 55 56 57
# ... change the type of the single column in table 't4'
ALTER TABLE t4 MODIFY a FLOAT;
# ... change the type of the middle column of table 't5'
ALTER TABLE t5 MODIFY b FLOAT;
# ... change the type of the last column of table 't6'
ALTER TABLE t6 MODIFY c FLOAT;

58 59 60 61 62 63 64 65 66 67 68
# ... add one byte worth of null bytes to the table on the slave
ALTER TABLE t7 ADD e1 INT, ADD e2 INT, ADD e3 INT, ADD e4 INT,
               ADD e5 INT, ADD e6 INT, ADD e7 INT, ADD e8 INT;

# ... add 8 columns that are nullable: t8 will not be entirely
#     nullable and have no null bits (just an X bit)
ALTER TABLE t8 ADD e1 INT NOT NULL DEFAULT 0, ADD e2 INT NOT NULL DEFAULT 0,
               ADD e3 INT NOT NULL DEFAULT 0, ADD e4 INT NOT NULL DEFAULT 0,
               ADD e5 INT NOT NULL DEFAULT 0, ADD e6 INT NOT NULL DEFAULT 0,
               ADD e7 INT NOT NULL DEFAULT 0, ADD e8 INT NOT NULL DEFAULT 0;
        
69 70
# Insert some values for tables on slave side. These should not be
# modified when the row from the master is applied.
71 72 73
INSERT INTO t1_int  VALUES (2, 4, 4711);
INSERT INTO t1_char VALUES (2, 4, 'Foo is a bar');
INSERT INTO t1_bit  VALUES (2, 4, b'101', b'11100', b'01');
74 75

--echo **** On Master ****
76
connection master;
77 78 79 80 81 82 83 84 85 86
INSERT INTO t1_int VALUES (1,2);
INSERT INTO t1_int VALUES (2,5);
INSERT INTO t1_bit VALUES (1,2);
INSERT INTO t1_bit VALUES (2,5);
INSERT INTO t1_char VALUES (1,2);
INSERT INTO t1_char VALUES (2,5);
SELECT * FROM t1_int;
SELECT * FROM t1_bit;
SELECT * FROM t1_char;
--echo **** On Slave ****
87
sync_slave_with_master;
88
SELECT a,b,x FROM t1_int;
89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit;
SELECT a,b,x FROM t1_char;

--echo **** On Master ****
connection master;
UPDATE t1_int  SET b=2*b WHERE a=2;
UPDATE t1_char SET b=2*b WHERE a=2;
UPDATE t1_bit  SET b=2*b WHERE a=2;
SELECT * FROM t1_int;
SELECT * FROM t1_bit;
SELECT * FROM t1_char;
--echo **** On Slave ****
sync_slave_with_master;
SELECT a,b,x FROM t1_int;
SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit;
104 105
SELECT a,b,x FROM t1_char;

106
# Each of these inserts should generate an error and stop the slave
107 108 109 110 111 112

connection master;
INSERT INTO t9 VALUES (2);
sync_slave_with_master;
# Now slave is guaranteed to be running
connection master;
113
INSERT INTO t1_nodef VALUES (1,2);
114 115 116
connection slave;
wait_for_slave_to_stop;
--replace_result $MASTER_MYPORT MASTER_PORT
117 118
--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 #
--query_vertical SHOW SLAVE STATUS
119 120 121 122
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;

connection master;
123
INSERT INTO t9 VALUES (2);
124 125 126
sync_slave_with_master;
# Now slave is guaranteed to be running
connection master;
127
INSERT INTO t2 VALUES (2,4);
128 129 130
connection slave;
wait_for_slave_to_stop;
--replace_result $MASTER_MYPORT MASTER_PORT
131 132
--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 #
--query_vertical SHOW SLAVE STATUS
133 134 135 136 137 138 139 140 141 142 143 144
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;

connection master;
INSERT INTO t9 VALUES (4);
sync_slave_with_master;
# Now slave is guaranteed to be running
connection master;
INSERT INTO t4 VALUES (4);
connection slave;
wait_for_slave_to_stop;
--replace_result $MASTER_MYPORT MASTER_PORT
145 146
--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 #
--query_vertical SHOW SLAVE STATUS
147 148 149 150 151 152 153 154 155 156 157 158
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;

connection master;
INSERT INTO t9 VALUES (5);
sync_slave_with_master;
# Now slave is guaranteed to be running
connection master;
INSERT INTO t5 VALUES (5,10,25);
connection slave;
wait_for_slave_to_stop;
--replace_result $MASTER_MYPORT MASTER_PORT
159 160
--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 #
--query_vertical SHOW SLAVE STATUS
161 162 163 164 165 166 167 168 169 170 171 172
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;

connection master;
INSERT INTO t9 VALUES (6);
sync_slave_with_master;
# Now slave is guaranteed to be running
connection master;
INSERT INTO t6 VALUES (6,12,36);
connection slave;
wait_for_slave_to_stop;
--replace_result $MASTER_MYPORT MASTER_PORT
173 174
--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 #
--query_vertical SHOW SLAVE STATUS
175 176 177
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;

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
connection master;
INSERT INTO t9 VALUES (6);
sync_slave_with_master;
--replace_result $MASTER_MYPORT MASTER_PORT
--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 #
--query_vertical SHOW SLAVE STATUS

# Testing some tables extra field that can be null and cannot be null
# (but have default values)

connection master;
INSERT INTO t7 VALUES (1),(2),(3);
INSERT INTO t8 VALUES (1),(2),(3);
SELECT * FROM t7;
SELECT * FROM t8;
sync_slave_with_master;
SELECT * FROM t7;
SELECT * FROM t8;

# We will now try to update and then delete a row on the master where
# the extra field on the slave does not have a default value. This
# update should not generate an error even though there is no default
# for the extra column. 

--echo **** On Master ****
connection master;
TRUNCATE t1_nodef;
SET SQL_LOG_BIN=0;
INSERT INTO t1_nodef VALUES (1,2);
INSERT INTO t1_nodef VALUES (2,4);
SET SQL_LOG_BIN=1;
209 210
sync_slave_with_master;

211 212 213 214
--echo **** On Slave ****
connection slave;
INSERT INTO t1_nodef VALUES (1,2,3);
INSERT INTO t1_nodef VALUES (2,4,6);
215

216 217 218 219
--echo **** On Master ****
connection master;
UPDATE t1_nodef SET b=2*b WHERE a=1;
SELECT * FROM t1_nodef;
220

221 222 223
--echo **** On Slave ****
sync_slave_with_master;
SELECT * FROM t1_nodef;
224

225 226 227 228
--echo **** On Master ****
connection master;
DELETE FROM t1_nodef WHERE a=2;
SELECT * FROM t1_nodef;
229

230 231 232 233 234
--echo **** On Slave ****
sync_slave_with_master;
SELECT * FROM t1_nodef;

--echo **** Cleanup ****
235 236
connection master;
--disable_warnings
237
DROP TABLE IF EXISTS t1_int,t1_bit,t1_char,t1_nodef;
238
DROP TABLE IF EXISTS t2,t3,t4,t5,t6,t7,t8,t9;
239 240
--enable_warnings
sync_slave_with_master;