rpl_row_tabledefs_3innodb.result 10.3 KB
Newer Older
1 2 3 4 5 6
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
7
STOP SLAVE;
8
SET @my_sql_mode= @@global.sql_mode;
9 10
SET GLOBAL SQL_MODE='STRICT_ALL_TABLES';
START SLAVE;
11 12 13 14 15 16 17 18 19
CREATE TABLE t1_int (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
CREATE TABLE t1_bit (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
CREATE TABLE t1_char (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
CREATE TABLE t1_nodef (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
CREATE TABLE t4 (a INT) ENGINE='InnoDB';
CREATE TABLE t5 (a INT, b INT, c INT) ENGINE='InnoDB';
CREATE TABLE t6 (a INT, b INT, c INT) ENGINE='InnoDB';
20 21
CREATE TABLE t7 (a INT NOT NULL) ENGINE='InnoDB';
CREATE TABLE t8 (a INT NOT NULL) ENGINE='InnoDB';
22
CREATE TABLE t9 (a INT) ENGINE='InnoDB';
23
ALTER TABLE t1_int ADD x INT DEFAULT 42;
24 25 26 27
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';
28 29 30
ALTER TABLE t1_char ADD x CHAR(20) DEFAULT 'Just a test';
ALTER TABLE t1_nodef ADD x INT NOT NULL;
ALTER TABLE t2 DROP b;
31 32 33
ALTER TABLE t4 MODIFY a FLOAT;
ALTER TABLE t5 MODIFY b FLOAT;
ALTER TABLE t6 MODIFY c FLOAT;
34 35 36 37 38 39
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;
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;
40 41 42
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');
43 44 45 46 47 48 49
**** On Master ****
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);
50
SELECT * FROM t1_int ORDER BY a;
51 52 53
a	b
1	2
2	5
54
SELECT * FROM t1_bit ORDER BY a;
55 56 57
a	b
1	2
2	5
58
SELECT * FROM t1_char ORDER BY a;
59 60 61 62
a	b
1	2
2	5
**** On Slave ****
63
SELECT a,b,x FROM t1_int ORDER BY a;
64 65
a	b	x
1	2	42
66 67
2	5	4711
SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit ORDER BY a;
68 69
a	b	HEX(x)	HEX(y)	HEX(z)
1	2	3	15	2
70 71
2	5	5	1C	1
SELECT a,b,x FROM t1_char ORDER BY a;
72 73
a	b	x
1	2	Just a test
74
2	5	Foo is a bar
75 76 77 78
**** On 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;
79
SELECT * FROM t1_int ORDER BY a;
80 81 82
a	b
1	2
2	10
83
SELECT * FROM t1_bit ORDER BY a;
84 85 86
a	b
1	2
2	10
87
SELECT * FROM t1_char ORDER BY a;
88 89 90 91
a	b
1	2
2	10
**** On Slave ****
92
SELECT a,b,x FROM t1_int ORDER BY a;
93 94
a	b	x
1	2	42
95 96
2	10	4711
SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit ORDER BY a;
97 98
a	b	HEX(x)	HEX(y)	HEX(z)
1	2	3	15	2
99 100
2	10	5	1C	1
SELECT a,b,x FROM t1_char ORDER BY a;
101 102
a	b	x
1	2	Just a test
103
2	10	Foo is a bar
104 105
INSERT INTO t9 VALUES (2);
INSERT INTO t1_nodef VALUES (1,2);
106 107 108 109 110 111 112
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	MASTER_PORT
Connect_Retry	1
Master_Log_File	master-bin.000001
113
Read_Master_Log_Pos	#
114 115 116 117 118 119 120 121 122 123 124
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
125
Last_Errno	1364
126
Last_Error	Error in Write_rows event: error during transaction execution on table test.t1_nodef
127
Skip_Counter	0
128
Exec_Master_Log_Pos	#
129 130 131 132 133 134 135 136 137 138 139
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
140
Master_SSL_Verify_Server_Cert	No
141 142 143 144
Last_IO_Errno	0
Last_IO_Error	
Last_SQL_Errno	1364
Last_SQL_Error	Error in Write_rows event: error during transaction execution on table test.t1_nodef
145 146 147 148 149 150 151 152 153 154 155
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
INSERT INTO t9 VALUES (2);
INSERT INTO t2 VALUES (2,4);
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	MASTER_PORT
Connect_Retry	1
Master_Log_File	master-bin.000001
156
Read_Master_Log_Pos	#
157 158 159 160 161 162 163 164 165 166 167
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
168
Last_Errno	1532
169
Last_Error	Table definition on master and slave does not match: Table width mismatch - received 2 columns, test.t2 has 1 columns
170
Skip_Counter	0
171
Exec_Master_Log_Pos	#
172 173 174 175 176 177 178 179 180 181 182
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
183
Master_SSL_Verify_Server_Cert	No
184 185
Last_IO_Errno	0
Last_IO_Error	
186
Last_SQL_Errno	1532
187
Last_SQL_Error	Table definition on master and slave does not match: Table width mismatch - received 2 columns, test.t2 has 1 columns
188 189 190 191 192 193 194 195 196 197 198
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
INSERT INTO t9 VALUES (4);
INSERT INTO t4 VALUES (4);
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	MASTER_PORT
Connect_Retry	1
Master_Log_File	master-bin.000001
199
Read_Master_Log_Pos	#
200 201 202 203 204 205 206 207 208 209 210
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
211
Last_Errno	1532
212
Last_Error	Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4
213
Skip_Counter	0
214
Exec_Master_Log_Pos	#
215 216 217 218 219 220 221 222 223 224 225
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
226
Master_SSL_Verify_Server_Cert	No
227 228
Last_IO_Errno	0
Last_IO_Error	
229
Last_SQL_Errno	1532
230
Last_SQL_Error	Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4
231 232 233 234 235 236 237 238 239 240 241
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
INSERT INTO t9 VALUES (5);
INSERT INTO t5 VALUES (5,10,25);
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	MASTER_PORT
Connect_Retry	1
Master_Log_File	master-bin.000001
242
Read_Master_Log_Pos	#
243 244 245 246 247 248 249 250 251 252 253
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
254
Last_Errno	1532
255
Last_Error	Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4
256
Skip_Counter	0
257
Exec_Master_Log_Pos	#
258 259 260 261 262 263 264 265 266 267 268
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
269
Master_SSL_Verify_Server_Cert	No
270 271
Last_IO_Errno	0
Last_IO_Error	
272
Last_SQL_Errno	1532
273
Last_SQL_Error	Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4
274 275 276 277 278 279 280 281 282 283 284
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
INSERT INTO t9 VALUES (6);
INSERT INTO t6 VALUES (6,12,36);
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	MASTER_PORT
Connect_Retry	1
Master_Log_File	master-bin.000001
285
Read_Master_Log_Pos	#
286 287 288 289 290 291 292 293 294 295 296
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
297
Last_Errno	1532
298
Last_Error	Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4
299
Skip_Counter	0
300
Exec_Master_Log_Pos	#
301 302 303 304 305 306 307 308 309 310 311
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
312
Master_SSL_Verify_Server_Cert	No
313 314
Last_IO_Errno	0
Last_IO_Error	
315
Last_SQL_Errno	1532
316
Last_SQL_Error	Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4
317 318
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353
INSERT INTO t9 VALUES (6);
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	MASTER_PORT
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	Yes
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	0
Last_Error	
Skip_Counter	0
Exec_Master_Log_Pos	#
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
354
Master_SSL_Verify_Server_Cert	No
355 356 357 358
Last_IO_Errno	0
Last_IO_Error	
Last_SQL_Errno	0
Last_SQL_Error	
359 360
INSERT INTO t7 VALUES (1),(2),(3);
INSERT INTO t8 VALUES (1),(2),(3);
361
SELECT * FROM t7 ORDER BY a;
362 363 364 365
a
1
2
3
366
SELECT * FROM t8 ORDER BY a;
367 368 369 370
a
1
2
3
371
SELECT * FROM t7 ORDER BY a;
372 373 374 375
a	e1	e2	e3	e4	e5	e6	e7	e8
1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
3	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
376
SELECT * FROM t8 ORDER BY a;
377 378 379 380 381 382 383 384 385 386 387 388 389 390 391
a	e1	e2	e3	e4	e5	e6	e7	e8
1	0	0	0	0	0	0	0	0
2	0	0	0	0	0	0	0	0
3	0	0	0	0	0	0	0	0
**** On 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;
**** On Slave ****
INSERT INTO t1_nodef VALUES (1,2,3);
INSERT INTO t1_nodef VALUES (2,4,6);
**** On Master ****
UPDATE t1_nodef SET b=2*b WHERE a=1;
392
SELECT * FROM t1_nodef ORDER BY a;
393 394 395 396
a	b
1	4
2	4
**** On Slave ****
397
SELECT * FROM t1_nodef ORDER BY a;
398 399 400 401 402
a	b	x
1	4	3
2	4	6
**** On Master ****
DELETE FROM t1_nodef WHERE a=2;
403
SELECT * FROM t1_nodef ORDER BY a;
404 405 406
a	b
1	4
**** On Slave ****
407
SELECT * FROM t1_nodef ORDER BY a;
408 409 410
a	b	x
1	4	3
**** Cleanup ****
411
DROP TABLE IF EXISTS t1_int,t1_bit,t1_char,t1_nodef;
412
DROP TABLE IF EXISTS t2,t3,t4,t5,t6,t7,t8,t9;
413
SET @@global.sql_mode= @my_sql_mode;