rpl_extraCol_myisam.result 27.2 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
call mtr.add_suppression("Slave: Unknown table 't6' Error_code: 1051");
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
**** Diff Table Def Start ****
*** On Slave ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t1 (a INT, b INT PRIMARY KEY, c CHAR(20),
d FLOAT DEFAULT '2.00', 
e CHAR(4) DEFAULT 'TEST') 
ENGINE='MyISAM';
*** Create t1 on Master ***
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c CHAR(10)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
INSERT INTO t1 () VALUES(1,2,'TEXAS'),(2,1,'AUSTIN'),(3,4,'QA');
SELECT * FROM t1 ORDER BY a;
a	b	c
1	2	TEXAS
2	1	AUSTIN
3	4	QA
*** Select from slave ***
SELECT * FROM t1 ORDER BY a;
a	b	c	d	e
1	2	TEXAS	2	TEST
2	1	AUSTIN	2	TEST
3	4	QA	2	TEST
*** Drop t1  ***
DROP TABLE t1;
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
*** Create t2 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t2 (a INT, b INT PRIMARY KEY, c CHAR(5),
d FLOAT DEFAULT '2.00',
e CHAR(5) DEFAULT 'TEST2')
ENGINE='MyISAM';
*** Create t2 on Master ***
CREATE TABLE t2 (a INT PRIMARY KEY, b INT, c CHAR(10)
) ENGINE='MyISAM';
RESET MASTER;
*** Master Data Insert ***
INSERT INTO t2 () VALUES(1,2,'Kyle, TEX'),(2,1,'JOE AUSTIN'),(3,4,'QA TESTING');
SELECT * FROM t2 ORDER BY a;
a	b	c
1	2	Kyle, TEX
2	1	JOE AUSTIN
3	4	QA TESTING
*** Start Slave ***
START SLAVE;
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
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	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
76
Last_Errno	1535
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93
Last_Error	Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size.
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	#
Master_SSL_Verify_Server_Cert	No
Last_IO_Errno	#
Last_IO_Error	#
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
94
Last_SQL_Errno	1535
95 96 97 98 99 100 101 102 103
Last_SQL_Error	Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size.
STOP SLAVE;
RESET SLAVE;
SELECT * FROM t2 ORDER BY a;
a	b	c	d	e
RESET MASTER;
START SLAVE;
*** Drop t2  ***
DROP TABLE t2;
104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127
*** Create t3 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t3 (a INT, b INT PRIMARY KEY, c CHAR(20),
d FLOAT DEFAULT '2.00',
e CHAR(5) DEFAULT 'TEST2')
ENGINE='MyISAM';
*** Create t3 on Master ***
CREATE TABLE t3 (a BLOB, b INT PRIMARY KEY, c CHAR(20)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t3 () VALUES(@b1,2,'Kyle, TEX'),(@b1,1,'JOE AUSTIN'),(@b1,4,'QA TESTING');
********************************************
*** Expect slave to fail with Error 1522 ***
********************************************
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
128
Master_Port	#
129 130 131 132 133 134 135 136 137 138 139
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	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
140
Replicate_Ignore_Table	#
141 142
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
143
Last_Errno	1535
144
Last_Error	Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3
145 146 147 148 149 150 151 152 153 154 155 156 157
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	#
158
Master_SSL_Verify_Server_Cert	No
159 160
Last_IO_Errno	#
Last_IO_Error	#
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
161
Last_SQL_Errno	1535
162
Last_SQL_Error	Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3
163 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
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
*** Drop t3  ***
DROP TABLE t3;
*** Create t4 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t4 (a INT, b INT PRIMARY KEY, c CHAR(20),
d FLOAT DEFAULT '2.00',
e CHAR(5) DEFAULT 'TEST2')
ENGINE='MyISAM';
*** Create t4 on Master ***
CREATE TABLE t4 (a DECIMAL(8,2), b INT PRIMARY KEY, c CHAR(20)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
INSERT INTO t4 () VALUES(100.22,2,'Kyle, TEX'),(200.26,1,'JOE AUSTIN'),
(30000.22,4,'QA TESTING');
********************************************
*** Expect slave to fail with Error 1522 ***
********************************************
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
190
Master_Port	#
191 192 193 194 195 196 197 198 199 200 201
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	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
202
Replicate_Ignore_Table	#
203 204
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
205
Last_Errno	1535
206
Last_Error	Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3
207 208 209 210 211 212 213 214 215 216 217 218 219
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	#
220
Master_SSL_Verify_Server_Cert	No
221 222
Last_IO_Errno	#
Last_IO_Error	#
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
223
Last_SQL_Errno	1535
224
Last_SQL_Error	Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3
225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
*** Drop t4  ***
DROP TABLE t4;
*** Create t5 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t5 (a INT PRIMARY KEY, b CHAR(5),
c FLOAT, d INT, e DOUBLE,
f DECIMAL(8,2))ENGINE='MyISAM';
*** Create t5 on Master ***
CREATE TABLE t5 (a INT PRIMARY KEY, b VARCHAR(6),
c DECIMAL(8,2), d BIT, e BLOB,
f FLOAT) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
INSERT INTO t5 () VALUES(1,'Kyle',200.23,1,'b1b1',23.00098),
(2,'JOE',300.01,0,'b2b2',1.0000009);
********************************************
*** Expect slave to fail with Error 1522 ***
********************************************
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
252
Master_Port	#
253 254 255 256 257 258 259 260 261 262 263
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	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
264
Replicate_Ignore_Table	#
265 266
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
267
Last_Errno	1535
268
Last_Error	Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246
269 270 271 272 273 274 275 276 277 278 279 280 281
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	#
282
Master_SSL_Verify_Server_Cert	No
283 284
Last_IO_Errno	#
Last_IO_Error	#
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
285
Last_SQL_Errno	1535
286
Last_SQL_Error	Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246
287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
*** Drop t5  ***
DROP TABLE t5;
*** Create t6 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t6 (a INT PRIMARY KEY, b CHAR(5),
c FLOAT, d INT)ENGINE='MyISAM';
*** Create t6 on Master ***
CREATE TABLE t6 (a INT PRIMARY KEY, b VARCHAR(6),
c DECIMAL(8,2), d BIT 
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
INSERT INTO t6 () VALUES(1,'Kyle',200.23,1),
(2,'JOE',300.01,0);
********************************************
*** Expect slave to fail with Error 1522 ***
********************************************
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
313
Master_Port	#
314 315 316 317 318 319 320 321 322 323 324
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	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
325
Replicate_Ignore_Table	#
326 327
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
328
Last_Errno	1535
329
Last_Error	Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3
330 331 332 333 334 335 336 337 338 339 340 341 342
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	#
343
Master_SSL_Verify_Server_Cert	No
344 345
Last_IO_Errno	#
Last_IO_Error	#
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
346
Last_SQL_Errno	1535
347
Last_SQL_Error	Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3
348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3;
*** Drop t6  ***
DROP TABLE t6;
DROP TABLE t6;
START SLAVE;
**** Diff Table Def End ****
**** Extra Colums Start ****
*** Create t7 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t7 (a INT KEY, b BLOB, c CHAR(5),
d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
e CHAR(20) DEFAULT 'Extra Column Testing')
ENGINE='MyISAM';
*** Create t7 on Master ***
CREATE TABLE t7 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t7 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
SELECT * FROM t7 ORDER BY a;
a	b	c
1	b1b1	Kyle
2	b1b1	JOE
3	b1b1	QA
*** Select from slave ***
SELECT * FROM t7 ORDER BY a;
a	b	c	d	e
1	b1b1	Kyle	0000-00-00 00:00:00	Extra Column Testing
2	b1b1	JOE	0000-00-00 00:00:00	Extra Column Testing
3	b1b1	QA	0000-00-00 00:00:00	Extra Column Testing
*** Drop t7  ***
DROP TABLE t7;
*** Create t8 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t8 (a INT KEY, b BLOB, c CHAR(5),
d TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
e INT)ENGINE='MyISAM';
*** Create t8 on Master ***
CREATE TABLE t8 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t8 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
*** Drop t8  ***
DROP TABLE t8;
403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t9 (a INT KEY, b BLOB, c CHAR(5),
d TIMESTAMP,
e INT NOT NULL) ENGINE='MyISAM';
*** Create t9 on Master ***
CREATE TABLE t9 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t9 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
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	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	#
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	1364
438
Last_Error	Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 330
439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455
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	#
Master_SSL_Verify_Server_Cert	No
Last_IO_Errno	#
Last_IO_Error	#
Last_SQL_Errno	1364
456
Last_SQL_Error	Could not execute Write_rows event on table test.t9; Field 'e' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 330
457 458
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480
*** Create t10 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t10 (a INT KEY, b BLOB, f DOUBLE DEFAULT '233', 
c CHAR(5), e INT DEFAULT '1')ENGINE='MyISAM';
*** Create t10 on Master ***
CREATE TABLE t10 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t10 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
********************************************
*** Expect slave to fail with Error 1522 ***
********************************************
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
481
Master_Port	#
482 483 484 485 486 487 488 489 490 491 492
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	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
493
Replicate_Ignore_Table	#
494 495
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
496
Last_Errno	1535
497
Last_Error	Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5
498 499 500 501 502 503 504 505 506 507 508 509 510
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	#
511
Master_SSL_Verify_Server_Cert	No
512 513
Last_IO_Errno	#
Last_IO_Error	#
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
514
Last_SQL_Errno	1535
515
Last_SQL_Error	Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5
516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
*** Drop t10  ***
DROP TABLE t10;
*** Create t11 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t11 (a INT KEY, b BLOB, f TEXT,
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='MyISAM';
*** Create t11 on Master ***
CREATE TABLE t11 (a INT PRIMARY KEY, b BLOB, c VARCHAR(254)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t11 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
********************************************
*** Expect slave to fail with Error 1522 ***
********************************************
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
542
Master_Port	#
543 544 545 546 547 548 549 550 551 552 553
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	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
554
Replicate_Ignore_Table	#
555 556
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
557
Last_Errno	1535
558
Last_Error	Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252
559 560 561 562 563 564 565 566 567 568 569 570 571
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	#
572
Master_SSL_Verify_Server_Cert	No
573 574
Last_IO_Errno	#
Last_IO_Error	#
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
575
Last_SQL_Errno	1535
576
Last_SQL_Error	Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252
577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
*** Drop t11  ***
DROP TABLE t11;
*** Create t12 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t12 (a INT KEY, b BLOB, f TEXT,
c CHAR(5) DEFAULT 'test', e INT DEFAULT '1')ENGINE='MyISAM';
*** Create t12 on Master ***
CREATE TABLE t12 (a INT PRIMARY KEY, b BLOB, c BLOB
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t12 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
SELECT * FROM t12 ORDER BY a;
a	b	c
1	b1b1b1b1b1b1b1b1	Kyle
2	b1b1b1b1b1b1b1b1	JOE
3	b1b1b1b1b1b1b1b1	QA
*** Select on Slave ***
SELECT * FROM t12 ORDER BY a;
a	b	f	c	e
1	b1b1b1b1b1b1b1b1	Kyle	test	1
2	b1b1b1b1b1b1b1b1	JOE	test	1
3	b1b1b1b1b1b1b1b1	QA	test	1
*** Drop t12  ***
DROP TABLE t12;
**** Extra Colums End ****
*** BUG 22177 Start ***
*** Create t13 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t13 (a INT KEY, b BLOB, c CHAR(5),
d INT DEFAULT '1',
e TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
)ENGINE='MyISAM';
*** Create t13 on Master ***
CREATE TABLE t13 (a INT PRIMARY KEY, b BLOB, c CHAR(5)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t13 () VALUES(1,@b1,'Kyle'),(2,@b1,'JOE'),(3,@b1,'QA');
SELECT * FROM t13 ORDER BY a;
a	b	c
1	b1b1b1b1b1b1b1b1	Kyle
2	b1b1b1b1b1b1b1b1	JOE
3	b1b1b1b1b1b1b1b1	QA
*** Select on Slave ****
SELECT * FROM t13 ORDER BY a;
a	b	c	d	e
1	b1b1b1b1b1b1b1b1	Kyle	1	CURRENT_TIMESTAMP
2	b1b1b1b1b1b1b1b1	JOE	1	CURRENT_TIMESTAMP
3	b1b1b1b1b1b1b1b1	QA	1	CURRENT_TIMESTAMP
*** Drop t13  ***
DROP TABLE t13;
*** 22117 END *** 
*** Alter Master Table Testing Start ***
*** Create t14 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t14 (c1 INT KEY, c4 BLOB, c5 CHAR(5),
c6 INT DEFAULT '1',
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
)ENGINE='MyISAM';
*** Create t14 on Master ***
CREATE TABLE t14 (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
ALTER TABLE t14 ADD COLUMN c2 DECIMAL(8,2) AFTER c1;
ALTER TABLE t14 ADD COLUMN c3 TEXT AFTER c2;
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t14 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'),
(2,2.00,'This Test Should work',@b1,'JOE'),
(3,3.00,'If is does not, I will open a bug',@b1,'QA');
SELECT * FROM t14 ORDER BY c1;
c1	c2	c3	c4	c5
1	1.00	Replication Testing Extra Col	b1b1b1b1b1b1b1b1	Kyle
2	2.00	This Test Should work	b1b1b1b1b1b1b1b1	JOE
3	3.00	If is does not, I will open a bug	b1b1b1b1b1b1b1b1	QA
*** Select on Slave ****
SELECT * FROM t14 ORDER BY c1;
c1	c2	c3	c4	c5	c6	c7
1	1.00	Replication Testing Extra Col	b1b1b1b1b1b1b1b1	Kyle	1	CURRENT_TIMESTAMP
2	2.00	This Test Should work	b1b1b1b1b1b1b1b1	JOE	1	CURRENT_TIMESTAMP
3	3.00	If is does not, I will open a bug	b1b1b1b1b1b1b1b1	QA	1	CURRENT_TIMESTAMP
675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734
*** Create t14a on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t14a (c1 INT KEY, c4 BLOB, c5 CHAR(5),
c6 INT DEFAULT '1',
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
)ENGINE='MyISAM';
*** Create t14a on Master ***
CREATE TABLE t14a (c1 INT PRIMARY KEY, c4 BLOB, c5 CHAR(5)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t14a () VALUES(1,@b1,'Kyle'),
(2,@b1,'JOE'),
(3,@b1,'QA');
SELECT * FROM t14a ORDER BY c1;
c1	c4	c5
1	b1b1b1b1b1b1b1b1	Kyle
2	b1b1b1b1b1b1b1b1	JOE
3	b1b1b1b1b1b1b1b1	QA
*** Select on Slave ****
SELECT * FROM t14a ORDER BY c1;
c1	c4	c5	c6	c7
1	b1b1b1b1b1b1b1b1	Kyle	1	CURRENT_TIMESTAMP
2	b1b1b1b1b1b1b1b1	JOE	1	CURRENT_TIMESTAMP
3	b1b1b1b1b1b1b1b1	QA	1	CURRENT_TIMESTAMP
STOP SLAVE;
RESET SLAVE;
*** Master Drop c5 ***
ALTER TABLE t14a DROP COLUMN c5;
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t14a () VALUES(4,@b1),
(5,@b1),
(6,@b1);
SELECT * FROM t14a ORDER BY c1;
c1	c4
1	b1b1b1b1b1b1b1b1
2	b1b1b1b1b1b1b1b1
3	b1b1b1b1b1b1b1b1
4	b1b1b1b1b1b1b1b1
5	b1b1b1b1b1b1b1b1
6	b1b1b1b1b1b1b1b1
*** Select on Slave ****
SELECT * FROM t14a ORDER BY c1;
c1	c4	c5	c6	c7
1	b1b1b1b1b1b1b1b1	Kyle	1	CURRENT_TIMESTAMP
2	b1b1b1b1b1b1b1b1	JOE	1	CURRENT_TIMESTAMP
3	b1b1b1b1b1b1b1b1	QA	1	CURRENT_TIMESTAMP
4	b1b1b1b1b1b1b1b1	NULL	1	CURRENT_TIMESTAMP
5	b1b1b1b1b1b1b1b1	NULL	1	CURRENT_TIMESTAMP
6	b1b1b1b1b1b1b1b1	NULL	1	CURRENT_TIMESTAMP
735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791
*** connect to master and drop columns ***
ALTER TABLE t14 DROP COLUMN c2;
ALTER TABLE t14 DROP COLUMN c4;
*** Select from Master ***
SELECT * FROM t14 ORDER BY c1;
c1	c3	c5
1	Replication Testing Extra Col	Kyle
2	This Test Should work	JOE
3	If is does not, I will open a bug	QA
*** Select from Slave ***
SELECT * FROM t14 ORDER BY c1;
c1	c3	c5	c6	c7
1	Replication Testing Extra Col	Kyle	1	CURRENT_TIMESTAMP
2	This Test Should work	JOE	1	CURRENT_TIMESTAMP
3	If is does not, I will open a bug	QA	1	CURRENT_TIMESTAMP
*** Drop t14  ***
DROP TABLE t14;
*** Create t15 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t15 (c1 INT KEY, c2 DECIMAL(8,2), c3 TEXT,
c4 BLOB, c5 CHAR(5),
c6 INT DEFAULT '1',
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
)ENGINE='MyISAM';
*** Create t15 on Master ***
CREATE TABLE t15 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT,
c4 BLOB, c5 CHAR(5)) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t15 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'),
(2,2.00,'This Test Should work',@b1,'JOE'),
(3,3.00,'If is does not, I will open a bug',@b1,'QA');
SELECT * FROM t15 ORDER BY c1;
c1	c2	c3	c4	c5
1	1.00	Replication Testing Extra Col	b1b1b1b1b1b1b1b1	Kyle
2	2.00	This Test Should work	b1b1b1b1b1b1b1b1	JOE
3	3.00	If is does not, I will open a bug	b1b1b1b1b1b1b1b1	QA
*** Select on Slave ****
SELECT * FROM t15 ORDER BY c1;
c1	c2	c3	c4	c5	c6	c7
1	1.00	Replication Testing Extra Col	b1b1b1b1b1b1b1b1	Kyle	1	CURRENT_TIMESTAMP
2	2.00	This Test Should work	b1b1b1b1b1b1b1b1	JOE	1	CURRENT_TIMESTAMP
3	3.00	If is does not, I will open a bug	b1b1b1b1b1b1b1b1	QA	1	CURRENT_TIMESTAMP
*** Add column on master that is a Extra on Slave ***
ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5;
********************************************
*** Expect slave to fail with Error 1060 ***
********************************************
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
792
Master_Port	#
793 794 795 796 797 798 799 800 801 802 803
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	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
804
Replicate_Ignore_Table	#
805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	1060
Last_Error	Error 'Duplicate column name 'c6'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5'
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	#
822
Master_SSL_Verify_Server_Cert	No
823 824
Last_IO_Errno	#
Last_IO_Error	#
825 826
Last_SQL_Errno	1060
Last_SQL_Error	Error 'Duplicate column name 'c6'' on query. Default database: 'test'. Query: 'ALTER TABLE t15 ADD COLUMN c6 INT AFTER c5'
827
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
828 829 830 831 832 833 834 835 836 837 838 839 840 841 842
START SLAVE;
*** Try to insert in master ****
INSERT INTO t15 () VALUES(5,2.00,'Replication Testing',@b1,'Buda',2);
SELECT * FROM t15 ORDER BY c1;
c1	c2	c3	c4	c5	c6
1	1.00	Replication Testing Extra Col	b1b1b1b1b1b1b1b1	Kyle	NULL
2	2.00	This Test Should work	b1b1b1b1b1b1b1b1	JOE	NULL
3	3.00	If is does not, I will open a bug	b1b1b1b1b1b1b1b1	QA	NULL
5	2.00	Replication Testing	b1b1b1b1b1b1b1b1	Buda	2
*** Try to select from slave ****
SELECT * FROM t15 ORDER BY c1;
c1	c2	c3	c4	c5	c6	c7
1	1.00	Replication Testing Extra Col	b1b1b1b1b1b1b1b1	Kyle	1	CURRENT_TIMESTAMP
2	2.00	This Test Should work	b1b1b1b1b1b1b1b1	JOE	1	CURRENT_TIMESTAMP
3	3.00	If is does not, I will open a bug	b1b1b1b1b1b1b1b1	QA	1	CURRENT_TIMESTAMP
843
5	2.00	Replication Testing	b1b1b1b1b1b1b1b1	Buda	2	CURRENT_TIMESTAMP
844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888
*** DROP TABLE t15 ***
DROP TABLE t15;
*** Create t16 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t16 (c1 INT KEY, c2 DECIMAL(8,2), c3 TEXT,
c4 BLOB, c5 CHAR(5),
c6 INT DEFAULT '1',
c7 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
)ENGINE='MyISAM';
*** Create t16 on Master ***
CREATE TABLE t16 (c1 INT PRIMARY KEY, c2 DECIMAL(8,2), c3 TEXT,
c4 BLOB, c5 CHAR(5))ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
set @b1 = 'b1b1b1b1';
set @b1 = concat(@b1,@b1);
INSERT INTO t16 () VALUES(1,1.00,'Replication Testing Extra Col',@b1,'Kyle'),
(2,2.00,'This Test Should work',@b1,'JOE'),
(3,3.00,'If is does not, I will open a bug',@b1,'QA');
SELECT * FROM t16 ORDER BY c1;
c1	c2	c3	c4	c5
1	1.00	Replication Testing Extra Col	b1b1b1b1b1b1b1b1	Kyle
2	2.00	This Test Should work	b1b1b1b1b1b1b1b1	JOE
3	3.00	If is does not, I will open a bug	b1b1b1b1b1b1b1b1	QA
*** Select on Slave ****
SELECT * FROM t16 ORDER BY c1;
c1	c2	c3	c4	c5	c6	c7
1	1.00	Replication Testing Extra Col	b1b1b1b1b1b1b1b1	Kyle	1	CURRENT_TIMESTAMP
2	2.00	This Test Should work	b1b1b1b1b1b1b1b1	JOE	1	CURRENT_TIMESTAMP
3	3.00	If is does not, I will open a bug	b1b1b1b1b1b1b1b1	QA	1	CURRENT_TIMESTAMP
*** Add Partition on master ***
ALTER TABLE t16 PARTITION BY KEY(c1) PARTITIONS 4;
INSERT INTO t16 () VALUES(4,1.00,'Replication Rocks',@b1,'Omer');
SHOW CREATE TABLE t16;
Table	Create Table
t16	CREATE TABLE `t16` (
  `c1` int(11) NOT NULL,
  `c2` decimal(8,2) DEFAULT NULL,
  `c3` text,
  `c4` blob,
  `c5` char(5) DEFAULT NULL,
  PRIMARY KEY (`c1`)
889 890 891
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (c1)
PARTITIONS 4 */
892 893 894 895 896 897 898 899 900 901 902 903
*** Show table on Slave ****
SHOW CREATE TABLE t16;
Table	Create Table
t16	CREATE TABLE `t16` (
  `c1` int(11) NOT NULL,
  `c2` decimal(8,2) DEFAULT NULL,
  `c3` text,
  `c4` blob,
  `c5` char(5) DEFAULT NULL,
  `c6` int(11) DEFAULT '1',
  `c7` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`c1`)
904 905 906
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (c1)
PARTITIONS 4 */
907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931
*** DROP TABLE t16 ***
DROP TABLE t16;
*** Alter Master End ***
*** Create t17 on slave  ***
STOP SLAVE;
RESET SLAVE;
CREATE TABLE t17 (a SMALLINT, b INT PRIMARY KEY, c CHAR(5),
d FLOAT DEFAULT '2.00',
e CHAR(5) DEFAULT 'TEST2')
ENGINE='MyISAM';
*** Create t17 on Master ***
CREATE TABLE t17 (a BIGINT PRIMARY KEY, b INT, c CHAR(10)
) ENGINE='MyISAM';
RESET MASTER;
*** Start Slave ***
START SLAVE;
*** Master Data Insert ***
INSERT INTO t17 () VALUES(9223372036854775807,2,'Kyle, TEX');
********************************************
*** Expect slave to fail with Error 1522 ***
********************************************
SHOW SLAVE STATUS;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
932
Master_Port	#
933 934 935 936 937 938 939 940 941 942 943
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	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
944
Replicate_Ignore_Table	#
945 946
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
947
Last_Errno	1535
948
Last_Error	Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2
949 950 951 952 953 954 955 956 957 958 959 960 961
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	#
962
Master_SSL_Verify_Server_Cert	No
963 964
Last_IO_Errno	#
Last_IO_Error	#
gshchepa/uchum@gleb.loc's avatar
gshchepa/uchum@gleb.loc committed
965
Last_SQL_Errno	1535
966
Last_SQL_Error	Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2
967 968 969 970
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
START SLAVE;
** DROP table t17 ***
DROP TABLE t17;